第三周

一、树与二叉树

1. 树的定义
树是一种数据结构,它是由n(n>=1)个有限节点组成一个具有层次关系的集合。

在这里插入图片描述

把它叫做“树”是因为它看起来像一棵倒挂的树,也就是说它是根朝上,而叶朝下的。它具有以下的特点:
(01) 每个节点有零个或多个子节点;
(02) 没有父节点的节点称为根节点;
(03) 每一个非根节点有且只有一个父节点;
(04) 除了根节点外,每个子节点可以分为多个不相交的子树。

2. 树的基本术语
若一个结点有子树,那么该结点称为子树根的"双亲",子树的根是该结点的"孩子"。有相同双亲的结点互为"兄弟"。一个结点的所有子树上的任何结点都是该结点的后裔。从根结点到某个结点的路径上的所有结点都是该结点的祖先。

结点的度:结点拥有的子树的数目。
叶子:度为零的结点。
分支结点:度不为零的结点。
树的度:树中结点的最大的度。

层次:根结点的层次为1,其余结点的层次等于该结点的双亲结点的层次加1。
树的高度:树中结点的最大层次。
无序树:如果树中结点的各子树之间的次序是不重要的,可以交换位置。
有序树:如果树中结点的各子树之间的次序是重要的, 不可以交换位置。
森林:0个或多个不相交的树组成。对森林加上一个根,森林即成为树;删去根,树即成为森林。

二叉树的介绍
1. 二叉树的定义
二叉树是每个节点最多有两个子树的树结构。它有五种基本形态:二叉树可以是空集;根可以有空的左子树或右子树;或者左、右子树皆为空。

在这里插入图片描述

2. 二叉树的性质
二叉树有以下几个性质:TODO(上标和下标)
性质1:二叉树第i层上的结点数目最多为 2{i-1} (i≥1)。
性质2:深度为k的二叉树至多有2{k}-1个结点(k≥1)。
性质3:包含n个结点的二叉树的高度至少为log2 (n+1)。
性质4:在任意一棵二叉树中,若终端结点的个数为n0,度为2的结点数为n2,则n0=n2+1。

2.1 性质1:二叉树第i层上的结点数目最多为 2{i-1} (i≥1)
证明:下面用"数学归纳法"进行证明。
(01) 当i=1时,第i层的节点数目为2{i-1}=2{0}=1。因为第1层上只有一个根结点,所以命题成立。
(02) 假设当i>1,第i层的节点数目为2{i-1}。这个是根据(01)推断出来的!
下面根据这个假设,推断出"第(i+1)层的节点数目为2{i}“即可。
由于二叉树的每个结点至多有两个孩子,故"第(i+1)层上的结点数目” 最多是 “第i层的结点数目的2倍”。即,第(i+1)层上的结点数目最大值=2×2{i-1}=2{i}。
故假设成立,原命题得证!

2.2 性质2:深度为k的二叉树至多有2{k}-1个结点(k≥1)

证明:在具有相同深度的二叉树中,当每一层都含有最大结点数时,其树中结点数最多。利用"性质1"可知,深度为k的二叉树的结点数至多为:
20+21+…+2k-1=2k-1
故原命题得证!

2.3 性质3:包含n个结点的二叉树的高度至少为log2 (n+1)
证明:根据"性质2"可知,高度为h的二叉树最多有2{h}–1个结点。反之,对于包含n个节点的二叉树的高度至少为log2(n+1)。

2.4 性质4:在任意一棵二叉树中,若终端结点的个数为n0,度为2的结点数为n2,则n0=n2+1
证明:因为二叉树中所有结点的度数均不大于2,所以结点总数(记为n)=“0度结点数(n0)” + “1度结点数(n1)” + “2度结点数(n2)”。由此,得到等式一。
(等式一) n=n0+n1+n2
  另一方面,0度结点没有孩子,1度结点有一个孩子,2度结点有两个孩子,故二叉树中孩子结点总数是:n1+2n2。此外,只有根不是任何结点的孩子。故二叉树中的结点总数又可表示为等式二。
(等式二) n=n1+2n2+1
由(等式一)和(等式二)计算得到:n0=n2+1。原命题得证!

3. 满二叉树,完全二叉树和二叉查找树
3.1 满二叉树
定义:高度为h,并且由2{h} –1个结点的二叉树,被称为满二叉树。

在这里插入图片描述

3.2 完全二叉树
定义:一棵二叉树中,只有最下面两层结点的度可以小于2,并且最下一层的叶结点集中在靠左的若干位置上。这样的二叉树称为完全二叉树。
特点:叶子结点只能出现在最下层和次下层,且最下层的叶子结点集中在树的左部。显然,一棵满二叉树必定是一棵完全二叉树,而完全二叉树未必是满二叉树。

在这里插入图片描述

3.3 二叉查找树
定义:二叉查找树(Binary Search Tree),又被称为二叉搜索树。设x为二叉查找树中的一个结点,x节点包含关键字key,节点x的key值记为key[x]。如果y是x的左子树中的一个结点,则key[y] <= key[x];如果y是x的右子树的一个结点,则key[y] >= key[x]。
另一种定义:根节点的值大于等于其左子树中任意一个节点的值,小于等于其右节点中任意一节点的值,这一规则适用于二叉查找树中的每一个节点。
在这里插入图片描述

在二叉查找树中:
(01) 若任意节点的左子树不空,则左子树上所有结点的值均小于它的根结点的值;
(02) 任意节点的右子树不空,则右子树上所有结点的值均大于它的根结点的值;
(03) 任意节点的左、右子树也分别为二叉查找树。
(04) 没有键值相等的节点(no duplicate nodes)。

二、快排与归并

1.快速排序

从数组中随机选一个数,比这个数大的放右边,比这个数小的放左边。
如果一个数等于P的时候,既可以在左边又可以在右边。这么做是为了避免如果数组中所有的数都一样,则会造成数据的不平衡。快排的目的是能够使得左边和右边的数都差不多,这样的话时间复杂度就不会退化到O(N^2),而是O(N * logN)。
每次left 和 right进行比较的时候以,要用 <= 而不是 < 。但是当做A[left]/A[right]和 pivot比较的时候,应该用< 而不是 <=。因为单纯用 < 会造成死循环。在最左端只剩下两个数的时候,会造成一个死循环。有交集就会出现问题。只要是左右比较的时候,用 <= 而不用 <;当与中心点做比较的时候,用 < 而不用 <= 。

public void quicksort(int[] A, int left, int right){
    if (left > right) {
        return;
    }
    int mid = (left + right) /2
    int target = A[mid];
    while (left <= right) {
        while (left <= right && A[left] < target) {
            left++;
        }
        while (left <= right && A[right] > target) {
            right--;
        }
        if (left <= right) {
            int tmp = A[left];
            A[left] = A[right];
            A[right] = tmp;
        }
    }
quicksort(int[] A, 0, mid);
quicksort(int[] A, mid + 1, A.length - 1);
}

2.归并排序

先局部有序,再全局有序。但是由于需要合并两个数组,所以需要额外空间。由于开辟空间和回收空间需要时间消耗,所以归并排序在实际应用上没有快排好。

Merge sort 采用分治的方法,但也是递归。 用双指针方法比较两个排序好的数组。代码的重点在于merge的过程。需要注意的点是当循环结束之后,left 或者 right中有一个可能是没有全部放进去的,这时候要再分别循环一次以保证所有数都放进去了。

public void Sort(int[] A) {
    int[] result = new int[A.length];
    mergeSort(A, 0, A.length - 1, result);
    }
 
public void mergeSort(int[] A, int start, int end, int[] result) {
    if (start > end) {
        return;
    }
    int mid = (start + end) / 2;
    mergeSort(A, start, mid, result);
    mergeSort(A, mid + 1, end, result);
    merge(A, start, end, result);
}
 
public void merge(int[] A, int start, int end, int[] result) {
    int mid = (start + end) / 2;
    int leftIndex = start;
    int rightIndex = mid + 1;
    int tmp = 0;
    while (leftIndex <= mid && righIndex <= end) {
        if (A[leftIndex] <= A[rightIndex]) {
            result[tmp] = A[leftIndex];
            tmp++;
            leftIndex++;
        }
        else {
            result[tmp] = A[rightIndex];
            tmp++;
            rightIndex++;
        }
        while(leftIndex <= mid) {
            result[tmp] = A[leftIndex];
            tmp++;
            leftIndex++;
        }
        while(rightIndex <= end) {
            result[tmp] = A[rightIndex];
            tmp++;
            rightIndex++;
        }
    for(int i = 0; i <= end; i++){
        A[i] = result[i];
    }
}

三、BFS和DFS

在这里插入图片描述
1.DFS(深度优先搜索遍历)
从顶点v出发深度遍历图的算法
① 访问v
② 依次从顶点v未被访问的邻接点出发深度遍历。

#include<iostream>
#include<string.h>
using namespace std;  
int a[11][11];  
bool visited[11];  
  
void store_graph()  //邻接矩阵存储图  
{  
    int i,j;  
  
    for(i=1;i<=10;i++)  
        for(j=1;j<=10;j++)  
            cin>>a[i][j];  
}

void dfs_graph()    //深度遍历图  
{  
    void dfs(int v);  
  
    memset(visited,false,sizeof(visited));  
  
    for(int i=1;i<=10;i++)  //遍历每个顶点是为了防止图不连通时无法访问每个顶点  
        if(visited[i]==false)  
            dfs(i);
}

void dfs(int v)  //深度遍历顶点  
{  
    int Adj(int x);  
  
    cout<<v<<" ";  //访问顶点v
    visited[v]=true;
  
    int adj=Adj(v);
    while(adj!=0)
    {  
        if(visited[adj]==false)
            dfs(adj);      //递归调用是实现深度遍历的关键所在  
  
        adj=Adj(v);
    }  
}
  
int Adj(int x)   //求邻接点  
{
    for(int i=1;i<=10;i++)  
        if(a[x][i]==1 && visited[i]==false)  
            return i;  
  
    return 0;  
}
  
int main()  
{  
    cout<<"初始化图:"<<endl;  
    store_graph();  
  
    cout<<"dfs遍历结果:"<<endl;  
    dfs_graph();  
  
    return 0;  
}  

在这里插入图片描述
2.BFS(广度优先搜索遍历)
从顶点v出发遍历图的算法买描述如下:
①访问v
②假设最近一层的访问顶点依次为vi1,vi2,vi3…vik,则依次访问vi1,vi2,vi3…vik的未被访问的邻接点
③重复②知道没有未被访问的邻接点为止

#include<iostream>  
#include<queue>
#include<string.h>
using namespace std;  
  
int a[11][11];  
bool visited[11];  
  
void store_graph()    
{  
    for(int i=1;i<=10;i++)  
        for(int j=1;j<=10;j++)  
            cin>>a[i][j];  
}  
  
void bfs_graph()      
{  
    void bfs(int v);  
  
    memset(visited,false,sizeof(visited));  
  
    for(int i=1;i<=10;i++)    
        if(visited[i]==false)  
            bfs(i);  
}  
  
void bfs(int v)  
{  
    int Adj(int x);  
  
    queue<int> myqueue;  
    int adj,temp;  
  
    cout<<v<<" ";  
    visited[v]=true;  
    myqueue.push(v);  
  
    while(!myqueue.empty())    //队列非空表示还有顶点未遍历到  
    {  
        temp=myqueue.front();  //获得队列头元素
        myqueue.pop();         //头元素出队
  
        adj=Adj(temp);  
        while(adj!=0)  
        {  
            if(visited[adj]==false)  
            {  
                cout<<adj<<" ";  
                visited[adj]=true;  
                myqueue.push(adj);   //进队
            }  
  
            adj=Adj(temp);  
        }  
    }  
}  
  
int Adj(int x)     
{  
    for(int i=1;i<=10;i++)  
        if(a[x][i]==1 && visited[i]==false)  
            return i;  
  
    return 0;  
}  
  
int main()  
{  
    cout<<"初始化图:"<<endl;  
    store_graph();  
  
    cout<<"bfs遍历结果:"<<endl;  
    bfs_graph();  
  
    return 0;  
}  

在这里插入图片描述
bfs与dfs区别
在这里插入图片描述
总结:dfs()到达递归终止条件即返回,否则继续递归;
bfs()首先创建一个队列,然后把队首元素压入队列,再把下一层逐个元素送入队列

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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值