• oracle累计求和


     
    //将当前行某列的值与前面所有行的此列值相加,即累计求和:   
    //方法一:   
    with t as(    
         select 1 val from dual union all    
         select 3 from dual union all    
         select 5 from dual union all    
         select 7 from dual union all    
         select 9 from dual)    
    select val,    
           sum(val)    
           over (order by rownum rows between unbounded preceding and current row)    
           sum_val    
    from t    
    group by rownum,val    
    order by rownum;    
           VAL    SUM_VAL    
    ---------- ----------    
             1          1    
             3          4    
             5          9    
             7         16    
             9         25    
    //解析:    
    //sum(val)计算累积和;    
    //order by rownum 按照伪列rownum对查询的记录排序;    
    //between unbounded preceding and current row:定义了窗口的起点和终点;    
    //unbounded preceding:窗口的起点包括读取到的所有行;    
    //current row:窗口的终点是当前行,默认值,可以省略;   
    //  
    //方法二:  
    with cte_1 as(    
         select 1 val from dual union all    
         select 3 from dual union all    
         select 5 from dual union all    
         select 7 from dual union all    
         select 9 from dual  
         )   
    ,cte_2 as(  
        select rownum rn,val from cte_1  
        )  
    select a.val , sum(b.val) sum_val  
    from cte_2 a , cte_2 b  
    where b.rn <= a.rn  
    group by a.val  
    /  
    //方法三:  
    //创建一个递归函数,求和  
    //f(n) = x + f(n-1)  
    create table t  
    as  
    select 1 id,1 val from dual union all  
    select 2,3 from dual union all  
    select 3,5 from dual union all  
    select 4,7 from dual union all  
    select 5,9 from dual  
    /  
    create or replace function fun_recursion(x in int)   
    return integer is  
           n integer :=0;  
    begin  
         select val into n   
         from t  
         where id=x;  
         if x=1 then  
            return n;  
         else  
             return n + fun_recursion(x-1);  
         end if;  
         exception  
         when others then  
              dbms_output.put_line(sqlerrm);  
    end fun_recursion;  
    /  
    select val,fun_recursion(id) sum_val from t;  
           VAL    SUM_VAL  
    ---------- ----------  
             1          1  
             3          4  
             5          9  
             7         16  
             9         25  
    //  
  • 相关阅读:
    spark,hadoop集群安装注意
    TFRecord 使用
    python 路径引用问题
    flask使用模板
    tensorflow serving 模型部署
    docker使(二)—发布node应用镜像和容器
    docker使用(一)
    tensorBoard使用
    【疑难杂症】访问所有mapper方法都提醒invalid bound statement (not found)
    win10配置jdk12环境变量
  • 原文地址:https://www.cnblogs.com/lcword/p/5717432.html
Copyright © 2020-2023  润新知