SQL的聚集函数及汇总表的数据

• 聚集函数
  • AVG()函数
    • 注意
  • COUNT()函数
    • 注意
  • MAX()函数
    • 注意
  • MIN()函数
    • 注意
  • SUM()函数
    • 注意
• 聚集不同值DISTINCT
  • 注意
• 组合聚集函数
  • 取别名

聚集函数

汇总数据而不用把它们实际检索出来。有时候需要对表中的数据进行汇总，而不是想得到数据本身，如果返回数据本身则对时间、带宽和处理资源的浪费

• 确定表中行数
• 获得表中行组的和
• 找出列表的最大值、最小值、平均值

MySQL给出了5个聚集函数，运行在组上，计算和返回单个值的函数

• AVG() 返回某列的平均值
• COUNT() 返回某列的行数
• MAX() 返回某列的最大值
• MIN() 返回某列的最小值
• SUM() 返回某列之和
• 标准偏差聚集函数(略)

AVG()函数

1.通过对表中行数计算并计算特定列值之和，求得该列的平均值

SELECT
	AVG( prod_price ) AS avg_price 
FROM
	products;

2.确定特定行或列的平均值

SELECT
	AVG( prod_price ) AS avg_price 
FROM
	products 
WHERE
	vend_id = 1003;

注意

• 只适用于单个列
  AVG（）只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，如果要获得多饿列的平均值，必须使用多个AVG函数
• 忽略NULL值
  AVG()函数忽略NULL值

COUNT()函数

COUNT()函数进行计数，可确定表中行的数目或符合特定条件的行的数目。

• COUNT(*)不会忽略NULL值，就算该行所有值都为NULL
• COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

注意

• COUNT()函数用*代替列名不忽略函数，但是如果指定列名则会忽略函数

SELECT
	COUNT(*) AS num_cust 
FROM
	customers;

不忽略NULL值，返回5

SELECT
	COUNT( cust_email ) AS num_cust 
FROM
	customers;

忽略NULL值，返回3

MAX()函数

返回列中最大值，必须指定列名

SELECT
	MAX( prod_price ) AS max_price 
FROM
	products;

注意

• 对非数值数据使用MAX()
  在用于文本数据是，如果数据按相应的列排序，则MAX()返回最后一行
• NULL值
  MAX()函数忽略值为NULL的行

MIN()函数

返回指定列的最小值，要求指定列名

SELECT
	MIN( prod_price ) AS min_price 
FROM
	products;

注意

• 对非数值数据使用MIN()
  在用于文本数据是，如果数据按相应的列排序，则MAX()返回第一行
• NULL值
  MIN()函数忽略值为NULL的行

SUM()函数

返回指定列值的和。
1.

SELECT
	SUM( quantity ) AS items_ordered 
FROM
	orderitems 
WHERE
	order_num = 20005;

检索订购物品的总数

2.合计计算值

SELECT
	SUM( item_price * quantity ) AS total_price 
FROM
	orderitems 
WHERE
	order_num = 20005;

返回订单价值的总和

注意

• 所有的聚集函数都可以在多个列上计算
• NULL值的行被SUM()函数忽略

聚集不同值DISTINCT

MySQL5之后的版本新增点

• 所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）
• 只包含不同的值，指定DISTINCT参数

SELECT
	AVG( DISTINCT prod_price ) AS avg_price 
FROM
	products 
WHERE
	vend_id = 1003;

此时的平均值要比不去重前高，是因为有多个价格低的产品被去重了

注意

• 指定列名，DISTINCT必须使用列名，不能用于计算或表达式
• MIN()和MAX()，虽然可以在其函数中使用DISTINCT，但是没有意义，因为如果存在多个最小值、最大值一样即使去重也没有任何影响，返回的结果都是相通的

组合聚集函数

SELECT语句可以包含多个聚集函数

SELECT
	COUNT(*) AS num_items,
	MIN( prod_price ) AS peice_min,
	MAX( prod_price ) AS price_max,
	AVG( prod_price ) AS price_avg 
FROM
	products;

取别名

包含多个聚集函数结果时，使用唯一的别名字段会使得SQL更易于理解和使用