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;