实现同环比的简单SQL

来源：互联网发布：免费聊天交友软件编辑：程序博客网时间：2024/04/30 08:21

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

一、在程序中定义变量实现
要在程序中定义变量，那么就是研究如何得到取同环比的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       8000SQL> 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       5000SQL> 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      1000020170502       900020170503       8000SQL> 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           800020170502           800020170503           5000SQL> 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         500020170502         500020170503        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                500020170502          9000                 8000                500020170503          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   500020170502   9000   8000   500020170503   8000   5000   10000

阅读全文

0 0