如何编写Django QuerySet，可以正确计算DateTimeField的平均值吗？ [英] How to write a Django QuerySet the properly computes average of DateTimeField with grouping?

问题描述

这是我的Django模型：

Here is my Django model:

class MyModel(models.Model):
    a = IntegerField()
    b = DateTimeField()

这是我在这个模型上执行查询计数的QuerySet，每个价值 a 的最小，最大和平均值 b ：

Here is the QuerySet I execute on this model to find the count, min, max and average bs for each value of a:

>>> from django.db.models import Count, Max, Min, Avg
>>> MyModel.objects.extra(
...    select={'avg': 'AVG(UNIX_TIMESTAMP(b))'}
... ).values(a').annotate(
...     count=Count('b'), 
...     min=Min('b'), 
...     max=Max('b'),
... )

以下是上述QuerySet的结果：

Here is the result of the QuerySet above:

[
  {'a': 1, 'count': 5, 'min': datetime.datetime(2015, 2, 26, 1, 8, 21, tzinfo=<UTC>), 'max': datetime.datetime(2015, 2, 26, 1, 8, 22, tzinfo=<UTC>)}, 
  {'a': 2, 'count': 2, 'min': datetime.datetime(2015, 2, 26, 1, 8, 21, tzinfo=<UTC>), 'max': datetime.datetime(2015, 2, 26, 1, 8, 22, tzinfo=<UTC>)}
]

看到，QuerySet的结果不包括我计算的平均字段。我怎么能得到那个？我尝试了许多不同的排列。但是，如果我可以在那里获得 avg 字段，那么似乎可以通过 a 来解除分组。 p>

As you can see, the results of the QuerySet do not include the average field that I calculated. How can I get that in there? I have tried many different permutations. But if I can get the avg field in there, then it seems to screw up the grouping by a.

推荐答案

而不是使用Django ORM，您可以使用 raw sql查询。

Instead of using the Django ORM, you could use a raw sql query.

from django.db import connection
query = """
SELECT `a`,
       COUNT(b) AS `count`,
       MAX(b) AS `max`,
       AVG(UNIX_TIMESTAMP(b)) AS `avg`,
       MIN(b) AS `min`
FROM `<appname>_<modelname>`
GROUP BY `a`
"""
cursor = connection.cursor()
cursor.execute(query)
result = cursor.fetchall()

这将给你一些像：

(
    (a value1, count, max, avg as unix timestamp, min), ...
    (a value2, count, max, avg as unix timestamp, min), ...
    (a value3, count, max, avg as unix timestamp, min), ...
)

否则，最接近我可以使用Django的ORM将放弃额外的子句中的UNIX_TIMESTAMP转换：

Otherwise, the closest thing I could get using Django's ORM would be to abandon the UNIX_TIMESTAMP conversion in the extra clause:

from django.db.models import Count, Min, Max, Avg
MyModel.objects.all().values('a').annotate(
    count=Count('b'), 
    max=Max('b'), 
    avg=Avg('b'), 
    min=Min('b')
)

不幸的是，这将给你平均值为浮点数。

Unfortunately, this will give you the average as a float.

[
    {
        'count': 15366, 
         'a': 0, 
         'avg': 19898862327498.82, 
         'min': datetime.datetime(1900, 1, 1, 0, 0), 
         'max': datetime.datetime(2012, 7, 3, 0, 0)
    }, {
         'count': 1726, 
         'a': 1, 
         'avg': 19785827400927.0, 
         'min': datetime.datetime(1920, 8, 25, 0, 0), 
         'max': datetime.datetime(1994, 12, 29, 0, 0)
    }, 
    ...

]

你可以尝试使用某些东西像

You can try using something like

import datetime
datetime.datetime.strptime(str(int(ts)), '%Y%m%d%H%M%S%f')

将其转换回datetime对象，虽然这将是一个近似值，所以我建议使用raw sql。

to convert it back to a datetime object, though this will be an approximation, so I recommend using raw sql instead.

这篇关于如何编写Django QuerySet，可以正确计算DateTimeField的平均值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！