Oracle SQL 实现按ITEM分组的百分比聚合乘法计算

需求说明 #

原始数据 #

ITEM     PERCENTAGE
MOBILE | 2
MOBILE | 5
TABLET | 5
TABLET | 3

计算规则 #

按ITEM字段分组，对每组的PERCENTAGE执行计算：求所有(1 - PERCENTAGE/100)的乘积，即(1-PERCENTAGE_1%) * (1-PERCENTAGE_2%)（计算顺序不影响结果）

期望结果 #

ITEM     PERCENTAGE
MOBILE | 0.931
TABLET | 0.9215

实现方案 #

Oracle没有内置的聚合乘积函数，可通过对数转换实现乘积的聚合计算：乘积的自然对数等于各因子自然对数的和，再通过指数函数还原乘积。

假设数据存储在表your_table中，字段为ITEM和PERCENTAGE，对应的SQL语句如下：

SELECT 
    ITEM,
    ROUND(EXP(SUM(LN(1 - PERCENTAGE / 100))), 4) AS PERCENTAGE
FROM 
    your_table
GROUP BY 
    ITEM;

代码说明 #

1. 1 - PERCENTAGE / 100：将百分比转换为小数形式的剩余比例（比如2%转换为0.98）
2. LN()：对每个剩余比例取自然对数
3. SUM()：分组求和所有对数，等价于对所有剩余比例取对数后的乘积
4. EXP()：将求和后的对数结果还原为原始乘积
5. ROUND(..., 4)：按需求保留小数位数，确保结果和预期格式一致

特殊情况处理（可选） #

如果数据中存在PERCENTAGE = 100的记录，LN(0)会触发错误，可通过CASE语句过滤处理：

SELECT 
    ITEM,
    ROUND(EXP(SUM(LN(CASE WHEN PERCENTAGE <> 100 THEN 1 - PERCENTAGE / 100 ELSE 0 END))), 4) AS PERCENTAGE
FROM 
    your_table
GROUP BY 
    ITEM;

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