基于“小时"的每小时总计数在Django的datetime字段中 [英] Aggregate hourly count based on "HOUR" in datetime field in Django

问题描述

我正在将Django 2.2和MySQL一起使用.我有以下查询:这是供参考的模型:

I am using Django 2.2 along with MySQL. I have the following query: Here's the model for the reference :

class TrackCount(models.Model):
    people_count_obj_id = models.CharField(primary_key=True, default=uuid.uuid4,
                                           editable=False, max_length=255)
    recorded_at = models.DateTimeField()
    camera = models.ForeignKey(Camera, on_delete=models.PROTECT)
    store = models.ForeignKey(Store, on_delete=models.PROTECT)
    people_count_in = models.IntegerField()
    people_count_out = models.IntegerField()
    created_at = models.DateTimeField(auto_now=True)
    is_unique = models.BooleanField(default=False)

    def __str__(self):
        return "{}.{}.{}".format(self.people_count_in,
                                 self.people_count_out, str(self.store))

    class Meta:
        verbose_name = 'Track Count'
        verbose_name_plural = 'Track Counts'

=========================================================我的查询是这样的.

======================================================== My Query goes like this.

track_events = TrackCount.objects.filter(
            recorded_at__date=start_date.date(),
            recorded_at__hour__range=(8, 23),
            store__store_owner__id=self.kwargs['account_id']).order_by(
            'recorded_at'
        ).extra(
            select={
                'hour': 'hour(recorded_at)'
            }
        ).values(
            'hour'
        ).annotate(
            TotalPeople=Sum('people_count_in')
        )

但是当我检查记录时，得到的输出如下:

But when I check my records I get the output as following :

[
    {
        "hour": 17,
        "TotalPeople": 22
    },
    {
        "hour": 17,
        "TotalPeople": 19
    },
    {
        "hour": 17,
        "TotalPeople": 30
    },
    {
        "hour": 18,
        "TotalPeople": 33
    },
    {
        "hour": 18,
        "TotalPeople": 31
    },
    {
        "hour": 18,
        "TotalPeople": 32
    },
    {
        "hour": 19,
        "TotalPeople": 32
    },
    {
        "hour": 19,
        "TotalPeople": 21
}] 

但是我想要这样的输出:

But I would like the output something like this :

[{"hour":18，"TotalPeople":96} ....] 应该对同一小时的记录进行汇总.

[{ "hour": 18, "TotalPeople": 96}....] that is the record for same hour should be SUM-ed up together.

希望我的观点很清楚.TIA

Hope my point is clear. TIA

推荐答案

好的，实际上，我花了几个小时才能对此进行解码.因为我想要一个相同的 hour 值的SUM-meded答案，所以最终查询是这样的.

Okay, so practically it took me couple of hour to decode this. Since I wanted a SUM-med up answer for same value of hour the final query was something like this.

track_events = TrackCount.objects.filter(
            recorded_at__date=start_date.date(),
            recorded_at__hour__range=(8, 23),
            store__store_owner__id=self.kwargs['account_id']).order_by(
            'recorded_at'
        ).extra(
            select={
                'hour': 'hour(recorded_at)'
            }
        ).values(
            'hour'
        ).annotate(
            TotalPeople=Sum('people_count_in')
        ).order_by('hour')

这篇关于基于“小时"的每小时总计数在Django的datetime字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！