我有下一个数据:
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 nameUPD 使用窗口函数
推荐答案至少有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?
发布评论