本文讲解的知识点主要有 ：公用表表达式（CTE）、窗口函数、SQL NULL 函数及SQL 条件判断；针对人群：对SQL一知半解，停留在增删改查初级层面，大神可以飘过。
一、公用表表达式（Common Table Expression，CTE）
CTE 可以作为临时的结果集，可以在DML（Data Manipulation Language）语句中被多次引用，从而是语句更加简练。
公用表达式定义 ：
WITH express_name [(colum_name[,....n])]
AS
(CTE_query_definition)
（1）参数含义
express_name：公用表表达式名字；
colum_name ：字段名；
CTE_query_definition：一个Select语句。
（2）CTE特点
1）在一定程度上提高SQL语句的可读性
2）提高SQL语句执行性能
在多表进行 full join on （MySQL 目前不支持），每个部分都需要使用 一个select 查询结果集时，相应每个部分都需要去执行一遍，而使用WITH AS 语句 ，则需执行一次 ；另外，SQL优化器也会对WITH AS 短语做优化，连续被调用两次以上，优化器会自动将WITH AS 短语的结果集存放在一个临时表中。
3）可以在一个语句中多次引用公用表表达式
4）可以定义递归共用表表达式
实例讲解 ：
获取某APP每一天不同平台dau数据
这个好办，弄一个表变量不就可以了吗
一运行报错，查找一下PostgreSQL中并不支持declare 方式创建表变量，PostgreSQL只有通过函数返回record or ref Cursor。（MySQL支持）
declare 定义表变量
CTE方式
二、窗口函数
在将窗口函数前，先来对比一下聚合函数（min() /max() /avg()/ sum()等） 和 窗口函数 ：
（1）聚合函数返回的各个分组的结果，窗口函数则为每一行返回结果
窗口函数计算一些行集合的数据，与常规的聚集函数不同的是，窗口函数不会将参与计算的行合并成一行输出，而是保留它们原来的样子。
（2）聚合函数都支持窗口函数运算。
（3）聚合函数对一组值执行计算，并返回单个值。 除了 COUNT 以外，聚合函数都会忽略空值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。
（4）窗口函数是对一组值进行操作，不需要使用GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行和聚合列。
实例讲解 ：
假设现在要得到某APP所有版本crash率的平均值，按照通常的做法，一定是通过聚合函数AVG来求Crash率的平均值，输出结果中包含该APP 对应的版本、crash率及平均crash率。
（1）方法 1 （初学者经常犯的错误）
出错原因 ：Select 指定字段要么包含在聚合函数中，那么包含在Group By语句后面（Group by 对Select指定字段做了限制）。
（2）方法2
使用方法1 碰壁后 ，立马改写成如下方式 ，一看查询结果立马尴尬了，都按 version 和crash_rate Group by 了 ，还求啥avg
上述 两种方法均未达到我们的目的，上一节介绍了公用表达式（CTE）,那我们就先用这种方式来实现 ：
（3）CTE方式实现
CTE 方式实现
那有没有更加简单的方式了 ，咱也不卖关子了 ，竟然是将窗口函数，那肯定可用窗口函数来实现了
（4）窗口函数实现
窗口函数实现
窗口函数的作用是不是很强大 ，使用也相当简单 。
窗口函数使用OVER函数实现，OVER函数分带参和不带参两种。
OVER 函数定义：
OVER([PARTITION BY value_expression,..[n] ],<ORDER BY BY_Clause> )
其中可选参数PARTITION BY用于将数据按照特定字段分组 ; ORDER BY 用于排序。
三、SQL NULL 函数
不同的DB对应的SQL NULL 函数均有所不同，目前SQL NULL函数 主要有以下几种：
（1）ISNULL() —— SQL Server/MS Access
（2）NVL() —— Oracle
（3）IFNULL() —— MySQL
（4）COALESCE() —— MySQL / PostgreSQL
用法很简单，以COALESCE()为例，COALESCE(data,defaultValue) ，如果data为NULL，那么COALESCE(data,defaultValue) 将返回 defaultValue ，反之 ，返回data。
为什么数据库中很在意对NULL的处理，这牵扯到SQL的三值逻辑 ，True（真） 、False（假）、 UNKNOWN（不知道，不确定），在数据库中的NULL 相当于UNKNOWN，若对NULL值处理不当会造成意想不到的后果。
实例：新版app在原有上报基础上，新增了某些上报字段（没有设置默认值，深坑）， 后期需要对这个数据进行统计分析 ，如 某版本得分 = sum( 根据各项指标得分 * 权重)。
结果发现 低版本的得分为NULL ，原因很简单 某些字段只在高版本中才上报，而 NULL+ 数字 = NULL 。
正确做法：（1）Create Table 时，设置字段默认值 ；
（2）没有默认值，则可利用上述的SQL NULL 函数来实现。
三、SQL 条件判断(简要列举)
（1） case when 及if
（2） insert into .... where not exists () 及insert overwrite
某些数据库不支持replace ，如 PostgreSQL
引用：
（1）http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html
（2）http://www.w3school.com.cn/sql/sql_isnull.asp
（3）https://segmentfault.com/a/1190000008332604?utm_source=tuicool&utm_medium=referral
（4）http://www.cnblogs.com/jeffwongishandsome/archive/2010/12/04/1896672.html
网友评论