T4 第五周

#include <iostream>

using namespace std;

class Student
{
public:
	Student(int n=1,int s=1):num(n),score(s){}
	int showmax();
	void showmessage();

private:
	int num;
	int score;
};
void main()
{
	Student *p;
	Student a[5]=
	{
		Student(10010,80),
		Student(10011,85),
		Student(10012,73),
		Student(10013,98),
		Student(10014,99)
	};

	p=&a[0];
	for(int i=0;i<=3;i++)
	{
		if((*p).showmax()<a[i+1].showmax())
			p=&a[i+1];


	}
	(*p).showmessage();
}
void Student:: showmessage()
{
	cout<<num<<'\t'<<score;
}
int Student::showmax()
{
	return score;
}

 
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 ORDER 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; 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 ORDER 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; 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 ORDER 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; [42000][904] ORA-00904: "YYYY": 标识符无效 ORA-06512: 在 line 67
06-07
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 ...
06-07
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值