我有以下数据:
通过下面的查询,我得到如下结果:
我希望得到的结果是:
如何修改查询以获取最后一个表?
with minmax as (select t1.*,
case when rank=(min(rank) over (partition by id)) then status end as first_status,
case when rank=(min(rank) over (partition by id)) then status_date end as first_status_date,
case when rank=(max(rank) over (partition by id)) then status end as last_status,
case when rank=(max(rank) over (partition by id)) then status_date end as last_status_date,
max(rank) over (partition by id) as max
from (select id,
status_date,
status,
rank()
over (partition by id order by id, status_date) as rank
from history_table as hist
) as t1
group by 1, 2, 3, 4
order by id, status_date)
select distinct
id,
first_status,
first_status_date,
last_status,
last_status_date
from id_table as idt
left join minmax as mm on idt.id=mm.id
2条答案
按热度按时间vvppvyoh1#
您应该能够使用first\u value和last\u value分析函数来使用它,如下所示
这里是一个数据库小提琴链接
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=564f022fcf2d84afb054b0a48fa438ca
daolsyd02#
我认为最好用窗口函数和
distinct
:更经典的方法是
row_number()
和条件聚合-但我怀疑这里的效率有点低(而且也比较长):