Snowflake SQL中MIN与FIRST_VALUE窗口函数分组取数问题求解

原有SQL存在的问题 #

• 未做去重处理，查询结果会返回每个pro_id对应的所有原始行，无法得到每个pro_id唯一一条的结果
• first_d_date的逻辑存在两处错误：一是case判断的是当前行的sto_id，而非分组内o_date最小行的sto_id；二是带ORDER BY的min(d_date)窗口函数计算的是累计最小值，而非整个分组的非空d_date值

修正后的SQL（兼容MySQL8+/PostgreSQL/Oracle等支持窗口函数的数据库） #

WITH pro_first_info AS (
    SELECT 
        pro_id,
        sto_id AS first_sto_id,
        o_date AS min_o_date,
        d_date AS first_row_d_date,
        -- 取整个pro_id分组内的非空d_date最小值，无符合值则为NULL
        MIN(d_date) OVER (PARTITION BY pro_id) AS group_notnull_d_date,
        -- 按o_date升序打行号，行号1对应每个pro_id o_date最小的行
        ROW_NUMBER() OVER (PARTITION BY pro_id ORDER BY o_date ASC) AS rn
    FROM tab1
)
SELECT 
    first_sto_id,
    pro_id,
    min_o_date,
    CASE 
        WHEN first_sto_id != 30 THEN first_row_d_date
        ELSE COALESCE(first_row_d_date, group_notnull_d_date)
    END AS first_d_date
FROM pro_first_info
WHERE rn = 1;

逻辑说明 #

1. CTE层提前计算每个pro_id的全部分组属性，避免多层窗口函数嵌套
2. 外层过滤仅保留每个pro_id o_date最小的行，自然实现按pro_id去重
3. COALESCE函数自动处理优先取首行d_date、首行d_date为空再取分组非空d_date的逻辑，符合规则要求

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