前言:
目的:利用O2O优惠券数据集锻炼个人SQL编写能力
过程:将文件导入datagrip,利用SQL进行数据分析,具体分析内容见另一篇博客:O2O优惠券核销-数据分析
说明:本次SQL主要实现上述博客中的仪表盘部分
目录
1. 输出样本数据的整体情况
select
count(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. 查找各距离的领券人数/用券消费人数/核销率
# 查找各距离的领券人数/用券消费人数/核销率
select
Distance,
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 (
select
case
when 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 flag
from ddm.offline_train
)
select
flag 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. 不同优惠券类型的核销情况和平均领取距离
# 不同优惠券类型的核销情况和平均领取距离
select
Discount_rate,
avg(Distance) as '平均距离',
count(Date_received) as '领券人数',
sum(if(Date_received is not null and Date is not null,1,0)) as '