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

读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阶的对象

原文链接:https://www.cnblogs.com/lying7/p/17338506.html

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:读SQL进阶教程笔记13_SQL中的分组和层级 - Python技术站

(0)
上一篇 2023年4月22日
下一篇 2023年4月22日

相关文章

  • Mysql version can not be less than 4.1 出错解决办法

    题目指的是,在使用MySQL数据库的项目中,当MySQL版本低于4.1时,会出现“Mysql version can not be less than 4.1”的错误提示。下面是解决这个问题的详细攻略: 1. 检查MySQL版本 首先需要确保MySQL的版本高于4.1。具体的检查方法为: 打开终端或命令行窗口,输入mysql -V(注意是大写的V),然后回车…

    MySQL 2023年5月18日
    00
  • 一步步教你MySQL查询优化分析教程

    一步步教你MySQL查询优化分析教程 MySQL查询优化是提高数据库性能和响应时间的关键。本教程将一步步教你MySQL查询优化的过程。 步骤一:执行慢查询日志 为了知道哪些查询需要优化,我们需要先找出哪些查询运行缓慢。在MySQL中有一个非常有用的工具叫做慢查询日志。它会记录所有执行时间超过一定时间阈值的查询。可以通过以下步骤来启用慢查询日志: 打开my.c…

    MySQL 2023年5月19日
    00
  • MySQL中的日期时间类型与格式化方式

    MySQL中的日期时间类型和格式化方式是MySQL数据库中非常重要的一部分,它与数据库中的数据存储有关系,并且在进行查询运算、筛选和展示数据时,也有着非常重要的作用。在本篇文章中,我们将详细介绍MySQL中日期时间的类型和格式化方式,以及使用场景和示例说明。 日期时间类型 MySQL中日期和时间类型包括时间戳(TIMESTAMP)、日期(DATE)、时间(T…

    MySQL 2023年5月19日
    00
  • PHP数据库基于PDO操作类(mysql)

    这是网上找的关于Mysql的操作类,非常适合初学者使用 <?php class Mysql { protected static $_dbh = null; //静态属性,所有数据库实例共用,避免重复连接数据库 protected $_dbType = ‘mysql’; protected $_pconnect = true; //是否使用长连接 pr…

    MySQL 2023年4月13日
    00
  • 详解MySQL AS:设置别名

    MySQL AS是用于给SQL查询结果列、表和子查询设置别名的关键字。AS不是必需的,但它使得查询结果更易于阅读和理解。 AS用法示例: 列别名 在SELECT语句中,使用AS关键字为查询结果列设置别名。例如: SELECT first_name AS given_name, last_name AS family_name FROM customers; …

    MySQL 2023年3月9日
    00
  • Navicat连接mysql报错1251错误的解决方法

    下面是关于“Navicat连接mysql报错1251错误的解决方法”的完整攻略。 问题描述 当使用Navicat连接到mysql数据库时,可能会遇到错误代码为1251的错误提示,如下所示: 1251 – Client does not support authentication protocol requested by server; consider …

    MySQL 2023年5月18日
    00
  • 使用JDBC连接Mysql 8.0.11出现了各种错误的解决

    使用 JDBC 连接 MySQL 是 Web 开发中比较常见的一种方式,但确实在连接时会遇到各种问题,接下来整理出了一些常见问题及其解决方法。 问题1:链接失败,报错信息为“java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/databaseName”…

    MySQL 2023年5月18日
    00
  • mysql中TINYINT取值范围是多少

    这篇文章主要介绍了mysql中TINYINT取值范围是多少的相关知识,内容详细易懂,操作简单快捷,具有一定借鉴价值,相信大家阅读完这篇mysql中TINYINT取值范围是多少文章都会有所收获,下面我们一起来看看吧。 项目场景: 最近在开发过程中,看见表设计中是thinyint字段,但对于它的范围产生了好奇 问题描述: 当我们填写超过128数值的时候,该字段就…

    2023年4月9日
    00
合作推广
合作推广
分享本页
返回顶部