如何关联Date_Table与WL_Table实现日期区间计数统计

核心思路 #

通过交叉关联+日期区间过滤，让Date_Table的每个日期，匹配WL_Table中所有处于该日期区间内的记录，再按日期和WL_Name分组统计数量。这种方式不需要直接匹配字段，而是通过日期是否落在WL_Ent_Dt与WL_Exit_Dt之间的逻辑关联两张表。

具体SQL实现 #

假设Date_Table的日期字段名为Calendar_Dt，提供两种实现方式，可根据数据库特性选择：

方式1：显式交叉关联+区间过滤 #

SELECT
    dt.Calendar_Dt,
    wl.WL_Name,
    COUNT(wl.Pri_Key) AS Daily_Count,
    SUM(COUNT(wl.Pri_Key)) OVER (PARTITION BY dt.Calendar_Dt) AS Total_Daily_Count
FROM
    Date_Table dt
CROSS JOIN
    WL_Table wl
WHERE
    dt.Calendar_Dt BETWEEN wl.WL_Ent_Dt AND wl.WL_Exit_Dt
GROUP BY
    dt.Calendar_Dt, wl.WL_Name
ORDER BY
    dt.Calendar_Dt, wl.WL_Name;

方式2：兼容空退出日期的版本 #

如果存在WL_Exit_Dt为空的记录（表示未退出，持续有效），可以调整WHERE条件覆盖这种场景：

SELECT
    dt.Calendar_Dt,
    wl.WL_Name,
    COUNT(wl.Pri_Key) AS Daily_Count,
    SUM(COUNT(wl.Pri_Key)) OVER (PARTITION BY dt.Calendar_Dt) AS Total_Daily_Count
FROM
    Date_Table dt,
    WL_Table wl
WHERE
    dt.Calendar_Dt >= wl.WL_Ent_Dt
    AND (dt.Calendar_Dt <= wl.WL_Exit_Dt OR wl.WL_Exit_Dt IS NULL)
GROUP BY
    dt.Calendar_Dt, wl.WL_Name
ORDER BY
    dt.Calendar_Dt, wl.WL_Name;

性能优化建议 #

由于WL_Table有近百万行数据，直接交叉关联可能存在性能瓶颈，建议做以下优化：

• 给WL_Table创建联合索引：CREATE INDEX idx_wl_date_name ON WL_Table(WL_Ent_Dt, WL_Exit_Dt, WL_Name);，让数据库快速定位符合日期区间的记录，避免全表扫描。
• 确保Date_Table的Calendar_Dt字段是主键或唯一索引，加速关联过程。
• 如果只需统计特定日期范围，在WHERE中先过滤Date_Table的日期（比如dt.Calendar_Dt BETWEEN '2020-01-01' AND '2024-01-01'），减少关联的数据量。

补充：统计所有WL_Name（含当日无数据的） #

如果需要展示所有WL_Name（包括当日没有匹配记录的，计数为0），可以用左关联结合去重的WL_Name列表实现：

SELECT
    dt.Calendar_Dt,
    wl_all.WL_Name,
    COALESCE(COUNT(wl.Pri_Key), 0) AS Daily_Count,
    SUM(COALESCE(COUNT(wl.Pri_Key), 0)) OVER (PARTITION BY dt.Calendar_Dt) AS Total_Daily_Count
FROM
    Date_Table dt
CROSS JOIN (SELECT DISTINCT WL_Name FROM WL_Table) wl_all
LEFT JOIN
    WL_Table wl ON dt.Calendar_Dt BETWEEN wl.WL_Ent_Dt AND COALESCE(wl.WL_Exit_Dt, '9999-12-31')
    AND wl_all.WL_Name = wl.WL_Name
GROUP BY
    dt.Calendar_Dt, wl_all.WL_Name
ORDER BY
    dt.Calendar_Dt, wl_all.WL_Name;

内容的提问来源于stack exchange，提问作者Steps to Wellbeing