Django按日期和SUM值分组 [英] Django group by dates and SUM values
问题描述
是否可以在Django中重现以下mysql查询,而无需使用select方法?
Is it possible to reproduce the following mysql query in Django without using select method ?
MariaDB [db1]> SELECT datetime, SUM(datas) FROM table AND datetime BETWEEN '2013-07-26 13:00:00' AND '2013-07-26 23:00:00' GROUP BY datetime;
要获得这种结果:
+---------------------+-----------+
| datetime | SUM(data) |
+---------------------+-----------+
| 2013-07-26 13:00:00 | 489 |
| 2013-07-26 14:00:00 | 2923 |
| 2013-07-26 15:00:00 | 984 |
| 2013-07-26 16:00:00 | 2795 |
| 2013-07-26 17:00:00 | 1308 |
| 2013-07-26 18:00:00 | 1365 |
| 2013-07-26 19:00:00 | 1331 |
| 2013-07-26 20:00:00 | 914 |
| 2013-07-26 21:00:00 | 919 |
| 2013-07-26 22:00:00 | 722 |
| 2013-07-26 23:00:00 | 731 |
+---------------------+-----------+
11 rows in set (1.45 sec)
我现在得到了这种查询:
I got for now this kind of query :
>>> value = table.objects.filter(datetime__range=('2013-07-26 13:00:00',
'2013-07-26 23:00:00')).values('datetime', 'data').annotate(Sum('data'))
>>> print value.query
SELECT `table`.`datetime`, `table`.`data` SUM(`table`.`imps`) AS `data__sum`
FROM `table`
WHERE `table`.`datetime` BETWEEN 2013-07-26 13:00:00
and 2013-07-26 23:00:00 GROUP BY `table`.`datetime`,
`table`.`data` ORDER BY NULL
为什么总和对日期时间和数据都起作用?
Why sum operate on both datetime and data?
我尝试在django doc上的所有地方进行尝试,但都没有找到与我的问题相符的内容. 有什么建议吗?
I tried to everywhere on django doc, here on stack but didn't find something coherent with my problem. Any suggestion ?
推荐答案
嗯,您正在使用Count
,应该使用Sum
,并且values()
将确定进入GROUP BY
的内容,因此您应该使用
Hmm you are using Count
, you should use Sum
, and values()
will determine what goes into GROUP BY
so you should use values('datetime')
only. Your queryset should be something like this:
from django.db.models import Sum
values = self.model.objects.filter(
datetime__range=(self.dates[0], self.dates[1])
).values('datetime').annotate(data_sum=Sum('data'))
尽管我不太确定filter()
的顺序,所以可能是这样:
although I'm not so sure about the order of the filter()
, so it could be this:
values = self.model.objects.values('datetime').annotate(data_sum=Sum('data')).filter(
datetime__range=(self.dates[0], self.dates[1])
)
我想那时候你想尝试一下.如果要查看那些查询集的原始查询,请使用Queryset.query
:
I guess you would wanna try both then. If you want to see the raw query of those queryset, use Queryset.query
:
print self.model.objects.filter(
datetime__range=(self.dates[0], self.dates[1])
).values('datetime').annotate(data_sum=Sum('data')).query.__str__()
因此,您可以确保获得正确的查询.
So you can make sure you get the right query.
希望有帮助.
这篇关于Django按日期和SUM值分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!