Использование аннотации окна Django в сочетании с выражением отличия

У меня есть модель Django, хранящаяся в базе данных Postgres, состоящая из значений счетчиков с нерегулярными интервалами:

WidgetCount
 - Time
 - Count

Я пытаюсь использовать оконную функцию с Lag, чтобы предоставить мне значения предыдущей строки в качестве аннотации. Моя проблема в том, что когда я пытаюсь объединить это с некоторым отдельным усечением даты, оконная функция использует исходные строки, а не четко сгруппированные.

Например, если у меня есть следующие строки:

time                count
2020-01-20 05:00    15
2020-01-20 06:00    20
2020-01-20 09:00    30
2020-01-21 06:00    35
2020-01-21 07:00    40
2020-01-22 04:00    50
2020-01-22 06:00    54
2020-01-22 09:00    58

И я хочу вернуть набор запросов, показывающий первое чтение за день, я могу использовать:

from django.db.models.functions import Trunc

WidgetCount.objects.distinct("date").annotate(date=Trunc("time", "day"))

Что дает мне:

date        count
01/01/20    15
01/01/21    35
01/01/22    50

Я хотел бы добавить аннотацию, которая дает мне вчерашнее значение (чтобы я мог показать изменение за день).

date        count   yesterday_count
01/01/20    15
01/01/21    35      15
01/01/22    50      35

If I do:

from django.db.models.functions import Trunc, Lag
from django.db.models import Window

WidgetCount.objects.distinct("date").annotate(date=Trunc("time", "day"), yesterday_count=Window(expression=Lag("count")))

Возвращение второй строки дает мне 30 для вчера_count, то есть показывает мне предыдущую строку перед применением отдельного предложения.

Если я добавлю следующее предложение partiion:

WidgetCount.objects.distinct("date").annotate(date=Trunc("time", "day"), yesterday_count=Window(expression=Lag("count"), partition_by=F("date")))

Тогда вчера_count равно None для всех строк.

Я могу выполнить этот расчет на Python, если мне нужно, но это меня немного злит, и я хотел бы узнать, возможно ли то, что я пытаюсь сделать.

Спасибо!


django postgresql django-orm
person Ludo    schedule 14.04.2020    source источник

Ответы (2)


arrow_upward
4
arrow_downward

Я думаю, что основная проблема заключается в том, что вы смешиваете операции, которые используются в аннотации, для создания сгруппированного набора запросов, такого как сумма, с операцией, которая просто создает новое поле для каждой записи в данном наборе запросов, например yesterday_count=Window(expression=Lag("count")).

Так что здесь действительно важен порядок. Итак, когда вы попробуете:

WidgetCount.objects.distinct("date").annotate(date=Trunc("time", "day"), yesterday_count=Window(expression=Lag("count")))

Результатом запроса является просто аннотированный WidgetCount.objects.distinct ("дата"), группировка не выполняется.

Я бы предложил разделить ваши операции, чтобы стало легче понять, что происходит, и заметить, что вы выполняете итерацию по объекту python, поэтому не нужно делать никаких новых запросов!

Обратите внимание на использование операции SUM в качестве примера, поскольку я получаю непредвиденную ошибку с оператором FirstValue. Я отправляю сообщения Sum, чтобы продемонстрировать идею, которая осталась прежней. Идея должна быть такой же для первого значения, просто изменив acc_count=Sum("count") на first_count=FirstValue("count")

for truncDate_groups in Row.objects.annotate(trunc_date=Trunc('time','day')).values("trunc_date")\
                      .annotate(acc_count=Sum("count")).values("acc_count","trunc_date")\
                      .order_by('trunc_date')\
                      .annotate(y_count=Window(Lag("acc_count")))\
                      .values("trunc_date","acc_count","y_count"):
    print(truncDate_groups)

ВЫХОД:

{'trunc_date': datetime.datetime(2020, 1, 20, 0, 0, tzinfo=<UTC>), 'acc_count': 65, 'y_count': None}
{'trunc_date': datetime.datetime(2020, 1, 21, 0, 0, tzinfo=<UTC>), 'acc_count': 75, 'y_count': 162}
{'trunc_date': datetime.datetime(2020, 1, 22, 0, 0, tzinfo=<UTC>), 'acc_count': 162, 'y_count': 65}

Оказывается, оператор FirstValue требует использования функции Windows, поэтому вы не можете вложить FirtValue и затем вычислить Lag, поэтому в этом сценарии я не совсем уверен, сможете ли вы это сделать. Возникает вопрос, как получить доступ к столбцу First_Value без вложенных окон.

person Bernardo stearns reisen    schedule 27.04.2020

arrow_upward
0
arrow_downward

Я не тестировал его локально, но думаю, вы захотите GROUP BY вместо DISTINCT здесь.

WidgetCount.objects.values(
    date=Trunc('time', 'day'),
).order_by('date').annotate(
    date_count=Sum('count'),  # Will trigger a GROUP BY date
).annotate(
    yesterday_count=Window(Lag('date_count')),
)
person Simon Charette    schedule 22.04.2020
comment
Предлагаемый запрос не возвращает числа, которые я ищу, но я посмотрю на groupby. Спасибо. [{'date': datetime.datetime(2020, 1, 20, 0, 0, tzinfo=<UTC>), 'date_count': 65, 'yesterday_count': None}, {'date': datetime.datetime(2020, 1, 21, 0, 0, tzinfo=<UTC>), 'date_count': 75, 'yesterday_count': 162}, {'date': datetime.datetime(2020, 1, 22, 0, 0, tzinfo=<UTC>), 'date_count': 162, 'yesterday_count': 65}] - person Ludo; 24.04.2020