SQL进阶（一）-case when/having/自连接/时间窗口

背景

最近在做数据分析相关的工作，需要对大量数据进行清洗，过程中，发现自己的很多想法无法通过sql精准获取，非常别扭，所以找了一本《SQL进阶》来针对性的提升。
直接记录下，本书中，印象深刻的几种语法

Case when

case when 是一个非常实用的语法，常用场景：
1：你需要根据不同的返回值，重命名、做聚合计算
比如：有一个字段sex,1代表男，0代表女：
Case sex when ‘1’ then”男“ when “0” then “女” else ’其他‘ end
比如统计男士的总数：
sum(case when sex=1 then 1 else 0 end)
重点 ，需要注意写else/end,因为sql世界里，有一个非真非假的值，null
不写容易踩坑。
2：update set xxx case when xxx end
按不同的条件进行更新，一般可以用在一次性更新多个数据

自连接

主要作用：重组、排列、组合
语法：select from t1 a , t1 b
要点：
1：自连接经常和非等值连接结合
2：经常和group by 结合，可以生成递归集合
3：性能开销大，建议用于连接的列建立索引

三值逻辑和null

sql世界中，除了逻辑真、假外，还存在一种”不确定“，经常带来意想不到的情况
1：NULL不是值
2：因为NULL不是值，所以不能跟谓词做比较（结果是unkown）
经典案例：select xx from t where c1=NULL,执行失败
为什么会失败，因为对 NULL使用谓词(><=<>=等)的值是unknown,而不是真或者假。
建议使用 is null 来判断。
3：unkown 参与逻辑计算，sql运行会超出预期。尽量不要这么做

having

以集合为单位进行操作
经典案例
1：找出某个不满足条件的行
2：集合group by 求众数
select … from t1
group by xx
having count() >ALL(select count() from t1 group xx)
ALL()：必须大于括号里面的最大值
3:求中位数（类似 inner join的集合视图）
4：查询不包含NULL的值

总结：
1：SQL是面向集合的语言，要通过画集合图来思考
2：group by 用来生成子集
3：having用来调整集合的性质

时间窗口

https://zhuanlan.zhihu.com/p/92654574
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
那么语法中的<窗口函数>都有哪些呢？

<窗口函数>的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

集合操作

1：sql cross join:相当于整合成笛卡尔积集合
select * from TEST_A a CROSS JOIN TEST_B b;
或者
select * from TEST_A a ，TEST_B b;
2：EXCEPT 返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。

3:INTERSECT 返回 两个结果集的交集（即两个查询都返回的所有非重复值）。

4:union返回两个结果集的并集。
案例：
table A table B

Ａ：（SELECT * FROM TableA） EXCEPT （SELECT * FROM TableB）
结果：　１
(1 row(s) affected)
Ｂ：　SELECT * FROM TableA INTERSECT SELECT * FROM TableB
结果：２
　　　３
　　　４
　　　５
(４ row(s) affected)
参考：https://www.cnblogs.com/dyufei/archive/2009/11/11/2573976.html

注意：
1：如果集合运算符不指定ALL可选项，重复行会被排查掉，而且会产生排序，性能不好
2：union 和 intersect都具有幂等性，而except不具有
3：标准sql没有关系除的运算符，需要自己实现
常见手法：
1：嵌套使用 not exsit
2：使用having子句转换成1对1关系
3：把除法改为减法（去掉某些集合）
4：SQL处理数据有种方法
1：把数据看成忽略顺序的集合
2：把数据看成有序的集合，
1：首先使用自连接，生成起点和重点（例子：火车连座）
2：其次在子查询中描述内部每个元素必须满足的关系
5：在SQL中想要表达全量化时，需要转为双重否定，并使用 not exists；