Oracle SQL高效实现按日期分组统计DATE_A与DATE_B出现次数

要实现按日期分组统计DATE_A和DATE_B的出现次数，且包含仅在某一列出现的日期（次数记为0），最高效的方式是通过一次表扫描完成数据处理，避免多次扫描带来的性能损耗，具体实现如下：

方案一：基于UNION ALL+条件聚合 #

WITH date_records AS (
    -- 将DATE_A和DATE_B拆分为单日期列，标记来源
    SELECT DATE_A AS target_date, 'A' AS col_source FROM your_table
    UNION ALL
    SELECT DATE_B AS target_date, 'B' AS col_source FROM your_table
)
SELECT
    target_date AS "DATE",
    -- 统计DATE_A中的出现次数
    COUNT(CASE WHEN col_source = 'A' THEN 1 END) AS COUNT_A,
    -- 统计DATE_B中的出现次数
    COUNT(CASE WHEN col_source = 'B' THEN 1 END) AS COUNT_B
FROM date_records
GROUP BY target_date
-- 按年月降序排序，与示例结果一致
ORDER BY TO_DATE(target_date, 'MM-YYYY') DESC;

方案二：基于UNION ALL+数值累加（性能相当） #

如果更习惯用数值累加的方式，也可以用以下写法，逻辑和效率与方案一一致：

WITH date_records AS (
    SELECT DATE_A AS target_date, 1 AS cnt_a, 0 AS cnt_b FROM your_table
    UNION ALL
    SELECT DATE_B AS target_date, 0 AS cnt_a, 1 AS cnt_b FROM your_table
)
SELECT
    target_date AS "DATE",
    SUM(cnt_a) AS COUNT_A,
    SUM(cnt_b) AS COUNT_B
FROM date_records
GROUP BY target_date
ORDER BY TO_DATE(target_date, 'MM-YYYY') DESC;

效率说明 #

这两个方案都只需要扫描原表一次，通过UNION ALL将两列的日期数据合并为行数据，再通过聚合函数一次性统计次数，避免了多次关联或扫描表的开销。如果你的表数据量较大，建议给DATE_A和DATE_B建立普通索引，能进一步提升查询速度。

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