SQL第一弹——基础知识

大家好哇，想了半天决定秋招知识分享还是以数分最重要的“硬实力”最好讲也是自己最熟悉的SQL部分开始吧~不过这里我一般不会复述SQL的基础知识，只会分享一些校园课本之外的SQL进阶知识或是面试较高难度的部分。

1 SQL的执行顺序

from → join on → where → group by → with → having → select distinct → 窗口函数 → order by  → limit
# 以下是详细介绍
1.from    ：对FROM左边的表和右边的表计算笛卡尔积，产生虚拟表t1
2.on     ：对表t1进行ON筛选，只有符合条件的行才会记录在表t2中
3.join    ：如果指定了OUTER JOIN（如：left join、right join）,那么未匹配到的行作为外部行添加到表t3中
4.where   ：对表t3进行where条件过滤，只有符合条件的记录才会记录在表t4中
5.group by ：根据group by 子句中的列，对表t4记录进行分组的聚合函数（sum、count、max）操作，产生表t5
6.with    ：对表t5应用 rollup/cube，生成表t6
7.having   ：对表t6进行having过滤，只有符合条件的行才会记录在表t7中
8.select   ：执行select操作，选择指定的列，产生表t8
9.distinct  ：对表t8记录进行去重，产生表t9
10.order by ：对表t9记录进行排序，产生表t10
11.limit   ：取出指定的行，产生表t11,并将结果进行展示

理解SQL的执行顺序十分重要，get了执行顺序也就理解了写sql时很多的注意事项：

使用where筛选数据时，无法使用select中新生成的列（因为where先执行）。这也帮助我们理清写SQL逻辑思路：比如取成绩排名前十的学生名单时，一定是需要两层查询的，因为第一层查询在select里面使用窗口函数rank排名，必须在第二层循环里使用where筛选出前十。
第五步group by时完成对聚合函数的计算，所以在having里可以直接调用count(1) as name起的列名name。
在第五步group by及后面时，才可以使用select中自己定义的列名；由于where在前面执行，所以筛选条件时只能使用原始列名。

2 Hive SQL与MapReduce

MapReduce是一个基于Hadoop的分布式运算程序的编程框架，也是现在海量数据处理分析的核心框架。但是直接写MR代码的难度较高，而Hive SQL可以让我们撰写类SQL语言来实现MR计算。理清HQ与MR的对应关系，可以更好的帮助我们理解一些优化查询的方法。

简单来讲：①Map阶段会对输入数据进行切片并行进行多个map任务，主要是以join on的字段或是group by的字段为key，以其余字段作为value，输出键值对。 ②Reduce阶段则是对Map阶段的键值对结果进行汇总或是聚合函数运算，同一个key的数据会被分到同一个reduce任务里。

其实Map阶段和Reduce阶段之间，还有Shuffle阶段，涉及到数据在缓冲区的读写、归并排序等等。只要了解这点就够了：Shuffle阶段会根据 Key的值执行Hash算法，并将Key/Value对按照Hash值推至不同对Reduce中。

了解了Map和Reduce阶段，其实就能理解了为什么会发生数据倾斜从而进行查询优化：①Map阶段慢，可能是启动的map任务太少（不可分割的大文件）或者太多（小文件过多），涉及到数据的切片大小问题；②Reduce阶段慢，可能是只启动了一个reduce任务（比如使用distinct）或是某一个reduce任务过慢（某一个key的数据格外多）；③Shuffle阶段过慢，这个过程对于数据分析人员能够优化的方法并不多。

3 Hive SQL的宏观位置

最后补充一下Hive的一些宏观认知。

Hadoop是一个解决海量数据的存储和海量数据的分析计算问题的分布式系统基础架构，而Hive是基于Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为数据库表，并提供SQL查询功能，可以将SQL语句转换为上面提到的MapReduce任务进行运行。

总体上看，Hive处于整个数据流程的相对靠后的位置，这通常也是由数据分析人员负责的位置，前面较为底层的数据处理与计算过程通常由数据开发人员进行负责。

在整个数据加工过程中，数据仓库可以有以下几层的分类：

数据存储层：ODS（Operational Data Store）基本上就是数据从源表拉过来进行etl后的数据，是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的 ETL 之后，装入本层。

数据明细层：DWD（Data Warehouse Detail）：DWD又叫做数据明细表, 很多时候存储的都是事实表为主；是数据仓库的细节数据层，是对STAGE层数据进行沉淀，减少了抽取的复杂性。

数据中间层：DWM（Data WareHouse Middle）：是对DWD层的生产数据进行轻度综合和汇总统计(可以把复杂的清洗，处理包含，如根据PV日志生成的会话数据)。

数据服务层：DWS（Data WareHouse Servce）:又称数据集市或宽表。按照业务划分，如流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。这里做一层轻度的汇总会让计算更加的高效，在此基础上如果计算仅7天、30天、90天的行为的话会快很多。我们希望80%的业务都能通过我们的DWS层计算，而不是ODS。