【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 ;