Django查询集合按时间间隔 [英] Django queryset aggregate by time interval

问题描述

我正在编写一个Django视图，它将数据输出到客户端进行图形化（High Charts）。数据是每天记录一次给定参数的气候数据。

我的查询是这样的：

  format ='％Y-％m-％d'
 sd = datetime.datetime.strptime（startdate，format）
 ed = datetime.datetime.strptime（enddate，格式）
 
 data = Climate.objects.filter（recorded_on__range =（sd，ed））。order_by（'recorded_on'）
  

现在，随着范围的增加，数据集显然越来越大，而且在图表上不能很好地显示（除了显着减慢的情况之外）。

有没有办法将数据分组为时间段的平均值，特别是每个月的平均值或每年的平均值？

我意识到这可以在SQL中完成，如下所述： django聚合以通过日期范围分组来降低分辨率

但是我会喜欢知道Django本身是否有一个方便的方法。

或者直接修改db可能会更好，并使用一个脚本填充月和年的字段时间戳？

任何帮助不胜感激。

解决方案

使用django-qsstats-magic（https://github.com/kmike/django-qsstats-magic）？

它使事情非常容易图表，这里是一个来自他们的文档的时间表示例：

  from django.contrib.auth.models import User 
 import datetime，qsstats 
 
 qs = User.objects.all（）
 qss = qsstats.QuerySetStats（qs，'date_joined'）
 
 today = d atetime.date.today（）
 seven_days_ago = today  -  datetime.timedelta（days = 7）
 
 time_series = qss.time_series（seven_days_ago，today）
 print'最近7天：％s'％[t [1] for t in time_series] 
  

Hi I am writing a Django view which ouputs data for graphing on the client side (High Charts). The data is climate data with a given parameter recorded once per day.

My query is this:

format = '%Y-%m-%d' 
sd = datetime.datetime.strptime(startdate, format)
ed = datetime.datetime.strptime(enddate, format)

data = Climate.objects.filter(recorded_on__range = (sd, ed)).order_by('recorded_on')

Now, as the range is increased the dataset obviously gets larger and this does not present well on the graph (aside from slowing things down considerably).

Is there an way to group my data as averages in time periods - specifically average for each month or average for each year?

I realize this could be done in SQL as mentioned here: django aggregation to lower resolution using grouping by a date range

But I would like to know if there is a handy way in Django itself.

Or is it perhaps better to modify the db directly and use a script to populate month and year fields from the timestamp?

Any help much appreciated.

解决方案

Have you tried using django-qsstats-magic (https://github.com/kmike/django-qsstats-magic)?

It makes things very easy for charting, here is a timeseries example from their docs:

from django.contrib.auth.models import User
import datetime, qsstats

qs = User.objects.all()
qss = qsstats.QuerySetStats(qs, 'date_joined')

today = datetime.date.today()
seven_days_ago = today - datetime.timedelta(days=7)

time_series = qss.time_series(seven_days_ago, today)
print 'New users in the last 7 days: %s' % [t[1] for t in time_series]

这篇关于Django查询集合按时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！