PostgreSQL查询实现各产品价格四分位数计算及卖家价格分档

原有查询核心问题 #

• 窗口分区逻辑错误：需求是按cod_prod（每款产品）计算四分位数，但原有语句是按seller分区，导致分位数计算维度完全不符合要求
• 缺失分位阈值字段：没有单独计算每个产品的四个分位数值，所以无法输出1stQ到4thQ字段

正确SQL实现（兼容MySQL8.0+/PostgreSQL/BigQuery/Spark SQL等主流支持窗口函数的数据库） #

WITH prod_seller_price AS (
    -- 先聚合得到每个卖家对应产品的总价格，如无需对多日价格求和可直接取原表price字段
    SELECT 
        cod_prod,
        seller,
        SUM(price) AS sum_price
    FROM tablename
    GROUP BY cod_prod, seller
),
prod_quartiles AS (
    -- 计算每款产品的四个分位阈值，每个产品仅返回一行结果
    SELECT DISTINCT
        cod_prod,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY sum_price) OVER (PARTITION BY cod_prod) AS q1,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sum_price) OVER (PARTITION BY cod_prod) AS q2,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY sum_price) OVER (PARTITION BY cod_prod) AS q3,
        PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY sum_price) OVER (PARTITION BY cod_prod) AS q4
    FROM prod_seller_price
)
SELECT 
    psp.cod_prod AS Cod_Prod,
    psp.seller AS Seller,
    psp.sum_price AS Price,
    -- 根据分位阈值判断所属四分位区间，逻辑和示例输出完全一致
    CASE
        WHEN psp.sum_price <= pq.q1 THEN 1
        WHEN psp.sum_price <= pq.q2 THEN 2
        WHEN psp.sum_price <= pq.q3 THEN 3
        ELSE 4
    END AS Quartile,
    pq.q1 AS `1stQ`,
    pq.q2 AS `2ndQ`,
    pq.q3 AS `3rdQ`,
    pq.q4 AS `4thQ`
FROM prod_seller_price psp
JOIN prod_quartiles pq ON psp.cod_prod = pq.cod_prod
ORDER BY psp.cod_prod, Quartile DESC;

补充说明 #

• 若你的数据库不支持PERCENTILE_CONT，可改用PERCENTILE_DISC，二者区别为：PERCENTILE_CONT返回连续插值计算的分位值，PERCENTILE_DISC返回实际存在于数据集中的分位值，可根据业务需求选择
• 若你坚持使用NTILE函数，仅需要把原有语句的窗口分区字段改为PARTITION BY cod_prod即可，但NTILE是按行数量均分，当数据量不能被4整除时会出现前N组多一行的情况，和实际分位阈值的划分逻辑可能存在差异，更推荐使用上述阈值判断的方案

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