美文网首页
04 - 聚合与排序

04 - 聚合与排序

作者: 丑丑的小怪物 | 来源:发表于2019-08-15 19:25 被阅读0次

聚合函数

  • 聚合函数
  1. 通过 SQL 对数据进行某种操作或计算时需要使用函数。
  2. 用于汇总的函数称为聚合函数或者聚集函数
  3. 聚合,就是将多行汇总为一行
  • 5 个常用的函数
  1. COUNT:计算表中的记录数(行数)
  2. SUM:计算表中数值列中数据的合计值
  3. AVG:计算表中数值列中数据的平均值
  4. MAX:求出表中任意列中数据的最大值
  5. MIN:求出表中任意列中数据的最小值

COUNT 函数

COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数,而 COUNT(<列名>) 会得到NULL之外的数据行数。

-- 计算 purchase_price 不为 NULL 的数据条数
SELECT COUNT(purchase_price)
  FROM Product;

-- 计算总的数据条数
SELECT COUNT(*)
  FROM Product;

SUM 函数

聚合函数会将NULL排除在外。但COUNT(*)例外,并不会排除NULL

-- 计算合计值
SELECT SUM(sale_price), SUM(purchase_price)
  FROM Product;

AVG 函数

  1. 平均值的计算公式: ( 值的合计 )/( 值的个数 )
  2. 数据中含有 NULL 值得时候,会事先排除 NULL 再进行计算。
-- 计算平均值
SELECT AVG(sale_price)
  FROM Product;

SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;

MAX 函数 和 MIN 函数

MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。

-- 计算最大值和最小值
SELECT MAX(regist_date), MIN(regist_date)
  FROM Product;

SELECT MAX(sale_price), MIN(purchase_price)
  FROM Product;

使用聚合函数删除重复值(关键字 DISTINCT)

1. 在聚合函数的参数中使用DISTINCT,可以删除重复数据。
2. DISTINCT 必须写在括号中。这是因为必须要在计算行数之前删除 product_type 列中的重复数据。

-- 使用聚合函数去重
SELECT COUNT(DISTINCT product_type)
  FROM Product;

SELECT SUM(sale_price), SUM(DISTINCT sale_price)
  FROM Product;

对表进行分组

GROUP BY 子句

  • 语法
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;
  • 示例:
-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

1. 在 GROUP BY 子句中指定的列称为聚合键或者分组列。
2. GROUP BY 子句也和 SELECT 子句一样,可以通过逗号分隔指定多列。

  • 子句的书写顺序:

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
注:SQL 子句的顺序不能改变,也不能互相替换。

聚合键中包含 NULL 的情况

SELECT purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;

聚合键中包含NULL时,在结果中会以“不确定”行(空行)的形式表现出来。

同时使用 WHERE 子句和 GROUP BY 子句

  • 语法
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 WHERE
 GROUP BY <列名1>, <列名2>, <列名3>, ……;
  • 示例:
SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
  • GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序:

FROM → WHERE → GROUP BY → SELECT

与聚合函数和 GROUP BY 子句有关的常见错误

  • 在 SELECT 子句中书写了多余的列
-- 错误示例:
SELECT product_name, purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;
 
-- 正确:
SELECT product_name, purchase_price, COUNT(*)
  FROM Product
 GROUP BY product_name,purchase_price;

使用GROUP BY子句时,SELECT子句中不能出现聚合键之外的列名。


  • 在 GROUP BY 子句中写了列的别名

SQL 语句在 DBMS 内部的执行顺序造成了这个错误 —— SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时,SELECT 子句中定义的别名,DBMS 还并不知道。


  • 在 WHERE 子句中使用聚合函数
-- 错误示例:
SELECT product_type, COUNT(*)
  FROM Product
 WHERE COUNT(*) = 2
 GROUP BY product_type;
 
-- 正确示例:
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
 HAVING COUNT(*) = 2;

只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数。


为聚合结果指定条件

HAVING 子句

  • 语法:
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>
  • 使用 HAVING 子句时 SELECT 语句的顺序:

SELECT → FROM → WHERE → GROUP BY → HAVING

  • 示例:
-- 销售单价的平均值大于等于 2500 日元
SELECT product_type, AVG(sale_price)
  FROM Product
 GROUP BY product_type
HAVING AVG(sale_price) >= 2500;

HAVING 子句的构成要素

  • HAVING 子句中能够使用 3 种要素:
  1. 常数
  2. 聚合函数
  3. GROUP BY子句中指定的列名(即聚合键)

HAVING 子句 与 WHERE 子句

聚合键所对应的条件既可以写在 HAVING 子句当中,又可以写在 WHERE 子句当中.

  • 代码:

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING product_type = '衣服';

SELECT product_type, COUNT(*)
  FROM Product
WHERE product_type = '衣服'
 GROUP BY product_type;

以上两句 SQL 语句返回的结果是相同的.

  • HAVING 子句 与 WHERE 子句中条件的区别:
  1. WHERE 子句 = 指定行所对应的条件.
  2. HAVING 子句 = 指定组所对应的条件
  3. 聚合键所对应的条件不应该书写在HAVING子句当中,而应该书写在WHERE子句当中。

  • WHERE子句 和 HAVING子句的执行速度
  1. 通过 WHERE 子句指定条件时,由于排序之前就对数据进行了过滤,因此能够减少排序的数据量。但 HAVING 子句是在排序之后才对数据进行分组的,因此与在 WHERE 子句中指定条件比起来,需要排序的数据量就会多得多。虽然 DBMS 的内部处理不尽相同,但是对于排序处理来说,基本上都是一样的。
  2. WHERE 子句更具速度优势的另一个理由是,可以对 WHERE 子句指定条件所对应的列创建索引,这样也可以大幅提高处理速度。

对查询结果进行排序

ORDER BY 子句

  • 语法:
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……
 
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price;

ORDER BY子句通常写在SELECT语句的末尾。
DESC 降序
ASC 升序
未指定ORDER BY子句中排列顺序时会默认使用升序进行排列

  • 子句的书写顺序:
  1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 → 4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句
-- 按照销售单价由高到低(降序)进行排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC;

指定多个排序键

1. 如果想要对该顺序的商品进行更细致的排序的话,就需要再添加一个排序键。
2. 规则是优先使用左侧的键,如果该列存在相同值的话,再接着参考右侧的键。当然,也可以同时使用 3 个以上的排序键。

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price, product_id;

NULL 的顺序

排序键中包含NULL时,会在开头或末尾进行汇总。

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY purchase_price;

在排序键中使用显示用的别名

在 ORDER BY子句中可以使用SELECT子句中定义的别名。

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
  FROM Product
ORDER BY sp, id;

ORDER BY 子句中可以使用的列

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

SELECT product_name, sale_price, purchase_price
  FROM Product
ORDER BY product_id;

不要使用列编号

在 ORDER BY 子句中,可以使用在 SELECT 子句中出现的列所对应的编号

-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY sale_price DESC, product_id;

-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
ORDER BY 3 DESC, 1;

注意:
1.使用列编号,会导致代码阅读起来比较难
2.在 SQL-92{9[1992 年制定的 SQL 标准。]} 中已经明确指出该排序功能将来会被删除

相关文章

  • 04 - 聚合与排序

    聚合函数 聚合函数 通过 SQL 对数据进行某种操作或计算时需要使用函数。用于汇总的函数称为聚合函数或者聚集函数聚...

  • MICK-SQL基础教程(第二版)第三章 聚合与排序

    第三章 聚合与排序 对表进行聚合查询 聚合函数 所谓聚合,就是将多行汇总为一行。实际上,所有的聚合函数都是这样,输...

  • 排序与聚合函数

    排序 ORDER BY SELECT <*, column [alias], ...>FROM table [WH...

  • 开窗函数

    over在聚合函数中的使用:一般格式:聚合函数名(列) over(选项)over必须与聚合函数或排序函数一起使用...

  • DQL查询以及DCL(Data control Language

    排序 排序的时候使用order by 升序:asc 降序:desc 聚合函数 聚合函数作用count统计个数max...

  • 《SQL基础教程》学习笔记Ch3

    3聚合与排序 3-1对表进行聚合查询 聚合函数 COUNT:计算表中的记录数(行数)SUM:计算表中数值列中数据的...

  • 数据库查询语句

    条件与逻辑查询 模糊查询 范围查询 排序 聚合函数 分组 连接查询 分页 子查询

  • 【SQL.基础构建-第三节(3/4)】

    -- Tips:聚合和排序 -- 一、对表进行聚合查询 --1.聚合函数 --(1)5 个常用函数: --①CO...

  • 集合

    过滤、排序 聚合 分组 List转Map

  • 第三章 聚合与排序

    -- 第三章:聚合与排序-- 3-1SELECT COUNT()FROM Product;-- 3-1SELECT...

网友评论

      本文标题:04 - 聚合与排序

      本文链接:https://www.haomeiwen.com/subject/nvstsctx.html