在數據庫操作中，聚合查詢是非常重要的一部分。通過聚合查詢，我們可以對數據進行匯總、統計和分析。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中常見的聚合函數及其使用場景。這些聚合函數在數據分析和統計中非常有用，能夠幫助我們快速獲取數據的匯總信息。掌握這些聚合查詢的技巧，將使你在數據庫操作中更加得心應手。
前端開發面試題集錦和參考答案)
