例子:某某超市会员客户近三月消费情况
近三月消费情况SQL:
#非同年连续三月
方法1:#近三月月均消费情况-------------------------------2020/3/13 18:58:51---------
select a.user_code #客户编码
,a0.Consume_money #19年12月
,a1.Consume_money #20年1月
,a2.Consume_money #20年2月
,(a0.Consume_money + a1.Consume_money + a2.Consume_money)/3 as avg_Consume_money #近三月月均消费情况
from tmp_user_xxx_chaoshi a #某某超市客户表
left join (select user_code,Consume_money from tmp_user_xxx_chaoshi_xiaofei_2019 where op_time=201912) a0 on a0.user_code=a.user_code #某某超市客户消费表
left join (select user_code,Consume_money from tmp_user_xxx_chaoshi_xiaofei_2020 where op_time=202001) a1 on a1.user_code=a.user_code #某某超市客户消费表
left join (select user_code,Consume_money from tmp_user_xxx_chaoshi_xiaofei_2020 where op_time=202002) a2 on a2.user_code=a.user_code #某某超市客户消费表
where a.if_huiyuan_user=1 #会员客户
limit 4 #随机取10条数据
方法二:#某某超市会员客户近三月月均消费情况-------------------------------2020/3/13 18:58:51---------
select a.user_code #客户编码
,a0.Consume_money #19年12月
,a1.Consume_money #20年1月
,a2.Consume_money #20年2月
,(coalesce(a0.Consume_money,0)+coalesce(a1.Consume_money,0)+coalesce(a2.Consume_money,0))/
(case when coalesce(a0.Consume_money,0)>0 then 1 else 0 end + case when coalesce(a1.Consume_money,0)>0 then 1 else 0 end +
case when coalesce(a2.Consume_money,0)>0 then 1 else 0 end +
case when coalesce(a0.Consume_money,0)<=0 and coalesce(a1.Consume_money,0)<=0 and coalesce(a2.Consume_money,0)<=0
then 1 else 0 end
) as avg_Consume_money #近三月月均消费情况
from tmp_user_xxx_chaoshi a #某某超市客户表
left join (select user_code,Consume_money from tmp_user_xxx_chaoshi_xiaofei_2019 where op_time=201912) a0 on a0.user_code=a.user_code #某某超市客户消费表
left join (select user_code,Consume_money from tmp_user_xxx_chaoshi_xiaofei_2020 where op_time=202001) a1 on a1.user_code=a.user_code #某某超市客户消费表
left join (select user_code,Consume_money from tmp_user_xxx_chaoshi_xiaofei_2020 where op_time=202002) a2 on a2.user_code=a.user_code #某某超市客户消费表
where a.if_huiyuan_user=1 #会员客户
limit 4 #随机取10条数据
数据结果如下(只取了4行数据)
结果1 | ||||
客户编码 | 2019年12月 | 2020年1月 | 2020年2月 | 月均 | </