django聚合以较低的分辨率使用日期范围分组 [英] django aggregation to lower resolution using grouping by a date range

问题描述

可怕的标题，但让我解释一下：我有这个django模型包含一个时间戳（日期）和属性为log - f.e。消费一些资源的用户数量（值）。

horrible title, but let me explain: i've got this django model containing a timestamp (date) and the attribute to log - f.e. the number of users consuming some ressource - (value).

class Viewers(models.Model):
    date = models.DateTimeField()
    value = models.IntegerField()

每10秒表包含用户数。如下所示：

for each 10seconds the table contains the number of users. something like this:

| date | value |
|------|-------|
|  t1  |   15  |
|  t2  |   18  |
|  t3  |   27  |
|  t4  |   25  |
|  ..  |   ..  |
|  t30 |   38  |
|  t31 |   36  |
|  ..  |   ..  |

现在我想从这个数据生成不同的统计信息，每个都有另一个分辨率。 F.E.对于最后一天的图表，我不需要10秒的分辨率，所以我想要5分钟的步骤（通过平均从t1到t29，t30到t59的行的值（也可能是日期）构建。 ..），所以我会得到：

now i want to generate different statistics from this data, each with another resolution. f.e. for a chart of the last day i don't need the 10second resolution, so i want 5 minute steps (that are build by averaging the values (and maybe also the date) of the rows from t1 to t29, t30 to t59, ...), so that i'll get:

| date | value |
|------|-------|
|  t15 |   21  |
|  t45 |   32  |
|  ..  |   ..  |

保留变量的属性为start&结束时间戳和分辨率（如5分钟）。有没有办法使用django orm / queryset api，如果没有，怎么用custom sql来达到这个目的？

the attributes to keep variable are start & end timestamp and the resolution (like 5 minutes). is there a way using the django orm/queryset api and if not, how to reach this with custom sql?

推荐答案

from django.db.models import Avg

Viewers.objects.filter(date__range=(start_time, end_time)).aggregate(average=Avg('value'))

这将使您获得所有值的平均值 start_time 和 end_time ，作为字典返回，格式为 {'average' ：<平均值> 。

That will get you the average of all the values between start_time and end_time, returned as a dictionary in the form of { 'average': <the average> }.

start_time 和 end_time 需要是Python datetime对象。所以如果你有一个时间戳或某事，你需要先转换它。您也可以使用 datetime.timedelta 根据start_time计算 end_time 。对于五分钟的分辨率，如下所示：

start_time and end_time need to be Python datetime objects. So if you have a timestamp, or something, you'll need to convert it first. You can also use datetime.timedelta to calculate the end_time based on the start_time. For a five minute resolution, something like this:

from datetime import timedelta

end_time = start_time + timedelta(minutes=5)

这篇关于django聚合以较低的分辨率使用日期范围分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！