企业官网建设流程全解析

各位老铁！晚上好！
今天继续TASK06。
SQL训练营的内容，我们已经全部学完了，TASK06主要是练习题，帮大家掌握知识点。使用的数据，都是真实数据，更贴近我们的实际工作情况。
今天是第六部分，错过第五部分的老铁没关系，点击下方链接即可：
第五部分
上节课，我们讲解完了第四题，今天我们继续第五题。如下：
“数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》，试统计在2016年6月期间，线上总体优惠券弃用率为多少？并找出优惠券弃用率最高的商家。

弃用率 = 被领券但未使用的优惠券张数 / 总的被领取优惠券张数”
（想要数据来源的小伙伴，可以直接在下方留言，我提供原始数据。）

一分析题目
我们还是先进行第一步，分析题目
表格截图如下：

表格数据比较多，我这里截图一部分。
表格中，从左往右，以此为用户、商家、行为、优惠券、折扣率、接收日期和付款成交日期。
这里面主要关注的，就是Action一列。有0、1和2三个数字。分别对应三种不同的购物行为，这个是解题的关键。
0=未领券直接消费
买家没有使用消费券，直接购买商品，没有优惠，Counpon_id为空。
1=有领券且实际使用（用券消费）
买家领了券，且消费时，使用了优惠券，享有优惠。
2=领券但弃用
买家已经领取优惠券，下单满足门槛，但下单时忘记勾选使用优惠券，或下单后未支付，优惠券退回账户，最终没有使用

细心的小伙伴可能会发现，1的后面，有的有优惠券id，有的没有优惠券id。
没有优惠券id，是不是就是没有使用呀？
这里笔者给大家解释一下，id为空值的原因
1，优惠活动不发放独立的优惠券
比如我们上网购物经常遇到的限时特价、促销、店铺满减等活动，不会单独发放优惠券，不会生成唯一的Counpon_id。
2，主订单与子订单
有一些优惠信息，只存在于主订单内，对应的子订单不显示优惠信息，所以对应的消费行为没有优惠信息。

二解答
那么根据题目，我们要求出被领取但没有使用的优惠券的张数，这个其实就是计算Action=2的数量。
总的被领取的优惠券的总张数，那就是Action=2的数量，加上Action=1且Coupon_id不为空的数量。
Action=1 AND coupon_id IS NULL，代表优惠来自减免、购物津贴、不独立发行优惠券,不计入计算。
Action=2的数量，我们可以用Mysql求出

selectcount(*)fromccf_online_stage1_trainwhereAction=2;

结果为655789。

领取且被使用的消费券的数量，语句如下：

**selectcount(*)fromccf_online_stage1_trainwhereAction=1andCoupon_idisnotnull;**

结果为216438。
两个语句的结果相加，就是被领取的优惠券总数。
那这个求和，我们怎么计算呢，可以把上面两个语句嵌套，完了求和。
比如两个语句分别为A和B，我们可以
select A+B;
但很显然，这样很麻烦，涉及到很多重复计算。
有没有更简单的方法呢？
还真有。
我们可以分别统计两种情况的数量，一种是655789的情况，一种是216438的情况，用or语句求和。这里我们会用到case语句。
语句如下：

selectsum(casewhenAction=1andCoupon_idisnotnullorAction=2then1else0end)fromccf_online_stage1_train;

如果“Action=1 and Coupon_id is not null”或者“Action=2”，都算作1，其余都为0。
用sum语句求和。得出的就是总的被领取的消费券数量。
那么同理，Action=2的数量，我们也可以用case语句求出

selectsum(casewhenAction=2then1else0end)fromccf_online_stage1_train;

小伙伴可以自己算一下，结果跟之前的语句的结果一样。
计算弃用率，我们就可以用两个case的结果相除。语句如下：

selectsum(casewhenAction=2then1else0end)/sum(casewhenAction=1andCoupon_idisnotnullorAction=2then1else0end)fromccf_online_stage1_train;

结果为0.7519，也就是75.19%。我们需要把这个小数，变成百分数。乘以100，后面加‘%’，小数点后面保留三位，语句如下。
我们先乘以100。

selectsum(casewhenAction=2then1else0end)/sum(casewhenAction=1andCoupon_idisnotnullorAction=2then1else0end)*100'弃用率'fromccf_online_stage1_train;

结果为75.1856.我们把这个列起别名，这样更加直观。见下图。

接下来我们保留三位小数，用round函数。

selectround(sum(casewhenAction=2then1else0end)/sum(casewhenAction=1andCoupon_idisnotnullorAction=2then1else0end)*100,3)'弃用率'fromccf_online_stage1_train;

结果如下：75.186，我们看到，有四舍五入。

最后，我们加上‘%’，就得出了百分数。

selectconcat(round(sum(casewhenAction=2then1else0end)/sum(casewhenAction=1andCoupon_idisnotnullorAction=2then1else0end)*100,3),'%')'弃用率'fromccf_online_stage1_train;

用concat函数连接后，结果如下：

我们看到，数字后面增加了百分号。
目前我们很接近答案了，我们看一下题目，要求“试统计在2016年6月期间”，也就是我们要找出6月份的弃用率。
那我们来看表格，6月份怎么选取。
其中我们能看到，表格中有两列表示时间的，“Date_received”和“Date”。
第一个表示接收日期，或者领取日期。就是领取优惠券的日期。
第二个表示付款日期，就是用户选择商品，下单付款的日期。
那么这两个日期，我们选择哪一个呢？
1，从表格结构看，行为2(Action=2),对应的付款日期为空，如果我们使用付款日期筛选6月份，那根本筛选不出来！大家想想对吧？
因为“Date”非空值对应的行为，不死0就是1.
2，笔者也查询了一些资料，优惠券弃用率的标准口径是，指定周期内领取的优惠券，最终没有被使用的比例。
就好比6月份一共发出了1000张优惠券，官方要查出，这1000张中，有多少张没有被使用。
好的，有了上述两条逻辑，我们把语句写完整。

selectconcat(round(sum(casewhenAction=2then1else0end)/sum(casewhen(Action=1andCoupon_idisnotnull)orAction=2then1else0end)*100,3),'%')'弃用率'fromccf_online_stage1_trainwhereDate_received>='2016-06-01'andDate_received<'2016-07-01';

结果76.556%。
这个就是我们要的答案。

我们再往下看。“并找出优惠券弃用率最高的商家”。
那我们就按照商家来分组、排名。
在语句中，增加商家的ID。

selectMerchant_id,concat(round(sum(casewhenAction=2then1else0end)/sum(casewhen(Action=1andCoupon_idisnotnull)orAction=2then1else0end)*100,3),'%')'弃用率'fromccf_online_stage1_trainwhereDate_received>='2016-06-01'andDate_received<'2016-07-01'groupbyMerchant_id;

由于我们使用了聚合函数，所以select后面加上Merchant_id后，后面要用group by 。
我们运行完这个语句后，会显示每一个商家的弃用率。大家可以运行，答案行数比较多，我就不一一显示了。
选择弃用率最高的商家，那我们就用弃用率排名，选第一名。

selectMerchant_id,concat(round(sum(casewhenAction=2then1else0end)/sum(casewhen(Action=1andCoupon_idisnotnull)orAction=2then1else0end)*100,3),'%')giveup_ratefromccf_online_stage1_trainwhereDate_received>='2016-06-01'andDate_received<'2016-07-01'groupbyMerchant_idorderbygiveup_ratedesclimit1;

由于要按照弃用率排名，所以我们把弃用率改成英文，这样系统读取方便一些。
最终的结果是

三课程总结
通过上面的练习，我们学习了case…when语句的使用。
这个是我们专业领域的精进。
同时，我们也拓宽了我们的知识面。在电商领域中，Action中的0、1和2分别代表什么意思。
希望课后小伙伴能多多练习。
有什么不清楚的也欢迎评论区留言。