基于产品品类规模在PostgreSQL中实现产品权重与等级分配

现有table1表包含line、products、product variety、weight、rate字段，需实现以下两个逻辑：

1. 基于weight的对数计算权重，确保权重总和为100（对应Excel公式D2=LOG10(C2)/SUM(LOG10($C$2:$C$27))*100）
2. 根据权重计算等级rate：权重最大为1，次大为2，相同权重等级一致（对应Excel公式E2=SUMPRODUCT(($D2 < $D$2:$D$27)/COUNTIF($D$2:$D$27,$D$2:$D$27))+1）

完整SQL实现 #

WITH weighted_data AS (
    SELECT
        line,
        products,
        "product variety",
        weight,
        -- 计算归一化权重，总和为100
        (LOG10(weight) / SUM(LOG10(weight)) OVER ()) * 100 AS calculated_weight
    FROM table1
    WHERE weight > 0 -- 避免LOG10报错，确保weight为正
)
SELECT
    *,
    -- 密集排名：权重越高等级越小，相同权重等级相同
    DENSE_RANK() OVER (ORDER BY calculated_weight DESC) AS rate
FROM weighted_data;

关键逻辑说明 #

• 权重计算：使用窗口函数SUM(LOG10(weight)) OVER ()计算全表weight对数的总和，再按比例归一化到100，和Excel公式逻辑完全一致
• 等级计算：DENSE_RANK()窗口函数直接实现Excel公式的密集排名效果——相同权重的记录会获得相同等级，后续等级不会跳号，完全匹配SUMPRODUCT+COUNTIF的逻辑
• 注意事项：必须确保weight字段值为正数，否则LOG10()会抛出错误，可通过WHERE weight > 0过滤无效数据

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