sql - 甲骨文 SQL : efficient way to calculate business days in a month

Question

我有一个非常大的表，其中包含 dates、account、amount 等列，例如。

date        account       amount
4/1/2014    XXXXX1        80
4/1/2014    XXXXX1        20
4/2/2014    XXXXX1        840  
4/3/2014    XXXXX1        120
4/1/2014    XXXXX2        130
4/3/2014    XXXXX2        300
...........

(我有 40 个月的每日数据和多个帐户。)

我要的最终输出是每个账户每个月的平均金额。由于任何账户在一天内可能有记录也可能没有记录，并且我有一个单独的2011~2014年假期表，我将一个月内每个账户的金额相加并除以工作日数那个月的。请注意，周末/节假日很可能会有记录，因此我需要将它们排除在计算之外。另外，我想为原始表中的每个可用日期都有一个记录。例如。

date        account       amount
4/1/2014    XXXXX1        48 ((80+20+840+120)/22)
4/2/2014    XXXXX1        48  
4/3/2014    XXXXX1        48
4/1/2014    XXXXX2        19 ((130+300)/22)
4/3/2014    XXXXX2        19
...........

(假设以上是我拥有的 2014 年 4 月的唯一数据。)

我能够以一种笨拙且缓慢的方式执行此操作，但由于我需要将此过程与其他子查询结合起来，因此我确实需要优化此查询。我当前的代码如下所示:

<!-- language: lang-sql -->  

select
   date,
   account,
   sum(amount/days_mon) over (partition by last_day(date))
from(
     select
        date,
        -- there are more calculation to get the account numbers,
        -- so this subquery is necessary
        account, 
        amount,
        -- this is a list of month-end dates that the number of 
        -- business days in that month is 19. similar below.

        case when last_day(date) in ('','',...,'') then 19                  
             when last_day(date) in ('','',...,'') then 20
             when last_day(date) in ('','',...,'') then 21
             when last_day(date) in ('','',...,'') then 22
             when last_day(date) in ('','',...,'') then 23
        end as days_mon
      from mytable tb
      inner join lookup_businessday_list busi
        on tb.date = busi.date)

那么我怎样才能有效地实现上述目的呢？谢谢!

Answer 1

这种方法使用子查询分解——其他 RDBMS 风格称之为公用表表达式。这里的吸引力在于我们可以将一个 CTE 的输出作为输入传递给另一个。 Find out more .

第一个 CTE 生成给定月份的日期列表(您可以将其扩展到您喜欢的任何范围)。

第二个 CTE 在第一个 CTE 上使用反连接来过滤掉假期日期和非工作日日期。请注意，Day Number 因 NLS_TERRITORY 设置而异；在我的领域，周末是第 6 天和第 7 天，但是 SQL Fiddle is American so there it is 1 and 7 .

with dates as ( select date '2014-04-01' + ( level -  1) as d
               from dual
               connect by level <= 30 )
     , bdays as ( select d
                         , count(d) over () tot_d 
                  from   dates
                         left join holidays
                              on dates.d = holidays.hol_date
                  where holidays.hol_date is null   
                  and   to_number(to_char(dates.d, 'D')) between 2 and 6 
                )
select yt.account
       , yt.txn_date
       , sum(yt.amount) over (partition by yt.account, trunc(yt.txn_date,'MM'))
            /tot_d as avg_amt
from your_table yt
     join bdays 
          on bdays.d = yt.txn_date
order by yt.account
       , yt.txn_date
/

我没有四舍五入平均金额。