数据统计分析

保存数据的目的是为了对数据进行统计和分析，统计的作用是对过去工作进行总结，分析的作用是通过总结过去来谋划未来

1. 聚合函数

聚合函数对一组数值进行计算并返回单一的值，它也被称为聚集函数或者统计函数。聚合函数能够对整个数据集进行计算，并返回一行原始数据集合汇聚总结果的记录。包括SUM(),AVG(),COUNT(),MAX(),和MIN()函数，作用是在结果集中生成汇总值

聚合函数	支持的数据类型	功能
SUM()	数字	非空值求和
AVG()	数字	非空值求平均值
MIN()	数字，字符，日期	返回最小数字，最小字符串，最早日期
MAX()	数字，字符，日期	返回最大数字，做大字符串，最近日期
COUNT([distinct]*)	任意基于行的数据类型	统计全部记录行的数量，最多2147483647行

应用举例

#求和
SELECT SUM(成绩) FROM grade;

#求平均
SELECT AVG(年龄) FROM students;

#查最早
SELECT MIN(出生日期) FROM students;

#查最高
SELECT MAX(成绩) FROM studenst;

#求所有女生人数
SELECT COUNT(case when Type='女' then Type end) FROM students

#求整个表中所有的记录数
SELECT COUNT(*) FROM studens

1. 求平均值

AVG()函数是将一列中的值加起来求和，然后再处于非NULL值的数目，必须为数值类型

AVG([DISTINCT] expression)

DISTINCT :对非重复值进行运算
expression : 表达式，必须为数值型

#查询商品平均值
SELECT AVG(current_price) FROM goods;

# 查询平均值并去除小数点0
SELECT CAST(AVG(current_price) as real) AS 平均值 FROM goods；

#查询非重复的平均值，使用DISTINCT,如果该列存在空值，在计算平均值的时候忽略该行记录
SELECT AVG(DISTINCT current_price) FROM goods

2. 使用WHERE限制AVG()函数统计的行

#计算价格大于3000元的平均价格
SELECT AVG(DISTINCT current_price) AS 平均值 FROM goods
WHERE current_price >2000 ;

#显示商品价格大约平均值的信息
SELECT id AS 序号, `name` AS 名称,current_price AS 价格 FROM goods
WHERE (current_price >  (SELECT AVG(current_price) FROM goods));

2. 获取结果集行数

使用COUNT()函数可以获得行数，对于处理行的个数没有限制，可以返回满足SELECT语句的WHERE子语句条件的行数。

COUNT(*) 用于处理结果集的行

COUNT(EXPR)是对表达式的返回值进行处理，它返回的值可描述为传递到此函数列中非零值的计数。除了对行数计数之外，还可以对数据表中一列的数据值进行计数。

#查询价格大于3000元的商品个数
SELECT COUNT(*) AS 商品个数 FROM goods
WHERE (current_price >3000);

#使用表达式计算
#查询价格大于3000元的商品个数
SELECT COUNT(id) AS 商品个数 FROM goods
WHERE (current_price >3000);

COUNT()函数统计一个表达式返回的所有值，无论是否重复，不统计所有空值

#忽略重复统计
SELECT COUNT(DISTINCT subcat_id ) AS 商品种类 FROM goods;

注意COUNT(*)函数不能使用DISTINCT关键字，因为它统计的是一个表中所有行的数目，而不需要考虑这些行是否重复或者包含NULL值

3. 最大值与最小值

在确定列中最大值，MAX()忽略NULL值，但是该列中所有行都NULL值，那么对使用MAX()将返回NULL值

#计算最高价和最低价
SELECT MIN(current_price) AS 最低价 FROM goods WHERE introduction LIKE '%冰箱%';
SELECT MAX(current_price) AS 最高价 FROM goods WHERE introduction LIKE '%冰箱%';

#去掉最高价和最低价求平均值
SELECT
	AVG(current_price) AS 去掉最大值与最小值的平均值
FROM
	goods
WHERE
	introduction LIKE '%证书编号%'
AND current_price NOT IN (
	SELECT
		MIN(current_price)
	FROM
		goods
	WHERE
		introduction LIKE '%证书编号%'
)
UNION
	(
		SELECT
			MAX(current_price)
		FROM
			goods
		WHERE
			introduction LIKE '%证书编号%'
	);

为了可以在WHRER语句中使用两个以上的查询，可以使用UNION语句。它实现合并查询，要查询范围变大。

4. 对多列进行求和

SUM()进行求和，如果所有的行的表达式的值都为NULL或者FROM语句和WHRER语句共同返回一个空的结果，那么SUM()返回一个空值

#计算所有商品总和
SELECT SUM(current_price) AS 价格总和  FROM goods

#计算盈利总和
SELECT SUM(original_price - current_price) AS `盈利总和` FROM goods

5 . WHERE中使用聚合函数

每一个聚合函数都返回单个值，此时可以用WHERE语句进行条件查询，但是由于聚合函数不能与字段进行比较操作，所以聚合函数只能放在子查询中。将聚合行数作为WHERE语句的一部分，可以将一列中的值与单个统计值进行比较

# 查询指定时间范围内总价高于平均价格的记录
SELECT
	id,
	`name`,
	introduction,
	addtime,
	current_price
FROM
	goods
WHERE
	(
		addtime BETWEEN '2016-07-20 11:15:23'
		AND '2016-07-20 11:45:23'
	)
AND (current_price > (
	SELECT
		AVG(current_price)
	FROM
		goods
));

6. 多个聚合函数的使用

1. 使用多个聚合函数的注意事项

需要注意以下两点

多个聚合函数在 SQL server中不能嵌套
子查询不能作为一个聚合函数表达式

2. 聚合函数执行步骤

执行一条SELECT语句来显示一个或者多个聚合函数

首先生成一个中间表
如果SELECT存在一个WHERE语句，就对中间表的每一行根据其搜索的条件进行求值，清除那些求值结果为FALSE或者NULL的行，保留求值结果为TRUE的行
使用中间表的值来计算每个函数的值
将每个聚合函数统计的值作为结果表中的列值显示

注意，虽然使用聚合函数为查询带来了便利，但是使用多个聚合函数可能会要系统查询的效率明显降低，特别是在对容量大的数据表进行该操作时更为明显。在聚合函数中，除了COUNT(*) 函数之外，其他聚合函数都不对空值进行处理，同时COUNT(字段名称)函数也不统计null值的个数

分组统计

使用GROUP BY进行分组统计，以便汇总数据表的内容

1. 创建分组

1. GROUP BY

GROUP BY 在查询的结果集生成多个分类汇总

#分组分类
SELECT id,`name`,COUNT(*) AS 数量   FROM goods 
GROUP BY id

实质上是根据数据表中的列进行分类操作，结合使用聚合函数统计此列的每一类的数据

#每种分类下的商品相关信息统计
SELECT
	id AS 种类,
	MIN(current_price) AS 最低售价,
	MAX(original_price) AS 最高成本价,
	AVG(current_price) AS 平均售价,
	COUNT(*) AS 数量
FROM
	goods
GROUP BY
	id
ORDER BY
	MAX(current_price) DESC;

2. 创建多列分组

#多列分组下的商品相关信息统计
SELECT
	subcat_id AS 种类1,
  supercat_id AS 种类2,
	MIN(current_price) AS 最低售价,
	MAX(original_price) AS 最高成本价,
	AVG(current_price) AS 平均售价,
	COUNT(*) AS 数量
FROM
	goods
GROUP BY
	subcat_id,supercat_id
ORDER BY
	MAX(current_price) DESC;

sql并不会在同一结果表中同时给出两种分类汇总

3. 对表达式进行分组统计

#对表达式进行分组统计
SELECT 名称编号，添加日期  FROM 
(SELECT '名称:' +`name` +'编号为' + id  AS 名称编号,
'添加日期为'+ addtime AS 添加日期 FROM goods)
GROUP BY 名称编号，添加日期

2. HAVING 进行过滤分组

HAVING删除不能满足搜素条件的一组数据，WHERE不能用于限制聚合函数，HAVING可以来限制聚合函数

#分组统计原价大于1000而且售价小于所有平均售价的不同种类的数量

SELECT  id 编号,original_price 原价,COUNT(id) 数量 FROM goods
WHERE (original_price > 1000)
GROUP BY id,original_price
HAVING (original_price < (SELECT AVG(original_price) FROM goods))
ORDER BY original_price DESC;

以上语句执行流程

首先检查中间表并清除那些库存量不小于1000的数据行
数据库系统根据商品种类组合这些数据行
数据库使用HAVING搜索条件来检查每组的行
统计每个行组的id的数量，并在每组清除那些小于平均售价的行
最后查询结果集根绝original_price字段进行降序排列，然后将最终结果返回

WHRER的表达式必须单独进行计算，而在HAVING的搜索条件中的表达式通常是一组行进行计算，所有WHERE的搜索条件由使用列引用与实际值的表达式组成，而HAVING的搜索条件通常由一个或者多个聚合函数组成

通常带有HAVING的查询步骤如下：

根据FROM的数据表创建中间表，如果FROM语句只有一张表，那么中间表就是源数据表的副本
如果SELECT查询语句中含有WHERE语句，则根据搜索条件将不满足条件的行进行过滤
将中间表中的行根据GROUP BY 指定列排或者组
将HAVING中每个搜索条件应该用于查询每个组，如果其中某个组不满足一个或者多个搜索条件，则从中见表删除该组
统计SELECT语句中每一项并为每一项生成单一的行
如果查询也包括关键字DISTINCT，则从结果集中清除任何重复的行
如果在查询中存在ORDER BY语句，则根据列在ORDER BY子句中的列值对结果进行排序

HAVING语句类似与WHRER语句，表达式有三种类型，分别为NULL,TRUE和FALSE。如果HAVING语句对数据表中每一组数据求职的结果为TRUE或者NULL，数据库使用组中的行生成结果集的行，如果对一组数据求值结果为FALSE，则数据系统在结果集中不添加该组

WHERE和HAVING的区别如下：

WHERE 不能放在GROUP BY 后面，而HAVING可以
HAVING 是与GROUP BY 连在一起使用的，放在GROUP BY后面，此时的操作相当于WHERE子句
WHERE后面的条件中不能含有聚合函数，比如SUM(),AVG()等，而HAVING可以

总结，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

3. 对结果进行排序

可以使用ORDER BY 指定GROUP BY返回行的顺序

SELECT id 商品种类,COUNT(id),current_price  FROM goods
GROUP BY id
ORDER BY current_price DESC

4. SELECT 语句的顺序

下表列出了select语句使用的时候必须遵守的次序，以及子句是否需要使用

子句	说明	是否需要使用
SELECT	返回列表或者表达式	是
FROM	从中要检索的数据的表	是
WHERE	行级过滤	否
GROUP BY	分组查询	仅在按组统计计算聚集的时候使用
HAVING	组及过滤	否
ORDEER BY	对输出的数据排序	否

杨超 / mysql学习

数据统计分析

1. 聚合函数

1. 求平均值

2. 使用WHERE限制AVG()函数统计的行

2. 获取结果集行数

3. 最大值与最小值

4. 对多列进行求和

5 . WHERE中使用聚合函数

6. 多个聚合函数的使用

1. 使用多个聚合函数的注意事项

2. 聚合函数执行步骤

分组统计

1. 创建分组

1. GROUP BY

2. 创建多列分组

3. 对表达式进行分组统计

2. HAVING 进行过滤分组

3. 对结果进行排序

4. SELECT 语句的顺序

简介

发行版

贡献者

近期动态

杨超 / mysql学习 .gitee-modal { width: 500px !important; }

数据统计分析

1. 聚合函数

1. 求平均值

2. 使用WHERE限制AVG()函数统计的行

2. 获取结果集行数

3. 最大值与最小值

4. 对多列进行求和

5 . WHERE中使用聚合函数

6. 多个聚合函数的使用

1. 使用多个聚合函数的注意事项

2. 聚合函数执行步骤

分组统计

1. 创建分组

1. GROUP BY

2. 创建多列分组

3. 对表达式进行分组统计

2. HAVING 进行过滤分组

3. 对结果进行排序

4. SELECT 语句的顺序

简介

发行版

贡献者

近期动态

搜索帮助

杨超 / mysql学习