Django Window 注释与distinct 子句结合使用

Question

我有一个存储在 Postgres 数据库中的 Django 模型，它由不规则间隔的计数值组成:

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

如果我做:

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 的昨天_计数 - 即，它在应用不同的子句之前向我显示前一行。

如果我添加这样的分区子句:

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

然后对于所有行，昨天_计数是无。

如果需要，我可以在 Python 中进行这个计算，但这让我有点生气，我想知道我正在尝试做的事情是否可行。

谢谢!

Answer 1

我认为主要问题是您正在混合注释中使用的操作生成分组查询集，例如 sum 和一个操作，该操作简单地为给定查询集中的每个记录创建一个新字段，例如 yesterday_count=Window(expression=Lag("count")) .

所以订购在这里真的很重要。所以当你尝试:

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

结果查询集只是 WidgetCount.objects.distinct("date") 注释，不进行分组。

我建议将您的操作解耦，以便更容易理解正在发生的事情，并注意您正在迭代 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 列。