python - Django ORM - 如何使用values().annotate().values()执行复杂的GROUP BY

Question

我正在尝试将尽可能多的原始 SQL 转换为使用 Django ORM，但遇到了障碍。我正在尝试执行类似于此的查询:

SELECT table.x,
      MAX(table.y) AS y,
      table.group_category,
      table.group_number,
FROM table
GROUP BY table.group_category, table.group_number

到目前为止，我一直在尝试对此进行一些排列:

q = MyModel.objects\
    .filter(**filter_kwargs)\
    .values('group_category', 'group_number')\
    .annotate(y=Max('y'))\
    .values('x','y','group_category','group_number')

但是，这似乎不起作用。如果我排除最后一个 values()，它会生成以下内容(大致):

SELECT MAX(table.y) AS y,
      table.group_category,
      table.group_number,
FROM table
GROUP BY table.group_category, table.group_number

它不会选择table.x。但如果我包含最后一个 values()...

SELECT table.x,
      MAX(table.y) AS y,
      table.group_category,
      table.group_number,
FROM table
GROUP BY x, y, table.group_category, table.group_number

它按x, y分组。因此，显然正在发生的事情是所有值都被替换，并且注释使用 QuerySet 给定的任何值(因为它是延迟评估的？)。 docs on aggregation and values似乎表明按此顺序执行两个值函数会产生预期的效果，我发现 a writeup (自 2013 年起)这也表明了这一点。难道我做错了什么？这在 Django ORM 中仍然可能吗？有什么方法可以让我在不使用 extra() 的情况下做到这一点？还是原始 SQL？为了演示目的，我试图使这个示例尽可能简单，但我的实际问题涉及 JOIN。这会让事情变得复杂吗？

<小时/>

更新 1

我能够有点弄清楚，但是，它仍然没有产生我想要的 SQL 查询的最佳版本(上面)。为了获得我需要的结果，我改为执行查询来获取 MAX(table.y) ，然后使用 __in 来针对子查询的值将其用作子查询。子查询进行分组。

filtered = MyModel.objects.filter(**filter_kwargs)

subq = filtered\
    .values('group_category', 'group_number')\
    .annotate(y=Max('y'))\
    .values_list('y', flat=True)

q = filtered\
    .filter(y__in=subq)\
    .values('x','y','group_category','group_number')

正如我所说，这有效，因为它可以让我得到我需要的结果。问题是它比仅使用与 GROUP BY 不同的 SELECT 慢得多，因为它创建了一个相对庞大的子查询。我还没有将其标记为答案，因为它仍然没有生成与我真正想要的内容相匹配的查询。相反，它看起来像这样:

SELECT table.x,
      table.y,
      table.group_category,
      table.group_number,
FROM table
WHERE y IN 
    (SELECT MAX(U0.y) AS y
    FROM table U0
    GROUP BY U0.group_category, U0.group_number)

此外，看起来我什至不能使用 extra()因为它同样只会将列添加到已经属于 QuerySet 一部分的 SELECT 子句，即 values()。

<小时/>

更新2

事实证明，我的困惑解决方法不起作用，因为它获取所有 y(1 行)的 MAX 并返回它，而不是按 group_category 和 group_number 将它们分组在一起并使用 MAX 他们的y，所以我又回到了绘图板。

Answer 1

您似乎想要计算最大值但返回所有行而不进行任何分组。这就是Window函数用于(可从 Django 2.0 获得):

models = MyModel.objects.annotate(max=Window(
    expression=Max('y'),
    partition_by=[F('group_category'), F('group_number')],))

但是为什么你不使用 GROUP BY 的方法呢？工作？

在原始查询中，Django(以及数据库；您引用的 SQL 会引发语法错误)坚持按 x 分组的原因是如果你按 category 分组和number ，你可能有几个x category 一组分组的值和number 。 DB 应该选择哪一个？它无法为你做出这样的选择。

如果x并不重要，你可以忽略它。如果它很重要但对于一组 category 始终具有相同的值和number ，然后按 x 对查询进行分组不会伤害你。如有不同x值并且它们很重要，您需要决定选择哪一个(并相应地告诉数据库)。 y 也是如此。 .