第四周13/03/31

继续坚持记录每一天的收获

A. Roma and Changing Signs

题目链接 点击打开链接

此题很简单,只要细心就可以做。思路:先判断负数个数S跟操作步骤K的关系,若S>=K,则直接把K个负数变正数再求和即可,若S<K ,则先把所有负数变正数,再排序,剩下的K-S次对最小数操作  PS:最小数的改变对整个序列求和影响最小
代码:
#include <iostream>//A. Roma and Changing Signs
#include <stdio.h>
#include <cstring>
#include <algorithm>
#include <cmath>
#include <vector>
#include <map>
#include <stdlib.h>
#include <queue>
using namespace std;
int a[100005];
int main()
{
    int n,k,s=0,sum=0,i;
    scanf("%d%d",&n,&k);
    for(i=0; i<n; i++)
    {
        scanf("%d",&a[i]);
        if(a[i]<0)
            s++;
    }
    if(k<=s)
    {
        for(i=0; i<n; i++)
        {
            if(i<k)
                sum-=a[i];
            else
                sum+=a[i];
        }
    }
    else
    {
        for(i=0;i<s;i++)
        {
            a[i]=-a[i];
        }
        sort(a,a+n);
        for(i=1;i<n;i++)
        sum+=a[i];
        if((k-s)%2==1)
        sum-=a[0];
        else
        sum+=a[0];
    }
    printf("%d\n",sum);
    return 0;
}

B. Maxim and Discounts

题目链接: 点击打开链接

题目大意:去买东西有多种折扣,比如买Ai种商品最多免费取两个,问怎样使用折扣使得买到一样的东西花钱最少,但是免费拿的物品的价值不能超过篮子里最便宜的物品

思路:每次我们都取最小的篮子(Ai),因为让最小的篮子装满是最快的,我们免费拿的商品就比较多,对物品价值进行从大到小排序

代码:

#include <iostream>//B. Maxim and Discounts
#include <stdio.h>
#include <cstring>
#include <algorithm>
#include <cmath>
#include <vector>
#include <map>
#include <stdlib.h>
#include <queue>
using namespace std;
const int M=1000005;
int b[M],a;
int cmp(int a,int b)
{
    return a>b;
}
int main()
{
    int m,n,i,mmin=M,j;
    scanf("%d",&m);
    for(i=0; i<m; i++)
    {
        scanf("%d",&a);
        if(a<mmin)
            mmin=a;
    }
    scanf("%d",&n);
    for(i=0; i<n; i++)
        scanf("%d",&b[i]);
    sort(b,b+n,cmp);
    int sum=0;
    j=0;
    while(j<n)
    {
        for(i=1; i<=mmin&&i<=n; i++)
        {
            sum+=b[j++];
        }
        j+=2;
    }
    printf("%d\n",sum);
    return 0;
}

C. Maxim and Restaurant

题目 链接: 点击打开链接

看不懂,再说咯~~

代码:

#include <iostream>//c
#include <stdio.h>
#include <cstring>
#include <algorithm>
#include <cmath>
#include <vector>
#include <map>
#include <stdlib.h>
#include <queue>
using namespace std;
#define maxn 55
int a[maxn];
double fac[maxn],dp[maxn][maxn];
int main()
{
    int n,p;
    fac[0]=1;
    for(int i=1;i<=maxn;i++)
    fac[i]=i*fac[i-1];
    scanf("%d",&n);
    for(int i=0;i<n;i++)
    scanf("%d",&a[i]);
    scanf("%d",&p);
    double ans=0;
    for(int i=0;i<n;i++)
    {
        memset(dp,0,sizeof(dp));
        dp[0][0]=1;
        for(int l=0;l<n;l++)
        {
            if(l!=i)
            {
                for(int j=n-1;j>=1;j--)
                {
                    for(int k=p;k>=0;k--)
                    {
                        if(k>=a[l])
                        dp[j][k]+=dp[j-1][k-a[l]];
                    }
                }
            }
        }
        for(int j=0;j<n;j++)
        {
            for(int k=0;k<=p-a[i];k++)
            {
                ans+=dp[j][k]*fac[j]*fac[n-j-1];
            }
        }
    }
    printf("%.10lf\n",1.0*ans/fac[n]);
    return 0;
}


D. Squares

题目链接:点击打开链接

大水题,直接排序,取第K个点(K,0)即可

代码:

#include <iostream>//D. Squares
#include <stdio.h>
#include <cstring>
#include <cmath>
#include <algorithm>
using namespace std;
int a[55];
int f(int a,int b)
{
    return a>b;
}
int main()
{
    int n,k,i;
    cin>>n>>k;
    for(i=0;i<n;i++)
    cin>>a[i];
    if(k>n)
    {
        printf("-1\n");
        return 0;
    }
    sort(a,a+n,f);
    int x=a[k-1];
    int y=0;
    printf("%d %d",x,y);
    return 0;
}

F. Cycle in Graph

题目链接: 点击打开链接

唉,今天就做到这一题了
题意:给一个无序图,建图,查找图上有环的,判断环中是否有K+1个顶点
思路:建一个邻接表,进行DFS,也可以用Vector(这个的使用以后再说)
代码:
#include <iostream>
#include <stdio.h>
#include <algorithm>
#include <cmath>
#include <cstring>
#include <vector>
using namespace std;
struct Node
{
    int v,next;
}node[200010];
int n,m,k;
bool vis[100005];
int head[100005],edge;
void link(int u,int v)
{
    node[edge].v=v;
    node[edge].next=head[u];
    head[u]=edge++;
}
int ans[100005];
void find(int u,int len)
{
    if(!vis[u])
    {
        vis[u]=true;
        ans[len++]=u;
        if(len>k)
        {
            for(int i=head[u];i;i=node[i].next)
            {
                int v=node[i].v;
                if(v==ans[0])
                {
                    printf("%d\n",len);
                    for(int i=0;i<len;i++)
                    printf("%d ",ans[i]);
                    printf("\n");
                    //return;
                    exit(0);
                }
            }
        }
        for(int i=head[u];i;i=node[i].next)
        {
            find(node[i].v,len);
        }
        vis[u]=false;
    }
}
int main()
{
    int i,a,b;
    scanf("%d%d%d",&n,&m,&k);
    for(i=0;i<m;i++)
    {
        scanf("%d%d",&a,&b);
        link(a,b);
        link(b,a);
    }
    for(i=1;i<=n;i++)
    {
        find(i,0);
    }
    return 0;

}


G. Roadside Trees (Simplified Edition)

直接模拟,水题
代码:

#include <stdio.h>//G. Roadside Trees (Simplified Edition)
#include <iostream>
#include <cmath>
#include <algorithm>
#include <cstring>
using namespace std;
const int M=100005;
int tree[M];
int main()
{
    int n,i,s;
    scanf("%d",&n);
    for(i=1; i<=n; i++)
        scanf("%d",&tree[i]);
    s=n+tree[1];
    for(i=2; i<=n; i++)
    {
        if(tree[i]>tree[i-1])
            s+=(tree[i]-tree[i-1]);
        else
            s+=(tree[i-1]-tree[i]);
    }
    s+=n-1;
    printf("%d\n",s);
    return 0;
}

H. Escape from Stones

利用二分的思想,第一次向左跳,那个位置肯定处于最右边,第一次向右跳,那个位置肯定处于最左边,而且R总是处在L的左边,所以我们可以从左到右依次输出R的编号,从右往左依次输出L的编号
代码:
#include <iostream>
#include <algorithm>
#include <cstring>
#include <cmath>
#include <stdio.h>
#include <vector>
using  namespace std;
const int M=1000005;
char a[M];
int main()
{
    scanf("%s",a);
    int l=strlen(a);
    for(int i=0;i<l;i++)
    {
        if(a[i]=='r')cout<<i+1<<endl;
    }
    for(int i=l-1;i>=0;i--)
    {
        if(a[i]=='l')cout<<i+1<<endl;
    }
    return 0;
}

I. Good Sequences

看了好多代码,基本用的都是DP做的,真神奇。。
第一种方法:首先把不为互质数放在一块,应用DP求最好序列长度;
第二种方法:计算因子出现最多的那序列的长度,重点:具有两个以上因子的数,到达它时各个因子步骤是一样的,使它的因子序列长度全部变成它最好的序列长度,也就是这个a[i]的最好序列长度(转:扫描到序列中某个元素时,能不能将它加到之前的序列的末尾,只要看它与之前序列的最后一个元素是不是有公因子就行了
第一种代码:
#include <iostream>
#include <algorithm>
#include <cstring>
#include <cmath>
#include <stdio.h>
#include <vector>
using  namespace std;
const int M=100005;
int a[M],in[M];
int dp[M];
vector<int>lis[M];
int main()
{
    memset(dp,0,sizeof(dp));
    memset(in,0,sizeof(in));
    int n,i,Max=0,j;
    cin>>n;
    for(i=1; i<=n; i++)
    {
        scanf("%d",&a[i]);
        in[a[i]]=1;
        Max=max(Max,a[i]);
    }
    for(i=2; i<=Max; i++)
    {
        int tmp=0;
        for(j=i; j<=Max; j+=i)
        {
            if(in[j]==1)
            {
                if(tmp)
                    lis[j].push_back(tmp);
                tmp=j;
            }
        }
    }
    int ans=1;
    for(i=2; i<=Max; i++)
    {
        if(in[i]==1)
        {
            dp[i]=1;
            for(j=0; j<lis[i].size(); j++)
            {
                dp[i]=max(dp[lis[i][j]]+1,dp[i]);
            }
            ans=max(ans,dp[i]);
        }
    }
    printf("%d\n",ans);
    return 0;
}

第二种:

#include <iostream>
#include <algorithm>
#include <cstring>
#include <cmath>
#include <stdio.h>
#include <vector>
using  namespace std;
const int M=100005;
int dp[M];
int num[M];
int a[M];
int main()
{
    int n,ans=1,mmax,len,i,j;
    cin>>n;
    for(i=1;i<=n;i++)
    {
        scanf("%d",&a[i]);
        len=0;//记录因子个数
        mmax=0;
        for(j=2;j*j<=a[i];j++)
        {
            if( a[i]%j == 0)
            {
                num[len++]=j;
                if(dp[j]+1>mmax)
                mmax=dp[j]+1;
                while(a[i]%j == 0)
                a[i]/=j;
            }
        }
        if(a[i]>1)
        {
            num[len++]=a[i];
            if(dp[a[i]]+1 > mmax)
                mmax=dp[a[i]]+1;
        }
        for(int k=0;k<len;k++)//同一个式子产生的因子相当于因子个数一样多
        {
            dp[num[k]]=mmax;
        }
        if(mmax>ans)
        ans=mmax;
    }
    printf("%d\n",ans);
    return 0;
}







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
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值