Oracle多查询合并：将分类下帖子数与操作数结果合并为单表

表结构 #

POST表 #

id         title       content     category_id
----------  ----------  ----------  ------------  
 1          title1      Text...     1
 2          title2      Text...     1
 3          title3      Text...     1
 4          title4      Text...     3
 5          title5      Text...     2
 6          title6      Text...     1

CATEGORY表 #

id         name      
----------  ----------  
 1          category_1      
 2          category_2
 3          category_3   

ACTION_TYPE表 #

id         name      
----------  ----------  
 1          updated
 2          deleted
 3          restored
 4          hided

ACTION表 #

id         post_id         action_type_id    date
----------  ----------      --------------    -----
 1          1               1                 2017-01-01
 2          1               1                 2017-02-15
 3          1               3                 2018-06-10
 4          6               1                 2019-08-01
 5          5               2                 2019-12-09
 6          2               3                 2020-04-27
 7          2               1                 2020-07-29
 8          3               2                 2021-03-13

现有查询及结果 #

查询1：统计每个分类下的帖子数量 #

SQL语句：

select categories,  count(*) as cnt_posts_per_cat
   from(
        select 
        case
        when p.category_id is not null then c.name
        end as categories
        from post p  
        left join category c on p.category_id = c.id
        )
   group by categories
;

查询结果：

categories  cnt_posts_per_cat
----------  -------------------   
category_1  4
category_2  1
category_3  1

查询2：统计每个分类下帖子的有效操作数量 #

筛选restored、deleted、updated类型操作，取每个帖子的最后一次操作：
SQL语句：

select categories,  count(*) as cnt_actions_per_cat
   from(
        select  distinct  ac.post_id  AS action_post_id, max(ac.date) over (partition by  ac.post_id) as max_date, 
        case
        when ac.action_type_id is not null then act.name
        end as actions,
        case
        when p.category_id is not null then c.name
        else 'na'
        end as categories
        from action ac
        left join post p on ac.post_id = p.id  
        left join category c on p.category_id = c.id
        left join action_type act on ac.action_type_id = act.id
        where act.name in ('restored','deleted','updated')
        )
   group by categories
;

查询结果：

categories  cnt_actions_per_cat
----------  -------------------   
category_1  3
category_2  1
category_3  na

期望合并结果 #

需要将两个查询结果合并为包含三列的表：

categories  cnt_posts_per_cat   cnt_actions_per_cat
----------  -----------------   -------------------  
category_1  4                   3
category_2  1                   1
category_3  1                   na

错误尝试 #

使用UNION或UNION ALL合并得到不符合预期的结果：

categories  cnt_posts_per_cat   
----------  -----------------   
category_1  7                  
category_2  2                  
category_3  1           

正确Oracle SQL写法 #

UNION/UNION ALL用于合并行数据，而我们需要合并列数据，因此通过关联查询将两个子查询的结果按分类名称连接：

SELECT 
    q1.categories,
    q1.cnt_posts_per_cat,
    NVL(q2.cnt_actions_per_cat, 'na') AS cnt_actions_per_cat
FROM (
    -- 子查询1：获取每个分类的帖子数量
    SELECT 
        CASE WHEN p.category_id IS NOT NULL THEN c.name END AS categories,
        COUNT(*) AS cnt_posts_per_cat
    FROM post p  
    LEFT JOIN category c ON p.category_id = c.id
    GROUP BY CASE WHEN p.category_id IS NOT NULL THEN c.name END
) q1
LEFT JOIN (
    -- 子查询2：获取每个分类的有效操作数量
    SELECT 
        CASE WHEN p.category_id IS NOT NULL THEN c.name ELSE 'na' END AS categories,
        COUNT(*) AS cnt_actions_per_cat
    FROM (
        SELECT DISTINCT 
            ac.post_id,
            MAX(ac.date) OVER (PARTITION BY ac.post_id) AS max_date,
            act.name AS actions,
            CASE WHEN p.category_id IS NOT NULL THEN c.name ELSE 'na' END AS categories
        FROM action ac
        LEFT JOIN post p ON ac.post_id = p.id  
        LEFT JOIN category c ON p.category_id = c.id
        LEFT JOIN action_type act ON ac.action_type_id = act.id
        WHERE act.name IN ('restored','deleted','updated')
    ) sub
    GROUP BY CASE WHEN p.category_id IS NOT NULL THEN c.name ELSE 'na' END
) q2 
    ON q1.categories = q2.categories
ORDER BY q1.categories;

说明 #

1. 以第一个子查询的分类列表为主，使用LEFT JOIN关联第二个子查询的结果，确保所有存在帖子的分类都被保留。
2. 使用NVL函数将第二个子查询中不存在的分类对应的操作数转换为na，匹配期望结果的格式。

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