实现同环比的简单SQL

同环比是企业最常见的需求之一，如何快速的在数据库中实现同环比，以下为本人得到的一些简单经验，还望大神指正：

一、在程序中定义变量实现
要在程序中定义变量，那么就是研究如何得到取同环比的sql语句。

建立一张用于试验的表，内容包括日期、数量、吊牌金额和成交金额

SQL> create table retail(id number(10,0) primary key,billdate varchar2(8) not null,qty number(6,0) not null,tot_amt_list number(8,2) not null,tot_amt_actual number(8,2) not null);

Table created.

SQL> desc retail;

Name                          Null?    Type          
---------------------------- -------- ----------------------------
 ID                           NOT NULL NUMBER(10)
 BILLDATE                     NOT NULL VARCHAR2(8)
 QTY                          NOT NULL NUMBER(6)
 TOT_AMT_LIST                 NOT NULL NUMBER(8,2)
 TOT_AMT_ACTUAL               NOT NULL NUMBER(8,2)

SQL> select * from retail where billdate between 20170501 and 20170503;

        ID BILLDATE      QTY TOT_AMT_LIST TOT_AMT_ACTUAL
---------- -------- ---------- ------------ --------------
  1001     20170501        3          12000      10000
  1002     20170502        2          11000       9000
  1003     20170503        1          10000       8000

SQL> select * from retail where billdate between 20160501 and 20160503;

       ID BILLDATE        QTY   TOT_AMT_LIST TOT_AMT_ACTUAL
---------- -------- ---------- ------------ --------------
  1004     20160501        5          10000       8000
  1005     20160502        5          15000       8000
  1006     20160503        1           5000       5000

SQL> select * from retail where billdate between 20170401 and 20170403;

        ID BILLDATE       QTY TOT_AMT_LIST TOT_AMT_ACTUAL
---------- -------- ---------- ------------ --------------
  1007     20170401        2          10000       5000
  1008     20170402        2          10000       5000
  1009     20170403        2          10000      10000

之后，我们仅以成交金额为同环比的取值对象进行讨论

首先，分别取出相应时间节点的值，用空值填充其他时间节点值的位置

SQL> select billdate,tot_amt_actual,null as yoy_amt_actual,null as mom_amt_actual from retail where billdate between 20170501 and 20170503;

BILLDATE TOT_AMT_ACTUAL Y M
-------- -------------- - -
20170501      10000
20170502       9000
20170503       8000

SQL> select billdate+10000 as billdate,null as tot_amt_actual,tot_amt_actual as yoy_amt_actual,null as mom_amt_actual from retail where billdate between 20160501 and 20160503;

BILLDATE T YOY_AMT_ACTUAL M
-------- - -------------- -
20170501           8000
20170502           8000
20170503           5000

SQL> select billdate+100 as billdate,null as tot_amt_actual,null as yoy_amt_actual,tot_amt_actual as mom_amt_actual from retail where billdate between 20170401 and 20170403;

BILLDATE T Y MOM_AMT_ACTUAL
-------- - - --------------
20170501         5000
20170502         5000
20170503        10000

之后，使用UNION ALL将其合并，如果报数据类型不匹配，则加上TO_NUMBER函数

SQL> select TO_NUMBER(billdate) as billdate,tot_amt_actual,null as yoy_amt_actual,null as mom_amt_actual from retail 
 where billdate between 20170501 and 20170503
 union all
 select billdate+10000 as billdate,null as tot_amt_actual,tot_amt_actual as yoy_amt_actual,null as mom_amt_actual from retail 
 where billdate between 20160501 and 20160503
 union all
 select billdate+100 as billdate,null as tot_amt_actual,null as yoy_amt_actual,tot_amt_actual as mom_amt_actual from retail 
 where billdate between 20170401 and 20170403

  BILLDATE TOT_AMT_ACTUAL YOY_AMT_ACTUAL MOM_AMT_ACTUAL
---------- -------------- -------------- --------------
  20170501        10000
  20170502         9000
  20170503         8000
  20170501                     8000
  20170502                     8000
  20170503                     5000
  20170501                                    5000
  20170502                                    5000
  20170503                                   10000

最后，使用聚合函数将同一天数据进行合并

SQL> select a.billdate,SUM(tot_amt_actual),SUM(yoy_amt_actual),SUM(mom_amt_actual) from 
 (select TO_NUMBER(billdate) as billdate,tot_amt_actual,null as yoy_amt_actual,null as mom_amt_actual from retail 
 where billdate between 20170501 and 20170503
 union all
 select billdate+10000 as billdate,null as tot_amt_actual,tot_amt_actual as yoy_amt_actual,null as mom_amt_actual from retail 
 where billdate between 20160501 and 20160503
 union all
 select billdate+100 as billdate,null as tot_amt_actual,null as yoy_amt_actual,tot_amt_actual as mom_amt_actual from retail 
 where billdate between 20170401 and 20170403) a
 group by a.billdate 

  BILLDATE SUM(TOT_AMT_ACTUAL) SUM(YOY_AMT_ACTUAL) SUM(MOM_AMT_ACTUAL)
---------- ------------------- ------------------- -------------------
20170501         10000                 8000                5000
20170502          9000                 8000                5000
20170503          8000                 5000               10000

二、PL/SQL方式实现

    CREATE OR REPLACE PROCEDURE YOY_AND_MOM (v_startdate number,v_enddate number)
    is
    cursor c1 
    is 
    select a.billdate,SUM(tot_amt_actual) as tot_amt_actual,SUM(yoy_amt_actual) as yoy_amt_actual,SUM(mom_amt_actual) as mom_amt_actual from
    (select TO_NUMBER(billdate) as billdate,tot_amt_actual,null as yoy_amt_actual,null as mom_amt_actual from retail
    where billdate between v_startdate and v_enddate
    union all
    select billdate+10000 as billdate,null as tot_amt_actual,tot_amt_actual as yoy_amt_actual,null as mom_amt_actual from retail
    where billdate between v_startdate-10000 and v_enddate-10000
    union all
    select billdate+100 as billdate,null as tot_amt_actual,null as yoy_amt_actual,tot_amt_actual as mom_amt_actual from retail
    where billdate between v_startdate-100 and v_enddate-100) a
    group by a.billdate;
    begin
    for i in c1 loop
    dbms_output.put_line(i.billdate||'   '||i.tot_amt_actual||'   '||i.yoy_amt_actual||'   '||i.mom_amt_actual);
    end loop;
    end YOY_AND_MOM;

SQL> exec yoy_and_mom(20170501,20170503);

20170501   10000   8000   5000
20170502   9000   8000   5000
20170503   8000   5000   10000