最优路径：SQL基本功

看到本文，你就赚；看完本文，你就赢了；看懂本文，你很了不起！

最优的学习路径，更快的成为熟练的数据开发工程师：

1，认识SQL

SQL是结构化查询语言，SQL也是一个标准，每个数据库服务器都在标准的基础上进行了相应的调整和扩展，相应的，每个数据库对数据的各种操作语言的语法就会做出相应的调整

示例：（数据开发重点关注）

• DQL：SELECT等
• DML：INSERT、UPDATE、DELETE增删改查
• DPL：BEGIN TRANSACTION、COMMIT和ROLLBACK
• DCL：GRANT/REVOKE赋予/撤销
• DDL：CREATE，DROP新建删除表与视图等
• CCL：DECLARE CURSOR、FETCH INTO，UPDATE WHERE CURRENT

2，SQL的执行次序

2.1，SQL语句经历了什么？

可以用explain 查看执行计划，有兴趣可以自己动手实践一下：

explain  your_sql

2.2，SQL的执行顺序

执行的顺序：FROM->ON->JOIN->WHERE->GROUP BY->SUM,COUNT函数->HAVING->SELECT->DISTINCT->ORDER-BY->LIMIT

与写SQL的顺序不同，SQL的执行顺序并不是从select开始，而是从from开始

步骤分解： 

1、FROM：先去获取from里面的表，拿到对应的数据，生成虚拟表1。 

2、ON：对虚拟表1应用ON筛选，符合条件的数据生成虚拟表2。

3、JOIN：根据JOIN的类型去执行相对应的操作，获取对应的数据，生成虚拟表3。

4、WHERE：对虚拟表3的数据进行条件过滤，符合记录的数据生成虚拟表4。

5、GROUP BY：根据group by中的列，对虚拟表4进行数据分组操作，生成虚拟表5。

6、CUBE|ROLLUP（聚合函数使用）：主要是使用相关的聚合函数，生成虚拟表6。 

7、HAVING：对虚拟表6的数据过滤，生成虚拟表7，这个过滤是在where中无法完成的，同时count(expr)返回不为NULL的行数，而count(1)和count(*)是会返回包括NULL在内的行数。 

8、SELECT：选择指定的列，生成虚拟表8。 

9、DISTINCT：数据去重，生成虚拟表9。 

10、ORDER BY：对虚拟表9中的数据进行指定列的排序，生成虚拟表10。

11、LIMIT：取出指定行的记录，生成虚拟表11，返回给查询用户。

以上是SQL各关键词的执行顺序，如果在一条SQL语句里面你没有用到某个关键词那就不会被执行了。

理解SQL的逻辑执行顺序对我们在实际写SQL的过程中也会有帮助的。

3，SQL优化点

只针对DQL：SELECT 等的优化建议

• 尽量少用select *
• IN 包含的值不应过多
• in和exists、not in 和 not exists的场景
• 尽量少用or，同时尽量用union all 代替union
• 善于使用limit n
• 在Join表的时候使用相当类型的例，并将其索引
• 小心查询中的NULL
• 选择正确的查询引擎
• left join 小表为主联大表

4，常用的内置函数及平台用户自定义函数（udf）

4.1，常用的内置函数：

4.2，正则基本功

4.3，纯朴的位运算

-- 与或异
select 2&2  -- 2
select 1|2  -- 3  
select ~2   -- -3
-- pmod(double a, double b) 返回余数绝对值

4.4，日期变换

date_add(string startdate, int days） -- 日期后移
date_sub(string startdate, int days) -- 日期迁移
unix_timestamp(string date, string pattern) --日期字符串 转成 int
from_unixtime(bigint unixtime[, string format]) -- int转成日期字符串
select from_unixtime(1463042015872,'yyyy-MM-dd HH:mm:ss'

4.5，窗口函数（很重要）

分析函数:
LAG--排序向上去第n,
LEAD--排序向下去第n,
FIRST_VALUE--取分组排序当前行的第一个,  
LAST_VALUE--取分组排序当前行的最后一个就是自己；
ROW_NUMBER()OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,-- 分组排序，生产序号
LAST_VALUE(url)OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,-- 取分组内排序后，截止到当前行，第一个值  
FIRST_VALUE(url)OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2-- 取分组内排序后，截止到当前行，最后一个值

还有很多...读者自己探索吧

4.6，同样我们也可自定义函数-UDF

1，科普：HIVE中有三种UDF：

普通udf，用户定义聚集函数udaf: user-defined aggregate function，以及用户定义表生产函数udtf: user-defined table-generating function。

2，我们可以参考：udf文档-去开发自己个性化的自定义函数，来满足自己个性化的需求。

推荐阅读：