GROUP BY语句总结

GROUP BY语句从英文的字面意义上理解就是“根据(By)一定的规则进行分组(Group)”

作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理

在介绍GROUP BY语句之前,首先需要介绍和它关系密切的小伙伴——聚合函数


聚合函数是对多值数据执行计算并返回单值的函数

以下是常用的聚合函数:

1. AVG——返回平均值,其中空值被忽略

例: select  dept_no, avg(sal) from table group by dept_no   //统计不同部门的平均工资

2.COUNT——返回数量

例: select count(name) from table    //统计公司员工总数

count(字段名)与count(*)的区别:

如果字段名中包含空值NULL,那么count(字段名)会忽略该空值,而count(*)不会忽略,依然将其计入总数

例: 

dept_1 dept_2

 A             D

 B           null

 C            E

由于dept_2 中有 null 值,用 count(dept_2) 的结果就是 2,用 count(*) 的结果就是 3

3. MAX——返回最大值

例: select max(sal) from table   //查找公司的最高工资

4. MIN——返回最大值

例: select min(sal) from table   //查找公司的最低工资

5.SUM——返回和

例: select sum(sal) from table   //统计公司工资总额


GROUP BY + [分组字段]

其中分组字段可以有多个。在执行了这个操作以后,数据集将根据分组字段的值将一个数据集划分成各个不同的小组。

比如有如下数据集(Table_Fruitinfo),其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键:


Table_Fruitinfo

如果我们想知道每个国家有多少种水果,那么我们可以通过如下SQL语句来完成:

SELECT COUNT(*) AS 水果种类, ProductPlaceAS 出产国

FROM Table_Fruitinfo

GROUP BY ProductPlace

这句SQL语句可以解释成“我按照出产国家(ProductPlace)将数据集进行分组,然后分别统计各个国家的水果种类数”。

注意:如果我们这里水果种类不是用Count(*),而是类似如下写法的话:

SELECT FruitName, ProductPlace

FROM Table_Fruitinfo

GROUP BY ProductPlace

那么SQL在执行此语句的时候会报如下的类似错误:

选择列表中的列'Table_Fruitinfo.FruitName'无效,因为该列没有包含在聚合函数或GROUPBY子句中

这就是我们需要注意的一点,使用GROUP BY语句时,返回集中的非聚合字段要么包含在Group By语句的后面,作为分组的依据;要么就要包含在聚合函数中。

我们可以将GROUP BY操作想象成如下的一个过程:

首先通过SELECT语句得到一个结果集,然后根据分组字段,将具有相同分组字段的记录归并成了一条记录。这时候那些不作为分组依据的字段就有可能出现多个值(非聚合字段),但是一种分组情况只能有一条记录,而一个数据格是无法放入多个数值的,所以就需要通过一定的处理(聚合函数)将这些多值的列转化成单值,然后放入对应的数据格中。

GROUP BY ALL + [分组字段]

在不使用 ALL 关键字的情况下,包含 GROUP BY 子句的 SELECT 语句查找的结果只会显示满足搜索条件的记录。而使用 ALL 关键字,即使某些记录不满足搜索条件,查询结果也将显示 GROUP BY 子句生成的所有组,只是这些不满足搜索条件的记录不会进行真正的统计,而是用默认值0或者NULL来代替聚合函数的返回值。说得有点绕,举个例子吧。

还是使用之前的水果信息数据集

Table_Fruitinfo

首先我们不使用带ALL关键字的Group By语句:

SELECT COUNT(*) AS 水果种类, ProductPlace AS 出产国

FROM Table_Fruitinfo

WHERE (ProductPlace <> 'Japan')

GROUP BY ProductPlace

操作符 <> 表示  “不等于”

那么在最后结果中由于Japan不符合where语句,所以分组结果中将不会出现Japan。

现在我们加入ALL关键字:

SELECT COUNT(*) AS 水果种类, ProductPlace AS 出产国

FROM Table_Fruitinfo

WHERE ( ProductPlace <> 'Japan')

GROUP BY ALL ProductPlace

重新运行后,我们可以看到Japan的分组,但是对应的“水果种类”不会进行真正的统计,聚合函数会根据返回值的类型用默认值0或者NULL来代替聚合函数的返回值。

GROUP BY + [分组字段] WITH CUBE | ROLL UP

GROUP BY ALL语句不能和CUBE / ROLL UP关键字一起使用

CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。SELECT语句后跟所查找的维度列聚合函数。GROUP BY 后跟所查找的维度列关键字 WITH CUBE

结果集包含维度列中各值的所有可能组合(笛卡尔积),以及与这些维度值组合相匹配的基础行中的聚合值

例:

Inventory

以下查询将返回一个结果集,其中包含Item和Color的所有可能组合的Quantity小计:

SELECT Item, Color, SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH CUBE

以下是结果集:

结果集

可以看到,CUBE将维度列Item和Color的所有可能属性(包括null)进行组合,形成了3*3=9条不同的记录,每条记录都包含着一个小计QtySum

但是,CUBE 操作生成空值将会带来一个问题:如何区分 CUBE 操作生成的 NULL 值和在实际数据中返回的 NULL 值?

可以使用 GROUPING 函数解决此问题

如果列值来自真实数据(未知数据),GROUPING 函数将返回 0;如果列值是由 CUBE 操作生成的 NULL,则返回 1。

在 CUBE 操作中,生成的 NULL 代表所有值,所以可以将CUBE操作生成的任一 NULL 替换为字符串 ALL

真实数据中的 NULL 表示数据值未知,所以可以将真实数据中的 NULL替换为字符串 UNKNOWN

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

                         ELSE ISNULL(Item, 'UNKNOWN')

              END AS Item,

             CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

                        ELSE ISNULL(Color, 'UNKNOWN')

             END AS Color,

             SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH CUBE

ISNULL:使用指定的替换值替换 NULL。

语法:ISNULL ( check_expression , replacement_value )

如果 check_expression 不为 NULL,那么返回该表达式的值;否则返回 replacement_value。

包含具有多个维度的 CUBE 的 SELECT 语句可生成大型结果集,因为这些语句会为所有维度中各值的所有组合都生成相应的行。这些大型结果集包含的数据可能会过多而不易于阅读和理解。此问题的一种解决办法是将SELECT语句放入视图(VIEW)中:

数据库中的数据都是存储在表中的,而视图只是一个或多个表依照某个条件组合而成的结果集

一般来说可以用UPDATE,INSERT,DELETE等sql语句修改表中的数据,而对视图只能进行SELECT操作

CREATE VIEW InvCube AS

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

                          ELSE ISNULL(Item, 'UNKNOWN')

               END AS Item,

              CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

                         ELSE ISNULL(Color, 'UNKNOWN')

              END AS Color,

              SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH CUBE

然后即可用该视图来仅查询您感兴趣的维度值:

SELECT *

FROM InvCube

WHERE Item = 'Chair'

AND Color = 'ALL'

以下是结果集:

结果集

参考链接:使用 CUBE 汇总数据

参考链接:使用 ROLLUP 汇总数据

由于暂时没有理解CUBE与ROLLUP有什么实质性区别,所以先挖个坑,以后有机会再填

推荐阅读更多精彩内容