Redshift添加筛选逻辑列后关联字段返回NULL问题求助

核心现象 #

在Redshift中执行查询时出现以下异常：

• 仅查询f#.market时，所有字段非空（已确认目标分组存在于所有filter_cte_#中）
• 取消注释右侧的筛选逻辑列后，所有f#.market字段变为NULL，且筛选逻辑仅生效最后一个filter_0_criteria的>=.5条件，完全忽略OR左侧分支
• 单独取消某一个筛选逻辑时，对应的f#.market也会变为NULL

问题原因 #

1. 无关联标量子查询的优化冲突 #

你使用的(select * from filter_#_criteria)是不依赖主查询上下文的标量子查询，Redshift会优先计算这些全局比例值，再执行主查询的LEFT JOIN。但查询优化器会误判逻辑依赖：当SELECT子句中引入这些标量计算时，优化器可能将原本的LEFT JOIN逻辑改写为类似INNER JOIN的行为——如果筛选逻辑因标量值判断返回FALSE，会直接过滤掉对应行，导致f#.market因无匹配行变为NULL。

2. 逻辑表达式的隐含陷阱 #

你的筛选逻辑((select * from filter_#_criteria) < .5 and f#.market is not null) or ((select * from filter_#_criteria) >= .5)看似恒为TRUE，但实际执行时：

• 当比例<.5时，依赖f#.market is not null为真，但优化器先计算标量值再判断关联字段，此时f#.market可能已因关联逻辑被改写变为NULL，导致整个表达式返回FALSE，最终过滤掉该行
• 多个独立标量子查询会被优化器合并计算，只有最后一个filter_0_criteria的逻辑被保留执行，其他逻辑被忽略

解决方法 #

方法1：将比例计算整合到CTE，关联主查询上下文 #

把每个filter_#_criteria的比例计算嵌入对应CTE，确保比例与分组关联：

WITH filter_cte_9_with_ratio AS (
    SELECT 
        market, speed_group, dprt_time_segment, company, season,
        (COUNT(*)::FLOAT / (SELECT COUNT(*)::FLOAT FROM filter_cte_9 WHERE year IN (2020,2021))) AS ratio
    FROM filter_cte_9
    WHERE year IN (2020,2021)
    GROUP BY market, speed_group, dprt_time_segment, company, season
),
-- 同理定义filter_cte_8_with_ratio至filter_cte_0_with_ratio
select_statement_with_key AS (
    SELECT 
        *,
        market || speed_group || dprt_time_segment || company || season AS group_key
    FROM select_statement
)
SELECT 
    ss.group_key,
    f9.market,
    -- 简化原逻辑：比例<0.5时保留非空值，否则全保留
    CASE WHEN f9.ratio < 0.5 THEN f9.market ELSE f9.market END AS f9_filtered_market,
    f8.market,
    CASE WHEN f8.ratio < 0.5 THEN f8.market ELSE f8.market END AS f8_filtered_market,
    -- 依次添加其他f#的字段与筛选列
FROM select_statement_with_key ss
LEFT JOIN filter_cte_9_with_ratio f9 
    ON ss.market = f9.market 
    AND ss.speed_group = f9.speed_group 
    AND ss.dprt_time_segment = f9.dprt_time_segment 
    AND ss.company = f9.company 
    AND ss.season = f9.season
-- 同理LEFT JOIN其他filter_cte_#_with_ratio
WHERE ss.group_key = 'specific_group_you_know_exists'

方法2：用统一CTE管理所有比例，通过JOIN引入 #

将所有filter_#_criteria的比例计算放到一个CTE中，通过笛卡尔积关联到主查询：

WITH all_filter_ratios AS (
    SELECT 9 AS filter_id, (COUNT(*)::FLOAT / (SELECT COUNT(*)::FLOAT FROM filter_cte_9 WHERE year IN (2020,2021))) AS ratio
    FROM filter_cte_9 WHERE year IN (2020,2021)
    UNION ALL
    SELECT 8, (COUNT(*)::FLOAT / (SELECT COUNT(*)::FLOAT FROM filter_cte_8 WHERE year IN (2020,2021))) FROM filter_cte_8 WHERE year IN (2020,2021)
    -- 依次添加filter_id 7至0的比例计算
),
select_statement_with_key AS (
    SELECT 
        *,
        market || speed_group || dprt_time_segment || company || season AS group_key
    FROM select_statement
)
SELECT 
    ss.group_key,
    f9.market,
    CASE WHEN (SELECT ratio FROM all_filter_ratios WHERE filter_id=9) < 0.5 AND f9.market IS NOT NULL 
         THEN f9.market 
         ELSE f9.market END AS f9_filtered_market,
    f8.market,
    CASE WHEN (SELECT ratio FROM all_filter_ratios WHERE filter_id=8) < 0.5 AND f8.market IS NOT NULL 
         THEN f8.market 
         ELSE f8.market END AS f8_filtered_market,
    -- 依次添加其他f#的字段与筛选列
FROM select_statement_with_key ss
LEFT JOIN filter_cte_9 f9 
    ON ss.market = f9.market 
    AND ss.speed_group = f9.speed_group 
    AND ss.dprt_time_segment = f9.dprt_time_segment 
    AND ss.company = f9.company 
    AND ss.season = f9.season
LEFT JOIN filter_cte_8 f8 
    ON ss.market = f8.market 
    AND ss.speed_group = f8.speed_group 
    AND ss.dprt_time_segment = f8.dprt_time_segment 
    AND ss.company = f8.company 
    AND ss.season = f8.season
-- 同理LEFT JOIN其他filter_cte_#
CROSS JOIN all_filter_ratios
WHERE ss.group_key = 'specific_group_you_know_exists'
GROUP BY ss.group_key, f9.market, f8.market,
         (SELECT ratio FROM all_filter_ratios WHERE filter_id=9),
         (SELECT ratio FROM all_filter_ratios WHERE filter_id=8)
-- 依次添加其他filter_id的比例至GROUP BY

方法3：简化逻辑表达式，明确依赖关系 #

你的原逻辑本质上恒为TRUE（无论比例大小总有一个分支成立），如果真实需求是比例<0.5时仅保留非空的f#.market，否则全保留，可简化为：

CASE WHEN ratio < 0.5 THEN NULLIF(f#.market, NULL) ELSE f#.market END

核心是避免让优化器误判逻辑依赖，确保关联行不会被错误过滤。

关键注意事项 #

• Redshift对无关联标量子查询的优化逻辑特殊，尽量将标量计算与主查询上下文绑定，避免独立的全局标量查询
• 多个独立标量子查询容易被优化器合并，改用CTE或JOIN方式引入计算值，确保优化器能正确识别依赖关系

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