在postgresql中，我可以把它合并成一个查询吗？

j13ufse2 于 2023-05-17 发布在 PostgreSQL

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

我有下面的更新表……
| 参考|事件日期|列详细信息|事件类型|cat1|
| --------------|--------------|--------------|--------------|--------------|
| 二|昨天|ABC|第三型|猫X|
| 二|上周|abc3|十一型|B类|
| 二|今日|abc123|第四型|猫A|
| 二|上月|xyz|二十二式|Z轴|
| 二|去年|世界电视联合会|十一型|Z轴|
所以for refer = 2
abc123是基于最新日期的最新更新。
abc 3是event_type 11和cat = b的最新更新
xyz是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

来源：https://stackoverflow.com/questions/76264348/in-postgresql-can-i-combine-this-into-a-single-query

2条答案

按热度按时间

xzv2uavs1#

我们可以用三个row_number来枚举三个分区，然后用条件聚合来透视：

select refer,
    max(detail) filter(where rn1 = 1                                  ) detail,
    max(detail) filter(where rn2 = 1 and cat = 'z'                    ) detail2,
    max(detail) filter(where rn3 = 1 and cat = 'b' and event_type = 11) detail3
from (
    select t.*,
        row_number() over(partition by refer                  order by event_date desc) rn1,
        row_number() over(partition by refer, cat             order by event_date desc) rn2,
        row_number() over(partition by refer, event_type, cat order by event_date desc) rn3
    from mytable t
) t
group by refer

参考	细节	详细信息2	详细信息
二	abc123	xyz	abc3

fiddle
当然，这是假设您将日期存储为类似date的数据类型，而不是像'today'或'last year'这样的字符串。
或者，如果您希望结果以行而不是列的形式显示，那么我们不需要聚合：

select *
from (
    select t.*,
        row_number() over(partition by refer                  order by event_date desc) rn1,
        row_number() over(partition by refer, cat             order by event_date desc) rn2,
        row_number() over(partition by refer, event_type, cat order by event_date desc) rn3
    from mytable t
) t
where
    rn1 = 1
    or (rn2 = 1 and cat = 'z')
    or (rn3 = 1 and cat = 'b' and event_type = 11)

赞(0）回复(0）举报 2023-05-17

t2a7ltrp2#

您可以使用UNION（当同一日期出现在多个选择中时删除重复）：

SELECT t.*
FROM mytable t
INNER JOIN (
  select refer, max(event_date) as recent_date
  from mytable 
  where refer = 2 
  group by refer
  union
  select refer, max(event_date) as recent_date
  from mytable where event_type = 'type 11' and cat1 = 'cat b'
  group by refer
  union
  select refer, max(event_date) as recent_date
  from mytable
  where cat1 = 'cat z'
  group by refer
) AS s on s.refer = t.refer and s.recent_date = t.event_date

结果：

refer   event_date  column_detail   event_type  cat1
2       2023-05-08  abc3            type 11     cat b
2       2023-05-16  abc123          type 4      cat a
2       2023-04-16  xyz             type 22     cat z

Demo here

赞(0）回复(0）举报 2023-05-17

我来回答

在postgresql中，我可以把它合并成一个查询吗？

2条答案

相关问题

热门标签

最新问答