使用HiveQL计算PSI(Population Stability Index)

  • Post author:
  • Post category:其他



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将会非常简单,直接方法一就可以解决了。



版权声明:本文为yeshang_lady原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。