B. The least round way

本文介绍了一种算法,用于在一个非负整数构成的矩阵中找到一条从左上角到右下角的路径,使得路径上的数值乘积结束时包含的零最少。通过动态规划方法分别计算2和5因子的数量,并考虑特殊情况处理。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

There is a square matrix n × n, consisting of non-negative integer numbers. You should find such a way on it that

starts in the upper left cell of the matrix;
each following cell is to the right or down from the current cell;
the way ends in the bottom right cell.
Moreover, if we multiply together all the numbers along the way, the result should be the least “round”. In other words, it should end in the least possible number of zeros.

Input
The first line contains an integer number n (2 ≤ n ≤ 1000), n is the size of the matrix. Then follow n lines containing the matrix elements (non-negative integer numbers not exceeding 109).

Output
In the first line print the least number of trailing zeros. In the second line print the correspondent way itself.

Examples
input
3
1 2 3
4 5 6
7 8 9
output
0
DDRR
思路:利用dp思想,寻找2最少与5最少,最后也需要考虑特殊情况当数字为0时, 输入时记得用scanf输入。

#include <iostream>
#include <cstring>
#include <cmath>
#include <cstdio>
using namespace std;

const int MAX = 1010;
int n;

struct data{
    int value;
    int num_2;
    int num_5;
    char last_path[2];  //0 : "2_path" 1: "5_path"
}data[MAX][MAX];

int buffer_dp[MAX][MAX]={
    0
};


int count(int number,int base){
    int count = 0;
    if(number==0)
        return count;      
    while(number%base==0){
        count++;
        number=number/base;
    }
    return count;
}

void path_2(){
    buffer_dp[0][0] = data[0][0].num_2;
    for(int j = 1;j<n;++j){
        buffer_dp[0][j] = buffer_dp[0][j-1] + data[0][j].num_2;
        data[0][j].last_path[0] = 'R';
    }
    for(int i = 1;i<n;++i){
        buffer_dp[i][0] = buffer_dp[i-1][0] + data[i][0].num_2;
        data[i][0].last_path[0] = 'D';
    }

    for(int i=1;i<n;++i){
        for(int j = 1;j<n;++j){
            if(buffer_dp[i-1][j]<buffer_dp[i][j-1]){
                buffer_dp[i][j] = buffer_dp[i-1][j] + data[i][j].num_2;
                data[i][j].last_path[0] = 'D';
            }   
            else{
                buffer_dp[i][j] = buffer_dp[i][j-1] + data[i][j].num_2;
                data[i][j].last_path[0] = 'R';
            }
        }
    }             
}

void path_5(){
    buffer_dp[0][0] = data[0][0].num_5;
    for(int j = 1;j<n;++j){
        buffer_dp[0][j] = buffer_dp[0][j-1] + data[0][j].num_5;
        data[0][j].last_path[1] = 'R';
    }
    for(int i = 1;i<n;++i){
        buffer_dp[i][0] = buffer_dp[i-1][0] + data[i][0].num_5;
        data[i][0].last_path[1] = 'D';
    }

    for(int i=1;i<n;++i){
        for(int j = 1;j<n;++j){
            if(buffer_dp[i-1][j]<buffer_dp[i][j-1]){
                buffer_dp[i][j] = buffer_dp[i-1][j] + data[i][j].num_5;
                data[i][j].last_path[1] = 'D';
            }   
            else{
                buffer_dp[i][j] = buffer_dp[i][j-1] + data[i][j].num_5;
                data[i][j].last_path[1] = 'R';
            }
        }
    }             
}

void Print(int i,int j,int index){

    if( i==0 && j==0 ){
        return ;
    }

    char direction = data[i][j].last_path[index];


    if( direction == 'D')
        Print(i-1,j,index);
    else
        Print(i,j-1,index);
    cout<<direction;
}

void Print_zero(int zero_x,int zero_y){

    for(int i = 0;i<zero_x;++i)
        cout<<"D";
    for(int j = 0;j<zero_y;++j)
        cout<<"R";
    for(int i = zero_x+1 ;i<n;++i)
        cout<<"D";
    for(int j = zero_y+1 ;j<n;++j)
        cout<<"R";

}


int main(){

    bool zero = false;
    int zero_x ,zero_y;
    int value;
    cin>>n;
    for(int i=0;i<n;++i){
        for(int j=0;j<n;++j){
            scanf("%d",&value);
            data[i][j].value = value;

            if( value == 0){
                zero = true;
                zero_x = i;
                zero_y = j;
            }

            data[i][j].value = value;
            data[i][j].num_2 = count(value,2);
            data[i][j].num_5 = count(value,5);
        }
    }
    path_2();
    int min_2 = buffer_dp[n-1][n-1];
    if(min_2==0){
        cout<<min_2<<endl,Print(n-1,n-1,0);
        cout<<endl;
        return 0;
    }



    path_5();
    int min_5 = buffer_dp[n-1][n-1];
    if(min_5==0){
        cout<<min_5<<endl,Print(n-1,n-1,1);
        cout<<endl;
        return 0;
    }


    if(!zero)
        min_2 < min_5?(cout<<min_2<<endl,Print(n-1,n-1,0)):(cout<<min_5<<endl,Print(n-1,n-1,1));
    else{
        if(1<=min(min_2,min_5))
            cout<<1<<endl,Print_zero(zero_x,zero_y);
        else
            min_2 < min_5?(cout<<min_2<<endl,Print(n-1,n-1,0)):(cout<<min_5<<endl,Print(n-1,n-1,1));
    }   
    cout<<endl;

    return 0;
}
select * from( with vtailratio_tmp as ( select distinct d.*,e.OPERATE_WAY,e.min_tail_fare,e.YEAR_DAY,e.TAIL_COMMISSION_RATIO,e.AREA_FLAG,e.MIN_HOSTING,e.ENABLE_DATE,e.MAX_HOSTING from distributor_fund_protocol d, (select a.fund_code,a.distributor_code, nvl(c.OPERATE_WAY,b.OPERATE_WAY) OPERATE_WAY, 0.00 min_tail_fare, decode(nvl(c.YEAR_DAY,b.YEAR_DAY), '365', '1', '2', '0', '3', '3', '0', '2' ) YEAR_DAY, nvl(c.TAIL_COMMISSION_RATIO,b.TAIL_COMMISSION_RATIO) TAIL_COMMISSION_RATIO, nvl(c.AREA_FLAG,b.AREA_FLAG) AREA_FLAG, nvl(c.MIN_HOSTING,b.MIN_HOSTING) MIN_HOSTING, nvl(c.ENABLE_DATE,b.ENABLE_DATE) ENABLE_DATE, nvl(c.MAX_HOSTING,b.MAX_HOSTING) MAX_HOSTING from (select fund_code,distributor_code from fund_info,distributor_info) a, (select * from TAIL_COMMISSION_FARE_SET_ORI where DISTRIBUTOR_CODE = '***') b, (select * from TAIL_COMMISSION_FARE_SET_ORI where DISTRIBUTOR_CODE <> '***') c where a.fund_code = c.fund_code(+) and a.DISTRIBUTOR_CODE = c.DISTRIBUTOR_CODE(+) and a.fund_code = b.fund_code(+) )e where d.fund_code = e.fund_code and d.distributor_code = e.distributor_code ) select a.distributor_code distributor_code,di.distributor_name,a.fund_code fundcode,fi.fund_name,to_char(replace(sum(BALANCE), ','),'FM999,999,999,999,990.00')balance,to_char(replace(sum(SHARES), ','),'FM999,999,999,999,990.00') shares,to_char(replace(avg(balance), ','),'FM999,999,999,999,990.00') avgbalance,to_char(replace(sum(fee), ','),'FM999,999,999,999,990.00') fee, sum(income) income, avg(shares) avgshares, tfa.min_tail_fare,case when sum(fee)>=tfa.min_tail_fare then sum(fee) else 0 end paid_fee, to_char(nvl(TAIL_COMMISSION_RATIO,0)*100,'fm999999990.0099999')||'%' TAIL_COMMISSION_RATIO from( select distributor_code, fund_code, TRANSACTION_CFM_DATE,sum(balance) balance, sum(shares) shares, sum(fee) fee, sum(TAIL_COMMISSION_RATIO) TAIL_COMMISSION_RATIO,sum(income) income,avg(balance) avgbalance,avg(shares) avgshares from( select distributor_code, fund_code, TRANSACTION_CFM_DATE, share_class, sum(BALANCE) BALANCE, sum(SHARES) SHARES, sum(round(BALANCE /(case when YEAR_DAY>0 then YEAR_DAY else year.YearDays end)* nvl(TAIL_COMMISSION_RATIO, 0), 2)) fee, max(nvl(TAIL_COMMISSION_RATIO, 0)) TAIL_COMMISSION_RATIO, sum(nvl(income, 0)) income, avg(BALANCE) avgbalance, avg(SHARES) avgshares from( SELECT A.distributor_code, A.fund_code, 'A' share_class, A.TRANSACTION_CFM_DATE, decode(b.OPERATE_WAY, '0',a.balance, '1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0), '2',nvl(A.ORI_HOLD_BALANCE, 0), '3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) BALANCE, decode(b.OPERATE_WAY, '0',a.shares, '1',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0), '2',nvl(A.shares, 0), '3',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0)) SHARES, B.TAIL_COMMISSION_RATIO, A.income, nvl(b.YEAR_DAY,0) YEAR_DAY FROM( select a.distributor_code, a.fund_code, 'A' share_class, a.TRANSACTION_CFM_DATE, a.HOLD_DATE, a.HOLD_RATIO, nvl(a.income, 0) income, nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE, nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE, nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE, NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0) else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0) else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares from FUND_SALE_STAT a,fund_info b WHERE a.INDIVIDUAL_OR_INSTITUTION = '*' and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} and a.fund_code = b.fund_code ) A, ( select fund_code, distributor_code, AREA_FLAG, MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY, last_value(end_date)over(partition by fund_code, distributor_code, ENABLE_DATE order by end_date rows between unbounded preceding and unbounded following) end_date from(select fund_code, distributor_code, AREA_FLAG, MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY, nvl(lead(ENABLE_DATE)over(partition by fund_code, distributor_code order by ENABLE_DATE, MIN_HOSTING), '20991231') end_date from vtailratio_tmp where AREA_FLAG in ('0','1') ) ) B where A.fund_code = B.fund_code AND A.distributor_code = B.distributor_code and A.HOLD_DATE >= B.ENABLE_DATE and A.HOLD_DATE < B.end_date AND decode(b.OPERATE_WAY, '0',a.balance, '1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0), '2',nvl(A.ORI_HOLD_BALANCE, 0), '3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) > B.MIN_HOSTING AND decode(b.OPERATE_WAY, '0',a.balance, '1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0), '2',nvl(A.ORI_HOLD_BALANCE, 0), '3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) <= B.MAX_HOSTING and DECODE(B.AREA_FLAG,'2', a.HOLD_RATIO, '1', DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE), DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE)) >= B.MIN_HOSTING and DECODE(B.AREA_FLAG,'2', a.HOLD_RATIO, '1', DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE), DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE)) < B.MAX_HOSTING and not exists( select * from GRADING_FUND_PLAN tsc where tsc.MAIN_FUND_CODE = A.fund_code and tsc.SECTION_FUND_CODE = '******' ) and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} {"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"} {"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"} /** and #WhereSql **/ ) hz , (select case when substr({"origin" :"param", "field" :"dateStart","where" :"%s"}, 1, 4)=substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4) and mod(substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4),4)=0 then '366' else '365' end YearDays from dual) year where GetSysValueRX('System','TailSegment','0')=0 and GetSysValueRX('System','TailMode','1')=1 group by distributor_code,fund_code,share_class,TRANSACTION_CFM_DATE union all select distributor_code, fund_code, TRANSACTION_CFM_DATE, share_class, sum(BALANCE) BALANCE, sum(SHARES) SHARES, sum(round(BALANCE / (case when YEAR_DAY>0 then YEAR_DAY else year.YearDays end) * nvl(TAIL_COMMISSION_RATIO, 0), 2)) fee, max(nvl(TAIL_COMMISSION_RATIO, 0)) TAIL_COMMISSION_RATIO, sum(nvl(income, 0)) income, avg(BALANCE) avgbalance, avg(SHARES) avgshares from( SELECT a.distributor_code, a.fund_code, 'A' share_class, a.TRANSACTION_CFM_DATE, decode(e.OPERATE_WAY, '0',a.balance, '1',nvl(a.balance, 0) - nvl(a.REINVEST_BALANCE, 0), '2',nvl(a.ORI_HOLD_BALANCE, 0), '3',nvl(a.ORI_HOLD_BALANCE, 0) - nvl(a.ORI_REINVEST_BALANCE, 0)) BALANCE, decode(e.OPERATE_WAY, '0',a.shares, '1',nvl(a.shares, 0) - nvl(a.REINVEST_SHARE, 0), '2',nvl(a.shares, 0), '3',nvl(a.shares, 0) - nvl(a.REINVEST_SHARE, 0)) SHARES, E.TAIL_COMMISSION_RATIO, a.income, nvl(e.YEAR_DAY,0) YEAR_DAY FROM( select a.distributor_code, a.fund_code, 'A' share_class, a.TRANSACTION_CFM_DATE, a.HOLD_DATE, a.HOLD_RATIO, nvl(a.income, 0) income, nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE, nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE, nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE, NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0) else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0) else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares from FUND_SALE_STAT a,fund_info b WHERE a.INDIVIDUAL_OR_INSTITUTION = '*' and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} and a.fund_code = b.fund_code ) a, vtailratio_tmp tfa, (select A.TRANSACTION_CFM_DATE,b.TAIL_COMMISSION_RATIO, a.distributor_code, a.fund_code, B.ENABLE_DATE, B.end_date, b.YEAR_DAY, b.OPERATE_WAY from( select a.distributor_code, a.fund_code, 'A' share_class, a.TRANSACTION_CFM_DATE, a.HOLD_DATE, a.HOLD_RATIO, nvl(a.income, 0) income, nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE, nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE, nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE, NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0) else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0) else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares from FUND_SALE_STAT a,fund_info b WHERE a.INDIVIDUAL_OR_INSTITUTION = '*' and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} and a.fund_code = b.fund_code ) a, (select fund_code, distributor_code, AREA_FLAG, MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY, last_value(end_date)over(partition by fund_code, distributor_code, ENABLE_DATE order by end_date rows between unbounded preceding and unbounded following) end_date from( select fund_code, distributor_code, AREA_FLAG, MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE,OPERATE_WAY,YEAR_DAY, nvl(lead(ENABLE_DATE)over(partition by fund_code, distributor_code order by ENABLE_DATE, MIN_HOSTING), '20991231') end_date from vtailratio_tmp where AREA_FLAG in ('0','1') ) ) b WHERE b.fund_code = a.fund_code and b.distributor_code = a.distributor_code and a.HOLD_DATE >= B.ENABLE_DATE and a.HOLD_DATE < B.end_date group by a.distributor_code, a.fund_code, B.AREA_FLAG, B.TAIL_COMMISSION_RATIO, B.MIN_HOSTING, B.MAX_HOSTING, B.ENABLE_DATE, B.end_date,A.TRANSACTION_CFM_DATE,B.YEAR_DAY,b.OPERATE_WAY having DECODE(B.AREA_FLAG,'2', avg(a.HOLD_RATIO), '1', AVG(DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE)), AVG(DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE))) >= B.MIN_HOSTING and DECODE(B.AREA_FLAG,'2', avg(a.HOLD_RATIO), '1', AVG(DECODE(b.OPERATE_WAY, '0', SHARES, '1', A.SHARES - A.REINVEST_SHARE, '2', SHARES, '3', A.SHARES - A.REINVEST_SHARE)), AVG(DECODE(b.OPERATE_WAY, '0', A.BALANCE, '1', A.BALANCE - A.REINVEST_BALANCE, '2', a.ORI_HOLD_BALANCE, '3', A.ORI_HOLD_BALANCE - A.ORI_REINVEST_BALANCE))) < B.MAX_HOSTING ) e WHERE a.distributor_code = e.distributor_code(+) and a.fund_code = e.fund_code(+) AND A.TRANSACTION_CFM_DATE = E.TRANSACTION_CFM_DATE and a.fund_code = tfa.fund_code and a.distributor_code = tfa.distributor_code and a.HOLD_DATE >= e.ENABLE_DATE(+) and a.HOLD_DATE < e.end_date(+) and not exists(select * from GRADING_FUND_PLAN tsc where tsc.MAIN_FUND_CODE = a.fund_code and tsc.SECTION_FUND_CODE = '******') and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} {"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"} {"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"} /** and #WhereSql **/ ) hz, (select case when substr({"origin" :"param", "field" :"dateStart","where" :"%s"}, 1, 4)=substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4) and mod(substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4),4)=0 then '366' else '365' end YearDays from dual) year where GetSysValueRX('System','TailSegment','0')=0 and GetSysValueRX('System','TailMode','1')=2 group by distributor_code,fund_code,share_class,TRANSACTION_CFM_DATE union all select distributor_code, fund_code, TRANSACTION_CFM_DATE, share_class, BALANCE, SHARES, fee, TAIL_COMMISSION_RATIO, income, avgbalance, avgshares from( select distributor_code, fund_code, TRANSACTION_CFM_DATE, share_class, sum(BALANCE) BALANCE, sum(SHARES) SHARES, sum(nvl(( select round(sum(DECODE(AREA_FLAG,'2',ROUND(SUM(GREATEST(LEAST(m.MAX_HOSTING-m.MIN_HOSTING,HZA.BALANCE-m.MIN_HOSTING),0)*TAIL_COMMISSION_RATIO/(case when hzA.YEAR_DAY>0 then hzA.YEAR_DAY else year.YearDays end)),2), '1',ROUND(SUM(GREATEST(LEAST(m.MAX_HOSTING-m.MIN_HOSTING,HZA.SHARES-m.MIN_HOSTING),0)*hzA.NAV*TAIL_COMMISSION_RATIO/(case when hzA.YEAR_DAY>0 then hzA.YEAR_DAY else year.YearDays end)),2), ROUND(SUM(greatest(least(m.MAX_HOSTING - m.MIN_HOSTING,hzA.BALANCE- m.MIN_HOSTING),0) * TAIL_COMMISSION_RATIO / 365)),2)),2) from( select fund_code, distributor_code, AREA_FLAG, MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE, last_value(end_date)over(partition by fund_code, distributor_code, ENABLE_DATE order by end_date rows between unbounded preceding and unbounded following) end_date from( select fund_code, distributor_code, AREA_FLAG, MIN_HOSTING, MAX_HOSTING, TAIL_COMMISSION_RATIO, ENABLE_DATE, nvl(lead(ENABLE_DATE)over(partition by fund_code, distributor_code order by ENABLE_DATE, MIN_HOSTING), '20991231') end_date from vtailratio_tmp where AREA_FLAG in ('0','1')) ) m, (select case when substr({"origin" :"param", "field" :"dateStart","where" :"%s"}, 1, 4)=substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4) and mod(substr({"origin" :"param", "field" :"dateEnd","where" :"%s"}, 1, 4),4)=0 then '366' else '365' end YearDays from dual) year where distributor_code = hzA.distributor_code and fund_code = hzA.fund_code and hzA.HOLD_DATE >= ENABLE_DATE and hzA.HOLD_DATE < end_date GROUP BY AREA_FLAG),0)) fee, 0 TAIL_COMMISSION_RATIO, sum(nvl(income,0)) income, avg(BALANCE) avgbalance, avg(SHARES) avgshares from( SELECT A.HOLD_DATE, A.distributor_code, A.fund_code, 'A' share_class, A.TRANSACTION_CFM_DATE, decode(tfa.OPERATE_WAY, '0',a.balance, '1',nvl(A.balance, 0) - nvl(A.REINVEST_BALANCE, 0), '2',nvl(A.ORI_HOLD_BALANCE, 0), '3',nvl(A.ORI_HOLD_BALANCE, 0) - nvl(A.ORI_REINVEST_BALANCE, 0)) BALANCE, decode(tfa.OPERATE_WAY, '0',a.shares, '1',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0), '2',nvl(A.shares, 0), '3',nvl(A.shares, 0) - nvl(A.REINVEST_SHARE, 0)) SHARES, A.income ,D.NAV NAV, nvl(tfa.YEAR_DAY,0) YEAR_DAY FROM( select a.distributor_code, a.fund_code, 'A' share_class, a.TRANSACTION_CFM_DATE, a.HOLD_DATE, a.HOLD_RATIO, nvl(a.income, 0) income, nvl(a.REINVEST_SHARE, 0) REINVEST_SHARE, nvl(a.REINVEST_BALANCE, 0) REINVEST_BALANCE, nvl(a.ORI_HOLD_BALANCE, 0) ORI_HOLD_BALANCE, NVL(A.ORI_REINVEST_BALANCE, 0) ORI_REINVEST_BALANCE, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_BALANCE, 0) else nvl(a.HOLD_BALANCE, 0) + nvl(a.income, 0) end balance, case when b.ACC_INCOME_TO_SHARE_FLAG = '0' or /** #TACode || **/ '94'||a.distributor_code = '48002' then nvl(a.HOLD_SHARE, 0) else nvl(a.HOLD_SHARE, 0) + nvl(a.income, 0) end shares from FUND_SALE_STAT a,fund_info b WHERE a.INDIVIDUAL_OR_INSTITUTION = '*' and A.TRANSACTION_CFM_DATE >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.TRANSACTION_CFM_DATE <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} and a.fund_code = b.fund_code ) A, vtailratio_tmp tfa , NET_VALUE D where A.fund_code = tfa.fund_code AND A.distributor_code = tfa.distributor_code AND A.fund_code = D.fund_code AND D.TRANSACTION_CFM_DATE = (select min(fd.TRANSACTION_CFM_DATE) from NET_VALUE fd where fd.TRANSACTION_CFM_DATE >= a.TRANSACTION_CFM_DATE and fd.fund_code = a.fund_code ) and not exists(select * from GRADING_FUND_PLAN tsc where tsc.MAIN_FUND_CODE = A.fund_code and tsc.SECTION_FUND_CODE = '******') and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} {"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"} {"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"} /** and #WhereSql **/ ) hzA group by distributor_code,fund_code,share_class,TRANSACTION_CFM_DATE ) where GetSysValueRX('System', 'TailSegment', '0') = '1' AND GetSysValueRX('System', 'TailMode', '1') = '1' union all select A.distributor_code, A.fund_code, A.TRANSACTION_CFM_DATE,'A' share_class, 0 BALANCE, 0 SHARES, round(nvl(A.TRANSFER_FEE,0)*tfb.distributor_ratio,2) fee, 0 TAIL_COMMISSION_RATIO, 0 income, 0 avgbalance, 0 avgshares from( select t.* from( select * from trade_confirm union all select * from TRADE_CONFIRM_LOG ) t ) A,fee_belong tfb where A.fund_code = tfb.fund_code and A.distributor_code = tfb.distributor_code and tfb.fee_type = '31' and A.CHECK_RESULT = '1' and A.BUSINESS_CODE = '124' and A.TRANSFER_FEE>0 and GetSysValueRX('Report','TailsIncludeProfit','0')='1' /** and #WhereSql **/ and a.transaction_cfm_date >= {"origin" :"param", "field" :"dateStart","where" :"%s"} and a.transaction_cfm_date <= {"origin" :"param", "field" :"dateEnd","where" :"%s"} {"origin" :"param", "field":"agencyCodeList", "where" :" and a.distributor_code in (%s)"} {"origin" :"param", "field" :"fundCodeList", "where" :" and a.fund_code in (%s)"} ) group by distributor_code,fund_code,TRANSACTION_CFM_DATE )a, (select fund_code,distributor_code, min_tail_fare from( select fund_code,distributor_code,nvl(min_tail_fare,0) min_tail_fare, ROW_NUMBER() OVER(PARTITION BY fund_code , distributor_code ORDER BY fund_code DESC,distributor_code DESC) RANK from vtailratio_tmp) tfa where RANK=1 ) tfa,fund_info fi,distributor_info di where a.fund_code = tfa.fund_code and a.fund_code = fi.fund_code and a.distributor_code = di.distributor_code and a.distributor_code = tfa.distributor_code group by a.distributor_code,di.distributor_name,a.fund_code,fi.fund_name,tfa.min_tail_fare ,TAIL_COMMISSION_RATIO )order by fundcode,distributor_code给这段sql划分层次使人更容易理解
最新发布
07-16
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值