PostgreSQL中能否将多条件最新数据查询合并为单条查询？

我有如下更新记录表：

针对refer=2的情况，我需要获取三个特定的最新更新值：

• abc123：基于最新日期的全局最新更新；
• abc3：event_type为type 11且cat1为cat b的最新更新；
• xyz：cat1为cat z的最新更新。

目前我只能通过多条查询或CTE实现需求，代码如下：

with cte1 as (
select  t.refer,
        t.detail
from    
        (
            select ch.refer,
                  ch.detail,
                    row_number() over (partition by refer order by event_date desc) as rn
            from    table  ch
            ) as latest
    where ch.rn = 1
)
cte2 as(
select  t.refer,
        t.detail
from    
        (
            select ch.refer,
                  ch.detail,
                    row_number() over (partition by refer order by event_date desc) as rn
            from    table  ch
            where   event_type = '11'
            and     cat = 'b'
            ) as latest
    where ch.rn = 1
)
cte3 as(
select  t.refer,
        t.detail
from    
        (
            select ch.ref,
                  ch.detail,
                    row_number() over (partition by refer order by event_date desc) as rn
            from    table  ch
            where   event_type = 'z'   
            ) as latest
    where ch.rn = 1
    and cat = 'z'
);

请问能否将这些查询合并为单条PostgreSQL查询？

当然可以合并成单条查询，而且只需要扫描一次表，效率比多CTE更高。下面提供两种可行的写法：

方法一：用first_value()结合条件排序 #

直接通过窗口函数按不同规则提取目标值：

select distinct
    refer,
    -- 全局最新更新
    first_value("column detail") over (partition by refer order by event_date desc) as latest_global_detail,
    -- event_type=type11且cat1=cat b的最新更新
    first_value(case when event_type = 'type 11' and cat1 = 'cat b' then "column detail" end)
        over (partition by refer order by case when event_type = 'type 11' and cat1 = 'cat b' then event_date else null end desc nulls last) as latest_type11_catb_detail,
    -- cat1=cat z的最新更新
    first_value(case when cat1 = 'cat z' then "column detail" end)
        over (partition by refer order by case when cat1 = 'cat z' then event_date else null end desc nulls last) as latest_catz_detail
from your_table
where refer = 2;

方法二：用row_number()标记后聚合 #

先给不同维度的行标记排序序号，再通过聚合提取目标值：

select
    refer,
    max(case when rn_global = 1 then "column detail" end) as latest_global_detail,
    max(case when rn_type11_catb = 1 then "column detail" end) as latest_type11_catb_detail,
    max(case when rn_catz = 1 then "column detail" end) as latest_catz_detail
from (
    select
        refer,
        "column detail",
        -- 全局排序序号
        row_number() over (partition by refer order by event_date desc) as rn_global,
        -- 仅符合type11+catb的行排序，不符合的排到最后
        row_number() over (partition by refer order by case when event_type = 'type 11' and cat1 = 'cat b' then event_date else '1970-01-01' end desc) as rn_type11_catb,
        -- 仅符合catz的行排序，不符合的排到最后
        row_number() over (partition by refer order by case when cat1 = 'cat z' then event_date else '1970-01-01' end desc) as rn_catz
    from your_table
    where refer = 2
) t
group by refer;

注意事项 #

1. 修正了你原始SQL里的小错误：把关键字table换成了实际表名your_table，修正了cat→cat1、ch.ref→ch.refer、event_type='z'→cat1='cat z'这些笔误；
2. 如果需要支持多个refer值，去掉where refer=2即可，结果会按每个refer分组输出对应的值。

内容的提问来源于stack exchange，提问作者earbasher