一、聚合函数
用于汇总的函数称为聚合函数或者聚集函数, 本书中统称为聚合函数。所谓聚合,就是将多行汇总为一行。实际上,所有的聚合函数都是这样,输入多行输出一行。常见的聚合函数有:count(),sum(),min(),sum(),avg()
-- 看看这个表共有多少行
SELECT COUNT(*) FROM Product;
常见的聚合函数有以下五个:
函数名称 | 作用 |
---|---|
count() | 对某一列进行计数 |
max() | 求某一列的最大值 |
min() | 求某一列的最小值 |
sum() | 求某一列的和 |
avg() | 求某一列的平均值 |
这些函数的定义和平常的定义并无区别。但需要注意的是单元格中存在 null 的值,所以搞清楚聚合函数在处理含null 的列时的表现很重要,来看一个例子:
首先先放出使用的表:
select count(purchase_price),count(*) from product;
可以看出当使用count(*)时,查询的是所有的行数,但是如果跟的参数是某个列,查询的其实是不是null 的行数。
再看一个例子:
select avg(purchase_price),sum(purchase_price)/count(purchase_price) ,sum(purchase_price)/count(*)
from product;
可以看出,前两种表达式算出来的平均价格是一样的,也就是无论什么函数对于 null 值的数据都会自动忽略。
需要说明的是 前面我们说到所有数据类型的数据都可以进行排序,但是只有数值型和日期型数据可以进行算术运算,所以这里的max,min 函数是所有数据类型都可以使用的,但是对于字符串型数据只能使用max,min,但是对于日期型数据,似乎求和和求平均值也没有什么意义。
- distinct关键字的应用
在之前进行简单查询的时候,用过这个关键字,用在列名之前,会把重复的列名去除,在聚合函数中仍然可以使用。
SELECT COUNT(DISTINCT product_type) FROM Product;
这里需要注意一下逻辑顺序,应该是先去重再计数。如果代码进行以下修改
SELECT DISTINCT COUNT(product_type) FROM Product;
可以发现结果是很不同的,原因在于下面的命令是先计数再去重,但其实在完成计数后就是上面这一列,根本就不存在去不去重了。
其实不仅仅是count函数,别的聚合函数也可以使用distinct先去重,比如:
select sum(sale_price),sum(distinct sale_price) from product;
可以发现结果也是发生了变化,结果和上面说的相同。
二、分组
目前为止,我们看到的聚合函数的使用方法,无论是否包含 NULL,无论是否删除重复数据,都是针对表中的所有数据进行的汇总处理。下面,我们先把表分成几组,然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。
SELECT product_type, COUNT(*) FROM Product
GROUP BY product_type;
未使用 GROUP BY 子句时,结果只有 1 行,而这次的结果却是多行。这是因为不使用 GROUP BY 子句时,是将表中的所有数据作为一组来对待的。而使用 GROUP BY 子句时,会将表中的数据分为多个组进行处理。
打个比方,group by 子句就像是把整个表切成了几小份。group by 后面跟的列名也叫聚合键。
GROUP BY 子句的书写位置也有严格要求,一定要写在FROM 语句之后(如果有 WHERE 子句的话需要写在 WHERE 子句之后),顺序如下:select->from>where>group by。
当聚合键中含有null 数据时,会单独把null 作为一组,其实也就是不确定值。
SELECT regist_date , COUNT(*) FROM Product GROUP BY regist_date ;
上面说到group by 是跟在where子句后面的,来看一下where子句起到了什么作用
SELECT purchase_price, COUNT(*)
FROM Product
-- WHERE product_type = '衣服'
GROUP BY purchase_price;
这是不加where 过滤条件的,那如果加了where的过滤条件呢?
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
可以发现显示的行数变少了,原因是先执行了where的过滤子句,将数据过滤成只有两行,分组分了个寂寞。
所以可以看出,现在的执行顺序是,from->where->group by ->select.
在这一小节,在书写sql语句的过程中很容易发生的一些错误在于
- select 语句中含有多余的列,select 后面的跟的只能是聚合函数和聚合键
- group by 后面跟的列名是别名,虽然在select 语句中可以为列名指定别名,但是不要忘记了sql 执行的顺序是最后执行select 语句,所以在执行group by 语句时,还没有这个别名。再有一点,select 语句设置别名也只是显示在最后查询的表上,并没有改变原来的表的列名,但是group by 后面跟的列名仍然是原来表中的列名,因此不可在group by 中使用别名。
- group by 中显示的顺序是随机的,除非使用order by 子句指定
- 在where子句中使用聚合函数,来看下面一个例子
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
如果这时候我们想把数量为2的商品挑出来,能不能使用下面的代码呢?
ELECT product_type, COUNT(*) FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;
这一看好像是可以的,但其实不可以,会报错。其实分析一下执行顺序就行,我们知道先执行where子句,但是其实这时候还没有进行分组,那你count(*)得到的就是总函数,总函数又不会等于2,那么这时候得不到什么结果。
一个思想就是先分组,在去看哪个组的计数为2.这也就引出了下面的having子句。
SELECT product_type, COUNT(*) FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
这个就达到了我们的预期,可以看出,having子句是接在group by 后面的,就是在group by 分组后,再进行筛选。这时我们的语句顺序为 select->from ->where->group by ->having ,但是执行顺序确是:from ->where->group by ->having->select,
having 后面跟的也不是随意的,和select 语句一样,后面只能有聚合函数和聚合键,其实也很好理解,是先执行group by 语句,这时候的表中只有聚合函数和聚合键,这时候用别的列名来筛选肯定是不行的。
我们知道有些条件写在where中,但这些条件写在having中其实得到的结果相同,但一般还是写在having 中,其实从执行顺序上可以看出,where是对原始的表的行进行过滤,而having 是对分完后的组进行过滤,所以一般聚合键所对应的条件是扔在where中,而聚合函数的结果的筛选是扔在having中。
三、排序
到现在为止,我们讲了查询的过滤,分组,以及分组后再过滤,在分组的过程中,我们说到了数据显示的顺序其实是随机的,那如果要按照一定的顺序来排列,比如说,按照总分最多来对学生进行排序,诸如此类。
这时候我们可以添加order by 子句
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
这里并没有使用group by 子句,而是对整张表进行了排序。
不论何种情况, ORDER BY 子句都需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。 ORDER BY子句中书写的列名称为排序键。
.SELECT → FROM → WHERE → GROUP BY →HAVING → ORDER BY ,
当然,我们知道,排序也分顺序和降序 asc 和desc,默认都是按照升序排序,如果按降序排列,可使用下面的代码:
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;
对于排序键值相同的两行又该有什么顺序呢,其实我们可以给order by 指定多个排序键,比如
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;
这就表示,先按照sales_price排序,再按照product_id 排序,按规定是先用最左边的键排序,在改键相同时才会使用后面的键继续排序,也就是排序键之间也有优先级,那么对于多个排序键也是同样的道理。
那对于多个键排序如果要指定升序或降序的话,需要在每个键的后面加asc,或者时desc,比如
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price asc, product_id desc;
在前面讲运算符时讲到null 数据类型时,其实它是不参与运算的,那排序时遇到null 其实也是不参与的,一般都是放在开头或者末尾,但时究竟是开头还是末尾,根据软件的不同有所差异。
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;
前面我们说到group by 中不允许使用别名,因为在执行group by子句时还没有执行select 语句,这时候没有别名,那么在order by 中能不能使用别名呢?答案是可以的,为什么呢?在开头我们说了,order by 是在显示之前进行的排序,也就是,order by 是在select 语句执行之后,那么这时候已经有别名了,所以可以使用别名。
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
前面说到 having 和select 如果遇到了group by 必须使用 聚合键,但是order by可以,比如下面
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;
这是很有意思的一点,order by 调用了select 以外列的信息,甚至order by 中还可以和group by 以及聚合函数结合起来。
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);
总结
在这小节,我们讲完了聚合查询,很重要的是5个聚合函数以及有关null 的注意点,对表的分组,分组后再过滤的having 子句,having子句和where子句的区别,以及在select 执行后的order by 排序。