Используйте Django Model для поиска 1 записи в час и самой последней записи из другой временной метки

Django 3.2.9

db: (PostgreSQL) 14.0

Модель

class InventoryForecast(models.Model):
    count = models.IntegerField()
    forecast_for = models.DateTimeField(null=False)
    forecasted_at = models.DateTimeField(null=False)

Data

id count forecast_for forecasted_at
8 40910 2022-10-10 11:00 2022-09-04 12:00
9 40909 2022-10-10 11:00 2022-09-05 12:00
10 50202 2022-10-10 11:00 2022-09-06 12:00 (most recent forecast)
11 50301 2022-10-10 12:00 2022-09-04 12:00
12 50200 2022-10-10 12:00 2022-09-05 12:00
13 50309 2022-10-10 12:00 2022-09-06 12:00 (most recent forecast)

Как мне использовать Django Model, чтобы найти 1 запись за forecast_for час, и самую последнюю для forecasted_at значения? Так что в этом примере 2 записи.

Желаемые результаты

id count forecast_for forecasted_at
10 50202 2022-10-10 11:00 2022-09-06 12:00
13 50309 2022-10-10 12:00 2022-09-06 12:00

Что я пробовал

from django.db.models.functions import TruncHour, Max
>>> 
InventoryForecast.objects.annotate(
    hour=TruncHour('forecast_for')
).values('hour').annotate(
    most_recent_forecasted_at=Max('forecasted_at')
).values('hour', 'most_recent_forecasted_at')

SELECT DATE_TRUNC('hour', "app_inventoryforecast"."forecast_for" AT TIME ZONE 'UTC') AS "hour",
       MAX("app_inventoryforecast"."forecasted_at") AS "most_recent_forecasted_at"
  FROM "app_inventoryforecast"
 GROUP BY DATE_TRUNC('hour', "app_inventoryforecast"."forecast_for" AT TIME ZONE 'UTC')
 LIMIT 21

Execution time: 0.000353s [Database: default]
<QuerySet [{'hour': datetime.datetime(2022, 10, 10, 12, 0, tzinfo=<UTC>), 'most_recent_forecasted_at': datetime.datetime(2022, 9, 6, 11, 0, tzinfo=<UTC>)}, {'hour': datetime.datetime(2022, 10, 10
, 11, 0, tzinfo=<UTC>), 'most_recent_forecasted_at': datetime.datetime(2022, 9, 6, 11, 0, tzinfo=<UTC>)}]>

Это правильно работает в GROUP BY, но мне нужно значение count. Хитрость в том, что когда я добавляю это значение в values, оно изменяет мою группу по, возвращая слишком много записей.

>>>python
InventoryForecast.objects.annotate(hour=TruncHour('forecast_for')).values('hour').annotate(most_recent_forecasted_at=Max('forecasted_at')).values('hour', 'most_recent_forecasted_at', 'count', 'id').all().count(
)
SELECT COUNT(*)
  FROM (
        SELECT "app_inventoryforecast"."count" AS Col1,
               "app_inventoryforecast"."id" AS Col2,
               DATE_TRUNC('hour', "app_inventoryforecast"."forecast_for" AT TIME ZONE 'UTC') AS "hour",
               MAX("app_inventoryforecast"."forecasted_at") AS "most_recent_forecasted_at"
          FROM "app_inventoryforecast"
         GROUP BY DATE_TRUNC('hour', "app_inventoryforecast"."forecast_for" AT TIME ZONE 'UTC'),
                  "app_inventoryforecast"."id"
       ) subquery

Execution time: 0.002036s [Database: default]
6

Итак, это возвращает все строки примера, 6. Мне нужно выбрать все мои столбцы и сгруппировать только по усеченному часу, или что-то подобное, и вернуть 2 последние прогнозируемые строки

Это решение аннотирует новое поле forecast_for_hour, которое использует TruncHour для создания целого часа из временной метки forecast_for, затем упорядочивает по forecast_for_hour возрастанию и forecasted_at убыванию, группируя их. Поскольку вы используете PostgreSQL, мы можем вызвать distinct на forecast_for_hour, который благодаря упорядочиванию по forecasted_at нисходящей сортировке берет самый новый прогноз 

qs = (
        InventoryForecast.objects
        .annotate(forecast_for_hour=TruncHour('forecast_for'))
        .order_by('forecast_for_hour', '-forecasted_at')
        .distinct('forecast_for_hour')
    )
Вернуться на верх