按指定年份最后分配统计Post表的Category_Type分组记录数问题

业务需求 #

现有POST、CATEGORY、CATEGORY_TYPE和ASSIGNMENT四张表，其中ASSIGNMENT表记录分类到分类类型的分配关系。需按指定年份（示例为2018年）的最后分配结果，统计各category_type对应的Post数量。

表数据 #

POST表 #

+----+-------------+
| id | category_id |
+----+-------------+
|  1 |      1      |
|  2 |      2      |
|  3 |      1      |
|  4 |      3      |
|  5 |      1      |
|  6 |      1      |
|  7 |      1      |
|  8 |      3      |
|  9 |      2      |
| 10 |      2      |
| 11 |      4      |
+----+-------------+

CATEGORY表 #

+----+------------------+
| id |   category_name  |
+----+------------------+
|  1 |    category_1    |
|  2 |    category_2    |
|  3 |    category_3    |
|  4 |    category_4    |
|  5 |    category_5    |
+----+------------------+

CATEGORY_TYPE表 #

+----+------------------+
| id |  category_type   |
+----+------------------+
|  1 |     type_1       |
|  2 |     type_2       |
|  3 |     type_3       |
+----+------------------+

ASSIGNMENT表 #

+----+------------------+---------------------+--------------+
| id |   category_id    |   category_type_id  | Date         |
+----+------------------+---------------------+--------------+
|  1 |        1         |          3          |  2017-01-01  |
|  2 |        3         |          2          |  2017-11-10  |
|  3 |        1         |          2          |  2017-12-02  |
|  4 |        5         |          3          |  2018-01-01  |
|  5 |        2         |          1          |  2018-04-03  |
|  6 |        3         |          1          |  2018-05-06  |
|  7 |        2         |          2          |  2018-09-21  |
|  8 |        1         |          3          |  2018-11-01  |
|  9 |        4         |          2          |  2018-12-29  |
| 10 |        3         |          3          |  2019-02-16  |
| 11 |        5         |          1          |  2019-06-20  |
| 12 |        1         |          1          |  2019-12-01  |
+----+------------------+---------------------+--------------+

当前查询语句 #

select category_type, count(*) as cnt 
from(
select max(ctt.category_type) AS category_type
from post p 
        inner join category ct on (p.category_id = ct.id)
        left outer join (
            select category_id,
            max(category_type_id) keep (dense_rank last order by id) AS category_type_id
            from assignment ag
            where extract (year from ag.date) <= 2018
            group by category_id
            ) ag
        on (ct.id= ag.category_type_id)
        left join category_type ctt on ag.category_type_id = ctt.id      
        group by ctt.id
)
group by category_type;

预期与实际结果 #

预期结果 #

+---------------+----------+
| category_type |    cnt   |
+---------------+----------+
|     type_1    |     2    |
|     type_2    |     4    |
|     type_3    |     5    |
+---------------+----------+

（总和11，与Post总数一致）

实际返回结果 #

+---------------+----------+
| category_type |    cnt   |
+---------------+----------+
|     type_1    |     4    |
|     type_2    |     6    |
|     type_3    |     8    |
+---------------+----------+

问题分析与修正 #

错误点 #

1. 关联条件错误：原查询中on (ct.id= ag.category_type_id)将分类ID与分类类型ID关联，逻辑完全错误，应该关联分类ID：ct.id = ag.category_id。
2. 多余分组嵌套：内层子查询不需要group by ctt.id，导致分组逻辑混乱，统计结果重复。

修正后的SQL（Oracle语法） #

SELECT 
    ctt.category_type,
    COUNT(p.id) AS cnt
FROM 
    post p
INNER JOIN 
    category ct ON p.category_id = ct.id
LEFT JOIN (
    SELECT 
        category_id,
        MAX(category_type_id) KEEP (DENSE_RANK LAST ORDER BY date) AS category_type_id
    FROM 
        assignment
    WHERE 
        EXTRACT(YEAR FROM date) <= 2018
    GROUP BY 
        category_id
) ag ON ct.id = ag.category_id
LEFT JOIN 
    category_type ctt ON ag.category_type_id = ctt.id
GROUP BY 
    ctt.category_type
ORDER BY 
    ctt.category_type;

修正说明 #

• 修正关联条件，确保分类ID与分配记录中的分类ID匹配，正确获取每个分类的最后分配类型。
• 用KEEP (DENSE_RANK LAST ORDER BY date)直接获取每个分类在2018年及之前的最新分配记录。
• 外层直接统计每个分类类型对应的Post数量，逻辑清晰，避免多余分组导致的重复统计。

执行该语句后将得到预期结果，总和与Post总数一致。

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