# 阿里云天池龙珠计划 SQL 训练营 - Task06 part3

老铁们集合了今天继续TASK06。SQL训练营的内容我们已经全部学完了TASK06主要是练习题帮大家掌握知识点。使用的数据都是真实数据。更贴近我们的实际工作情况。今天是第三部分错过第二部分的老铁不用着急点击下方链接即可回顾课程。Task06 第二部分话不多说我们继续上课。今天来学习第三题题目如下“数据来源https://tianchi.aliyun.com/competition/entrance/231593/information使用Coupon Usage Data for O2O中的数据集《ccf_offline_stage1_test_revised.csv》试分别找出在2016年7月期间发放优惠券总金额最多和发放优惠券张数最多的商家。这里只考虑满减的金额不考虑打几折的优惠券。”6.1 表格介绍老规矩我们先看一下表格了解表格含义。表名ccf_offline_stage1_test_revised。表名各个部分含义如下如上为标题内容简单说表格记录了用户的领券行为只包含领用不包含使用。接下来我们来看表格里面的内容并且简单介绍一下表格。表格部分截图如下我们看道一共有六列我们按照顺序分别介绍每一列的含义。题目涉及到的列名我讲解一下。User_id就是用户ID用户就是购买产品的顾客。如我去一家商店购买了一支笔那么我就有一个唯一id。Merchant_id就是商家ID就是售卖产品的人。如我看了一家卖学习用品的商店那么我就有一个唯一id。Discount_rate这个就是优惠折扣率。它有两种格式满减 305、20020分别表示满30元减5元、满200元减20。 这种叫做满减券。折扣 0.9、0.8 分别表示打九折、打八折。 这种叫做折扣券。这两种优惠方式是我们购物经常遇到的。其实我们每一次领取优惠券包括使用都会有对应的记录。这些记录都是商家做分析的重要依据。所以mysql在电商领域应用非常广泛。6.2寻找答案老铁们对表格有了了解后解题也会快一些。我们来看题目“试分别找出在2016年7月期间发放优惠券总金额最多和发放优惠券张数最多的商家”。这其实是两部分1是找出优惠券金额最多的、2是找出张数最多的。我们先来看第一部分找出金额最多的。这里有提示只考虑满减的金额不考虑打折的。那我们要先找出满减的金额。满减的金额我们知道是Discount_rate列30020表示满300减20305表示满30减5。但是这一列有两种类型的数据一种是分数就是30020305还有一种就是小数0.90.8.表示打八折、打九折。从题目的要求可以知道我们要的都是分数的也就是带有“:”的。所以说我们第一个选择条件就是只选这种有冒号的内容。那用什么办法呢思考三秒123字符串用含有某一种符号我们是不是可以模糊查询即用like。由于冒号在中间它的前后两边都有数字那我们就用“%:%作为查找条件。我们写语句这里我们选择的内容先用“*”代替。select*fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%;查询结果如下大家可以敲代码试一试。找出了所有带冒号的内容下一步我们就要截取冒号后面的数字。如205我们只要520020我们只要20。这个我们用什么语句呢用substring_index。我给大家讲解一下这个的用法。substring_index是指按照指定分隔符分隔字符串并返回指定长度。什么意思我举个例子。有如下字符串600383.SH股票代码沪市。我现在只要它的代码即点左面的部分。我就可以用substring_index。代码如下selectsubstring_index(600383.SH,.,1);结果如下600383。我们一个一个讲。600383.SH就是我们要处理的字符串我们把它放在括号里面的第一位。我们呢取600383就是取’.‘右面的部分也就是这个点就是分隔符。所以括号内第二位我们选取’.。最后一个1代表取字符串中第一个’.的左面的全部字符串。本例中由于只有一个点所以这个点就是第一个它左面的部分就是600383.那我们在变换一下给它加一个点变为600383.SH.A1变成2。selectsubstring_index(600383.SH.A,.,2);结果如下600383.SH。即第二个点左面的全部。那有人可能会说我能取左面的可不可以取右面的呢可以我们在数字前面加一个负号’-就可以。就是1变成-12变成-2。我们看语句。selectsubstring_index(600383.SH.A,.,-2);大家可以想想如上的语句结果是什么。就是正数变成负数了。大家可以敲代码练习。结果是SH.A。就是右面第二个或者从右往左数第二个点它右面的全部内容需要返回。我们注意语句返回的都是字符串哪怕是600383其实也是字符串。如果说我们想把600383这样的字符串转化成数字为了便于后续做加减法我们还需要cast函数把字符串转换成数字。语句如下selectcast(600386asunsigned);括号里面600383就是需要转化成数字的字符串。as unsigned就是转化成无符号类型的正数即不允许右负数。股票代码天人都是正数所以可以用unsigned。as后面跟的就是目标类型。我们回到题目如何截取表格内冒号后面的数字我们用substring_indexcast函数。selectcast(substring_index(Discount_rate,:,-1)asunsigned)fromccf_offline_stage1_test_revised;大家可以自己敲代码看看是不是跟上面的代码逻辑应该一样。我们再结合之前的代码select *from ccf_offline_stage1_test_revisedwhere Discount_rate like ‘%:%’;完整代码如下select*,cast(substring_index(Discount_rate,:,-1)asunsigned)fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%;结果如下我们可以检查一下每一行“discount_rate冒号后面的数字应该跟我们截取的是一样的。这样我们就查询出了每一个满减券的满减额度。题目是让我们找出对应的商家那我们查询的范围也可以缩小在实际开发中也不建议写”*“。selectMerchant_id,Discount_rate,cast(substring_index(Discount_rate,:,-1)asunsigned)fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%;题目让我们找出发放优惠券总金额最多的商家。总金额怎么求我们看到求和那肯定要用sum。用了sum函数必须要有group by函数。由于查询的是商家那我们就按照商家分组。求出每一位商家发放满减券的总金额。语句如下selectMerchant_id,Discount_rate,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%groupbyMerchant_id,Discount_rate;第三列的列名比较长大家敲代码的时候可以取一个别名。题目要求求出最多的商家那我们就需要排名排名的函数是order by。by后面跟排名依据。我们依据的是满减券的总金额即“sum(cast(substring_index(Discount_rate, ‘:’, ‘-1’)as unsigned))”。我们降序排列即总金额最多的在第一行后面再加desc。完整语句如下selectMerchant_id,Discount_rate,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%groupbyMerchant_id,Discount_rateorderbysum(cast(substring_index(Discount_rate,:,-1)asunsigned))desc;结果如下题目还有一个要求是在2016年7约见表格中也给了发放时间所以我们还要加上一个筛选条件即日期。7月间即大于6月小于8月我们一般如下表示Date_received‘2016-07-01’ and Date_received‘2016-08-01’。日期大于等于7月1日小于等于8月1日。即整个7月。完整语句如下selectMerchant_id,Discount_rate,sum(cast(substring_index(Discount_rate,:,-1)asunsigned))fromccf_offline_stage1_test_revisedwhereDiscount_ratelike%:%andDate_received2016-07-01andDate_received2016-08-01groupbyMerchant_id,Discount_rateorderbysum(cast(substring_index(Discount_rate,:,-1)asunsigned))desc;结果如下结果为7月间发放满减券总金额的排名。我们看到第一名是Id为760的商家总金额149630元。好了到这呢我们这道题完成将近一半了。学习到现在估计大家很累了。我们这道题先讲解到这大家有什么意见和建议也可以评论区讨论。包括讲课的方式课程内容的深浅。我会虚心接受不断进步。希望评论区留下您的宝贵建议。