python - 在 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

I would have to calculate the amount of maximum equipments reserved at a certain time window

我猜 annotate with filter是你需要的

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))

在此之后您可以聚合 Max

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)