基础知识:
SUM函数结合窗口函数over用于聚合某一分区内的数据,可根据窗口滑动的范围来确定是记总,还是在分区中从某行累计到某行:
ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
ROWS BETWEEN current row AND unbounded following 是指当前行到最后一行的汇总
ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总
ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总
应用:
在求科目余额累计数的时候,每个月的科目不是固定的,当sum函数以年,公司,科目分区时,查询5月某个科目的数据时,分析函数字段应当是从1月到5月该科目的累计数,即使表中5月不存在该科目,5月也应当存在一条从1月到4月的sum字段的累计数。目前发现sum是做不到这样的操作。
解决方案:
写查询片段,不以over函数去sum每月科目的数据后,将月份写死为查询月。这种片段需要写12个(排除特殊期间)。
with cc as (
select * from (
select v.unitcode,v.unitname,v.yearV,v.subjcode,v.subjname,v.subjlev,v.ts,v.periodv,v.creditamount,v.debitamount,sum(v.creditamount* -1 + v.debitamount) balance
from ODS.Odsreyy_V_Gl_Balance@FMSLK v
where periodv <> '00'
group by v.unitcode,v.unitname,v.yearV,v.subjcode,v.subjname,v.subjlev,v.ts,v.periodv,v.creditamount,v.debitamount
)
union all
select * from (
select v.unitcode,v.unitname,v.yearV,v.subjcode,v.subjname,v.subjlev,v.ts,v.periodv,0 creditamoun,0 debitamount,sum(v.creditamount* -1 + v.debitamount) balance
from ODS.Odsreyy_V_Gl_Balance@FMSLK v
where periodv = '00'
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,ts,periodv
)
),
s1 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'01' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '01'
)
group by unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv
),
s2 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'02' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '02'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s3 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'03' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '03'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s4 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'04' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '04'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
)
,
s5 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'05' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '05'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s6 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'06' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '06'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s7 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'07' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '07'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s8 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'08' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '08'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s9 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'09' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '09'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s10 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'10' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '10'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s11 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'11' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '11'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
),
s12 as (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
sum(creditamount) creditamount ,
sum(debitamount) debitamount,
sum(balance) balance
from (
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,'12' periodv,creditamount ,debitamount, balance
from cc
where periodv <= '12'
)
group by unitcode,unitname,yearV,subjcode,subjname,subjlev,periodv
)
SELECT YEARMONTH,unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,SHamount,SHMARK,balance,'YONYOU' CLIENT
FROM(
SELECT YEARMONTH, M.ELEDIM_OUTPUT1 unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
SHamount,SHMARK,balance
FROM (
select yearv||periodv YEARMONTH,unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
creditamount*-1 SHamount,'H' SHMARK,balance
from(
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,creditamount ,debitamount,balance
from (
select * from s1
union all
select * from s2
union all
select * from s3
union all
select * from s4
union all
select * from s5
union all
select * from s6
union all
select * from s7
union all
select * from s8
union all
select * from s9
union all
select * from s10
union all
select * from s11
union all
select * from s12
)
order by subjcode , periodv
)
union all
select yearv||periodv YEARMONTH, unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,
debitamount SHamount,'S' SHMARK,balance
from(
select unitcode ,unitname,subjcode,subjname,subjlev,yearv,periodv,creditamount ,debitamount,
0 balance
from (
select * from s1
union all
select * from s2
union all
select * from s3
union all
select * from s4
union all
select * from s5
union all
select * from s6
union all
select * from s7
union all
select * from s8
union all
select * from s9
union all
select * from s10
union all
select * from s11
union all
select * from s12
)
order by subjcode , periodv
)
) S
LEFT JOIN MAP_REGOLA_TAB_ELEMENTO M ON M.COD_REGOLA_TAB = 'MAP_BUKRS_YONYOU' AND M.COD_MAPPATURA = 'HI_DATA_GL' AND M.ELEDIM_INPUT2 = S.unitcode
)
where unitcode is not null AND YEARMONTH IN ({IN-YEARMONTH}) AND UNITCODE IN ({IN-ENTITY})
order by subjcode