导语

本文讲解的知识点主要有：公用表表达式（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方式实现

那有没有更加简单的方式了 ？

咱也不卖关子了 ，既然是讲窗口函数，那肯定可用窗口函数来实现了。

（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。