为什么聚合函数不能与DISTINCT ON（...）一起使用？ [英] Why aggregate functions can not be used with DISTINCT ON(...)?

问题描述

问题是：

如何获取由汇总函数选择的行？

How to get row which was selected by aggregate function?

问题得到了答案，并部分解决了我的问题。但是由于以下原因，我仍然无法用 DISTINCT ON 替换 GROUP BY ：

The question was answered and partially resolve my problem. But I still can not replace GROUP BY with DISTINCT ON because of next reason:

我同时需要：

1. 选择汇总的 id 行（可以通过 DISTINCT ON 进行解析）


2. 对比率列求和（可以用 GROUP BY 解决）

1. Select id of aggregated row (may be resolved with DISTINCT ON)
2. Sum the ratio column (may be resolved with GROUP BY)

某些消耗的资源被用户消耗。第10天的用户中有一部分消费了 8 ，而第10天的用户中有一部分消费了 3 ，而第4天他没有使用资源。任务是最大程度地计费消耗的资源，而不在不消耗资源时计费

Some amount of resource is consumed by user. One part of day 10h user consumed 8 another part of day 10h user consumed 3 and 4h he do not consume resource. The task is to bill consumed resource by the maximum and do not bill when resource was not consumed

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

我通过下一个查询完成此任务：

I accomplish this task by next query:

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 ？

Why it is not allowed to use aggregation functions with 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 的问题。不需要使用子查询的解决方法

This will resolve also issues with ORDER BY. No need a workaround with subquery

是否存在技术原因使上一个示例的查询无法按所述方式工作？

UPD

从理论上讲，它可以像下一个一样工作：

UPD
In theory this can work like next:

第一个示例：

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

从（t2）
的订单中选择（name）id，name，amount，sum（ratio）

找到第一行后，它将保存其 id 和 name

下次找到第二行和下一个非重复行时，它将调用 sum 并累积比率

Next time when second and next non distinct row is found it will call to sum and accumulate ratio

第二个示例：

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

$中选择不同的（name）id，name，max（amount），sum（ratio）
b $ b

找到第一行后，它将保存其 id 和 name ，并累加te ratio 并将 ratio 的当前值设置为最大值

When the first distinct row found, it saves its id and name, accumulate ratio and set current value of ratio as maximum

下次找到第二行和下一个非唯一行时，它将调用 sum 并累积 ratio

Next time when second and next non distinct row is found it will call to sum and accumulate ratio

如果第二行和/或下一个非区别行中的任何一个对于比率列具有更大的价值，则将其另存为最大值和 id 的保存值已更新

If any of second and/or next non distinct row has greater value for ratio column it is saved as maximum and saved value for id is updated

UPD

如果多于一行，其中amount = max（amount） Postgres可以从任一行返回值。由于此操作是针对不在DISTINCT ON之下的任何字段完成的，所以请确保 ORDER BY

UPD
if more than one row where amount = max(amount) Postgres can return value from either row. As this is done for any field which is not under DISTINCT ON

code>子句。 此处

To be sure which of is returned the query maybe qualified by ORDER BY clause. Like this is done here

推荐答案

我不确定我是否完全理解您的问题（我不理解 10h用户的部分）。

I am not quiet sure if I understood your problem completely (I didn't understand the part with the "10h user").

但是我相信您正在搜索窗口功能。我从另一个问题扩展了我的小提琴，并借助这样的窗口函数添加了 SUM（ratio）。

But I believe you are searching for window functions. I expanded my fiddle from the other question a little bit and added your SUM(ratio) with help of such a window function.

这是您期望的吗？

演示：db<>小提琴

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

当然，您可以使用相同的窗口计算 MAX（amount）

Of course you can calculate the MAX(amount) with the same window function as well:

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 ）

No need of GROUP BY. OK, but you are needing an extra subquery in this case where you have to filter the result of the window function (amount = max_amount)

这篇关于为什么聚合函数不能与DISTINCT ON（...）一起使用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！