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

本文介绍了如何获取由聚合函数选择的行的ID？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有下一个数据：

id | name | amount | datefrom --------------------------- 3 | a | 8 | 2018-01-01 4 | a | 3 | 2018-01-15 10:00 5 | b | 1 | 2018-02-20

我可以将结果与下一个查询分组：

I can group result with the next query:

select name, max(amount) from table group by name

但是我也需要所选行的 id 。因此，我尝试过：

But I need the id of selected row too. Thus I have tried:

select max(id), name, max(amount) from table group by name

并且按预期返回：

id | name | amount ----------- 4 | a | 8 5 | b | 1

但是我需要ID包含 3 的 8 ：

But I need the id to have 3 for the amount of 8:

id | name | amount ----------- 3 | a | 8 5 | b | 1

这可能吗？

PS 。这是开票任务所必需的。某天 2018-01-15 的 a 配置已更改，用户消耗了10h的资源，而 8 并在第14天休息- 3 。我需要用最大值来计算这样的一天。因此，在2018年1月15日这一天，将忽略 id = 4 的行。 （对于第二天2018年1月16日，我将为 3 开票） 所以我为该行开票：

PS. This is required for billing task. At some day 2018-01-15 configuration of a was changed and user consumes some resource 10h with the amount of 8 and rests the day 14h -- 3. I need to count such a day by the maximum value. Thus row with id = 4 is just ignored for 2018-01-15 day. (for next day 2018-01-16 I will bill the amount of 3) So I take for billing the row:

3 | a | 8 | 2018-01-01

如果出现问题。我必须报告 id == 3 的行是错误的。

And if something is wrong with it. I must report that row with id == 3 is wrong.

但是当我使用聚合函数时，有关 id 丢失。

But when I used aggregation function the information about id is lost.

如果可能，会很棒：

select current(id), name, max(amount) from table group by name select aggregated_row(id), name, max(amount) from table group by name

此处 agg_row 引用该行由聚集函数 max

Here agg_row refer to the row which was selected by aggregation function max

UPD 我将任务解析为：

UPD I resolve the task as:

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

UPD 使用窗口函数

推荐答案

至少有3种方法，请参见下文：

There are at least 3 ways, see below:

CREATE TEMP TABLE test ( id integer, name text, amount numeric, datefrom timestamptz ); COPY test FROM STDIN (FORMAT csv); 3,a,8,2018-01-01 4,a,3,2018-01-15 10:00 5,b,1,2018-02-20 6,b,1,2019-01-01 \.

方法1.使用DISTINCT ON（特定于PostgreSQL）

Method 1. using DISTINCT ON (PostgreSQL-specific)

SELECT DISTINCT ON (name) id, name, amount FROM test ORDER BY name, amount DESC, datefrom ASC;

方法2.使用窗口函数

Method 2. using window functions

SELECT id, name, amount FROM ( SELECT *, row_number() OVER ( PARTITION BY name ORDER BY amount DESC, datefrom ASC) AS __rn FROM test) AS x WHERE x.__rn = 1;

方法3.使用相关子查询

Method 3. using corelated subquery

SELECT id, name, amount FROM test WHERE id = ( SELECT id FROM test AS t2 WHERE t2.name = test.name ORDER BY amount DESC, datefrom ASC LIMIT 1 );