SUM分析函数应用缺陷及解决办法

基础知识:

       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


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值