【数据库】MySQL常见聚合查询详解

在数据库操作中，聚合查询是非常重要的一部分。通过聚合查询，我们可以对数据进行汇总、统计和分析。MySQL提供了丰富的聚合函数来满足不同的需求。本文将详细介绍MySQL中常见的40个聚合函数及其使用场景，并通过8个的案例展示它们的用法。

一、聚合函数的概念

聚合函数是对一组值执行计算并返回单个值的函数。它们通常用于SELECT语句中，与GROUP BY子句一起使用，以便对数据进行分组和汇总。

二、常见的40个聚合函数

以下是MySQL中常见的40个聚合函数及其使用场景：

聚合函数	描述	使用场景
COUNT()	返回行数	统计记录数
SUM()	返回数值列的总和	计算总和
AVG()	返回数值列的平均值	计算平均值
MIN()	返回列中的最小值	查找最小值
MAX()	返回列中的最大值	查找最大值
GROUP_CONCAT()	返回一组值的连接字符串	将多行数据合并为一个字符串
STD()	返回数值列的标准差	计算标准差
VARIANCE()	返回数值列的方差	计算方差
BIT_AND()	返回按位与运算的结果	按位与运算
BIT_OR()	返回按位或运算的结果	按位或运算
BIT_XOR()	返回按位异或运算的结果	按位异或运算
JSON_ARRAYAGG()	返回JSON数组	将多行数据合并为JSON数组
JSON_OBJECTAGG()	返回JSON对象	将多行数据合并为JSON对象
VAR_POP()	返回总体方差	计算总体方差
VAR_SAMP()	返回样本方差	计算样本方差
STDDEV_POP()	返回总体标准差	计算总体标准差
STDDEV_SAMP()	返回样本标准差	计算样本标准差
COVAR_POP()	返回总体协方差	计算总体协方差
COVAR_SAMP()	返回样本协方差	计算样本协方差
CORR()	返回相关系数	计算相关系数
MEDIAN()	返回中位数	计算中位数
PERCENTILE_CONT()	返回连续百分位数	计算连续百分位数
PERCENTILE_DISC()	返回离散百分位数	计算离散百分位数
RANK()	返回行的排名	计算行的排名
DENSE_RANK()	返回行的密集排名	计算行的密集排名
ROW_NUMBER()	返回行的序号	计算行的序号
NTILE()	返回行的分桶号	计算行的分桶号
FIRST_VALUE()	返回窗口中的第一个值	获取窗口中的第一个值
LAST_VALUE()	返回窗口中的最后一个值	获取窗口中的最后一个值
LAG()	返回前一行中的值	获取前一行中的值
LEAD()	返回后一行中的值	获取后一行中的值
CUME_DIST()	返回累积分布	计算累积分布
PERCENT_RANK()	返回百分比排名	计算百分比排名
NTH_VALUE()	返回窗口中的第N个值	获取窗口中的第N个值
JSON_EXTRACT()	返回JSON文档中的值	提取JSON文档中的值
JSON_UNQUOTE()	返回JSON文档中的未引用值	提取JSON文档中的未引用值
JSON_CONTAINS()	返回JSON文档是否包含指定值	检查JSON文档是否包含指定值
JSON_CONTAINS_PATH()	返回JSON文档是否包含指定路径	检查JSON文档是否包含指定路径
JSON_KEYS()	返回JSON文档中的键	提取JSON文档中的键
JSON_LENGTH()	返回JSON文档的长度	计算JSON文档的长度

三、聚合查询的案例

1. 创建示例表

首先，我们创建一个示例表sales，用于演示各种聚合查询。

CREATE TABLE sales (id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(50),quantity INT,price DECIMAL(10, 2),sale_date DATE
);INSERT INTO sales (product_name, quantity, price, sale_date) VALUES
('Apple', 10, 1.50, '2023-01-01'),
('Banana', 20, 0.50, '2023-01-01'),
('Apple', 15, 1.50, '2023-01-02'),
('Banana', 25, 0.50, '2023-01-02'),
('Orange', 30, 0.75, '2023-01-03'),
('Orange', 35, 0.75, '2023-01-04');

2. 使用COUNT()统计记录数

-- 统计总记录数
SELECT COUNT(*) AS total_records FROM sales;-- 统计不同产品的记录数
SELECT product_name, COUNT(*) AS product_count
FROM sales
GROUP BY product_name;

输出结果:

+---------------+
| total_records |
+---------------+
|             6 |
+---------------++--------------+---------------+
| product_name | product_count |
+--------------+---------------+
| Apple        |             2 |
| Banana       |             2 |
| Orange       |             2 |
+--------------+---------------+

3. 使用SUM()计算总和

-- 计算所有产品的总销售额
SELECT SUM(quantity * price) AS total_sales FROM sales;-- 计算每个产品的总销售额
SELECT product_name, SUM(quantity * price) AS total_sales
FROM sales
GROUP BY product_name;

输出结果:

+-------------+
| total_sales |
+-------------+
|       83.75 |
+-------------++--------------+-------------+
| product_name | total_sales |
+--------------+-------------+
| Apple        |       37.50 |
| Banana       |       22.50 |
| Orange       |       48.75 |
+--------------+-------------+

4. 使用AVG()计算平均值

-- 计算所有产品的平均销售额
SELECT AVG(quantity * price) AS average_sales FROM sales;-- 计算每个产品的平均销售额
SELECT product_name, AVG(quantity * price) AS average_sales
FROM sales
GROUP BY product_name;

输出结果:

+---------------+
| average_sales |
+---------------+
|     13.958333 |
+---------------++--------------+---------------+
| product_name | average_sales |
+--------------+---------------+
| Apple        |        18.750 |
| Banana       |        11.250 |
| Orange       |        24.375 |
+--------------+---------------+

5. 使用MIN()和MAX()查找最小值和最大值

-- 查找所有产品的最小和最大销售额
SELECT MIN(quantity * price) AS min_sales, MAX(quantity * price) AS max_sales
FROM sales;-- 查找每个产品的最小和最大销售额
SELECT product_name, MIN(quantity * price) AS min_sales, MAX(quantity * price) AS max_sales
FROM sales
GROUP BY product_name;

输出结果:

+-----------+-----------+
| min_sales | max_sales |
+-----------+-----------+
|     10.00 |     26.25 |
+-----------+-----------++--------------+-----------+-----------+
| product_name | min_sales | max_sales |
+--------------+-----------+-----------+
| Apple        |     15.00 |     22.50 |
| Banana       |     10.00 |     12.50 |
| Orange       |     22.50 |     26.25 |
+--------------+-----------+-----------+

6. 使用GROUP_CONCAT()合并字符串

-- 合并所有产品的名称
SELECT GROUP_CONCAT(product_name) AS all_products FROM sales;-- 合并每个销售日期的产品名称
SELECT sale_date, GROUP_CONCAT(product_name) AS products_sold
FROM sales
GROUP BY sale_date;

输出结果:

+---------------------------+
| all_products              |
+---------------------------+
| Apple,Banana,Apple,Banana,Orange,Orange |
+---------------------------++------------+---------------------+
| sale_date  | products_sold       |
+------------+---------------------+
| 2023-01-01 | Apple,Banana        |
| 2023-01-02 | Apple,Banana        |
| 2023-01-03 | Orange              |
| 2023-01-04 | Orange              |
+------------+---------------------+

7. 使用STD()和VARIANCE()计算标准差和方差

-- 计算所有销售额的标准差和方差
SELECT STD(quantity * price) AS std_sales, VARIANCE(quantity * price) AS variance_sales
FROM sales;-- 计算每个产品销售额的标准差和方差
SELECT product_name, STD(quantity * price) AS std_sales, VARIANCE(quantity * price) AS variance_sales
FROM sales
GROUP BY product_name;

输出结果:

+------------+----------------+
| std_sales  | variance_sales |
+------------+----------------+
|   6.614378 |      43.750000 |
+------------+----------------++--------------+------------+----------------+
| product_name | std_sales  | variance_sales |
+--------------+------------+----------------+
| Apple        |   3.750000 |      14.062500 |
| Banana       |   1.250000 |       1.562500 |
| Orange       |   1.875000 |       3.515625 |
+--------------+------------+----------------+

8. 使用BIT_AND()、BIT_OR()和BIT_XOR()进行位运算

-- 计算quantity列的按位与、按位或和按位异或
SELECT BIT_AND(quantity) AS bit_and, BIT_OR(quantity) AS bit_or, BIT_XOR(quantity) AS bit_xor
FROM sales;

输出结果:

+---------+--------+---------+
| bit_and | bit_or | bit_xor |
+---------+--------+---------+
|       0 |     35 |      15 |
+---------+--------+---------+

四、总结

通过本文的介绍和案例，我们详细了解了MySQL中常见的聚合函数及其使用场景。这些聚合函数在数据分析和统计中非常有用，能够帮助我们快速获取数据的汇总信息。掌握这些聚合查询的技巧，将使你在数据库操作中更加得心应手。