介绍一种蛮好用汇总量据的法子GROUPING SETS【彩民

2019-10-13 03:35 来源:未知

实例: Data Analyst at Adventure Works

 

比方您是二个数目剖判师,对于厂家二〇一六年的受益很感兴趣。那代表你须求分组汇总公司的每年每度的纯收入,查询语句如下:

Query 1. 集聚每年一次纯收入

 

 

USE AdventureWorks2012;
GO

SELECT
YEAR(OrderDate) AS OrderYear,
SUM(SubTotal) AS Income
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
ORDER BY OrderYear;
GO

 

 

 

Query 1 再次来到结果集:

OrderYear

Income

2005

11331809

2006

30674773.2

2007

42011037.2

2008

25828762.1

 

 

根据那一个结果集,可以见到该公二〇〇五到二〇一〇年的进项情形。那类数据音信对于商业剖析来讲很广阔。

唯独,假如您想要越来越多关于收入的音讯,比如其余汇总条件,你不能够不要重国民党的新生活运动行叁个GROUP BY子句。比如查询重回公司各样月的收入情况。查询语句如下:

Query 2. 集团各种月的受益

 

SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Income
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;
GO

 

 

结果集如下:

OrderYear

OrderMonth

Income

2005

7

962716.742

2005

8

2044600

2005

9

1639840.11

2005

10

1358050.47

2005

11

2868129.2

2005

12

2458472.43

2006

1

1309863.25

2006

2

2451605.62

2006

3

2099415.62

2006

4

1546592.23

2006

5

2942672.91

2006

6

1678567.42

2006

7

2894054.68

2006

8

4147192.18

2006

9

3235826.19

2006

10

2217544.45

2006

11

3388911.41

2006

12

2762527.22

2007

1

1756407.01

2007

2

2873936.93

2007

3

2049529.87

2007

4

2371677.7

2007

5

3443525.25

2007

6

2542671.93

2007

7

3554092.32

2007

8

5068341.51

2007

9

5059473.22

2007

10

3364506.26

2007

11

4683867.05

2007

12

5243008.13

2008

1

3009197.42

2008

2

4167855.43

2008

3

4221323.43

2008

4

3820583.49

2008

5

5194121.52

2008

6

5364840.18

2008

7

50840.63

 

 

那一个结果集要比以前的更详细一点。能够获得实际有个别月的低收入聚焦。分明GROUP BY 前面的列更加的多其越详细,结果常常也越来越多(除非有传递信赖键)。

比方你精心察看三个查询,你会意识她们都以基于个子的分组表明式举办分组集中的。后面包车型客车是依照年,前面包车型客车是依据年和月。

如果本身想查看二种汇计算果在二个结实聚焦应该怎么管理那?为了完成那几个目的,我们前面说了多少个方案,方案1便是利用UNION ALL,代码如下: 

 

Query 3. 集团创收外汇(每一年|每月)

 

 

SELECT
YEAR(OrderDate) AS OrderYear,
NULL AS OrderMonth, --Dummy Column
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
UNION ALL
SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;
GO

 

 

结果集如下图所示:

彩民之家高手论坛 1

  

里头白灰框内为根据年的聚集数据。乌紫框内为依据年和月的分组聚焦。

如图所示七个结实集被联合在协同了。注意。此时NULL出现在里边,使用NULL作为假列来标记order year分组的结果。因为按年分组未有那几个列。

就算你已经取得了想要的结果,可是这么需求形成五回的话语,接下去大家品尝一下grouping set,方案2。因为大家都以懒人吗,所以这么些主意必然要越发简明。指标正是“越来越少代码,同样结果”。接下来大家详细看一下:

Query 4.使用 GROUPING SETS实现均等结果 

 

 

SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY
GROUPING SETS
(
YEAR(OrderDate), --1st grouping set
(YEAR(OrderDate),MONTH(OrderDate)) --2nd grouping set
);
GO

 

结果集跟从前的一模一样。不过新的代码要少比很多。GROUPING SETS 操作符要和GROUP  BY 子句在一块儿使用。并且同意大家能够做三个多分组的询问。纵然如此,我们要留意检查内定的分组集。举个例子借使多少个分组满含五个列,即使列A和B,五个列都亟需满含在括号内:(column A, column B)。若无括号,这么些子句将会被定义为单身的分组,结果就差异了。

下边语句的结果如下:

彩民之家高手论坛 2

 

 

附带说一下,即使我们策动聚合整个结果集(不分组聚合全体数据),只供给加上有贰个空的括号在分组集里面就可以。查询语句如下:

Query 5. 加入总体集中结果

 

 

SELECT
YEAR(OrderDate) AS OrderYear,
MONTH(OrderDate) AS OrderMonth,
SUM(SubTotal) AS Incomes
FROM Sales.SalesOrderHeader
GROUP BY
GROUPING SETS
(
YEAR(OrderDate), --1st grouping set
(YEAR(OrderDate),MONTH(OrderDate)), --2nd grouping set
() --3rd grouping set (grand total)
);
GO

 

 

 

结果如图:

彩民之家高手论坛 3

留意最下方的42行,年月都为null,那些查询汇总了郑铁的有所收入,因为从没进展别的分组。

注意,要求重申三个十强,一定要有限支撑分组列字段部位NULL,因而NULLS无法被用作分组列在GROUPING SETS中使用。如若非要那多少个为空字段,须求接纳 GROUPING 或者 GROUPING_ID 函数判别是不是NULL来自GROUPING SETS 操作符。

1、GROUP BY 标准分组

为了便利描述,小编将把大致 GROUP BY 子句的分组称之为标准分组,把 GROUP BY 子句中出现的列(或表明式)称之为分组列。

SQLSE昂科拉VE奇骏中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数

 先来创建一个测量试验表

 1 USE [tempdb]
 2 GO
 3 
 4 CREATE TABLE #temptb(id INT ,NAME VARCHAR(200))
 5 GO
 6 
 7 INSERT INTO [#temptb] ( [id], [NAME] )
 8 SELECT 1,'中国' UNION ALL
 9 SELECT 2,'中国' UNION ALL
10 SELECT 3,'英国' UNION ALL
11 SELECT 4,'英国' UNION ALL
12 SELECT 5,'美国' UNION ALL
13 SELECT 6,'美国' UNION ALL
14 SELECT null, '法国' UNION ALL
15 SELECT 8,'法国' 
16 GO
17 
18 SELECT * FROM [#temptb]
19 GO

 

先来看一下SELECT语句的语法:

1 SELECT [ ALL | DISTINCT ] [ topSubclause ] aliasedExpr 
2       [{ , aliasedExpr }] FROM fromClause [ WHERE whereClause ] [ GROUP BY groupByClause [ HAVING havingClause ] ] [ ORDER BY orderByClause ]
3 or
4 SELECT VALUE [ ALL | DISTINCT ] [ topSubclause ] expr FROM fromClause [ WHERE whereClause ] [ GROUP BY groupByClause [ HAVING havingClause ] ] [ ORDER BY orderByClause

 

ALL关键字:钦命在结果聚集能够体现重复的行,那是默许的首要字,也正是说,当你在询问中不选用ALL关键字,暗许都曾经附加上了ALL这些根本字

诸如上边多个SQL语句,实际上是等价的,都会把重复的记录select出来

1 --这两个语句是等价的
2 SELECT * FROM [#temptb]
3 GO
4 -------------------------------------------
5 SELECT ALL * FROM [#temptb]
6 GO

假如您需求把独一值select出来,过滤掉这么些重复值须求选用DISTINCT关键字

1 SELECT DISTINCT([NAME]) FROM [#temptb]

而当您把SQL语句,字段放在ALL括号中,那时候就能够成为一个表明式,举例上边SQL语句

1 SELECT ALL([NAME] '您好') AS '国别' FROM [#temptb]

彩民之家高手论坛 4

在自己上一篇小说里:管理表重复记录(查询和删除)

在Name一样ID最大的记录,此中有多少个SQL语句

1 SELECT  *
2 FROM    [#temptb] a
3 WHERE   ID!<ALL ( SELECT    ID
4                   FROM      [#temptb]
5                   WHERE     Name = a.Name )

彩民之家高手论坛 5

假诺去掉ALL关键字会怎么样呢?

彩民之家高手论坛 6

因为子查询需求的是二个表明式,所以需求运用ALL关键字把她产生三个表明式,所以要用ALL

 

ALL关键字还足以放在GROUP BY 之后

这里要分三种情状,一种是SQL语句中有where子句的的,另一种是SQL语句中尚无where子句的

情况一:

1 SELECT AVG(id) FROM [#temptb] WHERE NAME='法国' GROUP BY ALL NAME
2 SELECT AVG(id) FROM [#temptb] WHERE NAME='法国'  GROUP BY NAME

彩民之家高手论坛 7

对此未有相符条件的行的组,这里是尚未切合name='法兰西共和国',作为聚合值的列值为NULL

譬如未有ALL关键字,GROUP BY子句将不出示未有相符条件的行的组

情况二:

1 SELECT AVG(id) FROM [#temptb]  GROUP BY ALL NAME
2 SELECT AVG(id) FROM [#temptb]  GROUP BY  NAME

当SQL语句中从不where子句的时候,查询出来的结果都以同等的

 

ALL关键字还足以放在UNION之后

1 USE [GPOSDB]
2 GO
3 INSERT INTO [dbo].[SystemPara] ( [ParaValue], [Name], [Description] )
4 SELECT 'nihao','nihao','nihao' UNION ALL
5 SELECT 'nihao','nihao','nihao' 

 


PERCENT关键字

PERCENT关键字要求跟TOP 关键字一齐使用

从结果聚集输出百分之N行,n必须是介于0~100中间的大背头

1 SELECT TOP 10 PERCENT * from [#temptb]
2 GO

地点的SQL语句意思是:从[#temptb]表中输出一成的记录数,因为尚未接纳order by子句,所以那条记下是随意的

彩民之家高手论坛 8

因为[#temptb]表有8条记录,8*十分之一=0.8 四舍五入之后也等于一条记下

1 SELECT TOP 30 PERCENT * from [#temptb]
2 GO

彩民之家高手论坛 9

8*肆分一=2.4 四舍五入之后也就是三条记下,SQLSEGL450VEENVISION在此尽管四舍五入不足三条记下,他也会输出偏大的数,也正是三条记下

 


CUBE关键字

CUBE关键字:假使必要在结果集内不止含有由GROUP BY提供的常规行,还饱含汇总行,能够用CUBE关键字。CUBE关键字与GROUP BY一齐行使

当使用CUBE关键字的时候,能够行使GROUPING函数来输出二个外加的列,当结果行是例行的行时,再次回到0;当结果行是汇总行时,重返1。

1 SELECT  AVG(id) AS '平均值', GROUPING(NAME) AS '是否已汇总'
2 FROM    [#temptb]
3 GROUP BY NAME
4         WITH CUBE

彩民之家高手论坛 10

提起底一行展现了GROUP BY的记录有稍许行,一共有4行记录,而在汇总行(即最终一行)是或不是已集中那列突显1,表示是汇总行


Grouping关键字

指令是还是不是聚合 GROUP BY 列表中的钦命列表明式。

在结果集中,假使 GROUPING 重返 1 则提示聚合;再次来到 0 则提示不凑合。

要是钦命了 GROUP BY,则 GROUPING 只可以用在 SELECT <select> 列表、HAVING 和 O奥迪Q7DEEnclave BY 子句中。

GROUPING 用于区分规范空值和由 ROLLUP、CUBE 或 GROUPING SETS 重返的空值。

用作 ROLLUP、CUBE 或 GROUPING SETS 操作结果回到的 NULL 是 NULL 的超过常规规应用。

它在结果集内作为列的占位符,表示整个。

 

以下示例将分组 SalesQuota 并集合 SaleYTD 数量。GROUPING 函数应用于 SalesQuota 列。

1 USE [AdventureWorks];
2 GO
3 SELECT  SalesQuota, SUM(SalesYTD) 'TotalSalesYTD',
4         GROUPING(SalesQuota) AS 'Grouping'
5 FROM    Sales.SalesPerson
6 GROUP BY SalesQuota
7         WITH ROLLUP;
8 GO

彩民之家高手论坛 11

结果集在 SalesQuota 下边突显八个空值。

先是个 NULL 代表从表中的这一列获得的空值组。

第4个 NULL 位于 ROLLUP 操作所增添的汇总行内部。

汇总行彰显全数 SalesQuota 组的 TotalSalesYTD 数量,并以 Grouping 列中的 1 举行指令。

 


 


对简易汇总报表使用 Transact-SQL

浮动轻易汇总报表的应用程序可利用下列 Transact-SQL 成分:

ROLLUP、CUBE 或 GROUPING SETS 运算符。那些是 SELECT 语句的 GROUP BY 子句的强盛。

COMPUTE 或 COMPUTE BY 运算符。那二种运算符也与 GROUP BY 相关联。

这个运算符生成的结果聚集,既富含各个品种的明细行,也包蕴每一个组的汇总行,汇总行呈现了该组的集结合计。

GROUP BY 子句可用来转移只含有各组的集聚而不富含其明细行的结果。

应用程序应选用 Analysis Services,并不是 CUBE、ROLLUP、COMPUTE 或 COMPUTE BY。

专程要在乎的是,CUBE 和 ROLLUP 应当只用在不可能访谈 OLE DB 或 ADO 的情况中,比如脚本或存款和储蓄进程中。

支撑 COMPUTE 和 COMPUTE BY 是为着向后优秀。

有道是优先选拔 ROLLUP 运算符而非 COMPUTE 或 COMPUTE BY。由 COMPUTE 或 COMPUTE BY 生成的汇总值将用作七个单身的结果集重临,

这个结果集以内还插入了包括各组明细行的结果集;或然充任包括合计的结果集重临,附加在主结果集之后。

拍卖这一个多少个结果集将大增应用程序代码的复杂性。服务器游标既不援救COMPUTE,也不扶植 COMPUTE BY。

但 ROLLUP 扶持服务器游标。CUBE 和 ROLLUP 将扭转单个结果集,个中满含嵌入的小计合计行。

其余,查询优化器临时还能为 ROLLUP 生成比为 COMPUTE 和 COMPUTE BY 生成的奉行布署越来越高速的试行布置。

一经应用不带那么些运算符的 GROUP BY,将赶回单个结果集,在那之中每组对应一行,行中满含该组的集聚小计。结果聚焦未有明细行。

 


SQLSERVER中Cube 、RollUp的用法

CubeRollUp可以对查询的多寡实行聚集,在数额总括中平时使用,尤其是做报表时,用在Select语句中

上边就对两种总计方法实行对照

SQL脚本如下:

 1 USE [tempdb]
 2 GO
 3 CREATE TABLE t_test
 4 (
 5   id INT ,
 6   productName VARCHAR(200) ,
 7   price MONEY ,
 8   num INT ,
 9   amount INT ,
10   operatedate DATETIME
11 )
12 GO
13 
14 --插入随机数据
15 DECLARE @i INT 
16 DECLARE @rand MONEY
17 DECLARE @date DATETIME
18 DECLARE @index INT 
19 DECLARE @DateBase INT 
20 SET @date = '2012-10-23'
21 SET @i = 1
22 WHILE ( @i < 18 ) 
23     BEGIN
24         SET @rand = RAND() * 20
25         SET @index = CAST(RAND() * 3 AS INT)
26         SET @DateBase = CAST(RAND() * 10 AS INT)
27  
28         INSERT  INTO t_test ( id, productName, price, num, amount, operatedate )
29         VALUES  ( @i, 'product'   CAST (@index AS VARCHAR(10)), @rand, 100,
30                   @rand * 100, @date   @DateBase )
31         SET @i = @i   1
32     END
33  
34  
35 SELECT  *  FROM    t_test

 彩民之家高手论坛 12

 分别用二种方法总计:

 1 --分别用两种方式统计:
 2  
 3 SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'
 4              ELSE CONVERT(VARCHAR(10), operatedate, 120)
 5         END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'
 6                         ELSE productName
 7                    END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,
 8         SUM(amount) AS 金额
 9 FROM    t_test
10 GROUP BY operatedate, productName  WITH ROLLUP;   
11 -------------------------------------------------------------------
12 SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'
13              ELSE CONVERT(VARCHAR(10), operatedate, 120)
14         END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'
15                         ELSE productName
16                    END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,
17         SUM(amount) AS 金额
18 FROM    t_test
19 GROUP BY operatedate, productName WITH CUBE; 

ROLLUP 根据分组顺序,先对第叁个字段operatedate分组,在组内举行总计,最终交给合计

彩民之家高手论坛 13

1 SELECT  CASE WHEN GROUPING(operatedate) = 1 THEN '小计'  --用GROUPING得出是否是汇总行,这个例子里最后一行是汇总行
2              ELSE CONVERT(VARCHAR(10), operatedate, 120)
3         END AS 日期, CASE WHEN GROUPING(productName) = 1 THEN '小计'
4                         ELSE productName
5                    END AS 产品名称, SUM(amount) / SUM(num) AS 平均价格, SUM(num) AS 数量,
6         SUM(amount) AS 金额
7 FROM    t_test
8 GROUP BY operatedate, productName  WITH ROLLUP;   --因为operatedate和productName字段都在GROUPING函数里统计是否汇总,所以GROUP BY后面就需要加operatedate和productName这两个字段

 

CUBE 会对全部的分组字段实行总括,如上例,先对日期求小计,也正是计算天天的成品总金额,然后总计各类产品的总金额,最后交给总的合计。

彩民之家高手论坛 14

ROLLUPCUBE的界别就是: ROLLUP 只会去总结group by 前面包车型大巴第二个字段每种分组的小计和率先个字段的一共
 
Grouping(字段名) 用来差异当前行是或不是小计爆发的行,  Grouping(字段名)=1 验证是总括行,Grouping(字段名)=0 表达是表中行

能够用在case,where 前面


另外一个例证

SQL脚本如下:

彩民之家高手论坛 15彩民之家高手论坛 16

 1 USE [tempdb]
 2 GO
 3 CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
 4 INSERT Sales VALUES(1, 2005, 12000)
 5 INSERT Sales VALUES(1, 2006, 18000)
 6 INSERT Sales VALUES(1, 2007, 25000)
 7 INSERT Sales VALUES(2, 2005, 15000)
 8 INSERT Sales VALUES(2, 2006, 6000)
 9 INSERT Sales VALUES(3, 2006, 20000)
10 INSERT Sales VALUES(3, 2007, 24000)
11 
12 SELECT * FROM [dbo].[Sales]

View Code

彩民之家高手论坛 17

ROLLUP

1 SELECT EmpId, Yr, SUM(Sales) AS Sales
2 FROM Sales
3 GROUP BY EmpId, Yr WITH ROLLUP

彩民之家高手论坛 18

CUBE

1 SELECT EmpId, Yr, SUM(Sales) AS Sales
2 FROM Sales
3 GROUP BY EmpId, Yr WITH CUBE

彩民之家高手论坛 19

CUBE比ROLLUP多了年度的总计,总括了二〇〇七、二零零六、二零零六年的出卖额

可以用下图来代表

彩民之家高手论坛 20

ROLLUP

 彩民之家高手论坛 21

彩民之家高手论坛 22

CUBE

彩民之家高手论坛 23

彩民之家高手论坛 24

彩民之家高手论坛 25

 

 


验证CUBE和ROLLUP 的区别

ROLLUPCUBE的差别就是: ROLLUP 只会去总计group by 前边的首先个字段每一个分组的小计和第贰个字段的总共

我们修改一下上边十三分实验

彩民之家高手论坛 26彩民之家高手论坛 27

 1 USE [tempdb]
 2 GO
 3 CREATE TABLE Sales (EmpId INT,productName VARCHAR(200), Yr INT, Sales MONEY)
 4 GO
 5 INSERT Sales VALUES(1,'product2', 2005, 12000)
 6 INSERT Sales VALUES(1,'product1', 2005, 18000)
 7 INSERT Sales VALUES(1,'product0', 2006, 25000)
 8 INSERT Sales VALUES(1,'product2', 2007, 15000)
 9 INSERT Sales VALUES(2,'product1', 2005, 60000)
10 INSERT Sales VALUES(2,'product1', 2006, 22000)
11 INSERT Sales VALUES(2,'product0', 2007, 24000)
12 INSERT Sales VALUES(3,'product0', 2005, 32000)
13 INSERT Sales VALUES(3,'product2', 2006, 42000)
14 INSERT Sales VALUES(3,'product0', 2007, 24000)
15 GO
16 
17 SELECT * FROM [dbo].[Sales]

View Code

彩民之家高手论坛 28

 ROLLUP

1 SELECT EmpId, Yr,[productName], SUM(Sales) AS Sales
2 FROM Sales
3 GROUP BY EmpId, Yr,[productName] WITH ROLLUP

彩民之家高手论坛 29

CUBE

1 SELECT EmpId, Yr,[productName], SUM(Sales) AS Sales
2 FROM Sales
3 GROUP BY EmpId, Yr,[productName] WITH CUBE

彩民之家高手论坛 30

彩民之家高手论坛 31

可以看出CUBE除了计算EmpId字段之外,还总结了GROUP BY前面包车型大巴Yr和productName那多个字段

而ROLLUP只总计了EmpId那个字段


 

总结

那个首要字和函数对平常用于总括的应用程序都极其有用,尽管大家对这些函数作用都很纯熟的话,在付出个中一定能够一箭穿心

此外,个人以为PERCENT关键字能够动用在分页上

 

如有不对的地方,迎接大家拍砖哦o(∩_∩)o

正文版权归小编全数,未经小编同意不得转发。

总结

本篇文章中,重要介绍如何选取另一种聚合查询艺术来促成多样分组聚合结果的集结。纯熟后你会发觉这种办法对于计算汇总的数量据特别有帮带,大大进步了我们代码的频率。

原来的文章地址

 

2.2、CUBE 分组

CUBE 是 GROUP BY 子句的一种扩张,它同意计算规范分组及拥有维度的小计、合计。语法:

GROUP BY CUBE(grouping_column_reference_list)

CUBE 会对全体希望的分组举办总结,进而生成交叉报表。CUBE 比 ROLLUP 的分组越来越多,且含有了 ROLLUP 的总计结果,且计量结果与分组列的一一无关,但万一列顺序区别,暗许的结果排序会有两样。当 CUBE 中钦命 n 列时,整个总计进程中分组织承办法有 2 的 n 次方种。如GROUP BY CUBE(A,B)一定于:按 GROUP BY(A,B) 分组的小计,加按 GROUP BY(A,NULL) 分组的小计,加按 GROUP BY(NULL,B) 分组的小计,加按 GROUP BY(NULL,NULL) 分组的商业事务。

示例 1:

SELECT t.dept_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY CUBE(t.dept_code);

结果:

DEPT_CODE                                          SUM_SALARY MAX_SALARY
-------------------------------------------------- ---------- ----------
                                                        21350       7500
010102                                                  13500       7500
010103                                                   7850       5050

示例 2:

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY CUBE(t.dept_code,t.post_code);

结果:

DEPT_CODE                                     POST_CODE                                  SUM_SALARY MAX_SALARY
--------------------------------------------- ------------------------------------------ ---------- ----------
                                                                                              21350       7500
                                              P40                                             12550       7500
                                              P50                                              8800       6000
010102                                                                                        13500       7500
010102                                        P40                                              7500       7500
010102                                        P50                                              6000       6000
010103                                                                                         7850       5050
010103                                        P40                                              5050       5050
010103                                        P50                                              2800       2800

演示 3,部分 CUBE 分组(无需一些小计、合计时可用该种写法):

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY t.dept_code,CUBE(t.post_code);

结果:

DEPT_CODE                                    POST_CODE                                SUM_SALARY MAX_SALARY
-------------------------------------------- ---------------------------------------- ---------- ----------
010102                                                                                     13500       7500
010102                                       P40                                            7500       7500
010102                                       P50                                            6000       6000
010103                                                                                      7850       5050
010103                                       P40                                            5050       5050
010103                                       P50                                            2800       2800

安不忘虞数据集

正文中颇有的询问都选择AdventureWorks二〇一一数据库中的数据,这里提供贰个下载地址方便使用(AdventureWorks2012)

4、总结

正文首要汇报了 Oracle 中分组查询的正规化分组、扩丰富组、扩大函数等 GROUP BY 相关的知识点。

正文链接
版权评释:本文为和讯博主 韩宗泽 原创,小编保留签名权!应接通过转载、演绎或其余传播情势来行使本文,但必得在大廷广众位置给出小编签字和本文链接!本身初写博客,水平有限,若有不当之处,敬请商议指正,谢谢!

介绍

  对于任何人来说,用T-SQL语句来写集会查询都以专业中器重的一环。我们大家也都很熟知GROUP BY子句来贯彻聚合表达式,不过一旦筹算在三个结实集中满含二种分裂的汇总括果,只怕会相比麻烦。作者将比如体现给我们使用GROUPING SETS操作符来成功那几个“混合的结果集”。

  大概当我们在筹算深入分析很大局面包车型客车数码集时,不明白从何动手,此时拍卖这种景况最佳的法子便是汇聚数据,赶快的拿走贰个数据预览。

在T-SQL中,使用GROUP BY子句在多个成团查询中来聚焦必要的多寡。那个子句由一组表明式定义的分组构成。结果聚焦每一行重临GROUP BY 子句中表达式的独一值可能组合,何况聚合函数,像COUNT也许SUM等足以对查询中的任何行开展联谊。可是,要是你想要两种不相同组合的汇集时,日常有三种办法:

  1.将不懂组合聚合的结果集UNIONALL在协同。

  2.使用 GROUPING SETS操作符,结合GROUP BY一齐在三个口舌中落实。

 

本文中,小编博览会示什么选取GROUPING SETS来兑现这一指标。

2.1、ROLLUP 分组

ROLLUP 是 GROUP BY 子句的一种扩充,它同意总计标准分组及部分维度的小计、合计。语法:

GROUP BY ROLLUP(grouping_column_reference_list)

ROLLUP 的揣度结果与括号中钦点列的逐个有关,因为 ROLLUP 的分组进度具备方向性,先总结标准分组,然后从右到左递减计算越来越高级中学一年级流的小计,直到全部列被总结完,最终计算合计。当 ROLLUP 中钦点 n 列时,整个计算进度中分组办法有 n 1 种。如GROUP BY ROLLUP(A,B)的分组进程也正是是:第 1 步按 GROUP BY(A,B) 分组求小计,第 2 步按 GROUP BY(A,NULL) 分组求小计,第 3 步按 GROUP BY(NULL,NULL) 分组求合计。

示例 1:

SELECT t.dept_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code);

结果:

DEPT_CODE                                          SUM_SALARY MAX_SALARY
-------------------------------------------------- ---------- ----------
010102                                                  13500       7500
010103                                                   7850       5050
                                                        21350       7500

示例 2:

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code,t.post_code);

结果:

DEPT_CODE                                      POST_CODE                                    SUM_SALARY MAX_SALARY
---------------------------------------------- -------------------------------------------- ---------- ----------
010102                                         P40                                                7500       7500
010102                                         P50                                                6000       6000
010102                                                                                           13500       7500
010103                                         P40                                                5050       5050
010103                                         P50                                                2800       2800
010103                                                                                            7850       5050
                                                                                                 21350       7500

演示 3,部分 ROLLUP 分组(没有须要或多或少小计、合计时可用该种写法):

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY t.dept_code,ROLLUP(t.post_code);

结果:

DEPT_CODE                                     POST_CODE                                    SUM_SALARY MAX_SALARY
--------------------------------------------- -------------------------------------------- ---------- ----------
010102                                        P40                                                7500       7500
010102                                        P50                                                6000       6000
010102                                                                                          13500       7500
010103                                        P40                                                5050       5050
010103                                        P50                                                2800       2800
010103                                                                                           7850       5050

3.3、GROUP_ID 函数

GROUP_ID 语法:GROUP_ID()。当该函数出现在 SELECT 子句中时,如若结果聚集存在 n 个再一次,那么它将重回范围从 0 到 n-第11中学的数字,那对于从询问结果中去除重复分组来讲特别平价。示例(日常经过HAVING GROUP_ID()<1将重新行全部剔除):

SELECT t.dept_code,SUM(t.post_salary) sum_salary,GROUP_ID() group_id
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code),CUBE(t.dept_code);

结果:

DEPT_CODE                                          SUM_SALARY   GROUP_ID
-------------------------------------------------- ---------- ----------
010102                                                  13500          0
010103                                                   7850          0
010102                                                  13500          2
010103                                                   7850          2
010102                                                  13500          1
010103                                                   7850          1
                                                        21350          0

2.3、GROUPING SETS 分组

GROUPING SETS 是 GROUP BY 子句的一种扩充,它同意一回总计八个正经分组的小计。语法:

GROUP BY GROUPING SETS(grouping_column_reference_list)

GROUPING SETS 的估测计算结果和分组列的相继非亲非故,结果集排序也和分组列的逐个非亲非故。当 GROUPING SETS 中钦赐 n 列时,整个计算进度中分组织承办法有 n 种。如GROUPING SETS(A,B,C)相当于 GROUP BY A、GROUP BY B 和 GROUP BY C 这 3 个分组 UNION ALL 的结果。

示例 1:

SELECT t.dept_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY GROUPING SETS(t.dept_code);

结果:

DEPT_CODE                                          SUM_SALARY MAX_SALARY
-------------------------------------------------- ---------- ----------
010102                                                  13500       7500
010103                                                   7850       5050

示例 2:

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY GROUPING SETS(t.dept_code,t.post_code);

结果:

DEPT_CODE                                     POST_CODE                                  SUM_SALARY MAX_SALARY
--------------------------------------------- ------------------------------------------ ---------- ----------
010102                                                                                        13500       7500
010103                                                                                         7850       5050
                                              P50                                              8800       6000
                                              P40                                             12550       7500

示例 3,部分 GROUPING SETS 分组:

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY t.dept_code,GROUPING SETS(t.post_code);

结果:

DEPT_CODE                                    POST_CODE                                 SUM_SALARY MAX_SALARY
-------------------------------------------- ----------------------------------------- ---------- ----------
010103                                       P40                                             5050       5050
010102                                       P40                                             7500       7500
010102                                       P50                                             6000       6000
010103                                       P50                                             2800       2800

示范 4,GROUPING SETS 基本上能用 ROLLUP 和 CUBE 作为它的参数;GROUPING SETS 只对单列实行分组,而不提供合计的功用,要是急需 GROUPING SETS 提供合计,可用 ROLLUP 或 CUBE 作参数来提供合计成效(注意 ROLLUP 和 CUBE 不收受 GROUPING SETS 作参数,ROLLUP 和 CUBE 之间互相作为参数也不能):

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY GROUPING SETS(ROLLUP(t.dept_code),ROLLUP(t.post_code));

结果:

DEPT_CODE                                    POST_CODE                                  SUM_SALARY MAX_SALARY
-------------------------------------------- ------------------------------------------ ---------- ----------
010102                                                                                       13500       7500
010103                                                                                        7850       5050
                                             P50                                              8800       6000
                                             P40                                             12550       7500
                                                                                             21350       7500
                                                                                             21350       7500

3.2、GROUPING_ID 函数

GROUPING_ID 语法:GROUPING_ID(expr [, expr ]...)。当该函数出现在 SELECT 子句中时,它将回来与行相关联的 GROUPING 位向量对应的数值。GROUPING_ID 函数按从左到右的顺序总结,即使此列是分组列,则为 0,如若是小计或协商则为 1,然后按列的逐条将总括结果组成二进制种类(位向量),最终将位向量转化为十进制数。GROUPING_ID 函数在职能上等效于四个 GROUPING 函数的结果,有了 GROUPING_ID 就不必再写八个 GROUPING 了,也使得行过滤条件更便于表明。当查问结果有几个汇聚品级时,该函数极其有用,可经过它的重临值来排序和过滤结果集。示例:

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,
  GROUPING_ID(t.dept_code) gd,
  GROUPING_ID(t.post_code) gp,
  GROUPING_ID(t.dept_code,t.post_code) gdp,
  GROUPING_ID(t.post_code,t.dept_code) gpd
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY CUBE(t.dept_code,t.post_code)
ORDER BY GROUPING_ID(t.dept_code,t.post_code);

结果(结果集相当于是按 GDP 升序排序):

DEPT_CODE                     POST_CODE                SUM_SALARY         GD         GP        GDP        GPD
----------------------------- ------------------------ ---------- ---------- ---------- ---------- ----------
010102                        P40                            7500          0          0          0          0
010103                        P40                            5050          0          0          0          0
010102                        P50                            6000          0          0          0          0
010103                        P50                            2800          0          0          0          0
010103                                                       7850          0          1          1          2
010102                                                      13500          0          1          1          2
                              P50                            8800          1          0          2          1
                              P40                           12550          1          0          2          1
                                                            21350          1          1          3          3

1.2、WHERE 和 HAVING 的区别?

自己先是要验证的是:“WHERE 和 HAVING 的分别?”那纯属是二个有失水准的标题!因为它两根本就从不可比性,实际上问这种主题材料的人,往往 SQL 基础也非常不足扎实。

在饱含 GROUP BY 子句的查询语句中:WHERE 子句的成效是在对查询结果分组前过滤行数据,将不切合条件的行去掉;而 HAVING 子句的效力是在对查询结果分组后过滤组数据,将不相符条件的组去掉。换句话说,因为聚合函数的功能是提供关于组的音讯,还没分组在此之前当然是不可能提供组的音信的,也等于说 WHERE 子句中不可能利用聚合函数,那约等于会并发 HAVING 子句的因由。有了 HAVING 子句,大家就能够很便利的在分组之后对组的多寡开展过滤了。日常的话,能用 WHERE 的过滤的就不应当用 HAVING 过滤!

1.1、GROUP BY 概述

在分组查询中,GROUP BY 子句的遵从便是按钦赐的(一或三个)列或表达式的值将选定行集进行分组,并针对性每一组重临一行从组中搜罗到的数额。基本语法:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[HAVING having_condition];

大约示例:

SELECT t.dept_code,
  MAX(t.post_salary) max_salary,  -- 部门最高岗位工资
  MIN(t.post_salary) min_salary,  -- 部门最低岗位工资
  AVG(t.post_salary) avg_salary,  -- 部门平均岗位工资
  SUM(t.post_salary) sum_salary,  -- 部门岗位工资之和
  COUNT(t.post_salary) cnt_salary -- 部门工资份数
FROM demo.t_staff t
GROUP BY t.dept_code
HAVING AVG(t.post_salary)>3500
ORDER BY t.dept_code;

注意事项

  • 1、SELECT 子句中只可以冒出分组列或聚合函数或常量。
  • 2、HAVING 子句中只好现身分组列或聚合函数或常量。
  • 3、GROUP BY 子句中只可以出现标量基元类型(如 VARCHAKuga2、NUMBE奥迪Q3、DATE 等)的列或常量,不可能现身 BLOB、CLOB 等门类的列。
  • 4、实际上分组列能够不出现在 SELECT 子句中,但那或者会让查询结果含义不显眼。
  • 5、在 SELECT、HAVING、GROUP BY 子句中还是可以出现一些特殊的函数,如 SYSDATE(认为没啥意思)。

2.4、复杂分组(组合列分组、重复列分组、连接分组)

轻松易行来讲:组合列分组就是同意 ROLLUP、CUBE 和 GROUPING SETS 中可以有四个列或列组合;重复列分组正是同意 GROUP BY 前面重复现身分组列;连接分组正是允许 GROUP BY 后边有八个 ROLLUP、CUBE 或 GROUPING SETS。

整合列分组有过滤有个别小计或总计一些非常的小计的遵守。后面包车型地铁某些 ROLLUP 和 部分 CUBE 都不曾左券,使用组合列分组不仅能够完结部分 ROLLUP 或 部分 CUBE 的效应,还是能有协商。如ROLLUP(A,(B,C)),不仅能过滤 B、C 的小计,还是能够臆度ABC 的磋商。

连年分组的分组品级是由具备 ROLLUP、CUBE 或 GROUPING SETS 分组的等第构成的笛Carl积。如ROLLUP(A,B),ROLLUP(C,D,E)的分组等第是 (2 1)×(3 1)=12,CUBE(A,B),CUBE(C,D,E)的分组等级是 (4)×(8)=32,CUBE(A,B),CUBE(C,D,E)的分组等第是 (4)×(8)=32。

按本人的精晓的话:复杂分组无非也就是对标准扩张分组的综合运用。在实质上开垦中,恐怕会遇上某些仅使用职业扩大分组无法兑现的须求,这时候就足以考虑灵活的运用专门的职业扩充分组,通过复杂分组来兑现。

3.1、GROUPING 函数

GROUPING 语法:GROUPING(expr)。因为原来数据中恐怕存在 NULL,且小计或协商的值也说不定为 NULL,那样一来就展现数据相比较散乱了。当该函数出现在 SELECT 子句中时,假使聚焦行的 expr 为 NULL,它就回去 1;假若常规行的 expr 为 NULL,它就赶回 0。平常将三个分组列作为该函数的参数,然后经过判别它的重返值来分裂聚焦行与常规行,进而越发对结果集美化或过滤。示例:

SELECT DECODE(GROUPING(t.dept_code),1,'合计',t.dept_code) dept_code,
  SUM(t.post_salary) sum_salary,GROUPING(t.dept_code) gd
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code);

结果:

DEPT_CODE                                          SUM_SALARY         GD
-------------------------------------------------- ---------- ----------
010102                                                  13500          0
010103                                                   7850          0
合计                                                    21350          1
  • 1、GROUP BY 标准分组
    • 1.1、GROUP BY 概述
    • 1.2、WHERE 和 HAVING 的区别?
  • 2、GROUP BY 扩张分组
    • 2.1、ROLLUP 分组
    • 2.2、CUBE 分组
    • 2.3、GROUPING SETS 分组
    • 2.4、复杂分组(组合列分组、重复列分组、连接分组)
  • 3、GROUP BY 增添函数
    • 3.1、GROUPING 函数
    • 3.2、GROUPING_ID 函数
    • 3.3、GROUP_ID 函数
  • 4、总结

3、GROUP BY 扩张函数

2、GROUP BY 增添分组

在实质上中国人民解放军海军事工业程大学业作支出中,独有规范分组只怕还相当不足,往往还亟需越来越多维度的小计、合计。针对那类须求,Oracle 提供了拉长的扩大分组功用;固然用 UNION ALL 平时也能促成类似功能,但相当不足灵活且质量十分低。

版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:介绍一种蛮好用汇总量据的法子GROUPING SETS【彩民