Django按小时分组 [英] Django group by hour

问题描述

我在Django中具有以下模型.

I have the following model in Django.

class StoreVideoEventSummary(models.Model):
    Customer = models.ForeignKey(GlobalCustomerDirectory, null=True, db_column='CustomerID', blank=True, db_index=True)
    Store = models.ForeignKey(Store, null=True, db_column='StoreID', blank=True, related_name="VideoEventSummary")
    Timestamp = models.DateTimeField(null=True, blank=True, db_index=True)
    PeopleCount = models.IntegerField(null=True, blank=True)

我想找出每小时进入商店的人数.

I would like to find out the number of people entering the store each hour.

为实现这一点，我试图按小时将Timestamp上的行分组，并对PeopleCount列求和.

To achieve this, I'm trying to group the rows by the hour on Timestamp and sum the PeopleCount column.

store_count_events = StoreVideoEventSummary.objects.filter(Timestamp__range=(start_time, end_time),
                                                       Customer__id=customer_id,
                                                       Store__StoreName=store)\
        .order_by("Timestamp")\
        .extra({
            "hour": "date_part(\'hour\', \"Timestamp\")"
        }).annotate(TotalPeople=Sum("PeopleCount"))

这似乎没有按小时分组结果，它只是向查询集中的每一行添加了一个新列TotalPeople，该列的值与PeopleCount相同.

This doesn't seem to group the results by the hour, it merely adds a new column TotalPeople which has the same value as PeopleCount to each row in the query set.

推荐答案

只需将其分为两个步骤

objs = StoreVideoEventSummary.objects.filter(Timestamp__range=(start_time, end_time),
                                                   Customer__id=customer_id,
                                                   Store__StoreName=store)\
    .order_by("Timestamp")

def date_hour(timestamp):
   return datetime.datetime.fromtimestamp(timestamp).strftime("%x %H")

groups = itertools.groupby(objs, lambda x:date_hour(x.Timestamp))
#since groups is an iterator and not a list you have not yet traversed the list
for group,matches in groups: #now you are traversing the list ...
    print group,"TTL:",sum(1 for _ in matches)

这使您可以按几个不同的条件进行分组

this allows you to group by several distinct criteria

如果您只想小时而不管日期如何，只需更改date_hour

if you just want the hour regardless of date just change date_hour

def date_hour(timestamp):
   return datetime.datetime.fromtimestamp(timestamp).strftime("%H")

如果您想按星期几分组，请使用

if you wanted to group by day of the week you just use

def date_hour(timestamp):
   return datetime.datetime.fromtimestamp(timestamp).strftime("%w %H")

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