Oracle查询：按分类统计各Post的最新Action_Type记录数

我拥有POST、CATEGORY、ACTION和ACTION_TYPE四张表，其中ACTION表存储所有操作记录，ACTION_TYPE表存储操作类型详情（例如ID=4的ACTION对应POST_ID=6、ACTION_TYPE_ID=1，表示对帖子6执行了更新操作，一个帖子可对应多条操作记录）。

各表结构及数据 #

POST表 #

id         title       content     category_id
----------  ----------  ----------  ------------  
 1          title1      Text...     3
 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（分类3）有两条updated操作，应该只统计最新的2017-02-15这条，现有查询却统计了2条。

现有查询语句：

select categories, actions,  count(*) as cnt_actions_per_cat
   from(
        select   
        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, actions
;

现有查询结果：

categories   actions     cnt
-----------  ----------  -----------   
category_1   updated     2
category_1   deleted     1
category_1   restored    1
category_2   updated     0
category_2   deleted     1
category_2   restored    0
category_3   updated     2
category_3   deleted     0
category_3   restored    1

期望结果 #

categories   actions     cnt
-----------  ----------  -----------   
category_1   updated     2
category_1   deleted     1
category_1   restored    1
category_2   updated     0
category_2   deleted     1
category_2   restored    0
category_3   updated     1 -- 仅统计帖子1的最新updated操作（2017-02-15）
category_3   deleted     0
category_3   restored    1

修正后的查询语句 #

WITH latest_actions AS (
    SELECT 
        post_id,
        action_type_id,
        MAX(date) AS latest_date
    FROM action
    WHERE action_type_id IN (SELECT id FROM action_type WHERE name IN ('restored','deleted','updated'))
    GROUP BY post_id, action_type_id
),
action_with_latest AS (
    SELECT 
        ac.post_id,
        ac.action_type_id
    FROM action ac
    JOIN latest_actions la 
        ON ac.post_id = la.post_id 
        AND ac.action_type_id = la.action_type_id 
        AND ac.date = la.latest_date
)
SELECT 
    COALESCE(c.name, 'na') AS categories,
    COALESCE(act.name, 'na') AS actions,
    COUNT(awl.post_id) AS cnt
FROM category c
CROSS JOIN action_type act
LEFT JOIN post p ON c.id = p.category_id
LEFT JOIN action_with_latest awl ON p.id = awl.post_id AND act.id = awl.action_type_id
WHERE act.name IN ('restored','deleted','updated')
GROUP BY c.name, act.name
ORDER BY c.name, act.name;

思路说明 #

1. latest_actions CTE：先找出每个帖子（post_id）每个操作类型（action_type_id）对应的最新操作日期。
2. action_with_latest CTE：关联原ACTION表，筛选出每个帖子每个操作类型的最新记录。
3. 主查询通过笛卡尔积关联CATEGORY和ACTION_TYPE，确保所有分类和操作类型的组合都被统计（包括数量为0的情况），再关联帖子和最新操作记录，最后按分类和操作类型分组统计数量。

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