最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

读SQL进阶教程笔记13_SQL中的分组和层级

来源：博客园


(资料图片仅供参考)

1.数据分组

1.1.SQL的语句中具有分组功能的是GROUP BY和PARTITION BY

1.1.1.两者都有数学的理论基础

1.1.2.都可以根据指定的列为表分组

1.1.3.区别仅仅在于，GROUP BY在分组之后会把每个分组聚合成一行数据

1.1.4.GROUP BY的作用是将一个个元素划分成若干个子集

1.2.示例

1.2.1.

SELECT member, team, age , RANK() OVER(PARTITION BY team ORDER BY age DESC) rn, DENSE_RANK() OVER(PARTITION BY team ORDER BY age DESC) dense_rn, ROW_NUMBER() OVER(PARTITION BY team ORDER BY age DESC) row_num FROM Members ORDER BY team, rn;

1.3.分割后的子集

1.3.1.它们全都是非空集合

1.3.1.1.还有一种只包含NULL的集合

1.3.2.所有子集的并集等于划分之前的集合

1.3.3.任何两个子集之间都没有交集

1.3.4.满足以上3个性质的各子集称为“类”（partition）

1.4.类的概念（即partition）

1.4.1.群论中有很多非常有趣的类，比如“剩余类”

1.4.1.1.通过对3取余给自然数集合N分类后

1.4.1.1.1.“模3剩余类”

1.4.1.2.模在SQL中也有实现，就是取模函数MOD

1.4.1.2.1.--对从1到10的整数以3为模求剩余类
SELECT MOD(num, 3) AS modulo,          num      FROM Natural     ORDER BY modulo, num;

1.4.1.3.--从原来的表中抽出（大约）五分之一行的数据

SELECT ＊ FROM SomeTbl WHERE MOD(seq, 5) = 0; --表中没有连续编号的列时，使用ROW_NUMBER函数就可以了 SELECT ＊ FROM (SELECT col, ROW_NUMBER() OVER(ORDER BY col) AS seq FROM SomeTbl) WHERE MOD(seq, 5) = 0;

1.4.2.正因为抽象，才有了广泛的应用

1.4.2.1.数学理论并不是脱离实际的游戏，它其实隐藏了大量能够用于日常工作的技巧

2.层级

2.1.SQL的世界其实是层级分明的等级社会

2.2.使用GROUP BY聚合之后，我们就不能引用原表中除聚合键之外的列

2.2.1.这只是SQL中的一种逻辑，是为了严格区分层级

2.3.GROUP BY中的阶与元素和集合的区别有关，因此属于集合论中的阶

2.3.1.对于EXISTS来说，层级的差别与EXISTS谓词及其参数有关，因此属于谓词逻辑中的阶

2.3.2.使用GROUP BY聚合之后，SQL的操作对象便由0阶的“行”变为了1阶的“行的集合”

2.4.标准SQL规定

2.4.1.在对表进行聚合查询的时候，只能在SELECT子句中写下面3种内容

2.4.2.通过GROUP BY子句指定的聚合键

2.4.3.聚合函数（SUM、AVG等）

2.4.4.常量

2.5.示例

2.5.1.--以组为单位进行聚合查询

SELECT team, AVG(age) FROM Teams GROUP BY team;

2.5.1.1.年龄只是每个人的属性，而不是小组的属性

2.5.1.2.小组指的是由多个人组成的集合

2.5.1.2.1.小组的属性只能是平均或者总和等统计性质的属性

2.5.2.--以组为单位进行聚合查询？

SELECT team, AVG(age), age FROM Teams GROUP BY team;

2.5.2.1.违反了标准SQL的规定，因此不具有可移植性

2.5.2.2.MySQL数据库支持这样的查询语句

2.5.2.3.强行将适用于个体的属性套用于团体之上，纯粹是一种分类错误

2.5.3.--错误

SELECT team, AVG(age), member FROM Teams GROUP BY team;

2.5.3.1.--正确

SELECT team, AVG(age), MAX(member) FROM Teams GROUP BY team;

2.5.4.--小组中年龄最大的成员

SELECT team, MAX(age), (SELECT MAX(member) FROM Teams T2 WHERE T2.team = T1.team AND T2.age = MAX(T1.age)) AS oldest FROM Teams T1 GROUP BY team;

2.5.4.1.子查询中的WHERE子句里使用了MAX(T1.age)这样的聚合函数作为条件

2.5.4.2.这里对外层的表T1也进行了聚合，这样一来我们就可以在SELECT子句中通过聚合函数来引用“age”列了

2.5.4.3.不能反过来在子查询中直接引用“age”列

2.6.单元素集合也是集合

2.6.1.单元素集合和空集一样，主要是为了保持理论的完整性而定义的

2.6.2.元素a和集合{a}之间存在着非常醒目的层级差别

2.6.2.1.这两个层级的区别分别对应着SQL中的WHERE子句和HAVING子句的区别

2.6.2.2.WHERE子句用于处理“行”这种0阶的对象

2.6.2.3.HAVING子句用来处理“集合”这种1阶的对象

关键词：