实现同环比的简单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
- 实现同环比的简单SQL
- 最高响应比算法的简单实现!
- SQL 求同比 环比
- 维特比算法viterbi的简单实现 python版
- SQL 速度比like鱼句快的另一种实现方法
- 同/异构sql的语句
- 关于同一个数据库的表备份和表恢复的sql简单语句
- 关于同一个数据库的表备份和表恢复的sql简单语句
- SQL Server AB表同结构的简单数据同步存储过程
- sql语句简单解决同个字段不同标识的具体意义
- sql语句简单解决同个字段不同标识的具体意义
- WebFOCUS当期环比的实现方法
- 5.4.2.1、SSAS-创建计算-同环比
- SSO单点登录一(Spring+SpringMVC+固定密码)实现的简单的同域SSOdemo
- SSO单点登录一(Spring+SpringMVC+固定密码)实现的简单的同域SSOdemo
- SQL语句实现上期比统计实例
- POJ 2769 简单的同余问题
- 同表连接更新的sql
- jdbcTemplate 实现查询返回list
- enum 枚举的使用
- java-07
- AndroidStudio git版本控制
- serializeArray()与 serialize()
- 实现同环比的简单SQL
- counting-sort
- css
- Linux 线程
- 【脚本语言系统】关于Python正则表达式sys.re, 你需要知道的事
- 给初学者的RxJava2.0教程(四)
- 041-Java-032
- 谁是最好的Coder
- MySQL整数类型、实数类型