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

发布于 2024-09-04 07:30:39 字数 352 浏览 4 评论 0原文

我想将此查询从 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;"

导致问题的部分是聚合时按月分组。我尝试了这个（这似乎合乎逻辑），但它不起作用：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心凉 2024-09-11 07:30:39

aggregate 只能生成一个聚合值。

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

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

因此，要获取 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;")

aggregate can only generate one aggregate value.

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.

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

回复收藏 0 原文