Oracle SQL如何统计各月同时出现在当月及上月的number数量

你可以通过月度维度自关联的方式批量计算所有月份的统计值，无需逐个月份写硬编码条件，具体SQL如下：

WITH 
-- 提取table1所有月份的去重number，按月份聚合
t1_monthly AS (
    SELECT 
        DISTINCT 
        TRUNC(t1.date_col, 'MM') AS month_start,
        TO_CHAR(t1.date_col, 'MM-YYYY') AS month_str,
        t1.number
    FROM table1 t1
),
-- 提取table2所有月份的去重number，按月份聚合
t2_monthly AS (
    SELECT 
        DISTINCT 
        TRUNC(t2.date_col, 'MM') AS month_start,
        t2.number
    FROM table2 t2
)
-- 关联当月t1和上月t2的交集统计
SELECT 
    t1.month_str AS "Month",
    COUNT(DISTINCT t1.number) AS "count_number(current_month but also in previous month)"
FROM t1_monthly t1
INNER JOIN t2_monthly t2 
    ON t1.number = t2.number
    -- t1的当前月减1个月等于t2的月份，即匹配上月的t2数据
    AND ADD_MONTHS(t1.month_start, -1) = t2.month_start
GROUP BY t1.month_str, t1.month_start
ORDER BY t1.month_start;

补充说明 #

• 代码里将date字段写为date_col是因为DATE是Oracle保留关键字，如果你实际字段名就是date，使用时需要加双引号包裹，比如TRUNC("DATE", 'MM')
• 用TRUNC(date_col, 'MM')把日期统一截取到当月第一天，避免日期格式不统一导致的匹配错误，如果你存储的日期本身就是每月1号的固定值（和你示例里的'1-sep-21'一致），可以去掉TRUNC逻辑直接匹配，性能更好
• 当前逻辑会自动过滤无上月数据的最早统计月份，如果你需要展示这类月份且统计值为0，可以把INNER JOIN改成LEFT JOIN，再用NVL处理统计结果

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