Django查询用户按月增长

Question

有没有一种方法可以使用Django ORM根据按年/月分组的date_joined获取User count()？

我能够在我的Django/Postgres项目中使用原始SQL获得这些数据，如下所示：

from django.db import connection
...    
    cursor = connection.cursor()
    cursor.execute('''
        SELECT
            to_char(date_joined, 'YYYY/MM') as month,
            cast(count(id) as int) as total
        FROM users_user
        GROUP BY month 
        ORDER BY month DESC
        ''')

这将为我返回一个列表：[('2015/12', 105), ('2016/01' , 78), ('2016/02', 95)...]

Answer 1

尝试：

from django.contrib.auth.models import User
from django.db.models import Count

User.objects.all() \
        .extra({'created': "to_char(date_joined, 'YYYY/MM')"}) \
        .values('created') \
        .annotate(created_count=Count('id')) \
        .order_by('-created')

Answer 2

在django 1.10+中，您可以使用以下内容：

from django.contrib.auth.models import User
from django.db.models import Count
from django.db.models.functions import TruncMonth


User.objects.all() \
    .annotate(month=TruncMonth("date_joined")) \
    .values("month") \
    .annotate(c=Count("id")) \
    .order_by("-month")

在幕后，ahmed给出的答案将转换为以下SQL：

SELECT ( To_char(date_joined, 'YYYY/MM') ) AS "created", 
       Count("users_user"."id")            AS "created_count" 
FROM   "users_user" 
GROUP  BY ( To_char(date_joined, 'YYYY/MM') ) 
ORDER  BY "created" DESC 

“newer”方法将运行以下SQL：

SELECT Date_trunc('month', "users_user"."date_joined" at time zone 
                           'Europe/London') AS 
       "month", 
       Count("users_user"."id") 
       AS "c" 
FROM   "users_user" 
GROUP  BY Date_trunc('month', "users_user"."date_joined" at time zone 
                              'Europe/London') 
ORDER  BY "month" DESC 

但这在很大程度上是无关紧要的-性能呢？

4,000 users:
    Method 1: 0.003886s
    Method 2: 0.005572s

50,000 users:
    Method 1: 0.064483s
    Method 2: 0.040544s

边际差异，但取决于您的用例/规模...