Django按日期和SUM值分组

问题描述：

是否可以在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的内容，因此您应该使用仅.您的queryset应该是这样的:

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.

希望有帮助.

相关推荐