在SQL中计算生存率

谢绝转载

根据美国2009年美国人口统计局数据，绘制平均寿命曲线：不同性别以及不同肤色的人口随年龄变化的生存率。

第一次体现了生存率和风险率的概念，以及递归的概念。为了保持公式的连贯性，使用 IF() 和 ISNUMBER()。

项目使用的数据是来自三个移动电话运营商的数据，对客户姓名进行了脱敏。生存分析依赖关于客户的两个信息：标记客户是停止还是活跃和客户任期（客户的活跃市场）。

数据探索

发现数据是左截断的，所以下面都限定  >= '2004-01-01' 。这里用UNION ALL和赋常数，将两个查询合并为一个查询。

风险率 = 遭遇风险的客户数量/可能遇到风险的客户总数

在PG SQL中用AVG时，要用1.0，否则出来的都是整数（int）。连接用“||”。

遇到了一个小问题，PG SQL中的日期加减和MySQL，SQL Server中的都不同，要先更改日期形式，默认更改为TIMESTAMP (无时区)格式，用“::”很方便。 

这里对于时间的处理很像我之前做的一道笔试SQL题，https://blog.csdn.net/dufemt/article/details/81416729 。自连接，加三天，当时觉得很厉害的技巧。

任意任期的生存率

 如图，在Excel中，反向累加用$固定。要实现递归计算要用到PEODUCT，但是在SQL中没有这个函数，怎么办？

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

SQL代码：

CTE计算每个任期的停止数（numstops）和客户数量（tenurepop）。
风险人口总数=大于等于任期的所有客户数量的总和（pop）。用反向累加 tenure DESC
每个任期的风险率（h）等于停止数除以风险人口总数
生存率(s)的计算有两个部分：第一对乘积的计算；第二是在窗口函数中的额外子句，用来说明窗口的范围

 =========================
h：每个任期的风险率
s：生存率——为所有（1-h）的乘积，每一个后续的生存率等于上一个生存率值*（1减上一个风险率）。

列的某一行数据的计算，基于该列的前一行数据，这种类型的公式被成为递归公式。列方向上的一行行计算

在Excel中有两种方法：
--法1：
H29=IF($C29=0, 1, H28*(1-G28))
--法2：
I=1-h （每个任期的生存率）
J27=PRODUCT(I$26:I26)
...
J31=PRODUCT(I$26:I30) 

1.计算列值的乘积
可惜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。

窗口范围

 如何剔除当前行？控制窗口范围ROWS和RANG区别

按市场计算生存率

 对比不同的市场

 

未完。。。。