PostgreSQL中按分析ID拆分2019/2020年订单数至列的查询问题

问题背景 #

现有两张表：

• analysis（an_id, an_name, an_cost, an_price, an_group）
• orders（ord_id, ord_datetime, ord_an）—— 分析项的销售订单数据

需求：为每个an_id展示2019年和2020年的订单数量，要求每行对应一个an_id，同时显示两年的订单数。原查询因分组逻辑问题，输出为每个an_id分两行显示（一行对应2019年，一行对应2020年），不符合需求。

原查询问题分析 #

原查询的核心问题是GROUP BY子句同时包含了year和an_id，导致每个an_id会按年份拆分为独立分组，最终输出两行数据。此外，CASE WHEN放在COUNT外部，仅对当前分组的年份计数，另一年份只能返回0。

修正方案：条件聚合 #

使用条件聚合（将CASE WHEN嵌套在聚合函数内部），同时仅按an_id分组，即可实现单行展示两年数据的需求。

方案1：基于原CTE修改 #

WITH helper AS (
    SELECT a.an_id, 
           o.ord_id, 
           EXTRACT(year from o.ord_datetime) AS year
    FROM analysis a 
    INNER JOIN orders o ON o.ord_an = a.an_id
    WHERE EXTRACT(year FROM o.ord_datetime) IN (2019, 2020)
)
SELECT an_id,
       -- 仅统计2019年的订单数，不满足条件的返回NULL，COUNT自动忽略
       COUNT(CASE WHEN year = 2019 THEN ord_id END) AS year2019,
       COUNT(CASE WHEN year = 2020 THEN ord_id END) AS year2020
FROM helper
GROUP BY an_id -- 仅按an_id分组，确保每个an_id一行
ORDER BY an_id;

方案2：简化为单查询（推荐） #

直接在主查询中完成条件聚合，无需CTE，同时改用LEFT JOIN确保无订单的an_id也能显示（若不需要可换回INNER JOIN）：

SELECT a.an_id,
       COUNT(CASE WHEN EXTRACT(year FROM o.ord_datetime) = 2019 THEN o.ord_id END) AS year2019,
       COUNT(CASE WHEN EXTRACT(year FROM o.ord_datetime) = 2020 THEN o.ord_id END) AS year2020
FROM analysis a
LEFT JOIN orders o 
    ON o.ord_an = a.an_id 
    AND EXTRACT(year FROM o.ord_datetime) IN (2019, 2020)
GROUP BY a.an_id
ORDER BY a.an_id;

原理说明 #

• 条件聚合中，CASE WHEN仅在满足年份条件时返回ord_id，否则返回NULL；
• COUNT()函数会自动忽略NULL值，因此只会统计对应年份的有效订单数；
• 仅按an_id分组，确保每个an_id生成唯一一行数据，同时展示两年的订单统计结果。

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