网站开发 公司 深圳,北京赛车网站开发,jsp旅游网站开发系统,四川网站建设设计公司哪家好汇总分析是数据报表中的基本功能#xff0c;例如产品销售金额的汇总、学生的平均身高和标准差统计等。SQL 定义了聚合函数#xff0c;可以实现数据的汇总分析。
本文比较五种主流数据库支持的常见聚合函数#xff0c;包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLi…汇总分析是数据报表中的基本功能例如产品销售金额的汇总、学生的平均身高和标准差统计等。SQL 定义了聚合函数可以实现数据的汇总分析。
本文比较五种主流数据库支持的常见聚合函数包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
聚合函数函数功能MySQLOracleSQL ServerPostgreSQLSQLiteCOUNT()返回查询结果或者表中的行数✔️✔️✔️✔️✔️AVG()计算一组数据的平均值✔️✔️✔️✔️✔️SUM()计算一组数值的总和✔️✔️✔️✔️✔️MAX()返回一组数据中的最大值✔️✔️✔️✔️✔️MIN()返回一组数据中的最小值✔️✔️✔️✔️✔️LISTAGG()将一组字符串合并成一个字符串GROUP_CONCAT()✔️STRING_AGG()STRING_AGG()GROUP_CONCAT()
除 LISTAGG 函数外以上聚合函数在 5 种主流数据库中的实现一致。
我们在使用聚合函数时需要注意两点
聚合函数的参数支持 DISTINCT 关键字表示在计算之前排除重复数据。聚合函数在计算时忽略数据中的 NULL 值COUNT(*) 函数除外。
接下来我们详细介绍这些聚合函数的作用。
使用 COUNT 函数统计行数
COUNT(*) 函数用于统计查询结果或者表中的行数。例如以下语句统计了员工的数量
SELECT COUNT(*) AS 员工数量
FROM employee;查询返回的结果如下
员工数量
------25员工表中包含 25 条记录也就是 25 名员工。
COUNT 函数也可以统计某个字段或者表达式不为空值的数量例如
SELECT COUNT(emp_id), COUNT(0)
FROM employee;查询返回的结果如下
COUNT(emp_id)|COUNT(0)
-------------|--------25| 25两个 COUNT 函数分别统计了员工编号和常量 0 不为空的数量两个结果都是 25因为每个员工都有一个编号而 COUNT(0) 和 COUNT(*) 的结果相同。
以下查询在 COUNT 函数中使用了 DISTINCT 关键字
SELECT COUNT(sex) AS 所有性别, COUNT(DISTINCT sex) AS 不同性别
FROM employee;查询返回的结果如下
所有性别|不同性别
-------|-------25| 2员工表中的不同性别只有“男”和“女”因此使用 DISTINCT 关键字之后的结果为 2。 提示除了 DISTINCT 关键字之外我们也可以使用 ALL 关键字表示汇总时不排除重复数据。因为 ALL 是默认值所以我们通常省略。 另外如果参数中存在空值COUNT 函数会忽略这些空值。以下查询统计了员工拥有奖金的情况
SELECT COUNT(*) AS 员工数量,COUNT(bonus) AS 拥有奖金,COUNT(*) - COUNT(bonus) AS 没有奖金
FROM employee;查询返回的结果如下
员工数量|拥有奖金|没有奖金
------|-------|-------25| 9| 16查询结果显示 9 名员工拥有奖金16 名员工没有奖金。
使用 AVG 函数计算平均值
AVG 函数用于计算一组数据的平均值。例如以下查询统计了所有员工的平均月薪
SELECT AVG(salary) AS 平均月薪
FROM employee;查询返回的结果如下
平均月薪
-----------
9832.000000所有员工的平均月薪为 9832 元。
如果我们为 AVG 函数指定了 DISTINCT 关键字则会在计算平均值之前排除重复数据。例如1、1、2 的平均值为(12)/2而不是(112)/3。例如以下查询返回了所有不重复月薪的平均值
SELECT AVG(DISTINCT salary) AS 平均月薪
FROM employee;查询返回的结果如下
平均月薪
-----------
9865.000000去掉重复数据之后的平均月薪有所增加。
另外如果参数中存在空值AVG 函数会忽略这些空值。例如1、2、NULL 的平均值为 (12)/2而不是 (12NULL)/3。以下查询返回了员工不包括没有奖金的员工的平均奖金
SELECT AVG(bonus) AS 平均奖金
FROM employee;查询返回的结果如下
平均奖金
-----------
6388.888889如果我们想要将没有奖金的员工当作奖金为零处理可以使用 CASE 表达式
SELECT AVG(CASE WHEN bonus IS NULL THEN 0 ELSE bonus END) AS 平均奖金
FROM employee;查询返回的结果如下
平均奖金
-----------
2300.000000使用 SUM 函数计算总和
SUM 函数用于计算一组数值的总和。例如以下语句返回了所有员工的月薪总和
SELECT SUM(salary) AS 月薪总和
FROM employee;查询返回的结果如下
月薪总和
---------
245800.00公司所有员工每个月的薪水总和为 245 800 元。
SUM 函数也可以利用 DISTINCT 关键字在计算总和之前排除重复数据一般很少使用。另外如果参数中存在空值SUM 函数会忽略这些空值。以下查询返回了所有员工的平均奖金没有奖金的员工被当作奖金为零处理
SELECT SUM(bonus)/COUNT(*) AS 平均奖金
FROM employee;查询返回的结果和前面的 CASE 表达式示例相同。
使用 MAX 函数返回最大值
MAX 函数用于返回一组数据中的最大值。例如以下查询返回了最晚入职的员工的入职时间
SELECT MAX(hire_date) AS 入职时间
FROM employee;查询返回的结果如下
入职时间
----------
2019-05-11最后一位员工的入职时间是 2019 年 5 月 11 日。
MAX 函数支持 DISTINCT 关键字但是没有实际意义因为它对结果没有影响。另外如果参数中存在空值MAX 函数会忽略这些空值。
使用 MIN 函数返回最小值
MIN 函数用于返回一组数据中的最小值。例如以下查询返回了第一位员工的入职时间
SELECT MIN(hire_date) AS 入职时间
FROM employee;查询返回的结果如下
入职时间
----------
2000-01-01第一位员工的入职时间是 2000 年 1 月 1 日。
MIN 函数支持 DISTINCT 关键字但是没有实际意义因为它对结果没有影响。另外如果参数中存在空值MIN 函数会忽略这些空值。
使用 LISTAGG 函数连接字符串
LISTAGG 函数用于对字符串进行聚合可以将多行字符串合并成单个字符串。例如以下查询返回了行政管理部门中所有员工的电子邮箱
-- Oracle
SELECT LISTAGG(email, ;) AS 收件人
FROM employee
WHERE dept_id 1;目前只有 Oracle 实现了该函数函数中的第二个参数用于指定连接字符串的分隔符默认为空。查询返回的结果如下
收件人
-------------------------------------------------------
liubeishuguo.com;guanyushuguo.com;zhangfeishuguo.comLISTAGG 函数支持 WITHIN GROUP 选项可以在合并之前对数据进行排序。例如
-- Oracle
SELECT LISTAGG(email, ;) WITHIN GROUP (ORDER BY email) AS 收件人
FROM employee
WHERE dept_id 1;其中 ORDER BY 表示对邮箱地址进行排序查询返回的结果如下
收件人
-------------------------------------------------------
guanyushuguo.com;liubeishuguo.com;zhangfeishuguo.comMySQL 提供了执行字符串聚合操作的 GROUP_CONCAT 函数例如
-- MySQL
SELECT GROUP_CONCAT(email ORDER BY email SEPARATOR ;) AS 收件人
FROM employee
WHERE dept_id 1;其中ORDER BY 表示对邮箱地址进行排序SEPARATOR 指定了连接字符串的分隔符默认为逗号。查询返回的结果和上面的示例相同。
SQLite 提供了和 MySQL 类似的 GROUP_CONCAT 函数但是调用参数不同。例如
-- SQLite
SELECT GROUP_CONCAT(email, ;) AS 收件人
FROM employee
WHERE dept_id 1;第 2 个参数指定了连接字符串的分隔符默认为逗号。SQLite 中的 GROUP_CONCAT 函数不支持数据排序查询返回的结果和上面的第 1 个 Oracle 示例相同。
Microsoft SQL Server 提供了执行字符串聚合操作的 STRING_AGG 函数例如
-- Microsoft SQL Server
SELECT STRING_AGG(email, ;) WITHIN GROUP (ORDER BY email) AS 收件人
FROM employee
WHERE dept_id 1;第 2 个参数指定了连接字符串的分隔符WITHIN GROUP 选项用于在合并之前对数据进行排序。查询返回的结果和上面的第 2 个 Oracle 示例相同。
PostgreSQL 提供了和 Microsoft SQL Server 类似的 STRING_AGG 函数但是调用参数不同。 例如
-- PostgreSQL
SELECT STRING_AGG(email, ; ORDER BY email) AS 收件人
FROM employee
WHERE dept_id 1;第 2 个参数同时指定了连接字符串的分隔符和数据的排序。查询返回的结果和上面的第 2 个 Oracle 示例相同。 以上字符串聚合函数都可以使用 DISTINCT 关键字在合并之前排除重复数据同时还会忽略数据中的 NULL 值。
