如何改写Snowflake左连接查询以保留无匹配的主表记录

问题根源 #

你的查询虽然用了LEFT JOIN关联SG_DRIVER_INCIDENTS（B表），但WHERE子句中对B表字段的过滤条件会自动排除B表无匹配的主表记录——因为当B表无匹配时，B的所有字段值都是NULL，而NULL参与任何比较判断都会返回UNKNOWN，最终被WHERE条件过滤掉。

解决方案 #

下面提供两种可行的改写方式，核心思路是避免在WHERE子句中直接过滤左连接的副表字段：

方案1：将B表的过滤条件移至LEFT JOIN的ON子句 #

把原本放在WHERE里的B表时间条件，放到左连接的ON关联条件中，这样只会筛选要和A表关联的B表记录，不会过滤A表本身的记录。同时优化日期处理逻辑（用Snowflake原生日期函数替代SUBSTRING，更高效且不易出错）：

SELECT 
    A.DRIVER_NAME AS DRIVER_NAME,
    A.DRIVER_ID AS DRIVER_ID,
    C.TRC_TERMINAL AS CSC,
    A.OBSERVATIONS AS OBSERVATIONS,
    A.INCIDENTS AS INCIDENTS,
    B.SPEED_LIMIT AS SPEED_LIMIT,
    B.SPEED AS SPEED,
    B.DIFFERENCE AS DIFFERENCE,
    A.REPORT_DATE AS REPORT_DATE,
    B.TIME AS TIME,
    
    CASE WHEN B.DIFFERENCE >= 6 AND B.DIFFERENCE <= 10 THEN '1' ELSE '0' END AS SIX_TEN_MPH,
    CASE WHEN B.DIFFERENCE > 10 AND B.DIFFERENCE <= 15 THEN '1' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
    CASE WHEN B.DIFFERENCE > 15 THEN '1' ELSE '0' END AS SIXTEEN_PLUS_MPH

FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
LEFT JOIN "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS" B
    ON A.DRIVER_ID = B.DRIVER_ID
    AND B.TIME BETWEEN '2022-07-01' AND '2022-07-31'
    AND DATE(B.TIME) <= A.REPORT_DATE                                          -- 改用DATE函数提取日期，替代SUBSTRING
    AND DATE(B.TIME) > DATEADD(week, -1, A.REPORT_DATE)                        -- 简化日期比较逻辑
LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
    ON B.Vehicle = C.TRC_NUMBER
WHERE 
    A.DRIVER_ID != ''  
    AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31'

方案2：先过滤B表再做左连接 #

先通过子查询筛选出符合时间条件的B表记录，再和A表做左连接，同样能保留A表无匹配的记录：

WITH filtered_incidents AS (
    SELECT *
    FROM "PROD"."PUBLIC"."SG_DRIVER_INCIDENTS"
    WHERE TIME BETWEEN '2022-07-01' AND '2022-07-31'
)
SELECT 
    A.DRIVER_NAME AS DRIVER_NAME,
    A.DRIVER_ID AS DRIVER_ID,
    C.TRC_TERMINAL AS CSC,
    A.OBSERVATIONS AS OBSERVATIONS,
    A.INCIDENTS AS INCIDENTS,
    B.SPEED_LIMIT AS SPEED_LIMIT,
    B.SPEED AS SPEED,
    B.DIFFERENCE AS DIFFERENCE,
    A.REPORT_DATE AS REPORT_DATE,
    B.TIME AS TIME,
    
    CASE WHEN B.DIFFERENCE >= 6 AND B.DIFFERENCE <= 10 THEN '1' ELSE '0' END AS SIX_TEN_MPH,
    CASE WHEN B.DIFFERENCE > 10 AND B.DIFFERENCE <= 15 THEN '1' ELSE '0' END AS ELEVEN_FIFTEEN_MPH,
    CASE WHEN B.DIFFERENCE > 15 THEN '1' ELSE '0' END AS SIXTEEN_PLUS_MPH

FROM "PROD"."PUBLIC"."SG_DRIVER_TREND" A
LEFT JOIN filtered_incidents B
    ON A.DRIVER_ID = B.DRIVER_ID
    AND DATE(B.TIME) <= A.REPORT_DATE
    AND DATE(B.TIME) > DATEADD(week, -1, A.REPORT_DATE)
LEFT JOIN "PROD"."PUBLIC"."TMW_TRACTORPROFILE" C
    ON B.Vehicle = C.TRC_NUMBER
WHERE 
    A.DRIVER_ID != ''  
    AND A.REPORT_DATE BETWEEN '2022-07-01' AND '2022-07-31'

注意事项 #

• 两种方案中，CASE语句里都明确用B.DIFFERENCE替代原查询的DIFFERENCE，避免当B为NULL时出现歧义；
• 用DATE(B.TIME)替代SUBSTRING(B.TIME, 0, 10)是更符合Snowflake最佳实践的写法，尤其是当TIME字段是TIMESTAMP类型时，转换更准确。

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