Django：按月/年分组的日期属性的总和

I'd like to put this query from SQL to Django:

我想把这个查询从SQL转到Django:

"select date_format(date, '%Y-%m') as month, sum(quantity) as hours from hourentries group by date_format(date, '%Y-%m') order by date;"

The part that causes problem is to group by month when aggregating. I tried this (which seemed logical), but it didn't work :

导致问题的部分是在聚合时按月分组。我试过这个(看似合乎逻辑),但它不起作用:

HourEntries.objects.order_by("date").values("date__month").aggregate(Sum("quantity"))

2 个解决方案

#1

aggregate can only generate one aggregate value.

aggregate只能生成一个聚合值。

You can get the aggregate sum of Hours of the current month by the following query.

您可以通过以下查询获得当月的总小时数。

from datetime import datetime
this_month = datetime.now().month
HourEntries.objects.filter(date__month=this_month).aggregate(Sum("quantity"))

So, to obtain the aggregate values of HourEntry's all the months, you can loop over the queryset for all the months in the db. But it is better to use the raw sql.

因此,要获取HourEntry所有月份的聚合值,您可以循环查看数据库中所有月份的查询集。但最好使用原始sql。

HourEntries.objects.raw("select date_format(date, '%Y-%m') as month, sum(quantity) as hours from hourentries group by date_format(date, '%Y-%m') order by date;")

#2

I guess you cannot aggregate on "quantity" after using values("date__month"), as this leaves only "date" and "month" in the QuerySet.

我猜你在使用值(“date__month”)之后无法聚合“数量”,因为这在QuerySet中只留下“date”和“month”。

#1