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

Question

我有以下数据：

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

我可以使用下面的查询来对结果进行分组：

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

但是我还需要选定行的id。因此，我尝试了以下方法：

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

正如预期的那样，它返回：

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

但我需要将id设置为3，以便数量为8：

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

这是否可能？

PS. 这是为了账单任务需要。某一天 2018-01-15，a 的配置发生了变化，用户使用了 10h 资源，金额为 8，并且剩余 14h 资源 -- 3。我需要计算这样的一天中的最大价值。因此，对于 2018-01-15 日，id = 4 的行被忽略。（对于下一天 2018-01-16，我将开具 3 的费用发票。）
所以我会选择如下行进行账单处理：

3  | a    |  8     | 2018-01-01

如果有什么问题，我需要报告id == 3的那一行出现了错误。

但是当我使用聚合函数时，关于id的信息丢失了。

如果有可能，那将会非常棒：

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选择的行。

更新：
我将任务解决为：

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

更新
最好使用窗口函数

- Eugen Konkov

---

关于您的 UPDATE：运算符将是 int = ANY(int[])，但使用 DISTINCT ON 就没有子查询的必要了。 - S-Man

@S-Man：请问您能否写出在SQL中的样子？ - Eugen Konkov

这个操作符在这个fiddle中有演示：https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=faf54fdfd929e524ad6cb728fcf8142f，但是我真的无法在不完全重建它的情况下让你的“UPDATE”查询工作 - 有太多问题了...它太复杂了:D - S-Man

@S-Man：感谢。现在我修复了我的查询。这里是它。 - Eugen Konkov

Answer 1

至少有三种方法，如下所示：

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
\.

Method 1. using DISTINCT ON (PostgreSQL-specific)

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

方法二：使用窗口函数

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;

方法三：使用相关子查询

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

- filiprem

Answer 2

演示：db<>fiddle

您需要使用DISTINCT ON，它可以过滤每个组的第一行。

SELECT DISTINCT ON (name) 
    * 
 FROM table 
 ORDER BY name, amount DESC

- S-Man

Answer 3

你需要一个嵌套的内连接。尝试这个 -

SELECT id, T2.name, T2.amount
FROM TABLE T
INNER JOIN (SELECT name, MAX(amount) amount
            FROM TABLE
            GROUP BY name) T2
ON T.amount = T2.amount

- Ankit Bajpai

---

这将产生重复项，当有两行具有相同的名称和相同的金额，但不同的日期和ID时。 - filiprem