03/23~03/29周报

本文深入探讨CSS的高级特性,包括计数器、边框、背景、渐变、文本溢出、换行、2D及3D转换、过渡与动画、多列布局和用户界面的调整。详细解析了如linear-gradient、radial-gradient、text-overflow、word-wrap、translate、rotate、scale、skew、matrix、@keyframes规则等关键CSS属性的使用方法。

【本周任务】
一、JavaScript(第一到第三章)
二、css复习
1.计数器
counter-reset - 创建或者重置计数器
counter-increment - 递增变量
content - 插入生成的内容
counter() 或 counters() 函数 - 将计数器的值添加到元素

body {
  counter-reset: section;
}
 
h2::before {
  counter-increment: section;
  content: "Section " counter(section) ": ";
}

2.边框
(1)阴影(box-shadow)
(2) 边界图片(border-image)
有round(平铺)和stretch(拉伸)
3. background
background-image(可以给不同的图片设置多个不同的属性)

#example1 { 
    background-image: url(img_flwr.gif), url(paper.gif); 
    background-position: right bottom, left top; 
    background-repeat: no-repeat, repeat; 
}

background-size(指定背景图像的大小)
background-origin(指定了背景图像的位置区域)
在这里插入图片描述
background-clip(背景剪裁属性是从指定位置开始绘制)
4.渐变
线性渐变(Linear Gradients)- 向下/向上/向左/向右/对角方向

 background-image: linear-gradient(#e66465, #9198e5);

径向渐变(Radial Gradients)- 由它们的中心定义

 background-image: radial-gradient(red, yellow, green);

5.Text Overflow

text-overflow: ellipsis; 

多出来的会省略
6.换行
长文本换行

p {word-wrap:break-word;}

单词拆分换行

p.test1 {
   word-break: keep-all;
}

7.2D转换
translate()
根据左(X轴)和顶部(Y轴)位置给定的参数,从当前元素位置移动。
rotate()
在一个给定度数顺时针旋转的元素
scale()
该元素增加或减少的大小,取决于宽度(X轴)和高度(Y轴)的参数
skew()
包含两个参数值,分别表示X轴和Y轴倾斜的角度,如果第二个参数为空,则默认为0,参数为负表示向相反方向倾斜。
*skewX(< angle>);表示只在X轴(水平方向)倾斜。
*skewY(< angle>);表示只在Y轴(垂直方向)倾斜。
matrix()
matrix 方法有六个参数,包含旋转,缩放,移动(平移)和倾斜功能
8.3D转换
rotateX()
rotateY()
9.过渡(transition 用于在一个属性中设置四个过渡属性)
transition-property 规定应用过渡的 CSS 属性的名称。
transition-duration 定义过渡效果花费的时间。默认是 0。
transition-timing-function 规定过渡效果的时间曲线。默认是 “ease”。
transition-delay规定过渡效果何时开始。默认是 0。
9.动画
@keyframes 规则
(1)from to
(2)百分比
10.多列
column-count分割的列数
column-gap列与列间的间隙
column-rule-style边框样式
column-rule-width
column-rule-color
column-rule 所有属性的简写
column-span指定元素跨越多少列
column-width
11.用户界面
(1)resize属性指定一个元素是否应该由用户去调整大小(both)
(2)box-sizing 属性允许您以确切的方式定义适应某个区域的具体内容
规定两个并排的带边框方框:

div
{
    box-sizing:border-box;
    width:50%;
    float:left;
}

(3)outline-offset 属性对轮廓进行偏移,并在超出边框边缘的位置绘制轮廓
规定边框边缘之外 15 像素处的轮廓:

div
{
    border:2px solid black;
    outline:2px solid red;
    outline-offset:15px;
}

【下周任务】
学习JS

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、付费专栏及课程。

余额充值