Django中的时间戳记TruncHour聚合 [英] Timestamp TruncHour aggregation in Django

问题描述

我有一个带有peoplecount和timestamp的数据，我想以小时为单位显示聚合.peoplecount对象的模型如下:

I have a data with peoplecount and timestamp which I want to show aggregated in an hour wise format.The model for peoplecount object is like below:

class PeopleCount(models.Model):
    """
    A webapp model classs to store People Count Details.
    """
    timestamp = models.DateTimeField(auto_now=True)
    people_count_entry = models.IntegerField(blank=True, null=True)
    people_count_exit = models.IntegerField(blank=True, null=True)
    store = models.ForeignKey(Store, blank=True, null=True)
    profile = models.ForeignKey(Profile)
    camera = models.ForeignKey(Camera)
    recorded_time = models.DateTimeField(null=True, blank=True)

    def str(self):
        return "People Count {}".format(self.timestamp)

    class Meta:
        verbose_name = "People Count"
        verbose_name_plural = "People Count"
        ordering = ['-timestamp']

我正在使用以下查询按小时获取数据:

and I am using below query to get data on hour basis:

queryset = PeopleCount.objects.filter(
                    **json.loads(
                        self.request.query_params['filter'])['object_params']
                ).annotate(
                    time_series=TruncHour('recorded_time')).values(
                    'time_series').annotate(
                    people_count_entry=Sum('people_count_entry')).values(
                    'time_series',
                    'people_count_entry').annotate(
                    people_count_exit=Sum('people_count_exit')).values(
                    'time_series', 'people_count_entry',
                    'people_count_exit')

上述查询的问题在于它实际上不是按小时汇总，而是为每个时间戳保留单独的值我必须在客户端进行操作.客户端上的方法可行，但是对于较大的查询集，它需要花费大量时间.希望我的问题陈述清楚.谢谢.

The problem with above query is that it actually doesn't aggregate on hour basis and instead keep individual values for each timestamp which I have to manipulate at client side. The approach on client side works but it takes a lot of time for larger queryset. Hope my problem statement is clear. Thanks.

推荐答案

阅读选择输出数据时，将使用查询集的 order_by()部分中提到的

字段(或在模型的默认排序中使用的字段)，即使不是其他情况也是如此.在 values()调用中指定.这些多余的字段用于将喜欢"的结果分组在一起...

Fields that are mentioned in the order_by() part of a queryset (or which are used in the default ordering on a model) are used when selecting the output data, even if they are not otherwise specified in the values() call. These extra fields are used to group "like" results together...

queryset = (
    PeopleCount.objects
    .filter(**json.loads(self.request.query_params['filter'])['object_params'])
    .order_by()    # THIS IS THE FIX - remove ordering by 'object_params'
    .annotate(time_series=TruncHour('recorded_time'))
    .values('time_series')  # output only this - to be grouped
    .annotate(
        people_count_entry=Sum('people_count_entry'),
        people_count_exit=Sum('people_count_exit'),
    )
    # no need to add or remove fields by .values()
    .order_by('time_series')  # order by truncated not by individual !!!
)
# print(str(queryset.query))  # SQL check is perfect for debugging

这篇关于Django中的时间戳记TruncHour聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！