Django -用与事件的开始DateTime重叠的参与者和对每个事件进行注释

Question

我有一个带有start DateTime和end DateTime的事件模型，以及参与者的数量。

对于每个事件对象，我希望得到与start DateTime重叠的任何事件中所有参与者的注释和。这样我就可以确保在任何时候都不会有太多的参与者。

class Event(models.Model):
    start = models.DateTime()
    end = models.DateTime()
    participants = models.IntegerField()

我一直在读关于窗口函数的文章，也许这在这里是可行的，但是我不能正确地理解它。

我尝试过这样做，但它不起作用，因为它希望用相同的start DateTime将事件组合在一起，而不是将开始和结束的DateTimes与原始的事件启动DateTime重叠起来。

starts = Event.objects.annotate(
    participants_sum=Window(
    expression=Sum('participants'),
    partition_by=[F('start'),],
    order_by=ExtractDay('start').asc(),
    ),
).values('participants', 'participants_sum', 'start')

如有任何建议，将不胜感激！

非常感谢@endre--在他/她的帮助下，我能够解决更大的问题。

最终结果--我希望在Events表中得到每个开始和结束转换的值，这样我就可以确定有太多参与者的时间段。但我担心解释太复杂了。

这是我最后得到的

from django.contrib.gis.db import models
from django.db.models import F, Window, Sum
from django.utils import timezone

overlap_filter_start = Q(start__lte=OuterRef('start'), end__gte=OuterRef('start'))
overlap_filter_end = Q(start__lte=OuterRef('end'), end__gte=OuterRef('end'))

subquery_start = Subquery(Event.objects
    .filter(overlap_filter_start)
    .annotate(sum_participants=Window(expression=Sum('participants'),))
    .values('sum_participants')[:1],
    output_field=models.IntegerField()
)

subquery_end = Subquery(Event.objects
    .filter(overlap_filter_end)
    .annotate(sum_participants=Window(expression=Sum('participants'),))
    .values('sum_participants')[:1],
    output_field=models.IntegerField()
)

# Will eventually filter the dates I'm checking over specific date ranges rather than the entire Event table
# but for simplicity, filtering from yesterday to tomorrow
before = timezone.now().date() - timezone.timedelta(days=1)
after = timezone.now().date() + timezone.timedelta(days=1)

events_start = Event.objects.filter(start__date__lte=after, start__date__gte=before).annotate(simultaneous_participants=subquery_start)
events_end = Event.objects.filter(end__date__lte=after, end__date__gte=before).annotate(simultaneous_participants=subquery_end)

# Here I combine the queries for *start* transition moments and *end* transition moments, and rename the DateTime I'm looking at to *moment*, and make sure to only return distinct moments (since two equal moments will have the same number of participants)

events = events_start.annotate(moment=F('start')).values('moment', 'simultaneous_participants').union(
    events_end.annotate(moment=F('end')).values('moment', 'simultaneous_participants')).order_by('moment').distinct()

for event in events:
    print(event)

print(events.count())

现在，我可以使用Python中相对较小的结果查询集和进程来确定参与者数量过高的地方，以及当参与者数量下降到可接受的水平时。

也许有一种更有效的方法来解决这个问题，但我对此很满意。比用Python做所有繁重的工作要好得多。

结果的输出如下所示：

{'simultaneous_participants': 45, 'moment': datetime.datetime(2019, 3, 23, 7, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 45, 'moment': datetime.datetime(2019, 3, 23, 11, 30, tzinfo=<UTC>)}
{'simultaneous_participants': 40, 'moment': datetime.datetime(2019, 3, 23, 14, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 40, 'moment': datetime.datetime(2019, 3, 23, 15, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 35, 'moment': datetime.datetime(2019, 3, 23, 16, 30, tzinfo=<UTC>)}
{'simultaneous_participants': 85, 'moment': datetime.datetime(2019, 3, 24, 19, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 125, 'moment': datetime.datetime(2019, 3, 25, 12, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 90, 'moment': datetime.datetime(2019, 3, 25, 12, 30, tzinfo=<UTC>)}
{'simultaneous_participants': 135, 'moment': datetime.datetime(2019, 3, 25, 13, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 110, 'moment': datetime.datetime(2019, 3, 25, 18, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 160, 'moment': datetime.datetime(2019, 3, 25, 19, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 160, 'moment': datetime.datetime(2019, 3, 25, 20, 30, tzinfo=<UTC>)}
{'simultaneous_participants': 115, 'moment': datetime.datetime(2019, 3, 25, 22, 0, tzinfo=<UTC>)}
{'simultaneous_participants': 80, 'moment': datetime.datetime(2019, 3, 25, 23, 30, tzinfo=<UTC>)}
14

Answer 1

若要使用基于单个事件的某些条件筛选的聚合对Events进行注释，您需要每个事件单独的子查询。

此筛选器应有助于查找与特定时间范围重叠的所有事件：

overlap_filter = Q(start__lte=OuterRef('end'), end__gte=OuterRef('start'))

这将使您在开始时间之前或结束时开始所有事件，并在开始时或之后结束。过滤器将在子查询中使用，对于OuterRef，我们引用外部查询中的字段。

接下来是子查询。它是从子查询中获取聚合的出乎意料的困难，因为聚合并不懒惰(=它们立即执行)，而Subquery需要这样做。解决办法之一是使用Window

subquery = Subquery(Event.objects
        .filter(overlap_filter)
        .annotate(sum_participants=Window(Sum('participants'),))
        .values('sum_participants')[:1],
    output_field=IntegerField()
)

最后，使用带注释的Events进行查询

events = Event.objects.annotate(simultaneous_participants=subquery)

请注意，虽然参与此计数的参与者与我们正在查看的Event重叠，但它们并不一定重叠--它们在Event期间的某个时间都存在，但并非所有参与者都同时在场--有些人可能在其他人到达之前就离开了。要计算实际出勤高峰，您需要查看较小的时间增量(取决于开始时间和结束时间是如何交错的)。