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

最新推荐文章于 2022-05-18 12:00:49 发布

RJ_Dream

最新推荐文章于 2022-05-18 12:00:49 发布

阅读量1k

点赞数

文章标签：分析函数 sum

本文链接：https://blog.csdn.net/RJ_Dream/article/details/73459814

版权

Oracle 草稿专栏收录该内容

8 篇文章 0 订阅

订阅专栏

基础知识：

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