无匹配关联列的两表按月聚合结果合并方案咨询

问题背景 #

两个数据表仅包含datetime列作为关联依据，无其他匹配列。尝试通过拼接生成的MonthYear别名进行JOIN时，不仅结果不符合预期，执行速度还极慢，但单独查询两个表却能瞬间完成。由于每月均有数据，且需求是按月统计并将两表结果放在相邻列（而非新增行），UNION无法满足需求。

原查询与结果 #

Response表查询 #

SELECT
LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2) AS 'MonthYear',
COUNT(CASE WHEN responseType = 'positive' THEN 1 END) AS 'Positive',
COUNT(CASE WHEN responseType = 'negative' THEN 1 END) AS 'Negative'
FROM Database.dbo.Response
WHERE [date] BETWEEN '2022/09/01' AND '2022/12/01'
GROUP BY LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2)
ORDER BY MAX([date]) 

查询结果：

Complaint表查询 #

SELECT 
LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2) AS 'MonthYear',
COUNT(CASE WHEN Reason = 'Legacy Unsub' THEN 1 END) AS 'Unsub',
COUNT(CASE WHEN Reason = 'Complaint' THEN 1 END) AS 'Complaint'
FROM Database.dbo.Complaint
WHERE [date] BETWEEN '2022/09/01' AND '2022/12/01'
GROUP BY LEFT(DATENAME(MONTH, [date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2)
ORDER BY MAX([date]) 

查询结果：

预期输出 #

优化解决方案 #

原JOIN速度慢的核心原因是GROUP BY使用了复杂的字符串拼接表达式，无法利用date列的索引，导致全表扫描。优化思路是先通过日期函数提取每月第一天作为关联键（比字符串更高效），同时保留MonthYear用于展示，最后将两个统计子查询通过该日期键JOIN。

优化后查询语句 #

WITH ResponseStats AS (
    SELECT
        -- 生成每月第一天作为关联与排序依据，可利用date列索引
        DATEFROMPARTS(YEAR([date]), MONTH([date]), 1) AS MonthStart,
        LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2) AS MonthYear,
        COUNT(CASE WHEN responseType = 'positive' THEN 1 END) AS Positive,
        COUNT(CASE WHEN responseType = 'negative' THEN 1 END) AS Negative
    FROM Database.dbo.Response
    WHERE [date] BETWEEN '2022/09/01' AND '2022/12/01'
    GROUP BY DATEFROMPARTS(YEAR([date]), MONTH([date]), 1), 
             LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2)
),
ComplaintStats AS (
    SELECT
        DATEFROMPARTS(YEAR([date]), MONTH([date]), 1) AS MonthStart,
        LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2) AS MonthYear,
        COUNT(CASE WHEN Reason = 'Legacy Unsub' THEN 1 END) AS Unsub,
        COUNT(CASE WHEN Reason = 'Complaint' THEN 1 END) AS Complaint
    FROM Database.dbo.Complaint
    WHERE [date] BETWEEN '2022/09/01' AND '2022/12/01'
    GROUP BY DATEFROMPARTS(YEAR([date]), MONTH([date]), 1), 
             LEFT(DATENAME(MONTH,[date]),3) + '-' + RIGHT('00' + CAST(YEAR([date]) AS VARCHAR),2)
)
SELECT
    rs.MonthYear,
    rs.Positive,
    rs.Negative,
    cs.Unsub,
    cs.Complaint
FROM ResponseStats rs
JOIN ComplaintStats cs ON rs.MonthStart = cs.MonthStart
ORDER BY rs.MonthStart

方案说明 #

1. CTE子查询：将两个表的月度统计逻辑拆分，让查询更清晰，同时避免重复计算。
2. 高效关联键：用DATEFROMPARTS生成的MonthStart（每月第一天）作为JOIN条件，比字符串MonthYear匹配更快，且能利用date列的索引。
3. 索引利用：GROUP BY时优先使用MonthStart，确保查询能命中date列的索引，大幅提升执行速度。

内容的提问来源于stack exchange，提问作者a.WOL7