Django：对按月/年分组的日期属性求和 [英] Django: Sum on an date attribute grouped by month/year

问题描述

我想将此查询从SQL放到Django：

I'd like to put this query from SQL to 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"))

推荐答案

汇总只能生成一个汇总值。

aggregate can only generate one aggregate value.

您可以获取小时的汇总

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

因此，要获取HourEntry所有月份的合计值，可以在db中遍历所有月份的查询集。但最好使用原始sql。

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.

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;")

这篇关于Django：对按月/年分组的日期属性求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！