巧用SQL开窗函数查找日期重叠数据疑点

最近，在开展扶贫金融贷款审计工作中，笔者巧用T-SQL开窗函数，解决了从扶贫金融贷款流水数据中查找出所有同一贷款人多笔贷款期限起止日期有重叠这一难题。

一、审计需求

根据《湖北省扶贫小额信贷贴息项目管理办法》（鄂政扶发[2014]22号）“对符合贷款条件的建档立卡户5万元以下的贷款提供贴息补助”，审计需要筛选出某一时点贷款余额大于5万元的贷款流水。由于同一建档立卡贫困户可能涉及多笔贷款，每笔贷款的起止日期不同，需要找出单笔贷款大于5万元或同一贷款期间的多笔贷款金额大于5万元的记录。如图1。

图1

二、问题分析

对于贷款期间没有重叠的，某一时点的贷款余额就是该笔贷款的金额，只需要加条件语句就可以查询出来。但对于贷款期间有重叠的，在日期重叠期间的贷款余额则为多笔贷款金额汇总。

三、解决思路

如何找出有贷款日期重叠的记录呢？首先对每个贷款人的贷款信息按借款日进行升序排序，然后用下一条记录的借款日与上一条记录的到期日进行比较，如果下一条记录的借款日早于上一次记录的到期日，说明日期有重叠。

四、解决方法

（一）开窗函数运用方法

通常一条查询语句只会有一个窗口，只返回一个值。而开窗函数就是把满足条件的数据分成几部分，每一部分数据可以通过像现实中的“窗口”对行集组进行聚合计算，每组可以返回多个值。

开窗函数格式：row_number()over(partition by分组列order by排序列desc)

row_number()从1开始，为每一条分组记录返回一个数字。

OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。

PARTITION BY子句用来定义行的分区来供进行聚合计算。与GROUP BY子句不同，PARTITION BY子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区，也不互相影响。

ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

（二）实现过程

1.区分每个贷款人的每笔贷款。利用开窗函数对扶贫小额贷款明细按贷款人身份证号进行分组排序，得到数据A。可以看到原数据后增加了一列NUM，并按贷款人身份证号进行了分组编号。如图2。

图2

select*,ROW_NUMBER()over(partition by身份证号order by借款日)num

from dbo.丹江口市_扶贫办_小额贷款_2016_2019

2.再次利用开窗函数对原数据进行分组编号，得到数据B。并利用贷款人身份证号及NUM实现将A和B数据建立关联，关联条件为“a.身份证号=b.身份证号and a.num+1=b.num”。

3.数据A和B重叠应满足下一条记录中的借款日应当小于上一条记录的到期日，这样才能形成贷款日期重叠，因此限定条件“b.借款日

图3

select a.*from

(select*,ROW_NUMBER()over(partition by身份证号order by借款日)num

from dbo.丹江口市_扶贫办_小额贷款_2016_2019)a

join

(select*,ROW_NUMBER()over(partition by身份证号order by借款日)num

from dbo.丹江口市_扶贫办_小额贷款_2016_2019)b

on a.身份证号=b.身份证号and a.num+1=b.num

where b.借款日

在实际工作中，考虑到存在贷款提前还款的情况，可以在条件语句中增加借款日与到期日间隔天数的来排除这种情况。