《数据分组与汇总》PPT课件.ppt

第7章 数据分组与汇总,使用 TOP n 列出前 n 个记录 使用聚合函数 GROUP BY 的基础知识 在结果集中生成汇总值 使用 COMPUTE 和 COMPUTE BY 子句,使用 TOP n 列出前 n 个记录,关键字 TOP n 只列出结果集中前 n 个或 前 n% 的记录。使用 TOP n 或 TOP n PERCENT 时，应注意 在 ORDER BY 子句中指定值的范围 关键字 TOP 后使用无符号的整数 如果 TOP n PERCENT 生成小数，则 SQL Server 将把这个数取整 可以在结果集中用 WITH TIES 子句包含那些值相等的记录，这时结果集中可以包含任意数目的行,使用 TOP n 列出前 n 个记录（续）,统计order details表中每个订单的总销售数量，将结果集按数量降序排列，并返回前10行。,USE northwind SELECT TOP 10 orderid ,(unitprice * quantity) as totalsale From order details Order by (unitprice * quantity) DESC GO,示例1,使用 TOP n 列出前 n 个记录（续）,USE northwind SELECT TOP 10 WITH TIES orderid ,(unitprice * quantity) as totalsale From order details Order by (unitprice * quantity) DESC GO,示例2,第7章 数据分组与汇总,使用 TOP n 列出前 n 个记录 使用聚合函数 GROUP BY 的基础知识 在结果集中生成汇总值 使用 COMPUTE 和 COMPUTE BY 子句,使用聚合函数,常见的聚合函数及其描述,使用聚合函数（续）,对包含空值的字段使用聚合函数时，应注意 SQL Server 的聚合函数（COUNT(*) 除外）将忽略字段中的空值 COUNT（*）将计算所有的行，即使每个字段都含有空值,使用聚合函数（续）,查询titles表中类型是popular_comp的书的平均价格,查询publishers表中供应商的数量,查询publishers表中供应商所在州的数量,select avg(price) as 平均价格 from titles where type= popular_comp,select count(*) as 供应商数量 from publishers,select count(state) as 供应商所在州的数量 from publishers,使用聚合函数（续）,查询titles表中书的类型有几种,select count( type) from titles,select count( distinct type ) from titles,说明：允许count()、sum()、avg()和distinct一起处理列或表达式中不同的值,第7章 数据分组与汇总,使用 TOP n 列出前 n 个记录 使用聚合函数 GROUP BY 的基础知识 在结果集中生成汇总值 使用 COMPUTE 和 COMPUTE BY 子句,GROUP BY 的基础知识,使用 GROUP BY 子句 联合使用 GROUP BY 子句和 HAVING 子句,GROUP BY 的基础知识,简单分组 按书的种类分类，求出各类书籍的数量,Select type, count(title) as 数量 from titles Group by type,使用 GROUP BY 子句,联合使用聚合函数和 GROUP BY 子句，能够把表中的记录分组，并对组中数据进行汇总。 使用 GROUP BY 子句时，应注意 对于指定的一组，SQL Server 只生成一条记录，不返回详细信息 不要对可包含空值的字段使用 GROUP BY 子句，因为空值也将被当作一组,使用 GROUP BY 子句（续）,SELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid GO,使用 GROUP BY 子句（续）,USE northwind SELECT productid,SUM(quantity) AS total_quantity FROM orderhist WHERE productid 2 GROUP BY productid GO,使用 GROUP BY 子句（续）,USE pubs SELECT royalty, AVG(price * 2) AS AveragePrice FROM pubs.dbo.titles GROUP BY royalty GO,示例,对包含空值的字段使用 GROUP BY 子句，空值也将被当作一组,使用 GROUP BY 子句（续）,按书的种类分类，求出3种类型书籍business,mod_cook,trad_cook的价格总和、平均价格以及各类书籍的数量,select type,sum(price) as total_price ,avg(price) as avg_price ,count(title) as quantity from titles where type in(business,mod_cook,trad_cook) group by type,使用 GROUP BY 子句（续）,SELECT后面每一列,除了出现在统计函数中的列以外，都必须包含在在GROUP BY子句中。 GROUP BY子句不支持对列分配的假名，也不支持任何使用统计函数的集合列。,select pub_id, type,sum(price) as total_price ,avg(price) as avg_price ,count(title) as quantity from titles where type in(business,mod_cook,trad_cook) group by type,使用 GROUP BY 子句（续）,根据多列组合进行分组统计计算 按书的类型分类，查询每个出版商销售的书的平均价和总销售额,select pub_id, type,sum(price) as total_price ,avg(price) as avg_price ,count(title) as quantity from titles group by type,联合使用 GROUP BY 子句和 HAVING 子句,在分组的同时，对字段或表达式指定搜索条件 使用 HAVING 子句时，应注意 只在使用 GROUP BY 子句的同时，使用 HAVING 子句来限制分组 可以引用任何出现在选择列表中的字段,联合使用 GROUP BY 子句和 HAVING 子句（续）,SELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)=30 GO,列出表 orderhist 中销售量多于30的每种产品。,select type , avg(price) from titles group by type having avg(price)$15,select type , avg(price) from titles where avg(price)$15 group by type,联合使用 GROUP BY 子句和 HAVING 子句（续）,按书的类型分类，查询平均价大于15的书的类型、平均价,第7章 数据分组与汇总,使用 TOP n 列出前 n 个记录 使用聚合函数 GROUP BY 的基础知识 在结果集中生成汇总值 使用 COMPUTE 和 COMPUTE BY 子句,在结果集中生成汇总值,使用带有 ROLLUP 运算符的 GROUP BY 子句 使用带有 CUBE 运算符的 GROUP BY 子句 使用 GROUPING 函数,使用ROLLUP运算符,SELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH ROLLUP ORDER BY productid, orderid GO,示例,使用ROLLUP运算符（续）,说明 GROUP BY A,B,C WITH ROLLUP 产生如下集合行的小计： （A，B，C） （A，B） （A） （）,使用ROLLUP运算符（续）,在生成包含小计和合计的报表时，ROLLUP 运算符很有用。 使用 GROUP BY 子句和 ROLLUP 操作符时，应注意 SQL Server 将在结果集中增加一行，这行将显示总和或平均值之类的汇总值，新增的行以 NULL 标识 使用 ROLLUP 时，确保出现在 GROUP BY 后的各字段，在数据库环境中具有确定的、有意义的关系,使用CUBE 运算符,CUBE 操作符比 ROLLUP 操作符 多产生两个汇总 值,SELECT productid, orderid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderid GO,描述,总数,对 orderid 1 中的所有行汇总,对 orderid 2 中的所有行汇总,只对 productid 1 中的行汇总,只对 productid 2 中的行汇总,只对 productid 3 中的行汇总,示例,使用CUBE 运算符（续）,CUBE与ROLLUP的共同点 都必须与GROUP BY子句联合使用,CUBE与ROLLUP的区别 CUBE 生成的结果集显示了所选列中值的所有组合的聚合 ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。,使用 GROUPING 函数,区分结果集中的细节数据和汇总数据 使用函数 GROUPING 时，应注意 SQL Server 将为 GROUPING 函数指定的字段生成一个新的字段 如果 SQL Server 返回 1，代表结果集中的这一记录是由 ROLLUP 或 CUBE 生成的 SQL Server 返回 0，代表该记录原本就在数据库的表中 出现在 GROUPING 函数中的字段也必须出现在 GROUP BY 子句中,1 代表前一字段的汇总值 0 代表前一字段的具体值,使用 GROUPING 函数（续）,SELECT productid, GROUPING (productid) ,orderid, GROUPING (orderid) ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid, orderid WITH CUBE ORDER BY productid, orderid GO,total_quantity,示例,第7章 数据分组与汇总,使用 TOP n 列出前 n 个记录 使用聚合函数 GROUP BY 的基础知识 在结果集中生成汇总值 使用 COMPUTE 和 COMPUTE BY 子句,使用 COMPUTE 和 COMPUTE BY 子句,生成某一列的明细值和汇总值的报表 为组中的子集生成明细值和汇总值的报表,使用 COMPUTE 和 COMPUTE BY 子句（续）,COMPUTE,SELECT productid, orderid ,quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) GO,示例 1,生成某一列的明细值和汇总值的报表,使用COMPUTE 子句的注意事项和原则 在一条语句中，可以同时使用多个 COMPUTE 和 COMPUTE BY 子句 SQL Server 中要求COMPUTE 子句中的字段与选择列表中的字段相同,为组中的子集生成明细值和汇总值的报表,使用COMPUTE BY子句的注意事项和原则 应当同时使用 ORDER BY 子句和 COMPUTE BY 子句，这样记录就会被分组显示 出现在 COMPUTE BY 子句后的字段必须出现在 ORDER BY 子句后，且顺序相同，始于同一表达式，不能略过任一表达式 可以同时产生多种汇总方式,随堂练习,SELECT productid, orderid ,quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) BY productid GO,SELECT productid, orderid ,quantity FROM orderhist ORDER BY productid, orderid COMPUTE SUM(quantity) BY orderid GO,比较,回顾,学习完本章后，将能够： 使用 TOP n 列出表中指定的前 n 个记录 使用聚合函数生成单个汇总值 使用聚合函数以及 GROUP BY 和 HAVING 子句，对某字段生成汇总值 使用聚合函数以及 GROUP BY 和 ROLLUP 或 CUBE 操作符，为表生成汇总值 使用 COMPUTE 和 COMPUTE BY 子句生成控制中断报告,练 习,请统计学期号为“第三学期”的每个学生所有科目的平均成绩 现在想评定第二学年（包括第三、第四学期）的优秀学生奖学金，要求选出平均成绩排前5名的同学（考虑并列） 前三学年（第一到第六学期）总平均分在90分以上而且没有一次考试不及格的同学的学号和总平均分,表名：SC,