之前的那种同比环比方式，效率不高。数据量一大，就不好用了。因此用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);
分析：
- with as 片段，提高效率。将 时间、票类、商家、分成放在m中
- ** 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;
