SQL自连接统计同客户跨年度同月交易数结果相同问题排查求助

问题核心原因 #

• 自连接产生笛卡尔积：你直接对原始交易表做自连接时，若同一用户同月份2021年有M笔交易、2019年有N笔交易，连接后会生成MN行关联数据，此时对两个表的transaction_id做count统计得到的结果都是MN，自然两个计数完全相同。
• 语法细节错误：SQL存在多处笔误，比如r2021transdate应为r2021.transdate，两个count统计项之间遗漏了逗号，部分数据库下datepart的参数顺序可能不符合规范（比如部分数据库是datepart(unit, date)，你写的是datepart(date, unit)需要确认适配）。

修复方案 #

推荐两种更可靠的实现方式：

方案1：先聚合再关联（逻辑和原有思路一致，修复笛卡尔积问题） #

先分别统计2019、2021年每个用户每月的交易笔数，再对相同用户、相同月份做关联，避免直接关联原始交易表产生冗余行。

WITH 
trans_2021 AS (
    SELECT 
        accountid,
        DATEPART(month, transdate) AS trans_month,
        COUNT(transaction_id) AS trans_count
    FROM rewards
    WHERE DATEPART(year, transdate) = 2021
    GROUP BY accountid, DATEPART(month, transdate)
),
trans_2019 AS (
    SELECT 
        accountid,
        DATEPART(month, transdate) AS trans_month,
        COUNT(transaction_id) AS trans_count
    FROM rewards
    WHERE DATEPART(year, transdate) = 2019
    GROUP BY accountid, DATEPART(month, transdate)
)
SELECT 
    2021 AS `year`,
    t21.trans_month,
    SUM(t21.trans_count) AS transidcount2021,
    SUM(t19.trans_count) AS transidcount2019
FROM trans_2021 t21
INNER JOIN trans_2019 t19
    ON t21.accountid = t19.accountid
    AND t21.trans_month = t19.trans_month
GROUP BY t21.trans_month

方案2：条件聚合（性能更优，无需自连接） #

直接对全表扫描一次，用条件判断分别统计两个年份的交易数，自动过滤掉不同时存在两个年份交易的用户。

SELECT 
    trans_month,
    SUM(trans_2021_cnt) AS transidcount2021,
    SUM(trans_2019_cnt) AS transidcount2019
FROM (
    SELECT 
        accountid,
        DATEPART(month, transdate) AS trans_month,
        COUNT(CASE WHEN DATEPART(year, transdate) = 2021 THEN transaction_id END) AS trans_2021_cnt,
        COUNT(CASE WHEN DATEPART(year, transdate) = 2019 THEN transaction_id END) AS trans_2019_cnt
    FROM rewards
    WHERE DATEPART(year, transdate) IN (2019, 2021)
    GROUP BY accountid, DATEPART(month, transdate)
) t
WHERE trans_2021_cnt > 0 AND trans_2019_cnt > 0
GROUP BY trans_month

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