Oracle筆記 之 使用over函數進行資料環比同比分析

本文示範如何使用Oracle資料庫的over分析函數滿足業務提出的資料分析需求（查詢按機構，分類查詢指定月份的環比，同比資料）。

over函數的具體使用方法請參考博文

概念分析

進行資料分析的第一步一定是弄明白業務人員的意圖，搞清楚業務提出的相關概念。

環比：本月相比上個月的業務增長量與上個月業務量的百分比值。

同比：本月相比上一年度同一月份的業務增長量與上一年度同一月份業務量的百分比值。

設計算法

業務分析需求的主要名額是標明月相關機構和類别的銷售量和上一個月或者上一年度同一月份同一機構和類别銷售量（即機構類别的月銷售量），然後求解相關數值的比值。

算法

1，對銷售資料按照機構，類别和月份進行彙總。

2，使用over函數求目前月份的前1條和前12條資料值。

3，計算相關資料間的比值，顯示結果。

資料質檢

本文争對的資料量少（千萬級）計算成本低（秒級）可以通過直接運作算法驗證，如果資料量大計算成本高請務必先對算法進行分析并選擇合适的資料驗證方式。

資料品質報告

1，分組資料量不平衡，相鄰資料差異很大。

2，存在資料量為0情況，可能導緻除數為0的異常。

3，分組連貫性沒法保證，使用over函數會出現取錯資料。

解決方案

1，争對除數可能問0的異常選擇計算前對除數進行判斷（使用Oracle的decode函數）。

2，争對分組不連貫的問題适當的做資料填充。

編寫算法

表結構設計

設計待分析資料表結構：

#tb_temp

create table tb_temp
(shop integer,
category integer,
salevalue number(20,2),
month integer);
           

#tb_analysis

create table tb_analysis
(shop integer,
category integer,
salevalue number(20,2),
onmonth number(20,2),
onyear number(20,2),
month integer);
           

資料抽取及填充

#抽取臨時資料

insert into tb_temp
  (shop, category, salevalue, month)
  select shop, category, sum(salevalue), to_number(to_char(saledate, 'yyyymm'))
    from tb_sale
   group by shop, category, to_number(to_char(saledate, 'yyyymm'));
           

#填充資料

#使用存儲過程

本文隻是一個簡單的填充資料過程，待讀者根據實際需求完善（異常處理，日志記錄等）。

create or replace procedure pk16_padd as
  months number;
  sdate  date;
  n      integer;
  cursor cs_groups is
    select distinct shop, category from tb_sale;
begin
  select trunc(min(saledate), 'month'),
         ceil(months_between(max(saledate), min(saledate)))
    into sdate, months
    from tb_sale;
  for sc in cs_groups loop
    n := 0;
    while n <= months loop
      insert into tb_temp
        (shop, category, salevalue, month)
      values
        (sc.shop,
         sc.category,
         0,
         to_number(to_char(add_months(sdate, n), 'yyyymm')));
      n := n + 1;
    end loop;
  end loop;
  commit;
end;
           

#抽取分析資料

insert into tb_analysis
  (shop, category, salevalue, onmonth, onyear, month)
  select shop,
         category,
         salevalue,
         lag(salevalue, 1, 0) over(partition by shop, category order by month) onyear,
         lag(salevalue, 12, 0) over(partition by shop, category order by month) onmonth,
         month
    from tb_temp

           

資料分析

select shop,
       category,
       salevalue,
       decode(onmonth,
              0,
              '∞',
              round((salevalue - onmonth) / onmonth * 100, 2)) onmonth,
       decode(onmonth,
              0,
              '∞',
              round((salevalue - onyear) / onyear * 100, 2)) onyear,
       month
  from tb_analysis