更新时间:2022-12-09 23:34:49
这有点痛苦,因为Postgres有很好的窗口函数 first_value()
和 last_value()
,但这些不是聚合函数。所以,这里有一个方法:
This is a bit of a pain, because Postgres has the nice window functions first_value()
and last_value()
, but these are not aggregation functions. So, here is one way:
select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue,
max(t.week) as maxWeek, max(lastvalue) as lastValue
from (select t.*, first_value(value) over (partition by name order by week) as firstvalue,
last_value(value) over (partition by name order by week) as lastvalue
from table t
) t
group by t.name;