DB2同比环比2(with as )

之前的那种同比环比方式，效率不高。数据量一大，就不好用了。因此用with as 写了另一个求同比环比的sql。

sql

creat table xxx as 
(with m(time,ticket_type,business_name,amount) as (select createtime,ticket_type,business_name,ticket_amount from xxx order by createtime)
select cur.*,
tb.amount tb_amount from m cur
left join m tb
on 
cur.business_name=tb.business_name and cur.ticket_type=tb.ticket_type and substr(to_date(cur.time,'yyyy-mm-dd')-1 year,1,10)=tb.time 
order by time desc);

分析：

1. with as 片段，提高效率。将 时间、票类、商家、分成放在m中
2. ** to_date(cur.time,'yyyy-mm-dd')-1 year** 求去年同期时间

注意：

由于时间有多个，需要有其他字段将分成唯一确定，因此要想分成和哪些有关。是由票类商家确定的。所以
cur.business_name=tb.business_name and cur.ticket_type=tb.ticket_type

否则，可能出现多对一，使结果不唯一。

结果

TIME TICKET_TYPE BUSINESS_NAME AMOUNT TB_AMOUNT
2017-03-01 套票 xxx 31.00 67.40
2017-03-01 套票 xxx 62.00 133.00
2017-03-01 套票 XXX 18.60 39.80
2017-03-01 套票 XXX 344.80 750.90
2017-03-01 套票 XXX 485.00 1103.20
2017-03-01 套票 XXX 93.00 200.00
2017-03-01 套票 XXX 95.20 217.30
2017-03-01 套票 XXX18.60 39.80
2017-03-01 XX XXX 122.78 305.71
2017-03-01 XX XXX45.00 105.00

将结果插入表中

create table xx (createtime varchar(64),ticket_type varchar(128),business_name varchar(128),amount numeric(18,2),b_amount numeric(18,2));
insert into xx 
with m(time,ticket_type,business_name,amount) 
as (select create time,ticket_type,business_name,ticket_amount from XXX order by createtime) 
select cur.*,
tb.amount tb_amount from m cur
left join m tb 
on cur.business_name=tb.business_name and cur.ticket_type=tb.ticket_type and substr(to_date(cur.time,'yyyy-mm-dd')-1 year,1,10)=tb.time order by time desc ;

select * from xx;