在Django中查找一次最多项目数

Question

我正在后台试用Django，目前有一个类似于以下的预订数据库模式：

| Equipment id | Amount |   Starting time   |    Ending time
|      1       |   2    |  2021-09-21 12:30 | 2021-09-21 16:00
|      1       |   3    |  2021-09-21 15:00 | 2021-09-21 20:00
|      1       |   5    |  2021-09-21 18:00 | 2021-09-21 20:00

1(
我必须计算在某个时间窗口保留的最大设备数量，例如17:00-21:00应返回8。我尝试使用queryset.annotate(maxAmount=Sum(amount))，但在这种情况下，它返回查询集中保留的所有设备的总和。

可能的解决方案是将具有重叠时间窗口的预订分组，然后从中找出最大数量吗？有什么命令可以这样把他们分组？

2(
同样的问题，但我也必须找到保留设备数量小于某个阈值的时间窗口。

上面数据的例子：我想找到保留设备数量小于3的时间：它应该返回00:00->12:30、16:00->18:00和20:00->00:00。

编辑：我用于生成查询集的代码：

def reservation_queryset(equipment_ids, starting_time, ending_time, time_between_res=0, id_to_ignore=None):
'''Queryset for returning matching reservations, for reservation checks'''
### Get reservations that are ongoing at the time with time windows at ends
# Reservations that start before this ends

timequery = Q(starting_time__lt=ending_time + timedelta(minutes=time_between_res))

# Reservations that end after this starts
timequery.add(Q(ending_time__gt=starting_time - timedelta(minutes=time_between_res)), Q.AND)
# Exclude the given reservation
equipmentquery = ~Q(id=id_to_ignore)
# Select the reservation equipments
equipmentquery.add(Q(equipments__equipment__in=equipment_ids), Q.AND)
# Combine the queries
fullquery = timequery
fullquery.add(equipmentquery, Q.AND)
# Apply the filter as distinct and get the queryset
existing_reservations = models.Reservation.objects.filter(fullquery).distinct()
return existing_reservations

编辑#2：这是我目前用来查找设备最大数量的代码：

def get_reserved_equipments(queryset):
'''Returns a object with amounts of reserved equipments in the given queryset.'''
values = queryset.prefetch_related('equipments').values('equipments__equipment')
reserved_equipments = values.annotate(amount=Sum('equipments__amount'))
return reserved_equipments```

Answer 1

我必须计算在某个时间窗口

我想用过滤器进行注释就是你需要的

from django.db.models import Sum, Q
qs_with_annotate = queryset.values('filed_with_equipment_id').annotate(amount_sum_17_21=Sum('amount', filter=Q(starting_time__hour__gte = 17) & Q(ending_time__hour__lt = 21))

之后你可以聚合最大

from django.db.models import Max
max_sum = qs_with_annotate.aggregate(max_sum = Max('amount_sum_17_21'))['max_sum']

和滤波器，以获得最大filed_with_equipment_id

qs_with_annotate.filter(amount_sum_17_21 = max_sum)