如何获取由聚合函数选择的行的ID？ [英] How to get id of the row which was selected by aggregate function?

问题描述

我有下一个数据：

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
); 

这篇关于如何获取由聚合函数选择的行的ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！