在SQL中完成Excel中PRODUCT()功能

Excel中的PRODUCT函数将所有以参数形式给出的数字相乘，并返回乘积值。我在计算用户生存率的时候遇到了一个问题。

(具体计算方法见博客另一篇)

如下图

 G列为每个用户任期的风险率，生存率为所有（1-风险率）的乘积，每一个后续的生存率等于上一个生存率值*（1减上一个风险率）。

在Excel中可以使用PRODUCT，首先计算每一任期的生存率（1-风险率），并且通过 '$26' 固定第一个单元格，完成 '累乘' 。列的某一行数据的计算，基于该列的前一行数据，这种类型的公式被成为递归公式。列方向上的一行行计算。

 

 

 

 

 

可惜SQL中没有PRODUCT()聚合函数，使用对数：自然对数之和等于对数字做成绩
SELECT EXP(SUM(LN(1 - h)))

这个表达式求增量生存率的log值的和，然后反向计算log，这是一种迂回但是高效的计算集合乘积的方法。
e^(LN(1-h0)+LN(1-h1)+……+LN(1-hn)) = e^(LN(1期生存率*2期生存率*……*n期生存率) = 1期生存率*2期生存率*……*n期生存率

这个计算公式是一个简单化的公式，因为所有的风险率并非都非负且小于1.常见的聚合乘积要考虑正负数以及0
逻辑：

SELECT (1 - 2 * MOD(SUM(CASE WHEN col < 0 THEN 1 ELSE 0 END), 2)) *
             MIN(CASE WHEN col = 0 THEN 0 ELSE 1 END) * 
             SUM(EXP(LN(ABS(CASE WHEN col = 0 THEN 1 ELSE col END))))
第一个表达式处理结果标识，计算小于0的数值的个数，如果计数结果为偶数，输出1，如果为奇数，结果为-1
第二个表达式处理0值，如果为0，那么结果为0。
第三个表达式实际处理乘积运算，添加很多处理，防止LN()报错，ABS确保正数，CASE确保不为0。