SQL环境下如何计算加权测试对照门店数据的中位数？

问题描述 #

摘要（TL;DR） #

我拥有测试指标列、对照指标列以及测试-对照权重列，可通过权重计算加权平均值，但下游仅能使用SQL计算环境，如何在SQL中计算加权中位数？

详细背景 #

我正在开展门店绩效的测试-对照分析。初始设计为22家测试门店，每家对应10家对照门店，每行数据权重设为1/10，确保每组测试门店及其对照组的权重总和为1.0。但部分对照门店不可用，导致部分测试门店仅匹配6、7、9家对照门店，我通过公式10/对照门店数量计算权重，并用以下SQL伪代码算出未加权与加权平均值：

/*** PSEUDOCODE ***/
SELECT location, 
       metric_name, 
       avg(metric_value) unwtd_avg,
       sum(metric_value * weights)/sum(weights) wtd_avg
FROM testcontrol
GROUP BY location, metric_name

该方法运行正常，但如何用类似方式计算加权中位数？我想到的暴力解法是：由于权重保留三位小数，将权重乘以1000得到整数，按该数值扩展数据集后取中位数。另外我尝试仿照加权平均值修改中位数函数，原函数为approx_quantiles(metric_value, 4)[OFFSET(2)]，修改为approx_quantiles(metric_value * weight, 4)[OFFSET(2)]，但验证未通过——这是因为该方法修改了行值，而非定位到中位数对应的行。

解决方案 #

方法1：基于累计权重的精确加权中位数 #

核心思路是通过窗口函数计算每行的累计权重，定位累计权重超过总权重50%的关键行，根据权重分布确定中位数。以下是适配场景的SQL示例：

WITH weighted_data AS (
    SELECT 
        location,
        metric_name,
        metric_value,
        weights,
        -- 计算分组内的总权重
        SUM(weights) OVER (PARTITION BY location, metric_name) AS total_weight,
        -- 按指标值排序，计算累计权重
        SUM(weights) OVER (PARTITION BY location, metric_name ORDER BY metric_value) AS cumulative_weight
    FROM testcontrol
),
median_candidates AS (
    SELECT 
        location,
        metric_name,
        metric_value,
        total_weight,
        cumulative_weight,
        -- 标记累计权重首次超过总权重一半的记录
        CASE WHEN cumulative_weight >= total_weight / 2.0 THEN 1 ELSE 0 END AS is_median_candidate
    FROM weighted_data
)
SELECT 
    location,
    metric_name,
    -- 处理两种情况：刚好有累计权重等于总权重一半时取前后值平均；否则取第一个超过阈值的值
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM median_candidates mc2 
            WHERE mc2.location = mc.location 
              AND mc2.metric_name = mc.metric_name 
              AND mc2.cumulative_weight = mc.total_weight / 2.0
        ) THEN (
            SELECT AVG(metric_value) FROM median_candidates mc3 
            WHERE mc3.location = mc.location 
              AND mc3.metric_name = mc.metric_name 
              AND (mc3.cumulative_weight = mc.total_weight / 2.0 OR mc3.cumulative_weight > mc.total_weight / 2.0)
        )
        ELSE (
            SELECT metric_value FROM median_candidates mc3 
            WHERE mc3.location = mc.location 
              AND mc3.metric_name = mc.metric_name 
              AND mc3.is_median_candidate = 1 
            ORDER BY cumulative_weight LIMIT 1
        )
    END AS weighted_median
FROM median_candidates mc
GROUP BY location, metric_name, total_weight;

该方法精准定位中位数位置，适合数据量适中的场景。

方法2：优化后的暴力扩展法 #

你提到的权重转整数扩展数据集的方法可以用SQL内置函数优化，避免手动处理重复行：

-- BigQuery 示例
WITH expanded_data AS (
    SELECT 
        location,
        metric_name,
        metric_value,
        -- 按权重*1000的整数次数生成重复行
        UNNEST(GENERATE_ARRAY(1, CAST(weights * 1000 AS INT64))) AS dummy
    FROM testcontrol
)
SELECT 
    location,
    metric_name,
    approx_quantiles(metric_value, 4)[OFFSET(2)] AS weighted_median
FROM expanded_data
GROUP BY location, metric_name;

注意：若权重小数位数超过3位，需调整乘数；数据量过大时该方法会占用较多资源，适合小数据集场景。

方法3：利用引擎原生加权分位数函数（部分SQL引擎支持） #

部分SQL引擎（如BigQuery、PostgreSQL）提供了支持权重的分位数函数，可直接调用：

-- PostgreSQL 示例：结合权重使用 PERCENTILE_CONT
SELECT 
    location,
    metric_name,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY metric_value) OVER (
        PARTITION BY location, metric_name 
        ORDER BY weights DESC
    ) AS weighted_median
FROM testcontrol
GROUP BY location, metric_name;

如果你的SQL引擎支持PERCENTILE_CONT或PERCENTILE_DISC的权重参数，这会是最简洁的实现方式。

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