思享工具箱

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE

摘要：
GROUPINGSETS用于一次读取所有数据并将其聚合到内存中以生成结果，从而减少IO开销并增加CPU和内存消耗。但是，在对多个列进行分组时，GROUPINGSETS的性能优于groupby。这里进行四次扫描的原因是，I GROUPBYGROUPINGSETS有四列ROLLUP和CUBEROLLUP以及CUBE，以根据特定规则生成多个组，然后根据各种分组统计数据在ROLLUP与CUBE之间进行区别：CUBE将统计所有分组字段，然后将它们相加。

1.创建表 Staff

CREATE TABLE [dbo].[Staff](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
    [Sex] [varchar](50) NULL,
    [Department] [varchar](50) NULL,
    [Money] [int] NULL,
    [CreateDate] [datetime] NULL
) ON [PRIMARY]

GO

2.为Staff表填充数据

INSERT INTO [dbo].[Staff]([Name],[Sex],[Department],[Money],[CreateDate])
SELECT 'Name1','男','技术部',3000,'2011-11-12'
UNION ALL
SELECT 'Name2','男','工程部',4000,'2013-11-12'
UNION ALL
SELECT 'Name3','女','工程部',3000,'2013-11-12'
UNION ALL
SELECT 'Name4','女','技术部',5000,'2012-11-12'
UNION ALL
SELECT 'Name5','女','技术部',6000,'2011-11-12'
UNION ALL
SELECT 'Name6','女','技术部',4000,'2013-11-12'
UNION ALL
SELECT 'Name7','女','技术部',5000,'2012-11-12'
UNION ALL
SELECT 'Name8','男','工程部',3000,'2012-11-12'
UNION ALL
SELECT 'Name9','男','工程部',6000,'2011-11-12'
UNION ALL
SELECT 'Name10','男','工程部',3000,'2011-11-12'
UNION ALL
SELECT 'Name11','男','技术部',3000,'2011-11-12'

GROUP BY 分组查询, 一般和聚合函数配合使用

SELECT  [DEPARTMENT],SEX, COUNT(1)
FROM DBO.[STAFF] 
GROUP BY SEX, [DEPARTMENT]

该段SQL是用于查询   某个部门下的男女员工数量 其数据结果如下
SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第1张

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第2张

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第3张

开销比较大

GROUPING SETS

使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集，并且其效率比 GROUP BY 要高，SQL Server 2008引入。

1.使用GROUP BY 子句的 UNION ALL 来统计 Staff 表中的性别、部门、薪资、入职年份

SET STATISTICS IO ON  
SET STATISTICS TIME ON

SELECT N'总人数' ,'',COUNT(0) FROM [DBO].[STAFF]
UNION ALL  
SELECT N'按性别划分', SEX,COUNT(0) FROM  [DBO].[STAFF] GROUP BY SEX  
UNION ALL  
SELECT N'按部门统计',[DEPARTMENT],COUNT(0) FROM  [DBO].[STAFF] GROUP BY [DEPARTMENT]  
UNION ALL  
SELECT N'按薪资统计',CONVERT(VARCHAR(10),[MONEY]),COUNT(0) FROM  [DBO].[STAFF] GROUP BY  [MONEY] 
UNION ALL  
SELECT N'按入职年份',CONVERT(VARCHAR(10),YEAR([CREATEDATE])),COUNT(0) FROM  [DBO].[STAFF] GROUP BY YEAR([CREATEDATE])

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第4张

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第5张
SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第6张

2.换成GROUPING SETS的写法

SET STATISTICS IO ON  
SET STATISTICS TIME ON  
GO
SELECT (CASE  
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=15 THEN N'总人数' 
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=7 THEN N'按性别划分'  
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=11 THEN N'按部门统计'  
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=13 THEN N'按薪资统计'   
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=14 THEN N'按入职年份'   
END  
),
(CASE  
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=15 THEN ''
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=7 THEN SEX  
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=11 THEN [DEPARTMENT]  
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=13 THEN CONVERT(VARCHAR(10),[MONEY])   
WHEN GROUPING_ID(SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]))=14 THEN CONVERT(VARCHAR(10),YEAR([CREATEDATE]))   
END  
) 
,
COUNT(1) 
FROM DBO.[STAFF]
GROUP BY GROUPING SETS (SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]),())

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第7张

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第8张

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第9张

从上述结果中可以看出,采用UNION ALL 是多次扫描表，并将扫描后的查询结果进行组合操作，会增加IO开销，减少CPU和内存开销。

采用GROUPING SETS 是一次性读取所有数据，并在内存中进行聚合操作生成结果，减少IO开销，对CPU和内存消耗增加。但GROUPING SETS 在多列分组时，其性能会比group by高。

这里扫描四次是因为我 GROUP BY GROUPING SETS (SEX,[DEPARTMENT],[MONEY],YEAR([CREATEDATE]),()) 了四列

ROLLUP与CUBE 

ROLLUP与CUBE  按一定的规则产生多种分组，然后按各种分组统计数据

ROLLUP与CUBE 区别：

　　CUBE 会对所有的分组字段进行统计,然后合计。

　　ROLLUP 按照分组顺序，对第一个字段进行组内统计，最后给出合计。
 
下面看我查询 
SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-ROLLUP' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX ,
        COUNT(0)
FROM DBO.[STAFF] 
GROUP BY   SEX   WITH ROLLUP

SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-CUBE' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX ,
        COUNT(0)
FROM DBO.[STAFF] 
GROUP BY   SEX   WITH CUBE

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第10张

看不出差别，我们再加一列
SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-ROLLUP' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX , 
      CASE WHEN (GROUPING([DEPARTMENT]) = 1) THEN '统计-ROLLUP' 
            ELSE ISNULL([DEPARTMENT], 'UNKNOWN') 
       END AS [DEPARTMENT], 
        COUNT(0) 
FROM DBO.[STAFF] 
GROUP BY   SEX,[DEPARTMENT]   WITH ROLLUP

SELECT  
      CASE WHEN (GROUPING(SEX) = 1) THEN '统计-CUBE' 
            ELSE ISNULL(SEX, 'UNKNOWN') 
       END AS SEX ,
      CASE WHEN (GROUPING([DEPARTMENT]) = 1) THEN  '统计-CUBE' 
            ELSE ISNULL([DEPARTMENT], 'UNKNOWN') 
       END AS [DEPARTMENT], 
        COUNT(0) 
FROM DBO.[STAFF] 
GROUP BY   SEX,[DEPARTMENT]  WITH CUBE

SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE第11张

可以看出 使用 ROLLUP 会先统计分组下的，然后在对GROUP BY的第一列字段进行统计，最后计算总数，而 CUBE 则是先分组统计，然后统计GRUOP BY 的每个字段，最后进行汇总。

 http://www.cnblogs.com/woxpp/p/4688715.html 

标签：#select#varchar#cube | 浏览：183 | 发布日期：

免责声明：文章转载自《SQL Server 之 GROUP BY、GROUPING SETS、ROLLUP、CUBE》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇003 数电 (3) : 门电路Linux中RTX和QQ安装下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

influxdb基本SQL操作2

influxdb0.13命令  1、数据构成 INSERT cpu_load_short,host=server01,region=us-west value=0.64,value2=0.86 1434055562000000000 第一部分：“cpu_load_short,host=server01,region=us-west” 第一部分称为key，...

oracle11g dataguard部署指南

一、Oracle11DB+DG配置 1. 单机环境介绍(PRIMARY DATABASE)主库primary public ip 192.168.0.252instanceoracledb_name oraclestorage mode /orasjrz/crds3db/oradata /orasjrz/crds3db/oraarch 2. 单机环境介绍（...

【转贴收藏】SQL常用语句，含查看数据库日志

--//清空日志checkpoint--//查看数据库日志select * from ::fn_dblog(null,null)select * from ::fn_dblog(null,null) where [current lsn]>='000001ae:00000074:0001' and [current lsn]<= '00000...

mysql修改definer方法

-- 函数、存储过程 select definer from mysql.proc; update mysql.proc set definer='billing@%'; -- 定时事件 select DEFINER from mysql.EVENTS; update mysql.EVENTS set definer='billing@%'; --视图 s...

把存储过程SELECT INTO到临时表

在开发过程中，很多时候要把结果集存放到临时表中，常用的方法有两种。 一. SELECT INTO1. 使用select into会自动生成临时表，不需要事先创建12 select * into #temp from sysobjectsselect * from #temp 2. 如果当前会话中，已存在同名的临时表 1 select * into #tem...

sqlite3应用

1. 安装sqlite3 sudo apt-get install sqlite3 sudo apt-get install libsqlite3-dev 2. sqlite常用命令 当前目录下建立或打开test.db数据库文件，并进入sqlite命令终端，以sqlite>前缀标识： #sqlite3 test.db 查看数据库文件信息命令(注意命令...

最新文章

随机推荐

思享工具箱导航