如何按city_id和street_id分组计算客户price的占比列？

现有cust表结构及数据如下：

需求说明 #

需要新增percentile列，计算规则为：每条记录的price除以同一city_id和street_id分组下的price总和，示例计算：

• 100/(100+200+300) = 0.16667
• 200/(100+200+300) = 0.33333
• 300/(100+200+300) = 0.500

期望输出 #

尝试过使用percentile函数但未成功，寻求可行实现方案。

你用错函数了，percentile是用来计算分位数的，和你的需求不匹配。正确的做法是用窗口函数计算分组内的总价，再用当前行price除以该总价即可，以下是主流SQL数据库的实现：

支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等） #

SELECT 
    rn,
    cust_id,
    price,
    street_id,
    city_id,
    ROUND(price / SUM(price) OVER (PARTITION BY city_id, street_id), 5) AS percentile
FROM cust;

代码说明 #

• SUM(price) OVER (PARTITION BY city_id, street_id)：计算每个city_id+street_id分组的总价，窗口函数会把这个总价匹配到分组内的每一行
• ROUND(...,5)：按需求保留小数位数，可根据实际情况调整

不支持窗口函数的数据库（如MySQL 5.x） #

用子查询关联分组总价：

SELECT 
    c1.rn,
    c1.cust_id,
    c1.price,
    c1.street_id,
    c1.city_id,
    ROUND(c1.price / c2.group_total, 5) AS percentile
FROM cust c1
JOIN (
    SELECT city_id, street_id, SUM(price) AS group_total
    FROM cust
    GROUP BY city_id, street_id
) c2 ON c1.city_id = c2.city_id AND c1.street_id = c2.street_id;

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