CREATE OR REPLACE PROCEDURE PURE_REPAYMENT_AMOUNT_FIVE AS
BEGIN
---------------------------------纯新放款金额(成功)-------------------------------------
DECLARE
v_1 DATE := TO_DATE('20250301', 'YYYYMMDD');
v_start DATE;
v_end DATE := TRUNC(SYSDATE);
v_current DATE;
v_sql VARCHAR2(32767); -- 增大缓冲区大小 (5000 -> 32767)
BEGIN
v_start := TRUNC(v_1);
FOR i IN 0..(v_end - v_start) LOOP
v_current := v_start + i;
v_sql :=
'MERGE INTO PURE_REPAYMENT_AMOUNT dest
USING (
WITH t1 AS (
SELECT
a.*,
CASE
WHEN a.COUNTRY_CODE = ''tz'' THEN a.IN_FORCE_DATE + 3 / 24
WHEN a.COUNTRY_CODE = ''ke'' THEN a.IN_FORCE_DATE + 3 / 24
WHEN a.COUNTRY_CODE = ''ci'' THEN a.IN_FORCE_DATE
WHEN a.COUNTRY_CODE = ''ph'' THEN a.IN_FORCE_DATE + 8 / 24
END AS NEW_DATE
FROM LOAN_BORROW_INFO_TEST a
WHERE a.COUNTRY_CODE <> ''gh''
AND a.RISK_SERIAL_NO <> ''googleplay''
AND a.ARCHIVED = 1
AND a.IN_FORCE_DATE IS NOT NULL
),
t2 AS (
SELECT DISTINCT phone, country_code, NEW_DATE
FROM t1
WHERE TRUNC(NEW_DATE) = TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'')
AND user_type_product = 0
),
t3 AS (
SELECT t1.NEW_DATE, t1.REPAYMENT_AMOUNT
FROM t2
LEFT JOIN t1
ON t2.phone = t1.phone
AND t2.country_code = t1.country_code
AND t1.NEW_DATE >= t2.NEW_DATE
AND t1.new_date IS NOT NULL
),
t4 AS (
SELECT
week_num,
TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'') AS pure_in_force_date,
SUM(REPAYMENT_AMOUNT / 100) AS total_AMOUNT
FROM (
SELECT
REPAYMENT_AMOUNT,
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) + 1 AS week_num
FROM t3
WHERE NEW_DATE BETWEEN TO_DATE(''2025-03-01'', ''YYYY-MM-DD'')
AND TRUNC(ADD_MONTHS(SYSDATE, 12), ''YYYY'') - 1
)
GROUP BY week_num
),
t5 AS (
SELECT *
FROM t4
PIVOT (
SUM(total_AMOUNT)
FOR week_num IN (
1 AS "第一周", 2 AS "第二周", 3 AS "第三周", 4 AS "第四周", 5 AS "第五周", 6 AS "第六周", 7 AS "第七周", 8 AS "第八周", 9 AS "第九周", 10 AS "第十周",
11 AS "第十一周", 12 AS "第十二周", 13 AS "第十三周", 14 AS "第十四周", 15 AS "第十五周", 16 AS "第十六周", 17 AS "第十七周", 18 AS "第十八周", 19 AS "第十九周", 20 AS "第二十周",
21 AS "第二十一周", 22 AS "第二十二周", 23 AS "第二十三周", 24 AS "第二十四周", 25 AS "第二十五周", 26 AS "第二十六周", 27 AS "第二十七周", 28 AS "第二十八周", 29 AS "第二十九周", 30 AS "第三十周",
31 AS "第三十一周", 32 AS "第三十二周", 33 AS "第三十三周", 34 AS "第三十四周", 35 AS "第三十五周", 36 AS "第三十六周", 37 AS "第三十七周", 38 AS "第三十八周", 39 AS "第三十九周", 40 AS "第四十周",
41 AS "第四十一周", 42 AS "第四十二周", 43 AS "第四十三周"
)
)
)
SELECT * FROM t5
) src
ON (dest.PURE_IN_FORCE_DATE = src.pure_in_force_date)
WHEN MATCHED THEN
UPDATE SET
dest."第一周" = src."第一周",
dest."第二周" = src."第二周",
dest."第三周" = src."第三周",
dest."第四周" = src."第四周",
dest."第五周" = src."第五周",
dest."第六周" = src."第六周",
dest."第七周" = src."第七周",
dest."第八周" = src."第八周",
dest."第九周" = src."第九周",
dest."第十周" = src."第十周",
dest."第十一周" = src."第十一周",
dest."第十二周" = src."第十二周",
dest."第十三周" = src."第十三周",
dest."第十四周" = src."第十四周",
dest."第十五周" = src."第十五周",
dest."第十六周" = src."第十六周",
dest."第十七周" = src."第十七周",
dest."第十八周" = src."第十八周",
dest."第十九周" = src."第十九周",
dest."第二十周" = src."第二十周",
dest."第二十一周" = src."第二十一周",
dest."第二十二周" = src."第二十二周",
dest."第二十三周" = src."第二十三周",
dest."第二十四周" = src."第二十四周",
dest."第二十五周" = src."第二十五周",
dest."第二十六周" = src."第二十六周",
dest."第二十七周" = src."第二十七周",
dest."第二十八周" = src."第二十八周",
dest."第二十九周" = src."第二十九周",
dest."第三十周" = src."第三十周",
dest."第三十一周" = src."第三十一周",
dest."第三十二周" = src."第三十二周",
dest."第三十三周" = src."第三十三周",
dest."第三十四周" = src."第三十四周",
dest."第三十五周" = src."第三十五周",
dest."第三十六周" = src."第三十六周",
dest."第三十七周" = src."第三十七周",
dest."第三十八周" = src."第三十八周",
dest."第三十九周" = src."第三十九周",
dest."第四十周" = src."第四十周",
dest."第四十一周" = src."第四十一周",
dest."第四十二周" = src."第四十二周",
dest."第四十三周" = src."第四十三周"
WHEN NOT MATCHED THEN
INSERT (
pure_in_force_date,
"第一周","第二周","第三周","第四周","第五周","第六周","第七周","第八周","第九周","第十周",
"第十一周","第十二周","第十三周","第十四周","第十五周","第十六周","第十七周","第十八周","第十九周","第二十周",
"第二十一周","第二十二周","第二十三周","第二十四周","第二十五周","第二十六周","第二十七周","第二十八周","第二十九周","第三十周",
"第三十一周","第三十二周","第三十三周","第三十四周","第三十五周","第三十六周","第三十七周","第三十八周","第三十九周","第四十周",
"第四十一周","第四十二周","第四十三周"
) VALUES (
src.pure_in_force_date,
src."第一周",src."第二周",src."第三周",src."第四周",src."第五周",src."第六周",src."第七周",src."第八周",src."第九周",src."第十周",
src."第十一周",src."第十二周",src."第十三周",src."第十四周",src."第十五周",src."第十六周",src."第十七周",src."第十八周",src."第十九周",src."第二十周",
src."第二十一周",src."第二十二周",src."第二十三周",src."第二十四周",src."第二十五周",src."第二十六周",src."第二十七周",src."第二十八周",src."第二十九周",src."第三十周",
src."第三十一周",src."第三十二周",src."第三十三周",src."第三十四周",src."第三十五周",src."第三十六周",src."第三十七周",src."第三十八周",src."第三十九周",src."第四十周",
src."第四十一周",src."第四十二周",src."第四十三周"
)';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
commit ;
/
-------------------------------纯新案均合同金额(应该没问题,运行七分钟)-----------------------------------
DECLARE
v_1 DATE := TO_DATE('20250301', 'YYYYMMDD');
v_start DATE;
v_end DATE := TRUNC(SYSDATE);
v_current DATE;
v_sql VARCHAR2(32767);
BEGIN
v_start := TRUNC(v_1);
FOR i IN 0..(v_end - v_start) LOOP
v_current := v_start + i;
v_sql :=
'MERGE INTO PURE_AVG_AMOUNT dest USING (
WITH t1 AS (
SELECT
a.*,
CASE
WHEN a.COUNTRY_CODE = ''tz'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ke'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ci'' THEN a.IN_FORCE_DATE
WHEN a.COUNTRY_CODE = ''ph'' THEN a.IN_FORCE_DATE + 8/24
END AS NEW_DATE
FROM LOAN_BORROW_INFO_TEST a
WHERE a.COUNTRY_CODE <> ''gh''
AND a.RISK_SERIAL_NO <> ''googleplay''
AND a.ARCHIVED = 1
AND a.IN_FORCE_DATE IS NOT NULL
),
t2 AS (
SELECT DISTINCT phone, country_code, NEW_DATE
FROM t1
WHERE TRUNC(NEW_DATE) = TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'')
AND user_type_product = 0
),
t3 AS (
SELECT t1.NEW_DATE, t1.REPAYMENT_AMOUNT
FROM t2
LEFT JOIN t1
ON t2.phone = t1.phone
AND t2.country_code = t1.country_code
AND t1.NEW_DATE >= t2.NEW_DATE
AND t1.new_date IS NOT NULL
),
t4 AS (
SELECT
week_num,
TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'') AS pure_in_force_date,
SUM(REPAYMENT_AMOUNT/100) / COUNT(1) AS avg_AMOUNT
FROM (
SELECT
REPAYMENT_AMOUNT,
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER()) / 7) + 1 AS week_num,
MIN(NEW_DATE) OVER() + TRUNC((NEW_DATE - MIN(NEW_DATE) OVER()) / 7) * 7 AS week_start,
MIN(NEW_DATE) OVER() + TRUNC((NEW_DATE - MIN(NEW_DATE) OVER()) / 7) * 7 + 6 AS week_end
FROM t3
WHERE NEW_DATE BETWEEN TO_DATE(''2025-03-01'', ''YYYY-MM-DD'')
AND TRUNC(ADD_MONTHS(SYSDATE, 12), ''YYYY'') - 1
)
GROUP BY week_num
ORDER BY week_num
),
t5 AS (
SELECT *
FROM t4
PIVOT(SUM(ROUND(avg_AMOUNT, 6))
FOR week_num IN (
1 AS "第一周", 2 AS "第二周", 3 AS "第三周", 4 AS "第四周", 5 AS "第五周", 6 AS "第六周", 7 AS "第七周", 8 AS "第八周", 9 AS "第九周", 10 AS "第十周",
11 AS "第十一周", 12 AS "第十二周", 13 AS "第十三周", 14 AS "第十四周", 15 AS "第十五周", 16 AS "第十六周", 17 AS "第十七周", 18 AS "第十八周", 19 AS "第十九周", 20 AS "第二十周",
21 AS "第二十一周", 22 AS "第二十二周", 23 AS "第二十三周", 24 AS "第二十四周", 25 AS "第二十五周", 26 AS "第二十六周", 27 AS "第二十七周", 28 AS "第二十八周", 29 AS "第二十九周", 30 AS "第三十周",
31 AS "第三十一周", 32 AS "第三十二周", 33 AS "第三十三周", 34 AS "第三十四周", 35 AS "第三十五周", 36 AS "第三十六周", 37 AS "第三十七周", 38 AS "第三十八周", 39 AS "第三十九周", 40 AS "第四十周",
41 AS "第四十一周", 42 AS "第四十二周", 43 AS "第四十三周"
)
)
)
SELECT t5.* FROM t5
) src
ON (dest.PURE_IN_FORCE_DATE = src.pure_in_force_date)
WHEN MATCHED THEN
UPDATE SET
dest."第一周" = src."第一周",
dest."第二周" = src."第二周",
dest."第三周" = src."第三周",
dest."第四周" = src."第四周",
dest."第五周" = src."第五周",
dest."第六周" = src."第六周",
dest."第七周" = src."第七周",
dest."第八周" = src."第八周",
dest."第九周" = src."第九周",
dest."第十周" = src."第十周",
dest."第十一周" = src."第十一周",
dest."第十二周" = src."第十二周",
dest."第十三周" = src."第十三周",
dest."第十四周" = src."第十四周",
dest."第十五周" = src."第十五周",
dest."第十六周" = src."第十六周",
dest."第十七周" = src."第十七周",
dest."第十八周" = src."第十八周",
dest."第十九周" = src."第十九周",
dest."第二十周" = src."第二十周",
dest."第二十一周" = src."第二十一周",
dest."第二十二周" = src."第二十二周",
dest."第二十三周" = src."第二十三周",
dest."第二十四周" = src."第二十四周",
dest."第二十五周" = src."第二十五周",
dest."第二十六周" = src."第二十六周",
dest."第二十七周" = src."第二十七周",
dest."第二十八周" = src."第二十八周",
dest."第二十九周" = src."第二十九周",
dest."第三十周" = src."第三十周",
dest."第三十一周" = src."第三十一周",
dest."第三十二周" = src."第三十二周",
dest."第三十三周" = src."第三十三周",
dest."第三十四周" = src."第三十四周",
dest."第三十五周" = src."第三十五周",
dest."第三十六周" = src."第三十六周",
dest."第三十七周" = src."第三十七周",
dest."第三十八周" = src."第三十八周",
dest."第三十九周" = src."第三十九周",
dest."第四十周" = src."第四十周",
dest."第四十一周" = src."第四十一周",
dest."第四十二周" = src."第四十二周",
dest."第四十三周" = src."第四十三周"
WHEN NOT MATCHED THEN
INSERT (
pure_in_force_date,
"第一周", "第二周", "第三周", "第四周", "第五周", "第六周", "第七周", "第八周", "第九周", "第十周",
"第十一周", "第十二周", "第十三周", "第十四周", "第十五周", "第十六周", "第十七周", "第十八周", "第十九周", "第二十周",
"第二十一周", "第二十二周", "第二十三周", "第二十四周", "第二十五周", "第二十六周", "第二十七周", "第二十八周", "第二十九周", "第三十周",
"第三十一周", "第三十二周", "第三十三周", "第三十四周", "第三十五周", "第三十六周", "第三十七周", "第三十八周", "第三十九周", "第四十周",
"第四十一周", "第四十二周", "第四十三周"
) VALUES (
src.pure_in_force_date,
src."第一周", src."第二周", src."第三周", src."第四周", src."第五周", src."第六周", src."第七周", src."第八周", src."第九周", src."第十周",
src."第十一周", src."第十二周", src."第十三周", src."第十四周", src."第十五周", src."第十六周", src."第十七周", src."第十八周", src."第十九周", src."第二十周",
src."第二十一周", src."第二十二周", src."第二十三周", src."第二十四周", src."第二十五周", src."第二十六周", src."第二十七周", src."第二十八周", src."第二十九周", src."第三十周",
src."第三十一周", src."第三十二周", src."第三十三周", src."第三十四周", src."第三十五周", src."第三十六周", src."第三十七周", src."第三十八周", src."第三十九周", src."第四十周",
src."第四十一周", src."第四十二周", src."第四十三周"
)';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
commit ;
/
---------------------------------纯新在贷人数(Connection closed)-------------------------------------
DECLARE
v_1 DATE := TO_DATE('20250301', 'YYYYMMDD');
v_start DATE;
v_end DATE := TRUNC(SYSDATE);
v_current DATE;
v_sql VARCHAR2(32767);
BEGIN
v_start := TRUNC(v_1);
FOR i IN 0..(v_end - v_start) LOOP
v_current := v_start + i;
v_sql :=
'MERGE INTO PURE_BORROW dest USING (
WITH t1 AS (
SELECT
a.*,
CASE
WHEN a.COUNTRY_CODE = ''tz'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ke'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ci'' THEN a.IN_FORCE_DATE
WHEN a.COUNTRY_CODE = ''ph'' THEN a.IN_FORCE_DATE + 8/24
END AS NEW_DATE
FROM LOAN_BORROW_INFO_TEST a
WHERE a.COUNTRY_CODE <> ''gh''
AND a.RISK_SERIAL_NO <> ''googleplay''
AND a.ARCHIVED = 1
AND a.IN_FORCE_DATE IS NOT NULL
),
t2 AS (
SELECT DISTINCT phone, country_code, NEW_DATE
FROM t1
WHERE TRUNC(NEW_DATE) = TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'')
AND user_type_product = 0
),
t3 AS (
SELECT t1.NEW_DATE, t1.phone
FROM t2
LEFT JOIN t1
ON t2.phone = t1.phone
AND t2.country_code = t1.country_code
AND t1.NEW_DATE >= t2.NEW_DATE
AND t1.new_date IS NOT NULL
),
t4 AS (
SELECT
week_num,
TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'') AS pure_in_force_date,
COUNT(1) AS pure_borrow
FROM (
SELECT
week_num,
ROW_NUMBER() OVER (PARTITION BY week_num, phone ORDER BY week_num) AS rn
FROM (
SELECT
phone,
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) + 1 AS week_num,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 AS week_start,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 + 6 AS week_end
FROM t3
WHERE NEW_DATE BETWEEN TO_DATE(''2025-03-01'', ''YYYY-MM-DD'')
AND TRUNC(ADD_MONTHS(SYSDATE, 12), ''YYYY'') - 1
)
)
WHERE rn = 1
GROUP BY week_num
)
SELECT *
FROM t4
PIVOT(
SUM(pure_borrow)
FOR week_num IN (
1 AS "第一周", 2 AS "第二周", 3 AS "第三周", 4 AS "第四周", 5 AS "第五周", 6 AS "第六周", 7 AS "第七周", 8 AS "第八周", 9 AS "第九周", 10 AS "第十周",
11 AS "第十一周", 12 AS "第十二周", 13 AS "第十三周", 14 AS "第十四周", 15 AS "第十五周", 16 AS "第十六周", 17 AS "第十七周", 18 AS "第十八周", 19 AS "第十九周", 20 AS "第二十周",
21 AS "第二十一周", 22 AS "第二十二周", 23 AS "第二十三周", 24 AS "第二十四周", 25 AS "第二十五周", 26 AS "第二十六周", 27 AS "第二十七周", 28 AS "第二十八周", 29 AS "第二十九周", 30 AS "第三十周",
31 AS "第三十一周", 32 AS "第三十二周", 33 AS "第三十三周", 34 AS "第三十四周", 35 AS "第三十五周", 36 AS "第三十六周", 37 AS "第三十七周", 38 AS "第三十八周", 39 AS "第三十九周", 40 AS "第四十周",
41 AS "第四十一周", 42 AS "第四十二周", 43 AS "第四十三周"
)
)
) src
ON (dest.PURE_IN_FORCE_DATE = src.pure_in_force_date)
WHEN MATCHED THEN
UPDATE SET
dest."第一周" = src."第一周",
dest."第二周" = src."第二周",
dest."第三周" = src."第三周",
dest."第四周" = src."第四周",
dest."第五周" = src."第五周",
dest."第六周" = src."第六周",
dest."第七周" = src."第七周",
dest."第八周" = src."第八周",
dest."第九周" = src."第九周",
dest."第十周" = src."第十周",
dest."第十一周" = src."第十一周",
dest."第十二周" = src."第十二周",
dest."第十三周" = src."第十三周",
dest."第十四周" = src."第十四周",
dest."第十五周" = src."第十五周",
dest."第十六周" = src."第十六周",
dest."第十七周" = src."第十七周",
dest."第十八周" = src."第十八周",
dest."第十九周" = src."第十九周",
dest."第二十周" = src."第二十周",
dest."第二十一周" = src."第二十一周",
dest."第二十二周" = src."第二十二周",
dest."第二十三周" = src."第二十三周",
dest."第二十四周" = src."第二十四周",
dest."第二十五周" = src."第二十五周",
dest."第二十六周" = src."第二十六周",
dest."第二十七周" = src."第二十七周",
dest."第二十八周" = src."第二十八周",
dest."第二十九周" = src."第二十九周",
dest."第三十周" = src."第三十周",
dest."第三十一周" = src."第三十一周",
dest."第三十二周" = src."第三十二周",
dest."第三十三周" = src."第三十三周",
dest."第三十四周" = src."第三十四周",
dest."第三十五周" = src."第三十五周",
dest."第三十六周" = src."第三十六周",
dest."第三十七周" = src."第三十七周",
dest."第三十八周" = src."第三十八周",
dest."第三十九周" = src."第三十九周",
dest."第四十周" = src."第四十周",
dest."第四十一周" = src."第四十一周",
dest."第四十二周" = src."第四十二周",
dest."第四十三周" = src."第四十三周"
WHEN NOT MATCHED THEN
INSERT (
pure_in_force_date,
"第一周", "第二周", "第三周", "第四周", "第五周", "第六周", "第七周", "第八周", "第九周", "第十周",
"第十一周", "第十二周", "第十三周", "第十四周", "第十五周", "第十六周", "第十七周", "第十八周", "第十九周", "第二十周",
"第二十一周", "第二十二周", "第二十三周", "第二十四周", "第二十五周", "第二十六周", "第二十七周", "第二十八周", "第二十九周", "第三十周",
"第三十一周", "第三十二周", "第三十三周", "第三十四周", "第三十五周", "第三十六周", "第三十七周", "第三十八周", "第三十九周", "第四十周",
"第四十一周", "第四十二周", "第四十三周"
) VALUES (
src.pure_in_force_date,
src."第一周", src."第二周", src."第三周", src."第四周", src."第五周", src."第六周", src."第七周", src."第八周", src."第九周", src."第十周",
src."第十一周", src."第十二周", src."第十三周", src."第十四周", src."第十五周", src."第十六周", src."第十七周", src."第十八周", src."第十九周", src."第二十周",
src."第二十一周", src."第二十二周", src."第二十三周", src."第二十四周", src."第二十五周", src."第二十六周", src."第二十七周", src."第二十八周", src."第二十九周", src."第三十周",
src."第三十一周", src."第三十二周", src."第三十三周", src."第三十四周", src."第三十五周", src."第三十六周", src."第三十七周", src."第三十八周", src."第三十九周", src."第四十周",
src."第四十一周", src."第四十二周", src."第四十三周"
)';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
commit ;
/
-----------------------------------纯新息差(Connection closed)--------------------------------------
DECLARE
v_1 DATE := TO_DATE('20250301', 'YYYYMMDD');
v_start DATE;
v_end DATE := TRUNC(SYSDATE);
v_current DATE;
v_sql VARCHAR2(32767);
BEGIN
v_start := TRUNC(v_1);
FOR i IN 0..(v_end - v_start) LOOP
v_current := v_start + i;
v_sql :=
'MERGE INTO PURE_SPREAD dest USING (
WITH t1 AS (
SELECT
a.*,
CASE
WHEN a.COUNTRY_CODE = ''tz'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ke'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ci'' THEN a.IN_FORCE_DATE
WHEN a.COUNTRY_CODE = ''ph'' THEN a.IN_FORCE_DATE + 8/24
END AS NEW_DATE
FROM LOAN_BORROW_INFO_TEST a
WHERE a.COUNTRY_CODE <> ''gh''
AND a.RISK_SERIAL_NO <> ''googleplay''
AND a.ARCHIVED = 1
AND a.IN_FORCE_DATE IS NOT NULL
),
t2 AS (
SELECT DISTINCT phone, country_code, NEW_DATE
FROM t1
WHERE TRUNC(NEW_DATE) = TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'')
AND user_type_product = 0
),
t3 AS (
SELECT t1.NEW_DATE, t1.ACTUAL_REPAYMENT_AMOUNT, t1.ACTUAL_AMOUNT, t1.state
FROM t2
LEFT JOIN t1
ON t2.phone = t1.phone
AND t2.country_code = t1.country_code
AND t1.NEW_DATE >= t2.NEW_DATE
AND t1.new_date IS NOT NULL
),
t4 AS (
SELECT
week_num,
TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'') AS pure_in_force_date,
SUM(rs - rl) / 100 AS Spread
FROM (
SELECT
CASE WHEN state != 6 THEN ACTUAL_REPAYMENT_AMOUNT - ACTUAL_AMOUNT ELSE 0 END AS rs,
CASE WHEN state = 6 THEN ACTUAL_AMOUNT ELSE 0 END AS rl,
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) + 1 AS week_num,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 AS week_start,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 + 6 AS week_end
FROM t3
WHERE NEW_DATE BETWEEN TO_DATE(''2025-03-01'', ''YYYY-MM-DD'')
AND TRUNC(ADD_MONTHS(SYSDATE, 12), ''YYYY'') - 1
)
GROUP BY week_num
)
SELECT *
FROM t4
PIVOT(
SUM(ROUND(Spread, 6))
FOR week_num IN (
1 AS "第一周", 2 AS "第二周", 3 AS "第三周", 4 AS "第四周", 5 AS "第五周", 6 AS "第六周", 7 AS "第七周", 8 AS "第八周", 9 AS "第九周", 10 AS "第十周",
11 AS "第十一周", 12 AS "第十二周", 13 AS "第十三周", 14 AS "第十四周", 15 AS "第十五周", 16 AS "第十六周", 17 AS "第十七周", 18 AS "第十八周", 19 AS "第十九周", 20 AS "第二十周",
21 AS "第二十一周", 22 AS "第二十二周", 23 AS "第二十三周", 24 AS "第二十四周", 25 AS "第二十五周", 26 AS "第二十六周", 27 AS "第二十七周", 28 AS "第二十八周", 29 AS "第二十九周", 30 AS "第三十周",
31 AS "第三十一周", 32 AS "第三十二周", 33 AS "第三十三周", 34 AS "第三十四周", 35 AS "第三十五周", 36 AS "第三十六周", 37 AS "第三十七周", 38 AS "第三十八周", 39 AS "第三十九周", 40 AS "第四十周",
41 AS "第四十一周", 42 AS "第四十二周", 43 AS "第四十三周"
)
)
) src
ON (dest.pure_in_force_date = src.pure_in_force_date)
WHEN MATCHED THEN
UPDATE SET
dest."第一周" = src."第一周",
dest."第二周" = src."第二周",
dest."第三周" = src."第三周",
dest."第四周" = src."第四周",
dest."第五周" = src."第五周",
dest."第六周" = src."第六周",
dest."第七周" = src."第七周",
dest."第八周" = src."第八周",
dest."第九周" = src."第九周",
dest."第十周" = src."第十周",
dest."第十一周" = src."第十一周",
dest."第十二周" = src."第十二周",
dest."第十三周" = src."第十三周",
dest."第十四周" = src."第十四周",
dest."第十五周" = src."第十五周",
dest."第十六周" = src."第十六周",
dest."第十七周" = src."第十七周",
dest."第十八周" = src."第十八周",
dest."第十九周" = src."第十九周",
dest."第二十周" = src."第二十周",
dest."第二十一周" = src."第二十一周",
dest."第二十二周" = src."第二十二周",
dest."第二十三周" = src."第二十三周",
dest."第二十四周" = src."第二十四周",
dest."第二十五周" = src."第二十五周",
dest."第二十六周" = src."第二十六周",
dest."第二十七周" = src."第二十七周",
dest."第二十八周" = src."第二十八周",
dest."第二十九周" = src."第二十九周",
dest."第三十周" = src."第三十周",
dest."第三十一周" = src."第三十一周",
dest."第三十二周" = src."第三十二周",
dest."第三十三周" = src."第三十三周",
dest."第三十四周" = src."第三十四周",
dest."第三十五周" = src."第三十五周",
dest."第三十六周" = src."第三十六周",
dest."第三十七周" = src."第三十七周",
dest."第三十八周" = src."第三十八周",
dest."第三十九周" = src."第三十九周",
dest."第四十周" = src."第四十周",
dest."第四十一周" = src."第四十一周",
dest."第四十二周" = src."第四十二周",
dest."第四十三周" = src."第四十三周"
WHEN NOT MATCHED THEN
INSERT (
pure_in_force_date,
"第一周", "第二周", "第三周", "第四周", "第五周", "第六周", "第七周", "第八周", "第九周", "第十周",
"第十一周", "第十二周", "第十三周", "第十四周", "第十五周", "第十六周", "第十七周", "第十八周", "第十九周", "第二十周",
"第二十一周", "第二十二周", "第二十三周", "第二十四周", "第二十五周", "第二十六周", "第二十七周", "第二十八周", "第二十九周", "第三十周",
"第三十一周", "第三十二周", "第三十三周", "第三十四周", "第三十五周", "第三十六周", "第三十七周", "第三十八周", "第三十九周", "第四十周",
"第四十一周", "第四十二周", "第四十三周"
) VALUES (
src.pure_in_force_date,
src."第一周", src."第二周", src."第三周", src."第四周", src."第五周", src."第六周", src."第七周", src."第八周", src."第九周", src."第十周",
src."第十一周", src."第十二周", src."第十三周", src."第十四周", src."第十五周", src."第十六周", src."第十七周", src."第十八周", src."第十九周", src."第二十周",
src."第二十一周", src."第二十二周", src."第二十三周", src."第二十四周", src."第二十五周", src."第二十六周", src."第二十七周", src."第二十八周", src."第二十九周", src."第三十周",
src."第三十一周", src."第三十二周", src."第三十三周", src."第三十四周", src."第三十五周", src."第三十六周", src."第三十七周", src."第三十八周", src."第三十九周", src."第四十周",
src."第四十一周", src."第四十二周", src."第四十三周"
)';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
commit ;
/
---------------------------------纯新人均息差( Connection closed)-------------------------------------
DECLARE
v_1 DATE := TO_DATE('20250301', 'YYYYMMDD');
v_start DATE;
v_end DATE := TRUNC(SYSDATE);
v_current DATE;
v_sql VARCHAR2(32767);
BEGIN
v_start := TRUNC(v_1);
FOR i IN 0..(v_end - v_start) LOOP
v_current := v_start + i;
v_sql :=
'MERGE INTO PURE_AVG_SPREAD dest USING (
WITH t1 AS (
SELECT
a.*,
CASE
WHEN a.COUNTRY_CODE = ''tz'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ke'' THEN a.IN_FORCE_DATE + 3/24
WHEN a.COUNTRY_CODE = ''ci'' THEN a.IN_FORCE_DATE
WHEN a.COUNTRY_CODE = ''ph'' THEN a.IN_FORCE_DATE + 8/24
END AS NEW_DATE
FROM LOAN_BORROW_INFO_TEST a
WHERE a.COUNTRY_CODE <> ''gh''
AND a.RISK_SERIAL_NO <> ''googleplay''
AND a.ARCHIVED = 1
AND a.IN_FORCE_DATE IS NOT NULL
),
t2 AS (
SELECT DISTINCT phone, country_code, NEW_DATE
FROM t1
WHERE TRUNC(NEW_DATE) = TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'')
AND user_type_product = 0
),
t3 AS (
SELECT t1.NEW_DATE, t1.ACTUAL_REPAYMENT_AMOUNT, t1.ACTUAL_AMOUNT, t1.state, t1.phone
FROM t2
LEFT JOIN t1
ON t2.phone = t1.phone
AND t2.country_code = t1.country_code
AND t1.NEW_DATE >= t2.NEW_DATE
AND t1.new_date IS NOT NULL
),
t4 AS (
SELECT
CASE WHEN state != 6 THEN (ACTUAL_REPAYMENT_AMOUNT - ACTUAL_AMOUNT)/100 ELSE 0 END AS rs,
CASE WHEN state = 6 THEN ACTUAL_AMOUNT/100 ELSE 0 END AS rl,
phone,
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) + 1 AS week_num,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 AS week_start,
MIN(NEW_DATE) OVER () +
TRUNC((NEW_DATE - MIN(NEW_DATE) OVER ()) / 7) * 7 + 6 AS week_end
FROM t3
WHERE NEW_DATE BETWEEN TO_DATE(''2025-03-01'', ''YYYY-MM-DD'')
AND TRUNC(ADD_MONTHS(SYSDATE, 12), ''YYYY'') - 1
),
t5 AS (
SELECT week_num, COUNT(1) AS num
FROM (
SELECT week_num, ROW_NUMBER() OVER (PARTITION BY week_num, phone ORDER BY week_num) AS rn
FROM t4
)
WHERE rn = 1
GROUP BY week_num
),
t6 AS (
SELECT
t4.week_num,
TO_DATE(''' || TO_CHAR(v_current, 'YYYYMMDD') || ''', ''YYYYMMDD'') AS pure_in_force_date,
SUM(t4.rs - t4.rl) AS mn,
t5.num
FROM t4
LEFT JOIN t5 ON t4.week_num = t5.week_num
GROUP BY t4.week_num, t5.num
),
t7 AS (
SELECT week_num, pure_in_force_date, mn/num AS avg_Spread
FROM t6
)
SELECT *
FROM t7
PIVOT(
SUM(ROUND(avg_Spread, 6))
FOR week_num IN (
1 AS "第一周", 2 AS "第二周", 3 AS "第三周", 4 AS "第四周", 5 AS "第五周", 6 AS "第六周", 7 AS "第七周", 8 AS "第八周", 9 AS "第九周", 10 AS "第十周",
11 AS "第十一周", 12 AS "第十二周", 13 AS "第十三周", 14 AS "第十四周", 15 AS "第十五周", 16 AS "第十六周", 17 AS "第十七周", 18 AS "第十八周", 19 AS "第十九周", 20 AS "第二十周",
21 AS "第二十一周", 22 AS "第二十二周", 23 AS "第二十三周", 24 AS "第二十四周", 25 AS "第二十五周", 26 AS "第二十六周", 27 AS "第二十七周", 28 AS "第二十八周", 29 AS "第二十九周", 30 AS "第三十周",
31 AS "第三十一周", 32 AS "第三十二周", 33 AS "第三十三周", 34 AS "第三十四周", 35 AS "第三十五周", 36 AS "第三十六周", 37 AS "第三十七周", 38 AS "第三十八周", 39 AS "第三十九周", 40 AS "第四十周",
41 AS "第四十一周", 42 AS "第四十二周", 43 AS "第四十三周"
)
)
) src
ON (dest.PURE_IN_FORCE_DATE = src.pure_in_force_date)
WHEN MATCHED THEN
UPDATE SET
dest."第一周" = src."第一周",
dest."第二周" = src."第二周",
dest."第三周" = src."第三周",
dest."第四周" = src."第四周",
dest."第五周" = src."第五周",
dest."第六周" = src."第六周",
dest."第七周" = src."第七周",
dest."第八周" = src."第八周",
dest."第九周" = src."第九周",
dest."第十周" = src."第十周",
dest."第十一周" = src."第十一周",
dest."第十二周" = src."第十二周",
dest."第十三周" = src."第十三周",
dest."第十四周" = src."第十四周",
dest."第十五周" = src."第十五周",
dest."第十六周" = src."第十六周",
dest."第十七周" = src."第十七周",
dest."第十八周" = src."第十八周",
dest."第十九周" = src."第十九周",
dest."第二十周" = src."第二十周",
dest."第二十一周" = src."第二十一周",
dest."第二十二周" = src."第二十二周",
dest."第二十三周" = src."第二十三周",
dest."第二十四周" = src."第二十四周",
dest."第二十五周" = src."第二十五周",
dest."第二十六周" = src."第二十六周",
dest."第二十七周" = src."第二十七周",
dest."第二十八周" = src."第二十八周",
dest."第二十九周" = src."第二十九周",
dest."第三十周" = src."第三十周",
dest."第三十一周" = src."第三十一周",
dest."第三十二周" = src."第三十二周",
dest."第三十三周" = src."第三十三周",
dest."第三十四周" = src."第三十四周",
dest."第三十五周" = src."第三十五周",
dest."第三十六周" = src."第三十六周",
dest."第三十七周" = src."第三十七周",
dest."第三十八周" = src."第三十八周",
dest."第三十九周" = src."第三十九周",
dest."第四十周" = src."第四十周",
dest."第四十一周" = src."第四十一周",
dest."第四十二周" = src."第四十二周",
dest."第四十三周" = src."第四十三周"
WHEN NOT MATCHED THEN
INSERT (
pure_in_force_date,
"第一周", "第二周", "第三周", "第四周", "第五周", "第六周", "第七周", "第八周", "第九周", "第十周",
"第十一周", "第十二周", "第十三周", "第十四周", "第十五周", "第十六周", "第十七周", "第十八周", "第十九周", "第二十周",
"第二十一周", "第二十二周", "第二十三周", "第二十四周", "第二十五周", "第二十六周", "第二十七周", "第二十八周", "第二十九周", "第三十周",
"第三十一周", "第三十二周", "第三十三周", "第三十四周", "第三十五周", "第三十六周", "第三十七周", "第三十八周", "第三十九周", "第四十周",
"第四十一周", "第四十二周", "第四十三周"
) VALUES (
src.pure_in_force_date,
src."第一周", src."第二周", src."第三周", src."第四周", src."第五周", src."第六周", src."第七周", src."第八周", src."第九周", src."第十周",
src."第十一周", src."第十二周", src."第十三周", src."第十四周", src."第十五周", src."第十六周", src."第十七周", src."第十八周", src."第十九周", src."第二十周",
src."第二十一周", src."第二十二周", src."第二十三周", src."第二十四周", src."第二十五周", src."第二十六周", src."第二十七周", src."第二十八周", src."第二十九周", src."第三十周",
src."第三十一周", src."第三十二周", src."第三十三周", src."第三十四周", src."第三十五周", src."第三十六周", src."第三十七周", src."第三十八周", src."第三十九周", src."第四十周",
src."第四十一周", src."第四十二周", src."第四十三周"
)';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
commit ;
/
END;
/
begin
PURE_REPAYMENT_AMOUNT_FIVE();
end;执行后报[65000][6550] ORA-06550: 第 2 行, 第 5 列: PLS-00905: 对象 ADMIN.PURE_REPAYMENT_AMOUNT_FIVE 无效 ORA-06550: 第 2 行, 第 5 列: PL/SQL: Statement igno ...