SQL难题：查询人员近5年来每年持有贷款记录的天数_sql中计算两个借款天数的方法-CSDN博客

本文链接：https://blog.csdn.net/GXEXAX/article/details/134011275

一、遇到的问题

这是我一次查询DB2时碰到的题目，要求从如下表中统计每个人近5年来持有贷款的天数。（计算日期包括贷款发放日期到期日期当天，数据统计不包括今年2023年，即2018年-2022年5年。）计算时间表格大概如下（数据是随便编的）：

贷款表
姓名	贷款发放日期	贷款到期日期
小明	2016-01-01	2019-01-31
小明	2017-02-01	2017-05-31
小明	2018-08-01	2019-03-31
小明	2019-06-01	2020-03-31
小明	2020-09-01	2021-08-31
小明	2021-03-01	2023-11-30
小红	2016-02-01	2017-09-30
小红	2018-06-01	2021-07-31
小红	2019-03-01	2020-02-29
小红	2021-09-01	2023-08-31
小张	2018-12-01	2019-05-31
小张	2023-01-01	2024-01-01

要求查询结果如下：

结果表
姓名	年份	持有贷款天数
小明	2018	365
小明	2019	304
小明	2020	213
小明	2021	365
小明	2022	365
小红	2018	365
小红	2019	365
小红	2020	366
小红	2021	334
小红	2022	365
小张	2018	31
小张	2019	151
小张	2020	0
小张	2021	0
小张	2022	0

二、简单讲解一下我的思路：

（以下SQL使用DB2语言）

第一步：必须判断哪些数据是有用的。

由于统计日期范围仅为2018年-2022年，那么只需要计算在该范围内的数据就行，也就是

WHERE 贷款到期日期 >= '2018-01-01'
AND 贷款发放日期 < '2023-01-01'

第二步：判断当前时间是否有贷款比较麻烦，可以使用反向思维：查询没有贷款的空缺时间。

那么如何查询贷款的空缺时间呢？我们需要使用ROW_NUMBER()对贷款开始时间和到期时间分别从前到后进行排序。然后再将贷款的当前到期日期和下一个发放日期使用LEF JOIN进行比较，如果中间存在缺口，则意味着存在空缺时间。

SELECT STA_DATE.姓名, 贷款到期日期, 贷款发放日期 FROM (
	SELECT 姓名, 贷款发放日期, 
	ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 贷款发放日期) RN_START 
    FROM 贷款表 START_DATE
	WHERE 贷款到期日期 >= '2018-01-01'
	AND 贷款发放日期 < '2023-01-01'
) STA_DATE LEFT JOIN (
	SELECT 姓名, 贷款到期日期, 
    ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 贷款到期日期) RN_END 
    FROM 贷款表
	WHERE 贷款到期日期 >= '2018-01-01'
	AND 贷款发放日期 < '2023-01-01'
) END_DATE ON STA_DATE.姓名 = END_DATE.姓名 AND RN_START - 1 = RN_END
WHERE 贷款到期日期 < 贷款发放日期
ORDER BY STA_DATE.姓名

需要注意的是，这样会筛选掉两种情况下的贷款数据：

1.只有一笔贷款的客户

2.最早的一笔贷款发放数据以及最晚的一笔贷款到期数据

所以需要在两边分别加入这两项查询，将上面的SQL夹在中间。对于低于这两边的数据，可以使用范围去限制：

SELECT 姓名, 
CASE WHEN 贷款到期日期 < '2017-01-01' THEN '2017-01-01' 
     ELSE 贷款到期日期 END AS 贷款到期日期,
CASE WHEN 贷款发放日期 < '2017-01-01' THEN '2017-01-01' 
     ELSE 贷款发放日期 END AS 贷款发放日期 
FROM (
	SELECT 姓名, 
    TO_DATE(TO_CHAR(YEAR(MIN(贷款发放日期))) || '-01-01', 'YYYY-MM-DD') AS 贷款到期日期,         
    MIN(贷款发放日期) AS 贷款发放日期 FROM 贷款表
	WHERE 贷款到期日期 >= '2018-01-01'
	AND 贷款发放日期 < '2023-01-01'
	GROUP BY 姓名
)
UNION
SELECT 姓名, 贷款到期日期, 贷款发放日期 FROM (
	SELECT STA_DATE.姓名, 贷款到期日期, 贷款发放日期 FROM (
		SELECT 姓名, 贷款发放日期, 
		ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 贷款发放日期) RN_START 
        FROM 贷款表
		WHERE 贷款到期日期 >= '2018-01-01'
		AND 贷款发放日期 < '2023-01-01'
	) STA_DATE LEFT JOIN (
		SELECT 姓名, 贷款到期日期, 
        ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 贷款到期日期) RN_END 
        FROM 贷款表
		WHERE 贷款到期日期 >= '2018-01-01'
		AND 贷款发放日期 < '2023-01-01'
	) END_DATE ON STA_DATE.姓名 = END_DATE.姓名 AND RN_START - 1 = RN_END
	WHERE 贷款到期日期 < 贷款发放日期
	ORDER BY STA_DATE.姓名
)
UNION
SELECT 姓名, 
CASE WHEN 贷款到期日期 > '2023-12-31' THEN '2023-12-31' 
     ELSE 贷款到期日期 END AS 贷款到期日期,
CASE WHEN 贷款发放日期 > '2023-12-31' THEN '2023-12-31' 
     ELSE 贷款发放日期 END AS 贷款发放日期 
FROM (
	SELECT 姓名, MAX(贷款到期日期) AS 贷款到期日期, 
    TO_DATE(TO_CHAR(YEAR(MAX(贷款到期日期))) || '-12-31', 'YYYY-MM-DD') AS 贷款发放日期 
    FROM 贷款表
	WHERE 贷款到期日期 >= '2018-01-01'
	AND 贷款发放日期 < '2023-01-01'
	GROUP BY 姓名
)

这样我们就获得了第一个表，我们用WITH ... AS临时表储存上述SQL，将其命名为BET_DATE。

贷款缺口表（**BET_DATE）**
姓名	上一次贷款到期日期	下一次贷款发放日期
小明	2017-01-01	2017-01-01
小明	2019-03-31	2019-06-01
小明	2020-03-31	2020-09-01
小明	2023-11-30	2023-12-31
小红	2017-01-01	2018-06-01
小红	2021-07-31	2021-09-01
小红	2023-08-31	2023-12-31
小张	2017-01-01	2018-12-01
小张	2019-05-31	2023-01-01
小张	2023-12-31	2023-12-31

第三步：有些贷款缺口跨了几年，需要将他们分成同年的缺口

我们注意到一些贷款缺口跨年了，比如小张从2019年5月31日之后就没有再贷过款，为了分别统计每年未贷款的时间，需要将该字段分割成2019年、2020年、2021年、2022年四年。首先统计贷款发放年份和到期年份相同的，然后统计到期年份比发放年份多1-4年的。

SELECT 姓名, 贷款到期日期, 贷款发放日期 FROM BET_DATE
WHERE YEAR(贷款到期日期) = YEAR(贷款发放日期)
UNION 
SELECT 姓名, 贷款到期日期, TO_DATE(TO_CHAR(YEAR(贷款到期日期)) || '-12-31', 'YYYY-MM-DD') FROM BET_DATE
WHERE YEAR(贷款到期日期) <> YEAR(贷款发放日期)
UNION
SELECT 姓名, TO_DATE(TO_CHAR(YEAR(贷款发放日期)) || '-01-01', 'YYYY-MM-DD'), 贷款发放日期 FROM BET_DATE
WHERE YEAR(贷款到期日期) <> YEAR(贷款发放日期)
UNION
SELECT 姓名, TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 1) || '-01-01', 'YYYY-MM-DD'), 
TO_DATE(TO_CHAR(YEAR(贷款发放日期)) || '-12-31', 'YYYY-MM-DD') FROM BET_DATE
WHERE YEAR(贷款到期日期) > YEAR(贷款发放日期) + 1
UNION
SELECT 姓名, TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 2) || '-01-01', 'YYYY-MM-DD'), 
TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 2) || '-12-31', 'YYYY-MM-DD') FROM BET_DATE
WHERE YEAR(贷款到期日期) > YEAR(贷款发放日期) + 2
UNION
SELECT 姓名, TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 3) || '-01-01', 'YYYY-MM-DD'), 
TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 3) || '-12-31', 'YYYY-MM-DD') FROM BET_DATE
WHERE YEAR(贷款到期日期) > YEAR(贷款发放日期) + 3
UNION
SELECT 姓名, TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 4) || '-01-01', 'YYYY-MM-DD'), 
TO_DATE(TO_CHAR(YEAR(贷款发放日期) + 4) || '-12-31', 'YYYY-MM-DD') FROM BET_DATE
WHERE YEAR(贷款到期日期) > YEAR(贷款发放日期) + 4

那之后，计算每年的贷款日期差值，将其命名为NO_LOAN_DATE

SELECT 姓名, YEAR(贷款到期日期) AS NO_LOAN_YEAR, 
SUM(DAYS(贷款发放日期)-DAYS(贷款到期日期) - 1) AS NO_LOAN_DATE FROM (上述代码)

结果如下：

未贷款日期表（**NO_LOAN_DATE）**
姓名	未贷款年份	未贷款天数
小明	2017	-1
小明	2019	61
小明	2020	153
小明	2023	30
小红	2017	363
小红	2018	150
小红	2021	31
小红	2023	121
小张	2017	363
小张	2018	333
小张	2019	213
小张	2023	-2

只有手动添加的2017以及2023两年日期才可能有负数，可以不用管。

接下来就很简单了，给每个客户创建2017-2023年的日期表，用日期减去未贷款日期表就能获得每年未贷款日期。

WITH ALL_CSNO AS (
	SELECT 姓名 FROM 贷款表
	GROUP BY 姓名
)
, YEAR_ALL_LOAN AS (
	SELECT 姓名, '2018' AS LOAN_YEAR, DAYS(TO_DATE('2018-12-31', 'YYYY-MM-DD')) -                 
    DAYS(TO_DATE('2018-01-01', 'YYYY-MM-DD')) + 1 AS YEAR_DAYS FROM ALL_CSNO
	UNION
	SELECT 姓名, '2019' AS LOAN_YEAR, DAYS(TO_DATE('2019-12-31', 'YYYY-MM-DD')) - 
    DAYS(TO_DATE('2019-01-01', 'YYYY-MM-DD')) + 1 AS YEAR_DAYS FROM ALL_CSNO
	UNION
	SELECT 姓名, '2020' AS LOAN_YEAR, DAYS(TO_DATE('2020-12-31', 'YYYY-MM-DD')) - 
    DAYS(TO_DATE('2020-01-01', 'YYYY-MM-DD')) + 1 AS YEAR_DAYS FROM ALL_CSNO
	UNION
	SELECT 姓名, '2021' AS LOAN_YEAR, DAYS(TO_DATE('2021-12-31', 'YYYY-MM-DD')) - 
    DAYS(TO_DATE('2021-01-01', 'YYYY-MM-DD')) + 1 AS YEAR_DAYS FROM ALL_CSNO
	UNION
	SELECT 姓名, '2022' AS LOAN_YEAR, DAYS(TO_DATE('2022-12-31', 'YYYY-MM-DD')) - 
    DAYS(TO_DATE('2022-01-01', 'YYYY-MM-DD')) + 1 AS YEAR_DAYS FROM ALL_CSNO
)


SELECT YAL.姓名, LOAN_YEAR, YEAR_DAYS - COALESCE(NO_LOAN_DATE, 0) AS LOAN_DAYS 
FROM YEAR_ALL_LOAN YAL
LEFT JOIN NO_LOAN_DATE NLD 
ON YAL.姓名 = NLD.姓名 AND YAL.LOAN_YEAR = NLD.NO_LOAN_YEAR
ORDER BY YAL.姓名, YAL.LOAN_YEAR