O2O优惠券核销-SQL实现
前言：
目的：利用O2O优惠券数据集锻炼个人SQL编写能力
过程：将文件导入datagrip，利用SQL进行数据分析，具体分析内容见另一篇博客：O2O优惠券核销-数据分析
说明：本次SQL主要实现上述博客中的仪表盘部分
目录
1. 输出样本数据的整体情况
2. 查找各距离的领券人数/用券消费人数/核销率
3. 消费券使用情况占比
4. 不同优惠券类型的核销情况和平均领取距离
5. 不同满减门槛的核销情况
6. 不同工作日的优惠券平均核销周期、核销率
7. 不同月份优惠券领券次数/核销次数/核销率
8. 不同领券次数用户的分布情况（平均核销率/占比）
9. 不同核销率用户分布（占比）
10. 不同领券次数商家的分布情况（平均核销率/占比）
11. 不同核销率的商家分布情况（占比）
12.仪表盘
1. 输出样本数据的整体情况
selectcount(User_id) as '样本总数',count(distinct Merchant_id) as '商家总数',count(distinct User_id) as '用户总数',count(Date) as '消费总数',count(Date_received) as '领券总数',(select count(*) from ddm.offline_train as a where a.Date_received is not null and a.Date is not null) as '领券消费总数',(select count(*) from ddm.offline_train as a where a.Date_received is null and a.Date is not null) as '无券消费总数',(select count(*) from ddm.offline_train as a where a.Date_received is not null and a.Date is not null)/count(Date_received) as '核销率'
from ddm.offline_train
2. 查找各距离的领券人数/用券消费人数/核销率
# 查找各距离的领券人数/用券消费人数/核销率
selectDistance,count(Coupon_id) as get_coupons_num,sum(if(Date_received is not null and Date is not null,1,0)) as user_coupons_num,sum(if(Date_received is not null and Date is not null,1,0)) /count(Coupon_id) as use_coupons_rate
from ddm.offline_train
where Distance is not null
group by Distance
order by distance
3. 消费券使用情况占比
# 消费券使用情况占比
with temp as (selectcasewhen Date_received is not null and Date is not null then '有券消费'when Date_received is not null and Date is null then '有券未消费'when Date_received is null and Date is not null then '无券消费'end as flagfrom ddm.offline_train
)
selectflag as '优惠券使用情况',concat(round(count(flag)/(select count(*) from temp)*100,2),'%') as '百分比'
from temp
group by flag
order by count(flag)/(select count(*) from temp)
4. 不同优惠券类型的核销情况和平均领取距离
# 不同优惠券类型的核销情况和平均领取距离
selectDiscount_rate,avg(Distance) as '平均距离',count(Date_received) as '领券人数',sum(if(Date_received is not null and Date is not null,1,0)) as '有券消费人数',sum(if(Date_received is not null and Date is not null,1,0))/count(Date_received) as '核销率'
from ddm.offline_train
where Date_received is not null
group by Discount_rate
order by '有券消费人数' desc
5. 不同满减门槛的核销情况
# 不同满减门槛的核销情况
selectmk as '门槛',count(*) as '领券数量',sum(if(Date is not null,1,0)) as '用券消费数量',concat(round(sum(if(Date is not null,1,0))/count(*)*100,2),'%') as '核销率'
from(selectDATE,convert(if(Discount_rate like '%.%',0,Discount_rate),signed) as mkfrom ddm.offline_train) as aa
where mk is not null
group by mk
order by mk
6. 不同工作日的优惠券平均核销周期、核销率
# 工作日平均核销间隔、核销率
with get_coupons as(selectweekday(Date_received)+1 as coupons_day,count(*) as coupons_get_numfrom ddm.offline_trainwhere Date_received is not nullgroup by weekday(Date_received)+1
),
use_coupons as(selectweekday(Date)+1 as coupons_day,count(*) as coupons_use_num,round(avg(datediff(Date,Date_received)),2) as use_intervalfrom ddm.offline_trainwhere Date is not null and Date_received is not nullgroup by weekday(Date)+1
)selectcoupons_day,use_interval,concat(round(coupons_use_num/coupons_get_num*100,2),'%') as coupons_use_rate
from get_coupons
inner join use_coupons using(coupons_day)
order by coupons_day
7. 不同月份优惠券领券次数/核销次数/核销率
# 不同月份领券次数/核销次数/核销率
select`month`,coupons_get_num,coupons_use_num,concat(round(coupons_use_num/coupons_get_num*100,2),'%') as coupons_use_rate
from(selectmonth(Date_received) as `month`,count(*) as coupons_get_numfrom ddm.offline_trainwhere Date_received is not nullgroup by month(Date_received)) as a
inner join(selectmonth(Date) as `month`,count(*) as coupons_use_numfrom ddm.offline_trainwhere Date_received is not null and Date is not nullgroup by month(Date)
)as b using(`month`)
order by `month`
8. 不同领券次数用户的分布情况（平均核销率/占比）
# 不同领券次数用户分布-平均核销率/占比
with temp as (selectUser_id,count(Date_received) as get_num,sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as user_rate,sum(if(Date is not null and Date_received is not null,1,0)) as use_num,casewhen count(Date_received)>10 then '10次以上'when count(Date_received)=0 then '0次'when count(Date_received) between 1 and 2 then '1-3次'else '其他次'end as flagfrom ddm.offline_traingroup by User_id
)selectflag,concat(round(avg(user_rate)*100,2),'%') as user_avg_use_rate,concat(round(count(*)/(select count(*) from temp)*100,2),'%') as user_percent
from temp
group by flag
order by (count(*)/(select count(*) from temp)) desc
9. 不同核销率用户分布（占比）
# 不同核销率用户分布
with temp as (selectUser_id,count(Date_received) as get_num,sum(if(Date is not null and Date_received is not null,1,0)) as use_num,sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as user_ratefrom ddm.offline_trainwhere Date_received is not nullgroup by User_id
)selecttag,concat(round(count(*)/(select count(*) from temp)*100,2),'%') as user_percent
from(selectUser_id,casewhen user_rate = 0 then '核销率：0'when user_rate > 0 and user_rate < 0.3 then '核销率：0-30%'when user_rate >= 0.3 and user_rate< 0.5 then '核销率：30%-50%'when user_rate >= 0.5 then '核销率：50%以上'end as tagfrom temp)aa
group by tag
order by user_percent desc
10. 不同领券次数商家的分布情况（平均核销率/占比）
# 不同领券次数用户分布-平均核销率/占比
with temp as (selectMerchant_id,count(Date_received) as get_num,sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as user_rate,sum(if(Date is not null and Date_received is not null,1,0)) as use_num,casewhen count(Date_received)>100 then '100次以上'when count(Date_received)=0 then '0次'when count(Date_received) between 1 and 10 then '1-10次'when count(Date_received) between 11 and 50 then '11-50次'when count(Date_received) between 51 and 100 then '51-100次'else '其他次'end as flagfrom ddm.offline_traingroup by Merchant_id
)selectflag as '被领券次数',concat(round(avg(user_rate)*100,2),'%') as Merchant_avg_use_rate,concat(round(count(*)/(select count(*) from temp)*100,2),'%') as Merchant_percent
from temp
group by flag
order by (count(*)/(select count(*) from temp)) desc
11. 不同核销率的商家分布情况（占比）
# 不同核销率用户分布
with temp as (selectMerchant_id,count(Date_received) as get_num,sum(if(Date is not null and Date_received is not null,1,0)) as use_num,sum(if(Date is not null and Date_received is not null,1,0))/count(Date_received) as Merchant_ratefrom ddm.offline_trainwhere Date_received is not nullgroup by Merchant_id
)selecttag,concat(round(count(*)/(select count(*) from temp)*100,2),'%') as Merchant_percent
from(selectMerchant_id,casewhen Merchant_rate = 0 then '核销率：0'when Merchant_rate > 0 and Merchant_rate < 0.2 then '核销率：0-20%'when Merchant_rate >= 0.2 and Merchant_rate< 0.3 then '核销率：20%-30%'when Merchant_rate >= 0.3 and Merchant_rate< 0.5 then '核销率：30%-50%'when Merchant_rate >= 0.5 then '核销率：50%以上'end as tagfrom temp)aa
group by tag
order by Merchant_percent desc
12.仪表盘
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！