Django按月和年查询组(Django query group by month and year)

 我有一个像这样的Django模型： 
 
class EVENT(models.Model):
    user = models.CharField(max_length=255)
    pay_time = models.DateTimeField(default=timezone.now)
 
 我需要计算每个月对某一组用户的平均记录数。 我在列表中有用户名，我希望按月和年分组，以便在平均计算中使用。 我的查询是这样的： 
 
from django.db import models
from django.db.models import Func, F, Count
class Month(Func):
    function = 'EXTRACT'
    template = '%(function)s(MONTH from %(expressions)s)'
    output_field = models.IntegerField()

class Year(Func):
    function = 'EXTRACT'
    template = '%(function)s(YEAR from %(expressions)s)'
    output_field = models.IntegerField()

#ru is the list of usernames I want to get the average per month for
trx = EVENT.objects.filter(user__in=ru).annotate(m=Month('pay_time'), y=Year('pay_time')).values('m', 'y').annotate(c=Count('id'))
 
 当我运行此查询时，我得到了我认为我想要的结果。 这是我得到的样本： 
 
<QuerySet [{'y': 2016, 'c': 61098, 'm': 4}, {'y': 2016, 'c': 104632, 'm': 5}]>
 
 我想确保这样运行此查询： 
 
trx2 = EVENT.objects.filter(user__in=ru, pay_time__year=2016, pay_time__month=4) 
 
 我得到了60990条记录的结果。 
 这意味着我从第一个查询得到的结果是不正确的。 我知道我可以在循环中使用第二个查询来获得我想要的但是与使用GROUP BY运行一个查询相比，这将非常慢。 
 
 谢谢， 
 
 编辑： 
 这是print trx.query的输出： 
 
SELECT 
    EXTRACT(YEAR from `events_event`.`pay_time`) AS `y`,  
    EXTRACT(MONTH from `events_event`.`pay_time`) AS `m`,
    COUNT(`events_event`.`id`) AS `c`
FROM `events_event`
WHERE  
 `events_event`.`user` IN ('user1', 'user2')
GROUP BY 
  EXTRACT(YEAR from `events_event`.`pay_time`),
  EXTRACT(MONTH from `events_event`.`pay_time`)  
ORDER BY NULL  
 
 这是print trx2.query的输出： 
 
SELECT
    `events_event`.`id`,
    `events_event`.`user`,
    `events_event`.`pay_time`,
FROM `events_event`
WHERE
    (`events_event`.`user` IN ('user1', 'user2')
 AND `events_event`.`pay_time` BETWEEN 2015-12-31 22:00:00 AND 2016-12-31 21:59:59.999999 
 AND EXTRACT(MONTH FROM CONVERT_TZ(`events_event`.`pay_time`, 'UTC', Africa/Cairo)) = 4)  

I have a Django model like this: 
 
class EVENT(models.Model):
    user = models.CharField(max_length=255)
    pay_time = models.DateTimeField(default=timezone.now)
 
I need to calculate the average number of records per month I have for a certain group of users. I have the user names in a list and I want to group by month and year to use in the average calculation. My query is like this: 
 
from django.db import models
from django.db.models import Func, F, Count
class Month(Func):
    function = 'EXTRACT'
    template = '%(function)s(MONTH from %(expressions)s)'
    output_field = models.IntegerField()

class Year(Func):
    function = 'EXTRACT'
    template = '%(function)s(YEAR from %(expressions)s)'
    output_field = models.IntegerField()

#ru is the list of usernames I want to get the average per month for
trx = EVENT.objects.filter(user__in=ru).annotate(m=Month('pay_time'), y=Year('pay_time')).values('m', 'y').annotate(c=Count('id'))
 
When I run this query I get the results I thought I wanted. That's a sample of what I got: 
 
<QuerySet [{'y': 2016, 'c': 61098, 'm': 4}, {'y': 2016, 'c': 104632, 'm': 5}]>
 
I wanted to make sure so I ran this query: 
 
trx2 = EVENT.objects.filter(user__in=ru, pay_time__year=2016, pay_time__month=4) 
 
And I got the result 60990 records.
 This means that the results I got from the first query are incorrect. I know I can use the second query in a loop to get what I want but this will be very slow compared to running one query using GROUP BY. 
 
Thanks,
 
EDIT:
 Here is the output of print trx.query: 
 
SELECT 
    EXTRACT(YEAR from `events_event`.`pay_time`) AS `y`,  
    EXTRACT(MONTH from `events_event`.`pay_time`) AS `m`,
    COUNT(`events_event`.`id`) AS `c`
FROM `events_event`
WHERE  
 `events_event`.`user` IN ('user1', 'user2')
GROUP BY 
  EXTRACT(YEAR from `events_event`.`pay_time`),
  EXTRACT(MONTH from `events_event`.`pay_time`)  
ORDER BY NULL  
 
And this is the output of print trx2.query: 
 
SELECT
    `events_event`.`id`,
    `events_event`.`user`,
    `events_event`.`pay_time`,
FROM `events_event`
WHERE
    (`events_event`.`user` IN ('user1', 'user2')
 AND `events_event`.`pay_time` BETWEEN 2015-12-31 22:00:00 AND 2016-12-31 21:59:59.999999 
 AND EXTRACT(MONTH FROM CONVERT_TZ(`events_event`.`pay_time`, 'UTC', Africa/Cairo)) = 4)  

原文：https://stackoverflow.com/questions/46140771