聚合函数为什么不能与DISTINCT ON(...)一起使用？

Question

问题是：

如何获取由聚合函数选择的行？

该问题已得到回答，并且部分解决了我的问题。但是我仍然无法用DISTINCT ON替换GROUP BY，因为有以下原因：

我需要两个操作：

1. 选择汇总行的id（可以使用DISTINCT ON解决） 2. 对ratio列求和（可以使用GROUP BY解决）

用户消耗了一定数量的资源。白天的一部分10小时用户消耗了8，另一部分10小时用户消耗了3，而4小时他没有消耗资源。任务是按最大值计费已消耗的资源，并在未使用资源时不进行计费。

 id | name | amount | ratio 
----+------+--------+-------
  1 | a    |      8 |    10
  2 | a    |      3 |    10

我通过下面的查询完成这个任务：

SELECT 
    (
       SELECT id FROM t2 
       WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount ) 
    ) id,
    name, 
    MAX(amount) ma,
    SUM( ratio )
FROM t2  tf
GROUP BY name

为什么不允许在 DISTINCT ON 中使用聚合函数？

select distinct on ( name ) id, name, amount, sum( ratio )
from t2
order by name, amount desc

甚至更简单：

select distinct on ( name ) id, name, max(amount), sum( ratio )
from t2

这也将解决与ORDER BY相关的问题。不需要使用子查询的解决方法。

是否有技术原因导致最后一个示例中描述的查询无法正常工作？

更新
理论上，它可以像下面这样工作：

第一个示例：

select distinct on ( name ) id, name, amount, sum( ratio )
from t2
order by name, amount desc

当找到第一个不同的行时，它会保存其id和name 下一次找到第二个及以后的非重复行时，它将调用sum并累加ratio 第二个示例：

select distinct on ( name ) id, name, max(amount), sum( ratio )
from t2

当找到第一个不同的行时，它会保存其id和name，累加ratio并将当前值的ratio设置为最大值。

下一次找到第二个及更多非重复行时，它将调用sum并累加ratio。

如果第二个和/或下一个非重复行中的任何一个具有更大的ratio列值，则将其保存为maximum，并更新id的保存值。 更新：
如果存在amount = max(amount)的多行，则Postgres可以从任一行返回值。由于这是针对未在DISTINCT ON下的任何字段执行的操作，因此可以通过ORDER BY子句限定查询以确保返回哪个值。就像这里所做的那样。

- Eugen Konkov

---

将GROUP BY替换为DISTINCT：https://stackoverflow.com/a/67167595/4632019 - Eugen Konkov

Answer 1

我不太确定我完全理解了你的问题（我没有理解关于“10h用户”的部分）。

但是我相信你正在寻找窗口函数。我在另一个问题的fiddle中进行了扩展，并借助这样的窗口函数添加了你的SUM(ratio)。

这符合你的预期吗？

演示：db<>fiddle

SELECT DISTINCT ON (name)
    id, 
    name, 
    amount,
    SUM(ratio) OVER (PARTITION BY name)
FROM test
ORDER BY name, amount DESC

当然，您也可以使用同样的窗口函数计算MAX(amount):

SELECT 
    id, 
    name, 
    max_amount, 
    sum_ratio 
FROM (
    SELECT 
        t.*,
        MAX(amount) OVER w as max_amount,
        SUM(ratio) OVER w as sum_ratio
    FROM test t
    WINDOW w as (PARTITION BY name)
    ORDER BY name
) s 
WHERE amount = max_amount

无需使用GROUP BY。但在此情况下需要额外的子查询，其中必须过滤窗口函数的结果(amount = max_amount)。

- S-Man

---

太好了！谢谢。这解决了我的问题，但这不是回答这个问题的答案：为什么。 - Eugen Konkov

为什么：因为在你的示例中，你没有说明你的分组框架是什么。也许你可以想象一种情况，你想按列X进行分组（求和、平均值、最大值等），但需要列Y作为不同的列。因此，有必要给出分组列。 - S-Man

@EugenKonkov，非常抱歉，你当然是对的。我漏掉了一件事情。如果你使用窗口函数，你可以得到正确的计算结果，但是你仍然需要过滤正确的行。我修复了这个示例，并编辑了查询。感谢您的建议。 - S-Man

需要使用 ORDER BY 子句吗？ - Eugen Konkov

@EugenKonkov 不是真的。这只是为了可视化（好的，编辑后忘记删除了，现在已经很晚了;)）。 - S-Man

Answer 2

回答我的问题：

是否有技术原因导致无法按照描述的方式查询最后一个示例？

我们必须考虑如果有多行满足 amount = max(amount)，我们该如何提取id。

 id | name | amount | ratio 
----+------+--------+-------
  1 | a    |      8 |    10
  2 | a    |      8 |    10

对于这个数据，上面的查询将会生成错误：

ERROR:  more than one row returned by a subquery used as an expression

- Eugen Konkov