表為消費(fèi)流水表
關(guān)鍵字段
mdid,viptype,je,jzrq
需求統(tǒng)計(jì)按各門店涉茧，各月份踏枣，所有會員以及三種不同會員卡會員的消費(fèi)筆數(shù)聋溜、消費(fèi)筆單價(jià)
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,'全部會員筆單價(jià)',hyxfbdj,'')) as 全部會員筆單價(jià),
max(decode(viptype,'積分卡會員筆單價(jià)',hyxfbdj,'')) as 積分卡會員筆單價(jià),
max(decode(viptype,'金卡會員筆單價(jià)',hyxfbdj,'')) as 金卡會員筆單價(jià),
max(decode(viptype,'鉆石卡會員筆單價(jià)',hyxfbdj,'')) as 鉆石卡會員筆單價(jià)
from temp
group by mdid,yf
order by mdid,yf
