巧用SQL開(kāi)窗函數(shù)查找日期重疊數(shù)據(jù)疑點(diǎn)

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

一、審計(jì)需求

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

圖1

二、問(wèn)題分析

對(duì)于貸款期間沒(méi)有重疊的，某一時(shí)點(diǎn)的貸款余額就是該筆貸款的金額，只需要加條件語(yǔ)句就可以查詢出來(lái)。但對(duì)于貸款期間有重疊的，在日期重疊期間的貸款余額則為多筆貸款金額匯總。

三、解決思路

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

四、解決方法

（一）開(kāi)窗函數(shù)運(yùn)用方法

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

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

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

OVER關(guān)鍵字后的括號(hào)中還經(jīng)常添加選項(xiàng)用以改變進(jìn)行聚合運(yùn)算的窗口范圍。

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

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

（二）實(shí)現(xiàn)過(guò)程

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

圖2

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

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

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

3.數(shù)據(jù)A和B重疊應(yīng)滿足下一條記錄中的借款日應(yīng)當(dāng)小于上一條記錄的到期日，這樣才能形成貸款日期重疊，因此限定條件“b.借款日

圖3

select a.*from

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

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

join

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

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

on a.身份證號(hào)=b.身份證號(hào)and a.num+1=b.num

where b.借款日

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