读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日

相关文章

  • 如何解决安装MySQL5.0后出现1607异常

    如何解决安装MySQL5.0后出现1607异常 问题描述 在安装Mysql5.0的过程中,可能会遇到1607异常,异常如下所示: Error 1607: Unable to install InstallShield Scripting Runtime 问题原因 该问题的主要原因是InstallShield脚本运行时没有正确安装或已损坏。 解决方案 方案一:…

    MySQL 2023年5月18日
    00
  • mysql优化配置参数

    下面我将为您详细讲解 MySQL 优化配置参数的完整攻略。 1. 确认 MySQL 版本 MySQL 是一个活跃的开源项目,为了保证提示,您需要了解您所使用 MySQL 的版本。通过以下命令可以查询: mysql -V 2. 修改 MySQL 配置文件 MySQL 的配置文件默认存放路径在 /etc/my.cnf 或 /etc/mysql/my.cnf,如果…

    MySQL 2023年5月18日
    00
  • MySql子查询IN的执行和优化的实现

    MySql子查询IN的执行和优化的实现是一个比较复杂的话题。在这里,我们将介绍子查询IN的基本概念,以及如何优化和调整查询语句,以获取更好的查询性能。 子查询IN的基本概念 子查询IN的基本概念是将一个查询语句嵌入到另一个查询语句中,通过比较两个结果集中的值,得到结果。例如: SELECT * FROM tablename WHERE id IN (SELE…

    MySQL 2023年5月19日
    00
  • mysql启动的error 2003和1067错误问题解决方法

    MySQL是一款开源的高性能关系型数据库管理系统,在使用过程中,可能会遇到一些错误和问题,最常见的就是启动时出现error 2003和1067错误。对此,我们需要进行一些调整和修复。以下是解决这两个错误的完整攻略。 MySQL启动错误2003 在启动MySQL过程中,有时候会出现“ERROR 2003 (HY000): Can’t connect to My…

    MySQL 2023年5月18日
    00
  • MySQL四种日志binlog/redolog/relaylog/undolog

    优质博文:IT-BLOG-CN 一、binlog binlog记录数据库表结构和表数据变更,比如update/delete/insert/truncate/create,它不会记录select。存储着每条变更的SQL语句和XID事务Id等等。binlog日志文件如下: [root@192.168.10.11]# mysqlbinlog mysql-binlo…

    MySQL 2023年4月17日
    00
  • MySQL 有关MHA搭建与切换的几个错误log汇总

    MySQL 有关 MHA 搭建与切换的几个错误log汇总 介绍 MHA(Master High Availability)是基于 Master-Slave 复制技术的 MySQL 高可用方案,可以实现自动故障切换和在线修改主从配置等功能。在使用 MHA 过程中,可能会遇到一些错误和问题,下面整理了几个常见的错误 log。 MHA 搭建相关 问题:mha-pr…

    MySQL 2023年5月18日
    00
  • MySQL数据库是什么

    MySQL数据库是一种开源、关系数据库管理系统,是目前互联网上最流行、最常用的数据库之一。它是由瑞典MySQL AB公司开发,并由Oracle公司管理和支持。MySQL的发展历程非常长,自1995年诞生以来,已经经历了多次重大升级和改进,包括版本升级、功能增强等,使其成为一个高效、可靠、功能强大、使用方便的数据库管理系统。 MySQL数据库的特点主要有: 开…

    2023年3月8日
    00
  • 数据库为什么需要备份?

    数据库是企业中非常重要的资产之一,包含着大量的重要数据,以及业务逻辑与关键性能参数。因此,数据库备份是维持企业生产力和业务持续运行的关键步骤之一。备份是指将原有数据库数据复制到另一个地方,旨在在原始数据库系统遇到故障或其他问题时,能够快速恢复数据。本文将详解为什么数据库需要备份。 防止数据丢失 在企业中,数据是极其重要的业务资产。数据丢失可能导致企业的生产中…

    MySQL 2023年3月10日
    00
合作推广
合作推广
分享本页
返回顶部