sql - PostgreSQL 按列分组和聚合

Question

我需要group by id 并选择 task最小/最大 seq如 start和 end

 id | task | seq    
----+------+-----
  1 | aaa  |   1 
  1 | bbb  |   2
  1 | ccc  |   3

SELECT
  id,
  CASE WHEN seq = MIN(seq) THEN task AS start,
  CASE WHEN seq = MAX(seq) THEN task AS end
FROM table
GROUP BY id;

但这导致

ERROR:  column "seq" must appear in the GROUP BY clause or be used in an aggregate function

但我不想分组 seq

Answer 1

一种方法使用数组:

SELECT id, 
       (ARRAY_AGG(task ORDER BY seq ASC))[1] as start_task,
       (ARRAY_AGG(task ORDER BY seq DESC))[1] as end_task
FROM table
GROUP BY id;

另一种方法使用带有 SELECT DISTINCT 的窗口函数:

select distinct id,
       first_value(task) over (partition by id order by seq) as start_task,
       first_value(task) over (partition by id order by seq desc) as end_task
from t;