使用HiveQL计算PSI(Population Stability Index）

1.群体稳定性指标

群体稳定性指标(Population Stability Index，PSI),通常用于检验模型及数据分布的稳定性。其计算公式如下：

psi = sum(（实际占比-预期占比）* ln(实际占比/预期占比))

2.HiveQL计算PSI

以下PSI计算公式主要是为了计算一些模型中的各个指标的稳定性，其主要解决了利用当天的标签分布和上一个有效日期（比如：有数据）的标签的稳定性PSI计算。

以下代码段假设psi_bin_rate_data中已经计算好了每个features的各个分箱的实际概率。该表的表结构如下：

字段名	字段释义
features	特征名
features_name	特征中文释义
bins	分箱
rate	分箱占比
date_time	日期

方法一：

select features,features_name,date_time,sum(psi_bins) as psi
from (select features,features_name,bins,(last_rate-rate)*ln(last_rate/rate) as psi_bins,date_time
	from (select features,features_name,bins,rate,
			lead(rate,1，0.000001)over(partition by features,features_name,bins order by date_time desc) as last_rate,
			date_time
		from psi_bins_rate_data)a
	)a
group by features,features_name,date_time

这种方法代码量最小，但是有一个缺陷。举个例子，现在feature='a01'在以下两个日期的分箱数据如下：

bins	date_time='2020-03-05'	date_time='2020-03-04'
[0,2]	0.5	0.6
[3,5]	0.3	0.2
[6,8]	0.1	0.2
>8	0.1

如果在psi_bin_rate_data中feature='a01'没有出现date_time='2020-03-04',分箱为>8的记录的话，按照lead()函数的意义，它会一直往下寻找，假如直到date_time=‘2020-03-01’，feature='a01'才有分箱为>8的记录的话，则会把这条记录中对应的rate拿过来计算，这个时候显然就不正确了。基于此，延伸出了方法二。

方法二：

select features,features_name,date_time,sum(psi_bins) as psi
from (
	select features,features_name,bins,(last_rate-rate)*ln(last_rate/rate) as psi_bins
	from 
		(select features,features_name,bins,rate,
			first_value(rate)over(partition by feature,feature_name,bins order by rn asc range between 1 preceding and current row) as last_rate,
			date_time
		from (select *,dense_rank()over(order by date_time desc) rn 
			from psi_bins_rate_data)a
		)a
	)a
group by features,features_name,date_time

在窗口函数中使用range,可以将日期固定，但需要先对日期进行排序编码。这种方法顺利填了方法一的坑，但这种方法也不是万能的。 举个例子，现在feature='a01'在以下两个日期的分箱数据如下：

bins	date_time='2020-03-05'	date_time='2020-03-04'
[0,2]	0.5	0.6
[3,5]	0.3	0.19
[6,8]		0.01
>8	0.2	0.3

如果在psi_bin_rate_data中feature='a01'没有出现date_time='2020-03-05',分箱为[6,8]的记录的话，使用方法二计算PSI仍然是不准确的。因为在最后求和各个bins_psi的死后, 只计算了[0,2]，[3,5]，>8 这三个分箱的bins_psi。所以计算出的PSI不准确。基于此，想到了方法三。

顺便，为什么方法二中的窗口函数用first_value,而不用lead，参考https://blog.csdn.net/yeshang_lady/article/details/104488404

方法三：

select features,features_name,sum(psi_bins) psi 
from (
	select features,features_name,bins,(last_rate-rate)*ln(last_rate/rate) psi_bins 
	from (
		select coalesce(x.features,y.features) features,
			coalesce(x.features_name,y.features_name) features_name, 
			coalesce(x.bins,y.bins) bins, nvl(x.rate,0.0001) rate,
			nvl(y.rate,0.0001) last_rate 
		from (select features,features_name,bins,rate 
			from (
				select *,dense_rank() over(order by date_time desc) rn 
				from psi_bins_rate_data) x 
				where rn=1
				)x 
			full outer join 
			(select features,features_name,bins,rate 
			from (
				select *,dense_rank() over(order by date_time desc) rn 
				from psi_bins_rate_data) y 
				where rn=2
				)y
			on x.features=y.features and x.bins=y.bins
		)t
	)t 
group by features,features_name 

相比前两种方法，方法三在PSI的计算上是正确的，但这种方法只能求date_time最大(或最小，dense_rank()over()改为正序排序即可)相比于前一天的PSI计算。

总结

• 如果缺失的分箱占比通常比较小的话，这三种方法计算出来的PSI，误差在可接受的范围内。
• 目前，我认为我在各个方法的缺陷郡来自于psi_bin_rate_data。因为在这张表中，如果某一个分箱下的rate为0，那么这个分箱是该表中是不会有记录的。而正是由于分箱数据不完备，导致计算出的PSI数据不准确。如果我们从psi_bin_rate_data 表入手，将分箱占比为0的记录也保留下来，那么计算PSI将会非常简单，直接方法一就可以解决了。