如何避免在CASE子句中重复调用PERCENTILE_CONT以替换NULL为0？

可以通过子查询或公共表表达式（CTE）先一次性计算出每位客户的中位数，再在外层处理NULL值替换为0，这样就能避免重复调用PERCENTILE_CONT，提升查询性能。

基础优化版本（仅包含有交易记录的客户） #

WITH customer_medians AS (
    SELECT 
        customer,
        -- 仅调用一次PERCENTILE_CONT计算中位数
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY transamt) OVER (PARTITION BY customer) AS raw_median
    FROM trans_table
    WHERE trans_date BETWEEN DATEADD(WEEK, -52, GETDATE()) AND GETDATE()
)
SELECT 
    customer,
    -- 用COALESCE直接将NULL替换为0，比CASE更简洁高效
    COALESCE(raw_median, 0) AS median_amt
FROM customer_medians
-- 用GROUP BY去重，替代原查询的DISTINCT，性能更稳定
GROUP BY customer, raw_median;

或者用子查询实现，逻辑完全一致：

SELECT 
    customer,
    COALESCE(raw_median, 0) AS median_amt
FROM (
    SELECT 
        customer,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY transamt) OVER (PARTITION BY customer) AS raw_median
    FROM trans_table
    WHERE trans_date BETWEEN DATEADD(WEEK, -52, GETDATE()) AND GETDATE()
) AS sub_query
GROUP BY customer, raw_median;

扩展：包含所有客户（含无交易记录的） #

如果需要统计所有客户（包括过去52周没有交易的客户），需要关联客户表做左连接，确保无交易客户也能被返回并赋值为0：

WITH customer_medians AS (
    SELECT 
        c.customer,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.transamt) OVER (PARTITION BY c.customer) AS raw_median
    FROM customers c
    LEFT JOIN trans_table t 
        ON c.customer = t.customer
        AND t.trans_date BETWEEN DATEADD(WEEK, -52, GETDATE()) AND GETDATE()
)
SELECT 
    customer,
    COALESCE(raw_median, 0) AS median_amt
FROM customer_medians
GROUP BY customer, raw_median;

关键优化点 #

1. 仅调用一次PERCENTILE_CONT：将中位数计算逻辑放在子查询/CTE中，避免重复计算带来的性能损耗
2. 用COALESCE替代CASE：COALESCE是专门处理NULL值替换的函数，语法更简洁，执行效率也优于CASE语句
3. GROUP BY替代DISTINCT：针对窗口函数返回的重复结果，用GROUP BY去重比DISTINCT更高效，逻辑也更清晰

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