SQL 对表进行聚合查询

学习重点

  • 使用聚合函数对表中的列进行计算合计值或者平均值等的汇总操作。

  • 通常,聚合函数会对 NULL 以外的对象进行汇总。但是只有 COUNT 函数例外,使用 COUNT(*) 可以查出包含 NULL 在内的全部数据的行数。

  • 使用 DISTINCT 关键字删除重复值。

一、聚合函数#

通过 SQL 对数据进行某种操作或计算时需要使用函数。例如,计算表中全部数据的行数时,可以使用 COUNT 函数。该函数就是使用 COUNT(计数)来命名的。除此之外,SQL 中还有很多其他用于汇总的函数,请大家先记住以下 5 个常用的函数。

KEYWORD

  • 函数

  • COUNT 函数

COUNT:计算表中的记录数(行数)

SUM:计算表中数值列中数据的合计值

AVG:计算表中数值列中数据的平均值

MAX:求出表中任意列中数据的最大值

MIN:求出表中任意列中数据的最小值

如上所示,用于汇总的函数称为聚合函数或者聚集函数,本教程中统称为聚合函数。所谓聚合,就是将多行汇总为一行。实际上,所有的聚合函数都是这样,输入多行输出一行。

KEYWORD

  • 聚合函数

  • 聚集函数

  • 聚合

接下来,本文将继续使用在 表的创建 中创建的 Product 表(图 1)来学习函数的使用方法。

Product 表的内容

图 1 Product 表的内容

二、计算表中数据的行数#

首先,我们以 COUNT 函数为例让大家对函数形成一个初步印象。函数这个词,与我们在学校数学课上学到的意思是一样的,就像是输入某个值就能输出相应结果的盒子一样 1

使用 COUNT 函数时,输入表的列,就能够输出数据行数。如图 2 所示,将表中的列放入名称为 COUNT 的盒子中,咔嗒咔嗒地进行计算,咕咚一下行数就出来了……就像自动售货机那样,很容易理解吧。

COUNT 函数的操作演示图

图 2 COUNT 函数的操作演示图

接下来让我们看一下 SQL 中的具体书写方法。COUNT 函数的语法本身非常简单,像代码清单 1 那样写在 SELECT 子句中就可以得到表中全部数据的行数了。

代码清单 1 计算全部数据的行数

计算全部数据的行数

执行结果

计算全部数据的行数执行结果

COUNT() 中的星号,我们在 SELECT 语句基础 中已经学过,代表全部列的意思。COUNT 函数的输入值就记述在其后的括号中。

此处的输入值称为参数或者 parameter,输出值称为返回值。这些称谓不仅本教程中会使用,在多数编程语言中使用函数时都会频繁出现,请大家牢记。

KEYWORD

  • 参数(parameter)

  • 返回值

三、计算 NULL 之外的数据的行数#

想要计算表中全部数据的行数时,可以像 SELECT COUNT(*)~ 这样使用星号。如果想得到 purchase_price 列(进货单价)中非空行数的话,可以像代码清单 2 那样,通过将对象列设定为参数来实现。

代码清单 2 计算 NULL 之外的数据行数

1
2
SELECT COUNT(purchase_price)
  FROM Product;

执行结果

count
-------
    6

此时,如图 1 所示,purchase_price 列中有两行数据是 NULL,因此并不应该计算这两行。对于 COUNT 函数来说,参数列不同计算的结果也会发生变化,这一点请大家特别注意。为了有助于大家理解,请看如下这个只包含 NULL 的表的极端例子。

只包含 NULL 的表

图 3 只包含 NULL 的表

我们来看一下针对上述表,将星号(*)和列名作为参数传递给 COUNT 函数时所得到的结果(代码清单 3)。

代码清单 3 将包含 NULL 的列作为参数时,COUNT(*)COUNT(<列名>) 的结果并不相同

1
2
SELECT COUNT(*), COUNT(col_1)
  FROM NullTbl;

执行结果

将包含 NULL 的列作为参数时,COUNT(*) 和 COUNT(&lt;列名&gt;) 的结果并不相同

如上所示,即使对同一个表使用 COUNT 函数,输入的参数不同得到的结果也会不同。由于将列名作为参数时会得到 NULL 之外的数据行数,所以得到的结果是 0 行。

该特性是 COUNT 函数所特有的,其他函数并不能将星号作为参数(如果使用星号会出错)。

法则 1

COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数,而 COUNT(<列名>) 会得到 NULL 之外的数据行数。

四、计算合计值#

接下来我们学习其他 4 个聚合函数的使用方法。这些函数的语法基本上与 COUNT 函数相同,但就像我们此前所说的那样,在这些函数中不能使用星号作为参数。

首先,我们使用计算合计值的 SUM 函数,求出销售单价的合计值(代码清单 4)。

KEYWORD

  • SUM 函数

代码清单 4 计算销售单价的合计值

1
2
SELECT SUM(sale_price)
  FROM Product;

执行结果

  sum
------
 16780

得到的结果 16780 日元,是所有销售单价(sale_price 列)的合计,与下述计算式的结果相同。

计算销售单价的合计值

接下来,我们将销售单价和进货单价(purchase_price 列)的合计值一起计算出来(代码清单 5)。

代码清单 5 计算销售单价和进货单价的合计值

1
2
SELECT SUM(sale_price), SUM(purchase_price)
  FROM Product;

执行结果

计算销售单价和进货单价的合计值执行结果

这次我们通过 SUM(purchase_price) 将进货单价的合计值也一起计算出来了,但有一点需要大家注意。具体的计算过程如下所示。

计算销售单价的合计值

大家都已经注意到了吧,与销售单价不同,进货单价中有两条不明数据 NULL。对于 SUM 函数来说,即使包含 NULL,也可以计算出合计值。还记得 算术运算符和比较运算符 中内容的读者可能会产生如下疑问。

“四则运算中如果存在 NULL,结果一定是 NULL,那此时进货单价的合计值会不会也是 NULL 呢?”

有这样疑问的读者思维很敏锐,但实际上这两者并不矛盾。从结果上说,所有的聚合函数,如果以列名为参数,那么在计算之前就已经把 NULL 排除在外了。因此,无论有多少个 NULL 都会被无视。这与“等价为 0”并不相同 2

因此,上述进货单价的计算表达式,实际上应该如下所示。

计算销售单价的合计值

法则 2

聚合函数会将 NULL 排除在外。但 COUNT(*) 例外,并不会排除 NULL

五、计算平均值#

接下来,我们练习一下计算多行数据的平均值。为此,我们需要使用 AVG 函数,其语法和 SUM 函数完全相同(代码清单 6)。

KEYWORD

  • AVG 函数

代码清单 6 计算销售单价的平均值

1
2
SELECT AVG(sale_price)
  FROM Product;

执行结果

         avg
----------------------
2097.5000000000000000

平均值的计算式如下所示。

平均值的计算式

( 值的合计 )/( 值的个数 ) 就是平均值的计算公式了。下面我们也像使用 SUM 函数那样,计算一下包含 NULL 的进货单价的平均值(代码清单 7)。

代码清单 7 计算销售单价和进货单价的平均值

1
2
SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;

执行结果

计算销售单价和进货单价的平均值

计算进货单价平均值的情况与 SUM 函数相同,会事先删除 NULL 再进行计算,因此计算式如下所示。

计算进货单价平均值的情况与 SUM 函数相同,会事先删除 NULL 再进行计算

需要注意的是分母是 6 而不是 8,减少的两个也就是那两条 NULL 的数据。

但是有时也想将 NULL 作为 0 进行计算。

将 NULL 作为 0 进行计算

六、计算最大值和最小值#

想要计算出多条记录中的最大值或最小值,可以分别使用 MAXMIN 函数,它们是英语 maximam(最大值)和 minimum(最小值)的缩写,很容易记住。

KEYWORD

  • MAX 函数

  • MIN 函数

这两个函数的语法与 SUM 的语法相同,使用时需要将列作为参数(代码清单 8)。

代码清单 8 计算销售单价的最大值和进货单价的最小值

1
2
SELECT MAX(sale_price), MIN(purchase_price)
  FROM Product;

执行结果

计算销售单价的最大值和进货单价的最小值

如图 3 所示,我们取得了相应的最大值和最小值。

但是,MAX/MIN 函数和 SUM/AVG 函数有一点不同,那就是 SUM/AVG 函数只能对数值类型的列使用,而 MAX/MIN 函数原则上可以适用于任何数据类型的列。例如,对图 1 中日期类型的列 regist_date 使用 MAX/MIN 函数进行计算的结果如下所示(代码清单 9)。

代码清单 9 计算登记日期的最大值和最小值

1
2
SELECT MAX(regist_date), MIN(regist_date)
  FROM Product;

执行结果

计算登记日期的最大值和最小值

刚刚我们说过 MAX/MIN 函数适用于任何数据类型的列,也就是说,只要是能够排序的数据,就肯定有最大值和最小值,也就能够使用这两个函数。对日期来说,平均值和合计值并没有什么实际意义,因此不能使用 SUM/AVG 函数。这点对于字符串类型的数据也适用,字符串类型的数据能够使用 MAX/MIN 函数,但不能使用 SUM/AVG 函数。

法则 3

MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。

七、使用聚合函数删除重复值(关键字 DISTINCT#

接下来我们考虑一下下面这种情况。

在图 1 中我们可以看到,商品种类(product_type 列)和销售单价(sale_price 列)的数据中,存在多行数据相同的情况。

例如,拿商品种类来说,表中总共有 3 种商品共 8 行数据,其中衣服 2 行,办公用品 2 行,厨房用具 4 行。如果想要计算出商品种类的个数,怎么做比较好呢?删除重复数据然后再计算数据行数似乎是个不错的办法。实际上,在使用 COUNT 函数时,将 SELECT 语句基础 中介绍过的 DISTINCT 关键字作为参数,就能得到我们想要的结果了(代码清单 10)。

KEYWORD

  • DISTINCT 关键字

代码清单 10 计算去除重复数据后的数据行数

1
2
SELECT COUNT(DISTINCT product_type)
  FROM Product;

执行结果

 count
-------
     3

请注意,这时 DISTINCT 必须写在括号中。这是因为必须要在计算行数之前删除 product_type 列中的重复数据。如果像代码清单 11 那样写在括号外的话,就会先计算出数据行数,然后再删除重复数据,结果就得到了 product_type 列的所有行数(也就是 8)。

代码清单 11 先计算数据行数再删除重复数据的结果

1
2
SELECT DISTINCT COUNT(product_type)
  FROM Product;

执行结果

 count
-------
     8

法则 4

想要计算值的种类时,可以在 COUNT 函数的参数中使用 DISTINCT

不仅限于 COUNT 函数,所有的聚合函数都可以使用 DISTINCT。下面我们来看一下使用 DISTINCT 和不使用 DISTINCTSUM 函数的执行结果(代码清单 12)。

代码清单 12 使不使用 DISTINCT 时的动作差异(SUM 函数)

1
2
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
  FROM Product;

执行结果

使不使用 DISTINCT 时的动作差异

左侧是未使用 DISTINCT 时的合计值,和我们之前计算的结果相同,都是 16780 日元。右侧是使用 DISTINCT 后的合计值,比之前的结果少了 500 日元。这是因为表中销售单价为 500 日元的商品有两种——“打孔器”和“叉子”,在删除重复数据之后,计算对象就只剩下一条记录了。

法则 5

在聚合函数的参数中使用 DISTINCT,可以删除重复数据。

请参阅#

(完)


  1. 函数中的函就是盒子的意思。 ↩︎

  2. 虽然使用 SUM 函数时,“将 NULL 除外”和“等同于 0”的结果相同,但使用 AVG 函数时,这两种情况的结果就完全不同了。接下来我们会详细介绍在 AVG 函数中使用包含 NULL 的列作为参数的例子。 ↩︎

comments powered by Disqus