# 数据统计分析 保存数据的目的是为了对数据进行统计和分析，统计的作用是对过去工作进行总结，分析的作用是通过总结过去来谋划未来 ## 1. 聚合函数 聚合函数对一组数值进行计算并返回单一的值，它也被称为聚集函数或者统计函数。聚合函数能够对整个数据集进行计算，并返回一行原始数据集合汇聚总结果的记录。 包括SUM(),AVG(),COUNT(),MAX(),和MIN()函数，作用是在结果集中生成汇总值 |聚合函数|支持的数据类型|功能| |:------|:------------|:----| |SUM()|数字|非空值求和| |AVG()|数字|非空值求平均值| |MIN()|数字，字符，日期|返回最小数字，最小字符串，最早日期| |MAX()|数字，字符，日期|返回最大数字，做大字符串，最近日期| |COUNT([distinct]*)|任意基于行的数据类型|统计全部记录行的数量，最多2147483647行| 应用举例 ```sql #求和 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值的数目，必须为数值类型 ```sql AVG([DISTINCT] expression) ``` - DISTINCT :对非重复值进行运算 - expression : 表达式，必须为数值型 ```sql #查询商品平均值 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()函数统计的行 ```sql #计算价格大于3000元的平均价格 SELECT AVG(DISTINCT current_price) AS 平均值 FROM goods WHERE current_price >2000 ; ``` ```sql #显示商品价格大约平均值的信息 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)是对表达式的返回值进行处理，它返回的值可描述为传递到此函数列中非零值的计数。除了对行数计数之外，还可以对数据表中一列的数据值进行计数。 ```sql #查询价格大于3000元的商品个数 SELECT COUNT(*) AS 商品个数 FROM goods WHERE (current_price >3000); #使用表达式计算 #查询价格大于3000元的商品个数 SELECT COUNT(id) AS 商品个数 FROM goods WHERE (current_price >3000); ``` COUNT()函数统计一个表达式返回的所有值，无论是否重复，不统计所有空值 ```sql #忽略重复统计 SELECT COUNT(DISTINCT subcat_id ) AS 商品种类 FROM goods; ``` 注意COUNT(*)函数不能使用DISTINCT关键字，因为它统计的是一个表中所有行的数目，而不需要考虑这些行是否重复或者包含NULL值 ## 3. 最大值与最小值 在确定列中最大值，MAX()忽略NULL值，但是该列中所有行都NULL值，那么对使用MAX()将返回NULL值 ```sql #计算最高价和最低价 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()返回一个空值 ```sql #计算所有商品总和 SELECT SUM(current_price) AS 价格总和 FROM goods ``` ```sql #计算盈利总和 SELECT SUM(original_price - current_price) AS `盈利总和` FROM goods ``` ## 5 . WHERE中使用聚合函数 每一个聚合函数都返回单个值，此时可以用WHERE语句进行条件查询，但是由于聚合函数不能与字段进行比较操作，所以聚合函数只能放在子查询中。将聚合行数作为WHERE语句的一部分，可以将一列中的值与单个统计值进行比较 ```sql # 查询指定时间范围内总价高于平均价格的记录 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 在查询的结果集生成多个分类汇总 ```sql #分组分类 SELECT id,`name`,COUNT(*) AS 数量 FROM goods GROUP BY id ``` 实质上是根据数据表中的列进行分类操作，结合使用聚合函数统计此列的每一类的数据 ```sql #每种分类下的商品相关信息统计 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. 创建多列分组 ```sql #多列分组下的商品相关信息统计 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. 对表达式进行分组统计 ```sql #对表达式进行分组统计 SELECT 名称编号，添加日期 FROM (SELECT '名称:' +`name` +'编号为' + id AS 名称编号, '添加日期为'+ addtime AS 添加日期 FROM goods) GROUP BY 名称编号，添加日期 ``` ## 2. HAVING 进行过滤分组 HAVING删除不能满足搜素条件的一组数据，WHERE不能用于限制聚合函数，HAVING可以来限制聚合函数 ```sql #分组统计原价大于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返回行的顺序 ```sql 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|对输出的数据排序|否|