企业官网建设流程全解析

老铁们，集合了！

今天继续TASK06。
SQL训练营的内容，我们已经全部学完了，TASK06主要是练习题，帮大家掌握知识点。使用的数据，都是真实数据，更贴近我们的实际工作情况。
今天是第五部分，错过第四部分的老铁没关系，点击下方链接即可
Part4
今天我们继续上课。
看第四题。
第四题：“请使用A股上市公司季度营收预测中的数据集《Macro&Industry.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%。

我再举几个例子，
ID：1020000008，为工业增加值：采矿业：当月同比。反应采矿业的实际情况。
ID：2160000004，波罗的海干散货指数（BDI）。干散货，一般指铁矿石、煤炭、化肥等。如果BDI上涨，说明全球大宗商品需求旺盛、贸易活跃。BDI下降，预示经济发展放缓。
ID：2170726266，深圳市商业住宅成交面积。反应深圳房地产市场的活跃度，成交面积越大，说明市场交易月旺盛。
ID：2020101522，全社会用电量：第一产业，通常为当月值。反应农业生产活动的电力消耗规模。一般春耕、秋收等农忙时节，用电量较高。也是题目要求我们计算的这个ID

拆解题目

接下来，我们拆解题目，全社会用电量:第一产业:当月值在2015年用电最高峰是发生在哪月？
我们先找出2015年，第一产业的用电高峰，发生在哪一个月份。语句入下：

select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'；

表格为macro industry，我们先选择所有，再根据用电量排名。
条件是：第一产业，2015年。所以where里面为like ‘%primary In%’，我们采用模糊查询。当然，也可以写全称。
2015年，根据习惯，一般写成：PERIOD_DATE>=‘2015-01-01’ and PERIOD_DATE<‘2016-01-01’。
**注意：**条件为时间段的，都使用左闭右开的写法。即使时间为2015-12-31：23：59：59，也小于26年1月1日，也在15年这个范围内，也会被包含进去。
根据如上语句，结果如下：

我们看最右面一列，15年用电量的高峰期，是8月份。2015-08-31，指15年8分月。
第二个问题，找出15年用电高峰的月份后，我们与去年同期相比。我们接下来找出14年8月份的用电量
由于是练习，我们用select的时候，还是全部选择。

select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-08-01'andPERIOD_DATE<'2014-09-01';

结果如下：

我们可以看到，15年8月份的用电量，为133.4165。14年8月份的用电量为130.3976。
同比增长，增长率为（133.4165-130.3976）/130.3976=2.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_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2016-01-01';

如上的查询结果，我们给每一个月的用电量进行了排序，有1，2，3，，，。排序1的，为最高的月份。
我们把查询结果看作一张表格，选取排序结果为1的行，就是用电量最高的月份。那我们就可以用子查询完成，如下：

select*from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2016-01-01')awhereElec_rank=1;

from后面括号内的，不再是一张表，而是我们生成的排序表格。a，是表的别名。这种查询，我们叫子查询。
当然，我们可不可以求出排序后，直接取第一位的，不执行子查询呢？
当然可以，这时，我们可以使用order by+limit。

select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2016-01-01'orderbyElec_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*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-01-01';

还是为了教学方便，select后面，我先用*代替选取内容。
查询条件为第一产业，14年的数据。
有小伙伴会问，查询条件，时间这个条件，可不可以直接写成8月份，代码还简洁。
我不建议怎么写，如果15年用电高峰不是8月份，是7月份，那我们还要改查询条件，没有通用性。
如上语句，查询结果如下：

有了这两张表的数据，接下来，我们就把这两张表结合起来，15年的数据少，我们就用左外连接，15年数据在左面，语句如下：

select*from(select*,row_number()over(orderbyDATA_VALUEdesc)Elec_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2016-01-01'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'%primary In%'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'Total Electricity Consumption: Primary Industry'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2015-12-31'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'Total Electricity Consumption: Primary Industry'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-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_rankfrom`macro industry`wherename_cnlike'Total Electricity Consumption: Primary Industry'andPERIOD_DATE>='2015-01-01'andPERIOD_DATE<'2016-01-01'orderbyElec_ranklimit1)aleftouterjoin(select*from`macro industry`wherename_cnlike'Total Electricity Consumption: Primary Industry'andPERIOD_DATE>='2014-01-01'andPERIOD_DATE<'2015-01-01')bonmonth(a.PERIOD_DATE)=month(b.PERIOD_DATE);

这里面，我用到了’ifnull’和’nullif’函数，这两个函数，对我们处理数据，非常的有用。
课程总结
通过上面的分析，我们学习了如何使用limit函数、窗口函数，如何进行增长率的运算。
负责的语句，都会含有嵌套语句，这个是我们今后的学习中，要经常使用，练习的。
文章的最后面，我又使用了’ifnull’和’nullif’函数，小伙伴也可以试一试这两个函数的功能。
有什么疑问，欢迎评论区留言。