【HANA SQL】HANA SQL 累计值计算

【HANA SQL】HANA SQL 累计值计算

需求：计算视图，ZSYB_TEST包含ZSYB、ZMONTH、ZDAY、ZJE。现在需要计算每个事业部，每个月按天累计的金额ZRUNING_JE，并存入另一张视图，ZSYB_RUNNING。

1，累加

     select "ZSYB" ZSYB ,"ZMONTH" ZMONTH ,"ZDAY" ZDAY,"ZBHSJZ" ZBHSJZ ,
     SUM("ZBHSJZ") OVER (PARTITION BY "ZSYB","ZMONTH" ORDER BY "ZDAY") RUNNING_ZBHSJZ
     from "ZSYB_TEST";

2，累减
累减稍微复杂些，首先根据事业部、月份进行分区，按照天升序，然后给每一天编号，1号的金额取正数，其他取负数。最后在按照累加那样进行sum。

    select 
    ZSYB,
    ZMONTH,
    ZDAY,
    ZBHSJZ,
    /*累加*/
    SUM(T.ZBHSJZ) OVER(PARTITION BY T.ZSYB,T.ZMONTH ORDER BY T.ZDAY) RUNNING_ZBHSJZ
    FROM
    (
        SELECT
           "ZSYB" ZSYB ,"ZMONTH" ZMONTH ,"ZDAY" ZDAY,
           /*进行编号，1号为正数，其他为负数*/
           CASE ROW_NUMBER() OVER (PARTITION BY "ZSYB","ZMONTH" ORDER BY "ZDAY")
           WHEN 1 THEN "ZBHSJZ"
           else -"ZBHSJZ"
           end  as  ZBHSJZ
           from "ZSYB_TEST"
        ) T ;

本文参考：https://www.cnblogs.com/sooner/p/8043013.html。