老铁们集合了今天继续TASK06。SQL训练营的内容我们已经全部学完了TASK06主要是练习题帮大家掌握知识点。使用的数据都是真实数据更贴近我们的实际工作情况。今天是第五部分错过第四部分的老铁没关系点击下方链接即可Part4今天我们继续上课。看第四题。第四题“请使用A股上市公司季度营收预测中的数据集《MacroIndustry.xlsx》中的sheet-INDIC_DATA请计算全社会用电量:第一产业:当月值在2015年用电最高峰是发生在哪月并且相比去年同期增长/减少了多少个百分比”数据我已经导入DG里面需要原始数据的可以私下联系我分析题目我们进行第一步分析题目表格结构如下为了让大家学到更多的知识扩宽知识面我先把表格里面的内容简单介绍一下。indic_id,为指标唯一ID如截图的1020000004中文含义是工业增加值全部当月同比。英文为Value Added of Industry: All: YoY。M代表月份即按月统计。其中YoY全称为Year-over-Year。指与去年同月相比的增长百分比。我们看截图第一行日期为2018-04-30指2018年4月的工业增加值同比去年4月增长7%。我再举几个例子ID1020000008为工业增加值采矿业当月同比。反应采矿业的实际情况。ID2160000004波罗的海干散货指数BDI。干散货一般指铁矿石、煤炭、化肥等。如果BDI上涨说明全球大宗商品需求旺盛、贸易活跃。BDI下降预示经济发展放缓。ID2170726266深圳市商业住宅成交面积。反应深圳房地产市场的活跃度成交面积越大说明市场交易月旺盛。ID2020101522全社会用电量第一产业通常为当月值。反应农业生产活动的电力消耗规模。一般春耕、秋收等农忙时节用电量较高。也是题目要求我们计算的这个ID拆解题目接下来我们拆解题目全社会用电量:第一产业:当月值在2015年用电最高峰是发生在哪月我们先找出2015年第一产业的用电高峰发生在哪一个月份。语句入下select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31表格为macro industry我们先选择所有再根据用电量排名。条件是第一产业2015年。所以where里面为like ‘%primary In%’我们采用模糊查询。当然也可以写全称。2015年根据习惯一般写成PERIOD_DATE‘2015-01-01’ and PERIOD_DATE‘2016-01-01’。**注意**条件为时间段的都使用左闭右开的写法。即使时间为2015-12-31235959也小于26年1月1日也在15年这个范围内也会被包含进去。根据如上语句结果如下我们看最右面一列15年用电量的高峰期是8月份。2015-08-31指15年8分月。第二个问题找出15年用电高峰的月份后我们与去年同期相比。我们接下来找出14年8月份的用电量由于是练习我们用select的时候还是全部选择。select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-08-01andPERIOD_DATE2014-09-01;结果如下我们可以看到15年8月份的用电量为133.4165。14年8月份的用电量为130.3976。同比增长增长率为133.4165-130.3976/130.39762.315%。我们可以用“select (133.4165-130.3976)/130.3976;”好了我们用三段sql语句回答了这个问题。但是我们可否把三段语句整合在一个sql语句群里面呢第二个问题这三段语句没有通用性如果是其它指标最高峰可能不是在8月份那第二段语句就要修改第三段语句也要改。那有没有一整段通用性强的语句呢我们接下来思考。既然要整合那我们可以把15年8月份的数据和14年8月份的数据整合在一张表上。这张表一列是15年的数据一列是14年的数据我们可以让这两列相减用得到的差再求增长率。根据思路我们还是先求出15年的用电高峰的月份。之前的SQL语句我们可以求出用电量的排名其实我们只要用电量最高的那个月份就可以其它的我们都不需要。那我们就只选取用电量最高的月份也给系统减负。select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2016-01-01;如上的查询结果我们给每一个月的用电量进行了排序有123。排序1的为最高的月份。我们把查询结果看作一张表格选取排序结果为1的行就是用电量最高的月份。那我们就可以用子查询完成如下select*from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2016-01-01)awhereElec_rank1;from后面括号内的不再是一张表而是我们生成的排序表格。a是表的别名。这种查询我们叫子查询。当然我们可不可以求出排序后直接取第一位的不执行子查询呢当然可以这时我们可以使用order bylimit。select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2016-01-01orderbyElec_ranklimit1;结果如下如上SQL语句中最后执行order by Elec_rank limit 1排序后选取排名为1的行。有了这一行我们就需要把14年8月份的用电量也加上。也就是两张表要联合在一起。14年8月份用电量加上也就是多了一列那我们需要外连接‘15年8月份用电量’VS‘14年8月份用电量’。连接条件就是两张表的月份相同。15年8月份的表我们命名为a表格就是我们刚才使用order by Elec_rank limit 1的这一段语句。14年8月份的表我们命名为b就需要我们提取14年的数值。如果不提取运算量会非常大。14年的数值如下select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01;还是为了教学方便select后面我先用*代替选取内容。查询条件为第一产业14年的数据。有小伙伴会问查询条件时间这个条件可不可以直接写成8月份代码还简洁。我不建议怎么写如果15年用电高峰不是8月份是7月份那我们还要改查询条件没有通用性。如上语句查询结果如下有了这两张表的数据接下来我们就把这两张表结合起来15年的数据少我们就用左外连接15年数据在左面语句如下select*from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2016-01-01orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);我们看如上语句a、b两张表左外连接a表为15年数据b表为14年数据。连接的条件为月份相同。由于a表只有15年8月份的数据所以得出来的最终结果为这两年的8月份数据。第一行由于我们使用的是星号导致查询出来的列非常多。现在我们只选取有用的部分更改如下selecta.name_cn,a.DATA_VALUE,b.DATA_VALUEfrom(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);我们选择了与答案相关的列结果如下题目还要求求出增长率和增长的数值我们一并求出。语句如下selecta.name_cn,a.DATA_VALUE,b.DATA_VALUE,(a.DATA_VALUE-b.DATA_VALUE)/b.DATA_VALUEfrom(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);小伙伴可以试一下我们一点一点完善。通过a的数据减去b的数据差再除以b的数据就得到增长率。我们知道增长率一般是百分比而我们求出来的是小数那我们怎么办呢Mysql没有直接把小数变成百分数的工具但是我们可以分两步走达到效果。首先乘以100接着再用concat函数把积和百分号连接。好我们试一下。selecta.name_cn,a.DATA_VALUE,b.DATA_VALUE,concat((a.DATA_VALUE-b.DATA_VALUE)/b.DATA_VALUE*100,%)from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);结果如下我们看到增加了一列数值为百分比。但是小数点后面数字位数非常多我们通常保留两位。这个时候我们就需要另一个工具round。我们试一下。selecta.name_cn,a.DATA_VALUE,b.DATA_VALUE,concat(round((a.DATA_VALUE-b.DATA_VALUE)/b.DATA_VALUE*100,2),%)from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);我们看一下语句里面用round小数点后面保留两位小数再用concat用%连接。结果如下其实到这里这道题答案已经出来了但是有一些地方我们还可以优化。我们可以给列起别名。selecta.name_cn,a.DATA_VALUE15年8月用电量,b.DATA_VALUE14年8月用电量,concat(round((a.DATA_VALUE-b.DATA_VALUE)/b.DATA_VALUE*100,2),%)同比增长率from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlike%primary In%andPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlike%primary In%andPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);结果如下这段语句中我们使用了like模糊查询名字但我们知道具体的名字为“Total Electricity Consumption: Primary Industry”。我们要知道模糊查询的话系统的压力非常大能不用模糊查询就不用模糊查询。selecta.name_cn,a.DATA_VALUE15年8月用电量,b.DATA_VALUE14年8月用电量,concat(round((a.DATA_VALUE-b.DATA_VALUE)/b.DATA_VALUE*100,2),%)同比增长率from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlikeTotal Electricity Consumption: Primary IndustryandPERIOD_DATE2015-01-01andPERIOD_DATE2015-12-31orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlikeTotal Electricity Consumption: Primary IndustryandPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);我们看结果如下图如果说我们继续往下深究我们发现为了计算增长率我们用了除法除法我们知道除数不能为0所以我们要规避除数为零的情况。这个时候就要使用nullif语句。语句如下selecta.name_cn,a.DATA_VALUE15年8月用电量,b.DATA_VALUE14年8月用电量,concat(round(ifnull((a.DATA_VALUE-b.DATA_VALUE)/nullif(b.DATA_VALUE,null),14年无数据)*100,2),%)同比增长率from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrommacro industrywherename_cnlikeTotal Electricity Consumption: Primary IndustryandPERIOD_DATE2015-01-01andPERIOD_DATE2016-01-01orderbyElec_ranklimit1)aleftouterjoin(select*frommacro industrywherename_cnlikeTotal Electricity Consumption: Primary IndustryandPERIOD_DATE2014-01-01andPERIOD_DATE2015-01-01)bonmonth(a.PERIOD_DATE)month(b.PERIOD_DATE);这里面我用到了’ifnull’和’nullif’函数这两个函数对我们处理数据非常的有用。课程总结通过上面的分析我们学习了如何使用limit函数、窗口函数如何进行增长率的运算。负责的语句都会含有嵌套语句这个是我们今后的学习中要经常使用练习的。文章的最后面我又使用了’ifnull’和’nullif’函数小伙伴也可以试一试这两个函数的功能。有什么疑问欢迎评论区留言。