SQL语句修正需求：跨表提取日期关联列结果异常

问题原因 #

你的SQL同时左连接cases_asgn和lor_rcv两个表，会导致两个表的记录形成笛卡尔积，使得COUNT统计时重复计算记录，最终结果不符合预期。

方案一：提前聚合子查询（推荐） #

这种方式先分别对两个业务表按日期完成统计，再与日期表关联，从根源避免笛卡尔积问题：

SELECT
    dd.dates,
    COALESCE(lor.LOR_Received, 0) AS LOR_Received,
    COALESCE(asg.Cases_Asgn, 0) AS Cases_Asgn
FROM
    jup_nipo_analytics_fms_workspace.dimdate dd
LEFT JOIN (
    SELECT
        LOR_Receipt_Dt,
        COUNT(cocom) AS LOR_Received
    FROM
        jup_nipo_analytics_fms_workspace.lor_rcv
    GROUP BY
        LOR_Receipt_Dt
) lor ON lor.LOR_Receipt_Dt = dd.dates
LEFT JOIN (
    SELECT
        Caseasgn_Dt,
        COUNT(Case) AS Cases_Asgn
    FROM
        jup_nipo_analytics_fms_workspace.cases_asgn
    GROUP BY
        Caseasgn_Dt
) asg ON asg.Caseasgn_Dt = dd.dates
WHERE
    dd.dates = '4/27/2023'

• COALESCE函数用于处理某日期在对应业务表无数据的情况，确保返回0而非NULL。

方案二：使用COUNT(DISTINCT)（适用于有唯一主键的场景） #

如果cases_asgn和lor_rcv表存在唯一主键字段（比如id），可以通过去重计数避免重复统计：

SELECT
    dd.dates,
    COUNT(DISTINCT lor.id) AS LOR_Received, -- 替换为lor表实际唯一主键
    COUNT(DISTINCT asg.id) AS Cases_Asgn    -- 替换为asg表实际唯一主键
FROM
    jup_nipo_analytics_fms_workspace.dimdate dd
LEFT JOIN jup_nipo_analytics_fms_workspace.cases_asgn asg ON asg.Caseasgn_Dt = dd.dates
LEFT JOIN jup_nipo_analytics_fms_workspace.lor_rcv lor ON lor.LOR_Receipt_Dt = dd.dates
WHERE
    dd.dates = '4/27/2023'
GROUP BY
    dd.dates

• 注意：这种方式会先产生笛卡尔积再去重，性能低于方案一，仅在无法使用子查询的场景下临时替代。

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