背景
最近在做数据分析相关的工作,需要对大量数据进行清洗,过程中,发现自己的很多想法无法通过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
A:(SELECT * FROM TableA) EXCEPT (SELECT * FROM TableB)
结果: 1
(1 row(s) affected)
B: SELECT * FROM TableA INTERSECT SELECT * FROM TableB
结果:2
3
4
5
(4 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;