如何关联日期表与Lease表，按年月统计退租记录（含零值）

现有两张表：

• 一张包含2010至2050年所有日期的日期表（假设名为Date_Dim，含字段Date）
• Lease数据表，包含Move_Out_Date（退租日期）字段

需求是统计2019年每个月的退租记录数，要求全年12个月都显示，无退租记录的月份计数为0。原Group By查询仅能返回有退租记录的月份，尝试交叉连接或左外连接日期表时，计数结果异常偏大。

原查询代码：

SELECT
    YEAR(move_out_date) MOYear,
    MONTH(move_out_date) MOMonth,
    COUNT(move_out_date) AS Count
FROM 
    lease l
WHERE
    YEAR(move_out_date) = '2019'
GROUP BY 
    YEAR(move_out_date),
    MONTH(move_out_date)
ORDER BY
    YEAR(move_out_date),
    MONTH(move_out_date)

正确解决方案 #

核心思路是先提取2019年的所有唯一月份作为基础维度，再左连接Lease表统计对应月份的退租数，避免全量日期连接导致的重复计数。

方法1：基于日期表生成月份维度

WITH Year_Months AS (
    SELECT DISTINCT
        YEAR(Date) AS MOYear,
        MONTH(Date) AS MOMonth
    FROM Date_Dim
    WHERE YEAR(Date) = 2019
)
SELECT
    ym.MOYear,
    ym.MOMonth,
    COUNT(l.Move_Out_Date) AS Count
FROM Year_Months ym
LEFT JOIN Lease l
    ON YEAR(l.Move_Out_Date) = ym.MOYear
    AND MONTH(l.Move_Out_Date) = ym.MOMonth
GROUP BY ym.MOYear, ym.MOMonth
ORDER BY ym.MOYear, ym.MOMonth;

方法2：手动生成2019年12个月（无需依赖日期表）

如果不想依赖日期表，也可以直接生成12个月份的数据集：

WITH Year_Months AS (
    SELECT 2019 AS MOYear, 1 AS MOMonth UNION ALL
    SELECT 2019, 2 UNION ALL
    SELECT 2019, 3 UNION ALL
    SELECT 2019, 4 UNION ALL
    SELECT 2019, 5 UNION ALL
    SELECT 2019, 6 UNION ALL
    SELECT 2019, 7 UNION ALL
    SELECT 2019, 8 UNION ALL
    SELECT 2019, 9 UNION ALL
    SELECT 2019, 10 UNION ALL
    SELECT 2019, 11 UNION ALL
    SELECT 2019, 12
)
SELECT
    ym.MOYear,
    ym.MOMonth,
    COUNT(l.Move_Out_Date) AS Count
FROM Year_Months ym
LEFT JOIN Lease l
    ON YEAR(l.Move_Out_Date) = ym.MOYear
    AND MONTH(l.Move_Out_Date) = ym.MOMonth
GROUP BY ym.MOYear, ym.MOMonth
ORDER BY ym.MOYear, ym.MOMonth;

为什么之前的连接会导致计数偏大？ #

之前直接用全量日期表和Lease表连接时，每个退租记录会和日期表中对应月份的所有日期匹配，导致同一条退租记录被多次计数。先提取唯一月份维度再连接，就能避免这种重复统计的问题。

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