在单独的sql列中生成第一个和最后一个状态

bvpmtnay 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(275)

我有以下数据：

通过下面的查询，我得到如下结果：

我希望得到的结果是：

如何修改查询以获取最后一个表？

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

sql postgresql window-functions Date pivot

来源：https://stackoverflow.com/questions/63078482/generate-first-and-last-status-in-separate-columns-sql

2条答案

按热度按时间

vvppvyoh1#

您应该能够使用first\u value和last\u value分析函数来使用它，如下所示

select distinct 
       id
      ,first_value(status) over(partition by id order by status_date) as first_status
      ,first_value(status_date) over(partition by id order by status_date) as first_status_date
      ,last_value(status) over(partition by id order by status_date      range between unbounded preceding and unbounded following) as last_status
      ,last_value(status_date) over(partition by id order by status_date range between unbounded preceding and unbounded following) as last_status_date
  from t   
order by id

+-----+--------------+---------------------+-------------+---------------------+
| id  | first_status |  first_status_date  | last_status |  last_status_date   |
+-----+--------------+---------------------+-------------+---------------------+
| 123 | appointment  | 2018-01-09 13:18:09 | left        | 2018-09-20 14:51:13 |
| 547 | appointment  | 2018-02-14 09:43:15 | sold        | 2018-06-06 07:36:56 |
+-----+--------------+---------------------+-------------+---------------------+

这里是一个数据库小提琴链接
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=564f022fcf2d84afb054b0a48fa438ca

赞(0）回复(0）举报 2021-07-24

daolsyd02#

我认为最好用窗口函数和 distinct :

select distinct
    id,
    first_value(status) over(partition by id order by status_date) first_status,
    min(status_date)         over(partition by id) first_status_date,
    first_value(status) over(partition by id order by status_date desc) last_status,
    max(status_date)         over(partition by id) last_status_date
from history_table
order by id

更经典的方法是 row_number() 和条件聚合-但我怀疑这里的效率有点低（而且也比较长）：

select
    id,
    min(status) filter(where rn_asc = 1) first_status,
    min(status_date) first_status_date,
    min(status) filter(where rn_desc = 1) last_status,
    min(status_date) last_status_date,
from (
    select 
        h.*,
        row_number() over(partition by id order by status_date) rn_asc,
        row_number() over(partition by id order by status_date desc) rn_desc
    from history_table h
) h
where 1 in (rn_asc, rn_desc)
group by id

赞(0）回复(0）举报 2021-07-24

我来回答

在单独的sql列中生成第一个和最后一个状态

2条答案

相关问题

热门标签

最新问答