最近，在開展扶貧金融貸款審計工作中，筆者巧用T-SQL開窗函數(shù)，解決了從扶貧金融貸款流水數(shù)據(jù)中查找出所有同一貸款人多筆貸款期限起止日期有重疊這一難題。

一、審計需求

根據(jù)《湖北省扶貧小額信貸貼息項目管理辦法》（鄂政扶發(fā)[2014]22號）“對符合貸款條件的建檔立卡戶5萬元以下的貸款提供貼息補助”，審計需要篩選出某一時點貸款余額大于5萬元的貸款流水。由于同一建檔立卡貧困戶可能涉及多筆貸款，每筆貸款的起止日期不同，需要找出單筆貸款大于5萬元或同一貸款期間的多筆貸款金額大于5萬元的記錄。如圖1。

圖1

二、問題分析

對于貸款期間沒有重疊的，某一時點的貸款余額就是該筆貸款的金額，只需要加條件語句就可以查詢出來。但對于貸款期間有重疊的，在日期重疊期間的貸款余額則為多筆貸款金額匯總。

三、解決思路

如何找出有貸款日期重疊的記錄呢？首先對每個貸款人的貸款信息按借款日進行升序排序，然后用下一條記錄的借款日與上一條記錄的到期日進行比較，如果下一條記錄的借款日早于上一次記錄的到期日，說明日期有重疊。

四、解決方法

（一）開窗函數(shù)運用方法

通常一條查詢語句只會有一個窗口，只返回一個值。而開窗函數(shù)就是把滿足條件的數(shù)據(jù)分成幾部分，每一部分數(shù)據(jù)可以通過像現(xiàn)實中的“窗口”對行集組進行聚合計算，每組可以返回多個值。

開窗函數(shù)格式：row_number()over(partition by分組列order by排序列desc)

row_number()從1開始，為每一條分組記錄返回一個數(shù)字。

OVER關鍵字后的括號中還經(jīng)常添加選項用以改變進行聚合運算的窗口范圍。

PARTITION BY子句用來定義行的分區(qū)來供進行聚合計算。與GROUP BY子句不同，PARTITION BY子句創(chuàng)建的分區(qū)是獨立于結果集的，創(chuàng)建的分區(qū)只是供進行聚合計算的，而且不同的開窗函數(shù)所創(chuàng)建的分區(qū)，也不互相影響。

ORDER BY子句可以對結果集按照指定的排序規(guī)則進行排序，并且在一個指定的范圍內(nèi)進行聚合運算。

（二）實現(xiàn)過程

1.區(qū)分每個貸款人的每筆貸款。利用開窗函數(shù)對扶貧小額貸款明細按貸款人身份證號進行分組排序，得到數(shù)據(jù)A?？梢钥吹皆瓟?shù)據(jù)后增加了一列NUM，并按貸款人身份證號進行了分組編號。如圖2。

圖2

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

from dbo.丹江口市_扶貧辦_小額貸款_2016_2019

2.再次利用開窗函數(shù)對原數(shù)據(jù)進行分組編號，得到數(shù)據(jù)B。并利用貸款人身份證號及NUM實現(xiàn)將A和B數(shù)據(jù)建立關聯(lián)，關聯(lián)條件為“a.身份證號=b.身份證號and a.num+1=b.num”。

3.數(shù)據(jù)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.借款日

在實際工作中，考慮到存在貸款提前還款的情況，可以在條件語句中增加借款日與到期日間隔天數(shù)的來排除這種情況。