在Snowflake中按国家与级别计算中位数及四分位数（含空值处理）

核心处理逻辑 #

• 用NVL(Value, 0)将Value列空值统一替换为0
• 通过UNPIVOT把多列的等级字段（Gold Level、Silver Level、Diamond Level）转成行结构，实现按Country+等级的分组需求
• 利用Snowflake分位函数计算统计值，推荐PERCENTILE_CONT（连续型分位，适配后续编码的数值计算场景），若需取数据集中实际值可改用PERCENTILE_DISC

完整SQL代码 #

WITH cleaned_data AS (
    -- 替换空值，标准化基础数据集
    SELECT 
        ID,
        Country,
        "Gold Level" AS gold_level,
        "Silver Level" AS silver_level,
        "Diamond Level" AS diamond_level,
        NVL(Value, 0) AS value
    FROM your_dataset_name
),
unpivoted_data AS (
    -- 将列维度的等级转为行维度，方便分组统计
    SELECT 
        Country,
        level_type,
        value
    FROM cleaned_data
    UNPIVOT (
        level_flag FOR level_type IN (gold_level, silver_level, diamond_level)
    )
    WHERE level_flag = 1 -- 假设等级列是标识位（1表示属于该等级），若为其他逻辑需调整此条件
)
-- 分组计算四分位数与中位数
SELECT 
    Country,
    level_type AS level,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY value) AS q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY value) AS q3
FROM unpivoted_data
GROUP BY Country, level_type;

输出结果示例 #

关键注意点 #

• 若等级列不是标识位（如直接存储等级名称），需调整UNPIVOT逻辑，确保level_type能准确区分不同等级
• PERCENTILE_CONT与PERCENTILE_DISC的选择：前者返回插值后的连续值，后者返回数据集中的实际离散值，按需选择即可
• 替换代码中的your_dataset_name为实际表名/视图名

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