SQL进阶-5-感受having的力量
HAVING
是SQL
中一个非常重要的功能,本文中将会介绍SQL
中HAVING
子句的用法。
SQL语句执行顺序
- select
- from
- where
- group by
- having
- order by(desc是降序)
从上面的顺序中可以看出来,HAVING子句是在group by 分组之后再执行的
案例1-寻找缺失的编号
需求
下面的数据中缺少4和7
判断是否缺失
1 | select '存在缺失的编号' as gap |
查询结果中出现了一行,说明存在缺失值;若一行都没有,则统计的总行数刚好是最大的seq值
从上面的语句中可以看出来,having子句是可以单出使用的,注意两点:
select
子句中不能使用原有表的列了- 使用常量(示例)或者使用聚合函数
解决-缺失的最小编号
先通过上面的语句确定真的是存在缺失值,再通过👇的语句来锁定缺失值的最小编号
1 | select min(seq+1) as gap |
如何理解上面👆的SQL语句?
seq值+1不在表中
编号seq | 编号seq+1 | 是否在数据中 |
---|---|---|
1 | 2 | 是 |
2 | 3 | 是 |
3 | 4 | 否 |
4 | 5 | 是 |
5 | 6 | 是 |
6 | 7 | 否 |
7 | 8 | 是 |
所以缺失值的最小编号是4(3+1)
案例2-求众数
简单地求平均值的缺点就是,数据很容易受到离群值(outlier)的影响,此时使用反映群体趋势的指标-众数mode更加合理。
众数指的是在数据中出现次数最多的数
解法1-使用all
1 | -- 求众数的SQL语句:使用谓词 |
解法2-max函数
使用ALL谓词在数据中存在空集或者NULL的时候会出现问题,使用极值函数代替
1 | select income,count(*) as number |
案例3-求中位数(having自连接)
中位数:指的是将集合中的元素按照升序排列后恰好位于正中间的数据
做法
将集合按照大小排列后,让两个子集同时拥有正中间的元素,共同部分的元素的平均值就是中位数
1 | -- 求解中位数:having 子句中使用非等值连接 |
1 | -- 求解中位数 |
关于count
count(*)
:可以查询NULL
的情况,查询的是所有行的数目
count(列名)
:和其他的聚合函数相同,需要先排除NULL
的情况
需求
找出哪些学院的学生全部提交了???
答案:理学院和经济学院,文学院和工学院有NULL的情况
解决
- 如果两个
count
方式的值相等,说明该列没有缺失值
1 | select dpt |
- 使用case解决:case表达式相当于是进行判断的函数,用来判断各个元素是否满足了某个条件的集合
1 | select dpt |
购物篮分析
需求1-带余除法
在下面的两个表中,插叙包含所有items中所有商品的店铺:item中的商品在店铺中全部存在,结果是$\color{red}{仙台和东京}$
表Items和表shopitems
解决-使用having语句
1 | select SI.shop |
- 先连接:两个表中的item必须相同,也是就说ShopItems中某个店铺的item必须包含Items表中的3个名字
- 对应数据:仙台4个名字,其中3个已经包含了items表中的数据;东京刚好3个都是Items中的数据
需求2-精确关系除法
在上面的例子中,仙台有4个item也被选中了,如何将其排除,只选择东京,刚好3个item的店铺呢???这种问题称之为精确关系除法
1 | -- 精确关系除法:使用外连接和count函数 |
两个表外联结的结果为:
因为是left连接,所以ShopItems中的数据全部保留,Item中的数据没有则为NULL