LEAD函数在数据分析中的应用

转载

SQL数据库开发 2022-03-24 15:22:15

点击上方SQL数据库开发，关注获取SQL视频教程

SQL专栏

SQL数据库基础知识汇总

SQL数据库高级知识汇总

Vintage分析

Vintage分析用到信贷资产行业，指的是每个月贷款的资产质量情况，要直接跟每个相同时间段内的余额做比较。注意这里比较有个前提，就是比较的事物应该是位于同一层面上的，不能将不同账龄的放款质量进行对比，要按账龄(month of book，MOB)的长短同步对比，从而了解同一产品不同时期放款的资产质量情况。Vintage分析方法能很好地解决时滞性问题，其核心思想是对不同时期的开户的资产进行分别跟踪，按照账龄的长短进行同步对比，从而了解不同时期发行信用卡的资产质量情况。

需求描述

Vintage分析目前被广泛应用于信用卡产业。举例说明根据账龄所做的拖欠二周期账户的Vintage分析，原始数据见表1：

LEAD函数在数据分析中的应用_sql数据库

LEAD函数在数据分析中的应用_sql_02

第一行数据意思是：数据2.12%为2018年4月所发信用卡在2018年7月时拖欠二周期的金额除以该批信用卡在2018年7月时透支余额，依此类推，得到全表的数据。在此基础上，按照账龄为经营时间减去发卡时间进行表间数据的转换，得到MOB，得到表2：

LEAD函数在数据分析中的应用_数据_03

通过vintage报表，可以看出，不同月份的发卡账户的同一mob下的拖欠率的变化情况。

这里我们的需求是：怎么将表1格式的数据转换成表2格式的数据？

需求实现

插入数据

同样这里假定已经存在表WN_table，含有date_faka、date_mob、overduerate这3个字段：

insert into WN_table values('2018-04', '2018-07', '2.12');
insert into WN_table values('2018-04', '2018-08', '2.19');
insert into WN_table values('2018-04', '2018-09', '3.1 ');
insert into WN_table values('2018-04', '2018-10', '2.58');
insert into WN_table values('2018-04', '2018-11', '2.65');
insert into WN_table values('2018-04', '2018-12', '2.84');
insert into WN_table values('2018-05', '2018-08', '2.47');
insert into WN_table values('2018-05', '2018-09', '2.52');
insert into WN_table values('2018-05', '2018-10', '2.53');
insert into WN_table values('2018-05', '2018-11', '2.52');
insert into WN_table values('2018-05', '2018-12', '2.49');
insert into WN_table values('2018-06', '2018-09', '1.63');
insert into WN_table values('2018-06', '2018-10', '1.88');
insert into WN_table values('2018-06', '2018-11', '1.87');
insert into WN_table values('2018-06', '2018-12', '2.1 ');
insert into WN_table values('2018-07', '2018-10', '3.32');
insert into WN_table values('2018-07', '2018-11', '3.88');
insert into WN_table values('2018-07', '2018-12', '3.46');
insert into WN_table values('2018-08', '2018-11', '2.37');
insert into WN_table values('2018-08', '2018-12', '1.46');
insert into WN_table values('2018-09', '2018-12', '2.51');

（提示：可以左右滑动代码）

Vintage报表

这里我们仍然使用LEAD窗口函数实现我们的需求：

如果对LEAD函数使用有疑问的朋友，可以先阅读LEAD函数的具体介绍：

SQL Server中的LAG函数与LEAD函数介绍

select
date_faka, M3,M4, M5, M6, M7, M8
from(select date_faka, overduerate M3, 
lead(overduerate,1) over(partition by date_faka order by date_mob) M4, 
lead(overduerate,2) over(partition by date_faka order by date_mob) M5, 
lead(overduerate,3) over(partition by date_faka order by date_mob) M6, 
lead(overduerate,4) over(partition by date_faka order by date_mob) M7, 
lead(overduerate,5) over(partition by date_faka order by date_mob) M8, 
row_number() over(partition by date_faka order by date_mob) row_num 
from WN_table) t
where row_num = 1

得到vintage报表：

LEAD函数在数据分析中的应用_sql_04

当然，也可以不将NULL呈现出来：

select
date_faka, 
case when M3 is null then '' else M3 end M3,
case when M4 is null then '' else M3 end M4,
case when M5 is null then '' else M3 end M5,
case when M6 is null then '' else M3 end M6,
case when M7 is null then '' else M3 end M7,
case when M8 is null then '' else M3 end M8
from(select date_faka, overduerate M3, 
lead(overduerate,1) over(partition by date_faka order by date_mob) M4, 
lead(overduerate,2) over(partition by date_faka order by date_mob) M5, 
lead(overduerate,3) over(partition by date_faka order by date_mob) M6, 
lead(overduerate,4) over(partition by date_faka order by date_mob) M7, 
lead(overduerate,5) over(partition by date_faka order by date_mob) M8, 
row_number() over(partition by date_faka order by date_mob) row_num 
from WN_table) t
where row_num = 1