理发店消费记录求同比（表；ods_user_info \源ORA_185_twedw \odsview)

–理发店的三张表
select * from ods_recharge;
select * from ods_user_info;
select * from ods_consume;

–求出理发店2017年7-12月份的月末的月份，会员类型，余款总额，和 同比。
select to_char(a.dayno,‘yyyy-mm’) 月份，
decode(a.cardtype,
1,‘普卡’,
2,‘银卡’,
3,‘金卡’,
4,‘白金卡’) 会员类型,
a.sum_bal 余额总额,
case when b.sum_bal = 0 then null
else to_char((a.sum_bal-b.sum_bal)/b.sum_bal,‘90.99’)||’%’
end 同比
from (select dayno,cardtype,sum(bal) sum_bal
from ods_user_info
where dayno>=add_months(to_date(201801,‘yyyymm’),-6)
and dayno< to_date(201801,‘yyyymm’)
and dayno=last_day(dayno)
group by dayno,cardtype) a
left join (select dayno,cardtype,sum(bal) sum_bal
from ods_user_info
where dayno>=add_months(to_date(201801,‘yyyymm’),-18)
and dayno< add_months(to_date(201801,‘yyyymm’),-12)
and dayno=last_day(dayno)
group by dayno,cardtype) b
on a.cardtype=b.cardtype
and trunc(a.dayno,‘mm’) = trunc(add_months(b.dayno,12),‘mm’);