Snowflake查询：筛选Obj1重复且对应Obj2不同的记录

优化后的查询语句 #

WITH filtered_data AS (
    -- 筛选符合基础条件的记录，包含Table2关联判断
    SELECT 
        t1.Obj1,
        t1.Obj2,
        t1.Obj3,
        t1.Obj4,
        t1.Obj5
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.Obj2 = t2.Obj2
    WHERE 
        t1.Obj3 <> '1'
        AND t1.Obj4 <> '1'
        AND t1.Obj5 = '1'
        AND LEFT(t1.Obj2, 1) <> 'A'
        AND t1.Obj1 IS NOT NULL 
        AND t2.Obj1 IS NULL
),
obj1_stats AS (
    -- 计算每个Obj1的总记录数和唯一Obj2的数量
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY Obj1) AS total_records,
        COUNT(DISTINCT Obj2) OVER (PARTITION BY Obj1) AS distinct_obj2_count
    FROM filtered_data
)
-- 筛选出Obj1重复且Obj2无重复的记录
SELECT 
    Obj1,
    Obj2,
    Obj3,
    Obj4,
    total_records AS Count1
FROM obj1_stats
WHERE 
    total_records > 1
    AND total_records = distinct_obj2_count
ORDER BY total_records DESC;

逻辑说明 #

• filtered_data CTE：将原查询中重复的筛选条件统一处理，同时完成与Table2的关联校验，避免重复代码，提升可读性。
• obj1_stats CTE：通过窗口函数统计每个Obj1的总记录数，以及该Obj1下不同Obj2的数量。当两个数值相等时，说明该Obj1下所有记录的Obj2均唯一，无重复值。
• 最终查询：仅保留总记录数大于1（即Obj1存在重复）且Obj2无重复的记录，同时返回原需求中的Count1（即该Obj1的总记录数）。

优势说明 #

1. 消除重复的筛选条件，降低后续维护成本。
2. 用窗口函数替代子查询关联，逻辑更直观，Snowflake对窗口函数的优化也能提升查询性能。
3. 通过数值对比精准筛选目标数据，逻辑清晰不易出错。

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