SQL写法（版本1）
SELECT
DATE(PAYMENT_DATE),
SUM(CASE WHEN PERIOD = 1 AND OVERDUE_LONG > 8 THEN MONTH_CAPITAL ELSE NULL END)/SUM(MONTH_CAPITAL) MOB1, -- MOB1账龄1期
SUM(CASE WHEN (PERIOD = 1 AND OVERDUE_LONG > 15) OR (PERIOD = 2 AND OVERDUE_LONG > 8) THEN MONTH_CAPITAL ELSE NULL END)/SUM (MONTH_CAPITAL) MOB2, -- MOB2账龄2期
SUM(CASE WHEN (PERIOD = 1 AND OVERDUE_LONG > 22) OR (PERIOD = 2 AND OVERDUE_LONG > 15) OR (PERIOD = 3 AND OVERDUE_LONG > 8) THEN MONTH_CAPITAL ELSE NULL END)/SUM (MONTH_CAPITAL) MOB3， -- M0B3账龄3期
SUM(CASE WHEN (PERIOD = 1 AND OVERDUE_LONG > 29) OR (PERIOD = 2 AND OVERDUE_LONG > 22) OR (PERIOD = 3 AND OVERDUE_LONG > 15) OR (PERIOD = 4 AND OVERDUE_LONG > 8) THEN MONTH_CAPITAL ELSE NULL END)/ SUM (MONTH_CAPITAL) MOB4, -- MOB4账龄4期
FROM
(
SELECT
*,
CASE WHEN E.REPAY_STATUS = 1 THEN DATEDIFF(DATE(NOW()), DATE (E.REPAY_TIME))
WHEN E.REPAY_STATUS = 2 AND REMIND_END_DATE IS NULL THEN 0
WHEN E.REPAY_STATUS = 2 AND REMIND_END_DATE IS NOT NULL THEN DATEDIFF(DATE(REMIND_END_DATE), DATE(E.REPAY_TIME)) ELSE NULL END OVERDUE_LONG -- 逾期天数计算
FROM
(
SELECT * FROM BOR.CONTRACT WHERE CREATE_TIME >= '2019-08-01 00:00:00' AND PERIOD = 4
) A #合同表
LEFT JOIN
BOR_INSTALLMENT_PLAN D #还款计划表
ON A.APPLY_ID = D.APPLY_ID
LEFT JOIN
HUANKUAN E #实际还款表
ON A.APPLY_ID = E.APPLY_ID AND D.PERIOD = E.PERIOD
LEFT JOIN
CUISHOU F #催收表
ON A.CONTRACI_ID = F.CONTRACT_ID AND DATE(D.REPAY_TIME) = DATE_ADD(DATE(F.OVERDUE_DATE), INTERVAL - 1 DAY)
WHERE B.APPLY = 'DD' -- 产品
AND B.FIRST_AGAIN = 2 -- 新户老户
AND B.CREATE_TIME >= '2019-06-01 00:00:00' -- 借款时间限制
) BB
GROUP BY DATE(PAYMENT_DATE) -- 分组
ORDER BY DATE(PAYMENT_DATE) -- 排序;
SQL写法（版本2）
先建一个month表，明确月末时间点。
-- Create table `months`
--
CREATE TABLE months (
num int(11) NOT NULL,
yyyymm varchar(20) DEFAULT NULL,
start_datetime datetime DEFAULT NULL,
end_datetime datetime DEFAULT NULL,
PRIMARY KEY (num)
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 80,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;
--
-- Create index `idx_end_datetime` on table `months`
--
ALTER TABLE months
ADD UNIQUE INDEX idx_end_datetime (end_datetime);
--
-- Create index `idx_start_datetime` on table `months`
--
ALTER TABLE months
ADD UNIQUE INDEX idx_start_datetime (start_datetime);
--
-- Create index `idx_yyyymm` on table `months`
--
ALTER TABLE months
ADD UNIQUE INDEX idx_yyyymm (yyyymm);
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(61, '2019/01', '2019-01-01 00:00:00', '2019-01-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(62, '2019/02', '2019-02-01 00:00:00', '2019-02-28 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(63, '2019/03', '2019-03-01 00:00:00', '2019-03-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(64, '2019/04', '2019-04-01 00:00:00', '2019-04-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(65, '2019/05', '2019-05-01 00:00:00', '2019-05-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(66, '2019/06', '2019-06-01 00:00:00', '2019-06-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(67, '2019/07', '2019-07-01 00:00:00', '2019-07-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(68, '2019/08', '2019-08-01 00:00:00', '2019-08-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(69, '2019/09', '2019-09-01 00:00:00', '2019-09-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(70, '2019/10', '2019-10-01 00:00:00', '2019-10-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(71, '2019/11', '2019-11-01 00:00:00', '2019-11-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(72, '2019/12', '2019-12-01 00:00:00', '2019-12-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(73, '2020/01', '2020-01-01 00:00:00', '2020-01-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(74, '2020/02', '2020-02-01 00:00:00', '2020-02-29 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(75, '2020/03', '2020-03-01 00:00:00', '2020-03-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(76, '2020/04', '2020-04-01 00:00:00', '2020-04-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(77, '2020/05', '2020-05-01 00:00:00', '2020-05-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(78, '2020/06', '2020-06-01 00:00:00', '2020-06-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(79, '2020/07', '2020-07-01 00:00:00', '2020-07-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(80, '2020/08', '2020-08-01 00:00:00', '2020-08-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(81, '2020/09', '2020-09-01 00:00:00', '2020-09-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(82, '2020/10', '2020-10-01 00:00:00', '2020-10-31 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(83, '2020/11', '2020-11-01 00:00:00', '2020-11-30 23:59:59');
INSERT INTO months(num, yyyymm, start_datetime, end_datetime) VALUES(84, '2020/12', '2020-12-01 00:00:00', '2020-12-31 23:59:59');
再进入正题。
SELECT
m.yyyymm AS '放款年月',
SUM(lrp.receivable_principal) AS '放款本金',
COUNT(lrp.id) AS '放款对应还款计划数',
COUNT(DISTINCT lrp.user_id) AS '放款对应用户数',
PERIOD_DIFF(DATE_FORMAT(STR_TO_DATE(m_after.yyyymm, '%Y/%m'), '%Y%m'), DATE_FORMAT(STR_TO_DATE(m.yyyymm, '%Y/%m'), '%Y%m')) AS '后续年月序号',
m_after.yyyymm AS '后续年月',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), lrp.receivable_principal, 0)) AS '累计应收本金',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), 1, 0)) AS '累计应收还款计划数',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), lrp.user_id, NULL)) AS '累计应收用户数',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 >= 1, lrp.receivable_principal, 0)) AS '累计逾期本金',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 >= 1, 1, 0)) AS '累计逾期还款计划数',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 >= 1, lrp.user_id, NULL)) AS '累计逾期还款用户数',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 1 AND 3, lrp.receivable_principal, 0)) AS '累计逾期本金(1-3)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 1 AND 3, 1, 0)) AS '累计逾期还款计划数(1-3)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 1 AND 3, lrp.user_id, NULL)) AS '累计逾期还款用户数(1-3)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 1 AND 30, lrp.receivable_principal, 0)) AS '累计逾期本金(1-30)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 1 AND 30, 1, 0)) AS '累计逾期还款计划数(1-30)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 1 AND 30, lrp.user_id, NULL)) AS '累计逾期还款用户数(1-30)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 31 AND 60, lrp.receivable_principal, 0)) AS '累计逾期本金(31-60)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 31 AND 60, 1, 0)) AS '累计逾期还款计划数(31-60)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 31 AND 60, lrp.user_id, NULL)) AS '累计逾期还款用户数(31-60)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 61 AND 90, lrp.receivable_principal, 0)) AS '累计逾期本金(61-90)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 61 AND 90, 1, 0)) AS '累计逾期还款计划数(61-90)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 61 AND 90, lrp.user_id, NULL)) AS '累计逾期还款用户数(61-90)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 91 AND 120, lrp.receivable_principal, 0)) AS '累计逾期本金(91-120)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 91 AND 120, 1, 0)) AS '累计逾期还款计划数(91-120)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 91 AND 120, lrp.user_id, NULL)) AS '累计逾期还款用户数(91-120)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 121 AND 150, lrp.receivable_principal, 0)) AS '累计逾期本金(121-150)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 121 AND 150, 1, 0)) AS '累计逾期还款计划数(121-150)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 121 AND 150, lrp.user_id, NULL)) AS '累计逾期还款用户数(121-150)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 151 AND 180, lrp.receivable_principal, 0)) AS '累计逾期本金(151-180)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 151 AND 180, 1, 0)) AS '累计逾期还款计划数(151-180)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 BETWEEN 151 AND 180, lrp.user_id, NULL)) AS '累计逾期还款用户数(151-180)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 > 180, lrp.receivable_principal, 0)) AS '累计逾期本金(>180)',
SUM(IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 > 180, 1, 0)) AS '累计逾期本金还款计划数(>180)',
COUNT(DISTINCT IF(lrp.repayment_due_date <= IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime) AND lrp.repayment_status IN (4, 5, 6) AND DATEDIFF(IF(lrp.repayment_date IS NULL OR lrp.repayment_date > IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), IF(NOW() < m_after.end_datetime, NOW(), m_after.end_datetime), '2000/01/01 23:59:59'), lrp.repayment_due_date) + 1 > 180, lrp.user_id, NULL)) AS '累计逾期本金还款用户数(>180)'
FROM p2p_system.months m
JOIN p2p_system.months m_after
ON m_after.yyyymm >= m.yyyymm
AND m_after.yyyymm <= '${QUERY_MONTH}'
JOIN p2p_loan.loan_contract lc
ON lc.isdel = 0
AND lc.contract_status IN (19, 7, 8, 9, 10)
AND lc.contract_transfer_time >= m.start_datetime
AND lc.contract_transfer_time <= m.end_datetime
${IF(len(repayment_type)==0,"","AND lc.repayment_type = "+repayment_type)}
${IF(len(term_month)==0,"","AND lc.term_month IN("+term_month+")")}
${IF(len(term_day)==0,"","AND lc.term_day IN("+term_day+")")}
${IF(len(channel)==0,"","AND (CASE WHEN lc.channel = '###' THEN '###' END) IN("+channel+")")}
JOIN p2p_loan.loan_repayment_plan lrp
ON lrp.isdel = 0
AND lrp.contract_number = lc.contract_number
WHERE m.yyyymm >= '${BEGIN_MONTH}'
AND m.yyyymm <= '${END_MONTH}'
GROUP BY m.yyyymm,
m_after.yyyymm
ORDER BY m.yyyymm,
m_after.yyyymm
python写法
import numpy as np
import pandas as pd
from monitor.commons.dateProcessing import Date
from monitor.commons.myThread import myThread, ALL_COMPLETED, wait
from monitor.commons.oralceClient import *
from monitor.commons.tools_02 import getApplyDataSQL, getOverduedaysInfoSqlAll, getLoanDataOfNoID, getLoanDataSqlALL, \
getScheduleDataSQLAll
def dataSave(sql_str):
# 大航旅业务库
dhl_user = ""
dhl_passwd = ""
dhl_url = ""
dhl_db, dhl_cursor = oracleConnect(dhl_user, dhl_passwd, dhl_url)
dd = pd.read_sql(sql_str, dhl_db)
return dd
def getVintageIndex(startDate, endDate):
"""
获取行索引（按月）
:param startDate: 开始时间
:param endDate: 结束时间
:return: 行索引、时间范围
"""
date_end = Date.date_range(startDate, endDate, freq="m")
date_start = [Date.first_day_of_month(x) for x in date_end]
date_range = list(zip(date_start, date_end))
dateIndex = [date_range[i][0].strftime("%Y-%m") for i in range(len(date_range))]
return dateIndex, date_range
def getVintageMobDate(startDate, endDate):
"""
获取Vintage的账龄（mob）区间
:param startDate: 开始时间
:param endDate: 结束时间
:return: 账龄名，账龄区间
"""
date_end = Date.date_range(startDate, endDate, freq="m")
date_start = [Date.first_day_of_month(x) for x in date_end]
date_range = list(zip(date_start, date_end))
colName = ["MOB_%s" % i for i in range(len(date_range))]
return colName, date_range
def getApplyData(apply_startDate, apply_endDate, productid):
# 获取申请信息
sql_str = getApplyDataSQL(apply_startDate, apply_endDate, productid)
dataApplyData = pd.read_sql(sql_str, dhl_db)
dataApplyData['SERIALNO'] = dataApplyData['SERIALNO'].astype('str')
return dataApplyData
def getLoanData(IDList, putout_startDate, putout_endDate, productId=""):
# 获取借据信息
if len(IDList) == 0:
loanDataSql = getLoanDataOfNoID(putout_startDate, putout_endDate, productId)
loanDataSqlALL = [loanDataSql]
else:
loanDataSqlALL = getLoanDataSqlALL(IDList, putout_startDate, putout_endDate)
loanDataInfo = [pd.read_sql(sql_str, dhl_db) for sql_str in loanDataSqlALL]
loanDataInfo = pd.concat(loanDataInfo)
loanDataInfo['SERIALNO'] = loanDataInfo['SERIALNO'].astype('str')
return loanDataInfo
# 获取还款计划
def getScheduleData(IDList):
"""
获取还款记录
:param IDList: 通过贷款（动支）ID获取还款记录
:return: 还款记录表
"""
IDList = [str(x) for x in IDList]
filedkey = 'serialno'
sql_str_all = getScheduleDataSQLAll(IDList, filedkey, max_length=5000)
mt = myThread(max_workers=10)
all_task = [mt.run(dataSave, sql_str) for sql_str in sql_str_all]
wait(all_task, return_when=ALL_COMPLETED)
data_list = [tt.result() for tt in all_task]
data_user_schedual = pd.concat(data_list)
return data_user_schedual
def getOverduedaysInfo(IDList, observationDate):
"""
从日切表中获取逾期天数
日切表：数据日备份表（每日数据备份）
:param IDList: 贷款（动支）ID列表
:param observationDate: 观察日
:return: 逾期情况表
"""
fieldKey = "serialno"
data_overduedays_sql = getOverduedaysInfoSqlAll(IDList, fieldKey, observationDate)
data_overduedays_list = [pd.read_sql(sql_str, sc_db) for sql_str in data_overduedays_sql]
data_overduedays = pd.concat(data_overduedays_list)
data_overduedays['LOAN_SERIALNO'] = data_overduedays['LOAN_SERIALNO'].astype("str")
return data_overduedays
def getVintageOfUser(dataBase, dataScheduleAll, putout_startDate, putout_endDate, observationDate, overdueDays):
"""
用户级的vintage表
从还款计划中获取逾期人数与逾期天数
:param dataBase: 动支信息表
:param dataScheduleAll: 还款计划表
:param putout_startDate: 动支开始时间
:param putout_endDate: 动支结束时间
:param observationDate: 观察时间点（vintage表的观察时间点）
:param overdueDays: 逾期天数（vintage dpd_n+）
:return:
"""
dataBase['PUTOUTDATE'] = pd.to_datetime(dataBase['PUTOUTDATE'])
dateIndex, date_range = getVintageIndex(putout_startDate, putout_endDate)
vintageName_all, mobRange_all = getVintageMobDate(putout_startDate, observationDate)
VintageOfUser = pd.DataFrame(None, columns=vintageName_all, index=dateIndex)
for i in range(len(date_range)):
PutOutStartDate, PutOutEndDate = date_range[i]
Index = dateIndex[i]
part_dataBase = dataBase[
(dataBase['PUTOUTDATE'] >= PutOutStartDate) & (dataBase['PUTOUTDATE'] <= PutOutEndDate)]
part_Schedule = dataScheduleAll[
dataScheduleAll['LOAN_SERIALNO'].isin(part_dataBase['LOAN_SERIALNO'])].reset_index(drop=True)
part_vintageName, part_mobRange = getVintageMobDate(PutOutStartDate, observationDate)
for j in range(len(part_vintageName)):
mob_name = part_vintageName[j]
part_PutOutStartDate, part_PutOutEndDate = part_mobRange[j]
# 获取逾期天数（从还款计划中获取逾期天数）
part_Schedule_01 = part_Schedule[(part_Schedule['PAYDATE'] <= part_PutOutEndDate)].reset_index(drop=True)
part_Schedule_01['ACTUALPAYDATE'] = pd.to_datetime(part_Schedule_01['ACTUALPAYDATE'])
part_Schedule_01['ACTUALPAYDATE'] = part_Schedule_01['ACTUALPAYDATE'].apply(
lambda x: part_PutOutEndDate if (pd.isna(x)) | (x > part_PutOutEndDate) else x)
part_Schedule_01['PAYDATE'] = pd.to_datetime(part_Schedule_01['PAYDATE'])
part_Schedule_01['OVERDUEDAYS'] = (part_Schedule_01['ACTUALPAYDATE'] - part_Schedule_01[
'PAYDATE']).dt.days + 1
part_overdueData = part_Schedule_01[part_Schedule_01["OVERDUEDAYS"] > overdueDays]
part_overdueData_01 = part_dataBase[part_dataBase["LOAN_SERIALNO"].isin(part_overdueData["LOAN_SERIALNO"])]
UserNum = part_dataBase['CUSTOMERID'].nunique()
overdueUserNum = part_overdueData_01['CUSTOMERID'].nunique()
overdueRotio = round(overdueUserNum / UserNum, 4) if UserNum > 0 else 0
VintageOfUser.loc[Index, mob_name] = overdueRotio
return VintageOfUser
def getVintageOfAmount(dataBase, putout_startDate, putout_endDate, observationEndDate, overdueDays):
"""
金额级的Vintage表；
从日切表中获取逾期金额与逾期天数
:param dataBase: 动支信息表
:param putout_startDate: 动支开始时间
:param putout_endDate: 动支结束时间
:param observationEndDate: 观察时间点（vintage表的观察时间点）
:param overdueDays: 逾期天数（vintage dpd_n+）
:return:
"""
dataBase['PUTOUTDATE'] = pd.to_datetime(dataBase['PUTOUTDATE'])
dateIndex, date_range = getVintageIndex(putout_startDate, putout_endDate)
vintageName_all, mobRange_all = getVintageMobDate(putout_startDate, observationEndDate)
VintageOfAmount = pd.DataFrame(None, columns=vintageName_all, index=dateIndex)
for i in range(len(date_range)):
PutOutStartDate, PutOutEndDate = date_range[i]
Index = dateIndex[i]
part_dataBase = dataBase[
(dataBase['PUTOUTDATE'] >= PutOutStartDate) & (dataBase['PUTOUTDATE'] <= PutOutEndDate)]
IDList = list(part_dataBase['LOAN_SERIALNO'].unique())
part_vintageName, part_mobRange = getVintageMobDate(PutOutStartDate, observationEndDate)
for j in range(len(part_vintageName)):
# print(i, j)
mob_name = part_vintageName[j]
part_PutOutStartDate, part_PutOutEndDate = part_mobRange[j]
part_obserivationDate = part_PutOutEndDate.strftime("%Y%m%d")
# 获取逾期天数（从日切表中获取逾期金额、逾期天数）
part_overdueData = getOverduedaysInfo(IDList, part_obserivationDate)
part_overdueData.drop_duplicates(inplace=True)
LoanAmount = part_dataBase[['LOAN_SERIALNO', "DZ_SUM"]].drop_duplicates()['DZ_SUM'].sum()
overdueUserNum = part_overdueData[part_overdueData["OVERDUEDAYS"] > overdueDays][
['NORMALBALANCE', 'OVERDUEBALANCE']].sum(skipna=True).sum(skipna=True)
overdueRotio = round(overdueUserNum / LoanAmount, 4) if LoanAmount > 0 else 0
VintageOfAmount.loc[Index, mob_name] = overdueRotio
return VintageOfAmount
def getVintageOfUser_01(dataBase, putout_startDate, putout_endDate, observationDate, overdueDays):
dataBase['PUTOUTDATE'] = pd.to_datetime(dataBase['PUTOUTDATE'])
dateIndex, date_range = getVintageIndex(putout_startDate, putout_endDate)
vintageName_all, mobRange_all = getVintageMobDate(putout_startDate, observationDate)
VintageOfUser = pd.DataFrame(None, columns=vintageName_all, index=dateIndex)
for i in range(len(date_range)):
PutOutStartDate, PutOutEndDate = date_range[i]
Index = dateIndex[i]
part_dataBase = dataBase[
(dataBase['PUTOUTDATE'] >= PutOutStartDate) & (dataBase['PUTOUTDATE'] <= PutOutEndDate)]
IDList = list(part_dataBase['LOAN_SERIALNO'].unique())
part_vintageName, part_mobRange = getVintageMobDate(PutOutStartDate, observationDate)
for j in range(len(part_vintageName)):
mob_name = part_vintageName[j]
part_PutOutStartDate, part_PutOutEndDate = part_mobRange[j]
part_obserivationDate = part_PutOutEndDate.strftime("%Y%m%d")
part_overdueData = getOverduedaysInfo(IDList, part_obserivationDate)
part_overdueData['LOAN_SERIALNO'] = part_overdueData['LOAN_SERIALNO'].astype("str")
part_overdueData.drop_duplicates(inplace=True)
LoanUserNum = part_dataBase["CUSTOMERID"].nunique()
LoanOverdueUserNum = part_dataBase[part_dataBase["LOAN_SERIALNO"].isin(
part_overdueData[part_overdueData["MAXOVERDUEDAYS"] > overdueDays]['LOAN_SERIALNO'].unique())][
"CUSTOMERID"].nunique()
overdueRotio = round(LoanOverdueUserNum / LoanUserNum, 4) if LoanUserNum > 0 else 0
VintageOfUser.loc[Index, mob_name] = overdueRotio
return VintageOfUser
if __name__ == '__main__':
apply_startDate = "2020/07/01"
apply_endDate = "2020/11/01"
putout_startDate = "2020/07/01"
putout_endDate = "2020/11/20"
observationDate = "2021/03/01"
overdueDays = 14
dataBase = getApplyData(apply_startDate, apply_endDate, "HLDXC01")
dataBase['SERIALNO'] = dataBase['SERIALNO'].astype("str")
dataBase['SF_NO'] = dataBase['SF_NO'].astype("str")
dataBase = dataBase[dataBase['APPROVESTATUS'] == '13']
IDList = list(dataBase['SERIALNO'].unique())
dataLoan = getLoanData(IDList, putout_startDate, putout_endDate)
dataLoan['LOAN_SERIALNO'] = dataLoan['LOAN_SERIALNO'].astype("str")
dataLoan['SERIALNO'] = dataLoan['SERIALNO'].astype("str")
dataLoan = dataLoan[dataLoan['DZ_SUM'] > 0]
dataBaseAll = pd.merge(dataBase, dataLoan, on="SERIALNO", how="left")
IDList = list(dataLoan['LOAN_SERIALNO'].unique())
data_Schedule = getScheduleData(IDList)
data_Schedule['LOAN_SERIALNO'] = data_Schedule['LOAN_SERIALNO'].astype("str")
data_Schedule['PAYDATE'] = pd.to_datetime(data_Schedule['PAYDATE'])
data_Schedule = data_Schedule[data_Schedule['PAYDATE'] <= pd.to_datetime("2021-03-01")].reset_index(drop=True)
data_Schedule['ACTUALPAYDATE'] = data_Schedule[['FINISHDATE', 'ACTUALPAYDATE']].apply(
lambda x: x[1] if ~np.equal(pd.isna(x[1]), True) else x[0], axis=1)
VintageOfUser = getVintageOfUser(dataBaseAll, data_Schedule, putout_startDate, putout_endDate, observationDate,
overdueDays)
VintageOfUser_01 = getVintageOfUser_01(dataBaseAll, putout_startDate, putout_endDate, observationDate,
overdueDays)
5715
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
