表为消费流水表
关键字段
mdid,viptype,je,jzrq
需求统计按各门店，各月份，所有会员以及三种不同会员卡会员的消费笔数、消费笔单价
with temp as(
SELECT '全部会员' as viptype,a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM') as yf,count() as hyxfbs,cast(sum(a.je)/count() as number(18,2)) as hyxfbdj
FROM BFCRM8.HYXFJL a
group by a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM')
UNION
SELECT '积分卡会员' as viptype,a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM') as yf,count() as hyxfbs,cast(sum(a.je)/count() as number(18,2)) as hyxfbdj
FROM BFCRM8.HYXFJL a
where VIPTYPE=101
group by a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM')
UNION
SELECT '金卡会员' as viptype,a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM') as yf,count() as hyxfbs,cast(sum(a.je)/count() as number(18,2)) as hyxfbdj
FROM BFCRM8.HYXFJL a
where VIPTYPE=108
group by a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM')
UNION
SELECT '钻石卡会员' as viptype,a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM') as yf,count() as hyxfbs,cast(sum(a.je)/count() as number(18,2)) as hyxfbdj
FROM BFCRM8.HYXFJL a
where VIPTYPE=107
group by a.mdid,"TO_CHAR"(a.JZRQ, 'yyyy-MM')
)
select
mdid,
yf,
max(decode(viptype,'全部会员',hyxfbs,'')) as 全部会员,
max(decode(viptype,'积分卡会员',hyxfbs,'')) as 积分卡会员,
max(decode(viptype,'金卡会员',hyxfbs,'')) as 金卡会员,
max(decode(viptype,'钻石卡会员',hyxfbs,'')) as 钻石卡会员,
max(decode(viptype,'全部会员笔单价',hyxfbdj,'')) as 全部会员笔单价,
max(decode(viptype,'积分卡会员笔单价',hyxfbdj,'')) as 积分卡会员笔单价,
max(decode(viptype,'金卡会员笔单价',hyxfbdj,'')) as 金卡会员笔单价,
max(decode(viptype,'钻石卡会员笔单价',hyxfbdj,'')) as 钻石卡会员笔单价
from temp
group by mdid,yf
order by mdid,yf
