时间戳

首页 > 解决方案 > Oracle如何根据月份数对列求和

sql - Oracle如何根据月份数对列求和

问题描述

我有一个基于 fyr、fund、glkey、globject 的第 1-12 个月的汇总表。我遇到的问题是总结该行的给定月份数的月份。我可以添加一个Case表达式来添加喜欢

WHEN  2 then  m01 + m02 
WHEN 12 then  m01 + m02 .. m12

有谁知道一个循环或更好的方法来完成这个？

在 SQL 中记录

我创建的函数将它传递给月份值 4 应该是 2537.50，但我得到 15741.09。 

    CREATE OR REPLACE FUNCTION gla_sum_asof_month (
    fyr   IN NUMBER  , fund  IN NUMBER  , au    IN CHAR
  , acct  IN NUMBER  , sacct IN NUMBER  , mnth  IN NUMBER  )
  RETURN NUMBER
  IS
  rtn_amt NUMBER(18,2) := 0;
  amt NUMBER(18,2) := 0;
BEGIN
SELECT SUM(amt) INTO rtn_amt FROM (
  SELECT *
    FROM   app_reports.vw_glamounts 
  UNPIVOT
   (
     amt  FOR fmonth
     IN (m01 AS 1, m02 AS 2, m03 AS 3, m04 AS 4 , m05 AS 5 , m06 AS 6,
         m07 AS 7, m08 AS 8 , m09 AS 9, m10 AS 10, m11 AS 11, m12 AS 12)
    )
   ) a
    WHERE  a.Fiscal_Year            = fyr
          AND    a.company          = fund
          AND    a.acct_unit        = au
          AND    a.account          = acct
          AND   a. sub_account      = sacct
          AND   a.fmonth            <= mnth;  -- filter # of months

   RETURN rtn_amt;
END gla_sum_asof_month;

标签： sqloraclesum

解决方案

您可以unpivot在第一步中获取数据，然后pivot通过条件聚合：

with t2 as
(
select fyr, fund, glkey, globject, 
       row_number() over (order by col) as rn,
       sum(val) over (order by col) as m 
  from t
 unpivot 
 (
  val for col in (m01 , m02, m03, .., m12)
 )  
)
select fyr, fund, glkey, globject,
       max(case when rn = 1 then m end) as m01,
       max(case when rn = 2 then m end) as m02,
       ...
       max(case when rn =12 then m end) as m12
  from t2
 group by fyr, fund, glkey, globject

order by col重要的是要考虑作为逻辑技巧。

Demo

推荐阅读