按周分组,并填补'缺失'周
在我的Django模型中,我有一个非常简单的模型,它表示一个事件(例如发生服务器警报)的单个事件:
class EventOccurrence:
event = models.ForeignKey(Event)
time = models.DateTimeField()
我的最终目标是制作一张表格或图表,显示过去n周内事件发生的次数。
所以我的问题有两个部分:
group_by
time
字段的一周? group_by
的结果,为任何缺失的星期添加一个零值? 例如,对于第二部分,我想要像这样转换结果:
| week | count | | week | count |
| 2 | 3 | | 2 | 3 |
| 3 | 5 | —— becomes —> | 3 | 5 |
| 5 | 1 | | 4 | 0 |
| 5 | 1 |
在Django中做这件事的最好方法是什么? 一般的Python解决方案也可以。
Django的DateField
和datetime
不支持week属性。 要在一个查询中获取所有内容,您需要执行以下操作:
from django.db import connection
cursor = connection.cursor()
cursor.execute(" SELECT WEEK(`time`) AS 'week', COUNT(*) AS 'count' FROM %s GROUP BY WEEK(`time`) ORDER BY WEEK(`time`)" % EventOccurrence._meta.db_table, [])
data = []
results = cursor.fetchall()
for i, row in enumerate(results[:-1]):
data.append(row)
week = row[0] + 1
next_week = results[i+1][0]
while week < next_week:
data.append( (week, 0) )
week += 1
data.append( results[-1] )
print data
在挖掘django查询api文档后,我没有找到通过django ORM系统进行查询的方法。 如果您的数据库品牌是MySQL,则光标是一种解决方法:
from django.db import connection, transaction
cursor = connection.cursor()
cursor.execute("""
select
week(time) as `week`,
count(*) as `count`
from EventOccurrence
group by week(time)
order by 1;""")
myData = dictfetchall(cursor)
在我看来,这是最好的性能解决方案。 但请注意,这不会弥补失踪周。
EDITED独立数据库品牌解决方案通过python(性能较低)
如果你正在寻找数据库品牌独立代码,那么你应该日复一日地通过python来聚合它。 如果这是您的案例,代码可能如下所示:
#get all weeks:
import datetime
weeks = set()
d7 = datetime.timedelta( days = 7)
iterDay = datetime.date(2012,1,1)
while iterDay <= datetime.now():
weeks.add( iterDay.isocalendar()[1] )
iterDay += d7
#get all events
allEvents = EventOccurrence.objects.value_list( 'time', flat=True )
#aggregate event by week
result = dict()
for w in weeks:
result.setdefault( w ,0)
for e in allEvents:
result[ e.isocalendar()[1] ] += 1
(免责声明:未经测试)
由于我必须通过加入查询多个表,我使用db视图来解决这些要求。
CREATE VIEW my_view
AS
SELECT
*, // <-- other fields goes here
YEAR(time_field) as year,
WEEK(time_field) as week
FROM my_table;
和模型为:
from django.db import models
class MyView(models.Model):
# other fields goes here
year = models.IntegerField()
week = models.IntegerField()
class Meta:
managed = False
db_table = 'my_view'
def query():
rows = MyView.objects.filter(week__range=[2, 5])
# to handle the rows
从这个数据库视图中获取行后,使用@danihp的方式填充“洞”周/月的0。
注意:这只针对MySQL后端进行测试,我不确定是否适用于MS SQL Server或其他。
链接地址: http://www.djcxy.com/p/57785.html