问题是：
如何获取由汇总函数选择的行？
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:
我同时需要：
某些消耗的资源被用户消耗。第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（...）一起使用？
发布评论