Django 1.11.20获取每个ISO周和年度的行数 [英] Django 1.11.20 get count of rows per ISO week and year

问题描述

我需要获取每个ISO周和每年的项目数.我尝试了以下

I need to get the count of items per ISO week and year. I tried the following

    from django.db.models.functions import ExtractWeek, ExtractYear
    from django.db.models.aggregates import Count

    count = myModel.objects.annotate(year=ExtractYear('created_at')) \
        .annotate(week=ExtractWeek('created_at')) \
        .values('year','week') \
        .annotate(count=Count('week'))

但是我的结果是

<QuerySet [{'year': 2019, 'week': 7, 'count': 1}, {'year': 2019, 'week': 7, 'count': 1}, {'year': 2019, 'week': 7, 'count': 1}, {'year': 2019, 'week': 7, 'count': 1}.......

如何获得更改，所以我的结果是:

How can I get change it so my result is:

预先感谢

推荐答案

问题并非不是缺少 order_by ，而是相反:默认的 order_by 可能存在于 myModel 通过指定默认排序的 ordering 字段进行，该字段将默认字段添加到破坏了您的分组的查询中.相关的Django文档为

The issue was not the missing order_by but likely the opposite: a default order_by presumably present in myModel by way of an ordering field specifying a default sort, which added another field to the query that sabotaged your grouping. The relevant Django doc is here.

您可以通过在查询中附加 .order_by()来摆脱它，也可以像在回答中一样指定其他顺序.

You can get rid of it by appending .order_by() to the query or you can specify a different ordering like you did in your answer.

这是一次又一次的吸引人(包括我自己)的障碍，所以我不希望在模型中不使用默认排序.

This is a snag that gets people again and again (myself included) so I prefer not to use default sorting in models.

PS:当事情没有按计划进行时，通常可以通过检查上次执行的查询来检查生成的SQL:

PS: When things aren't running to plan, it often helps to inspect the generated SQL by either checking the last executed query:

django.db.connection.queries[-1]

...或通过询问刚刚定义的查询的SQL:

... or by asking for the SQL of a query you've just defined:

str(my_query.query)

如果您喜欢自己所看到的内容，但又不喜欢SQL格式化的方式，请查看

And if you like what you see but don't like the way the SQL is (not) formatted, look into sqlparse.

这篇关于Django 1.11.20获取每个ISO周和年度的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！