sum over可以完美解决历史累加的问题
其中有数据的跨年问题,需要年度重新统计,就在partition by中增加年字段即可
select
PROJ_ID -- 项目id
,PLAN_MTH -- 计划月
,PLAN_ORDER_AMT AS PLAN_ORDER_AMT_M -- 计划认购金额(月)
,sum(PLAN_ORDER_AMT) over (partition by PROJ_ID,PLAN_YR order by PLAN_MTH ) AS PLAN_ORDER_AMT_Y -- 计划认购金额(年)
,PLAN_CNTRT_AMT AS PLAN_CNTRT_AMT_M -- 计划签约金额(月)
,sum(PLAN_CNTRT_AMT) over (partition by PROJ_ID,PLAN_YR order by PLAN_MTH ) AS PLAN_CNTRT