MySQL系列（2）——聚合函數查詢詳情 - mysql,數據庫,mysql,JSON,json,JavaScript,前端開發 huatechinfo 博客

文章目錄

一、聚合函數詳解

1、AVG()：平均值
2、BIT_AND()：按位與
3、BIT_OR()：按位或
4、BIT_XOR()：按位XOR
5、COUNT()：返回行數
6、COUNT(DISTINCT)：返回不同結果數
7、GROUP_CONCAT()：返回連接的字符串

（1）GROUP_CONCAT 函數的基本語法
（2）應用-數據彙總
（3）應用-字符串連接
（4）應用- 動態查詢
（5）應用添加order by和分隔符
（6）注意

8、JSON_ARRAYAGG()：將結果封裝為json數組
9、JSON_OBJECTAGG()：將結果封裝為單個json對象

（1）普通用法
（2）用於窗口函數

10、MAX()：返回最大值
11、MIN()：返回最小值
12、STD()：返回總體標準差
13、STDDEV()：返回總體標準差
14、STDDEV_POP()：返回總體標準差
15、STDDEV_SAMP()：返回樣本標準差
16、SUM()：求和
17、VAR_POP()：返回總體標準方差
18、VAR_SAMP()：樣本方差
19、VARIANCE()：返回總體標準方差

參考資料

一、聚合函數詳解

聚合函數通常與 GROUP BY 子句一起使用，將值分組為子集。如果不使用 GROUP BY 子句，則聚合函數等效於所有行分為一組。

聚合函數默認會忽略NULL 值。

大多數聚合函數都可以用作窗口函數，如果帶有over子句，會當做窗口函數來執行。

1、AVG()：平均值

-- 語法
-- DISTINCT 選項可以去重
-- 如果沒有匹配的行， AVG（） 返回 NULL。 
-- 如果 expr 為 NULL， 則為 NULL。
AVG([DISTINCT] expr) [over_clause]

mysql> SELECT student_name, AVG(test_score)
       FROM student
       GROUP BY student_name;

2、BIT_AND()：按位與

-- 語法
-- 如果沒有匹配的行， BIT_AND（） 返回中性 值（所有位設置為1），其長度與 參數值。
--NULL 值不會影響結果，除非所有值都是 NULL。
BIT_AND(expr) [over_clause]

3、BIT_OR()：按位或

-- 語法
-- 如果沒有匹配的行， BIT_OR（） 返回中性 值（所有位設置為0）
-- NULL 值不會影響結果，除非所有值都是 NULL。
BIT_OR(expr) [over_clause]

4、BIT_XOR()：按位XOR

-- 語法
-- 如果沒有匹配的行， BIT_XOR（） 返回中性 值（所有位設置為0）
-- NULL 值不會影響結果，除非所有值都是 NULL。
BIT_XOR(expr) [over_clause]

5、COUNT()：返回行數

-- 語法
-- 返回 SELECT 檢索的行中 expr 的非 NULL 值的計數 ，結果是一個 BIGINT 值。
-- 如果沒有匹配的行，  COUNT() 返回 0。 COUNT(NULL) 返回 0.
COUNT(expr) [over_clause]

-- COUNT(*) 有點 不同之處在於它返回行數的計數 無論是否包含 NULL 值。
-- InnoDB 處理 SELECT（*） 和 SELECT（1） 以同樣的方式操作，沒有區別。
mysql> SELECT student.student_name,COUNT(*)
       FROM student,course
       WHERE student.student_id=course.student_id
       GROUP BY student_name;

6、COUNT(DISTINCT)：返回不同結果數

-- 語法
-- 返回去重、非null的行數
-- 如果沒有匹配的行，  COUNT(DISTINCT) 返回 0。
COUNT(DISTINCT expr,[expr...])

mysql> SELECT COUNT(DISTINCT results) FROM student;

7、GROUP_CONCAT()：返回連接的字符串

GROUP_CONCAT 是一個用於將分組中的多個值連接成一個字符串的聚合函數。它在數據彙總、報告生成以及各種需要將多行數據合併為單行顯示的場景中非常實用。通過 GROUP_CONCAT，可以將同一組內的多個值合併為一個由指定分隔符分隔的字符串，簡化數據展示和分析。

（1）GROUP_CONCAT 函數的基本語法

GROUP_CONCAT 函數用於在分組查詢中，將組內的多個值連接成一個字符串。它可以與 ORDER BY 和 SEPARATOR 子句一起使用，以控制連接結果的排序和分隔符。

GROUP_CONCAT([DISTINCT] expression [ORDER BY expression ASC|DESC] [SEPARATOR 'separator'])

DISTINCT（可選）：去除重複值，只連接唯一的值。
expression：要連接的列或表達式。
ORDER BY（可選）：指定連接前的排序方式。
SEPARATOR（可選）：指定值之間的分隔符，默認為逗號（,）。

-- 示例
SELECT GROUP_CONCAT(name) AS names FROM users;
-- 結果
names
John,Doe,Jane

（2）應用-數據彙總

在生成彙總報告時，GROUP_CONCAT 可以將分組內的多個記錄合併為一個字符串，方便展示。例如，列出每個部門的所有員工姓名。

SELECT department, GROUP_CONCAT(employee_name) AS employees
FROM employees
GROUP BY department;

此查詢將返回每個部門及其對應的所有員工姓名，員工姓名之間以逗號分隔。

（3）應用-字符串連接

當需要將多行數據轉換為單行字符串時，GROUP_CONCAT 是一個理想的選擇。例如，將一個訂單中所有商品的名稱連接成一個字符串，便於顯示或進一步處理。

SELECT order_id, GROUP_CONCAT(product_name SEPARATOR '; ') AS products
FROM order_details
GROUP BY order_id;

此查詢將返回每個訂單的 order_id 及其包含的所有商品名稱，商品名稱之間以分號和空格分隔。

（4）應用- 動態查詢

在某些動態查詢生成的場景中，GROUP_CONCAT 可以用於構建動態的SQL語句。例如，根據不同的條件動態生成 WHERE 子句。

SELECT GROUP_CONCAT(DISTINCT CONCAT('\'', category, '\'')) AS categories
FROM products;

此查詢將返回所有不同的產品類別，並將其格式化為以單引號包裹的字符串，適用於動態生成 IN 子句。

（5）應用添加order by和分隔符

mysql> select department,group_concat(distinct emp_name order by salary desc separator '@') 
from emp group by department;
+------------+--------------------------------------------------------------------+
| department | group_concat(distinct emp_name order by salary desc separator '@') |
+------------+--------------------------------------------------------------------+
| 人事部     | 王大鵬@劉小貝@張小斐                                               |
| 財務部     | 趙剛@王飛飛@張晶晶                                                 |
| 銷售部     | 劉云云@劉雲鵬                                                      |
+------------+--------------------------------------------------------------------+
3 rows in set (0.00 sec)

（6）注意

1、最大長度 最大長度限制：GROUP_CONCAT 的結果長度受限於系統變量 group_concat_max_len。默認值可能較小，如果需要連接大量數據，應適當調整該變量。

SET SESSION group_concat_max_len = 1000000;

2、處理NULL值 GROUP_CONCAT 會忽略組內的 NULL 值，不會將其包含在連接結果中。

3、分隔符選擇 默認情況下，GROUP_CONCAT 使用逗號作為分隔符。可以通過 SEPARATOR 子句自定義分隔符，以滿足不同的需求。

SELECT GROUP_CONCAT(name SEPARATOR ' | ') AS names FROM users;

-- 結果
names
John | Doe | Jane

8、JSON_ARRAYAGG()：將結果封裝為json數組

-- 語法
-- 將結果集聚合為單個 JSON 數組，其元素 由行組成。
JSON_ARRAYAGG(col_or_expr) [over_clause]

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
    -> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

9、JSON_OBJECTAGG()：將結果封裝為單個json對象

（1）普通用法

-- 語法
-- 第一個用作鍵，第二個用作值，並返回包含鍵值對的 JSON 對象
-- 注意，重複的key可能會丟失，因為json不允許有重複的key
JSON_OBJECTAGG(key, value) [over_clause]

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
    -> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

-- key 重複示例

mysql> CREATE TABLE t(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t;
Query OK, 3 rows affected (0.08 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    5 |
| key  |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 4}           |
+----------------------+
1 row in set (0.00 sec)

（2）用於窗口函數

如果沒有 ORDER BY，框架就是整個分區，所有數據：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER () AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 4}  |
| {"key": 4}  |
| {"key": 4}  |
+-------------+

使用 ORDER BY：默認是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
| {"key": 4}  |
| {"key": 5}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 4}  |
| {"key": 3}  |
+-------------+

使用 ORDER BY 和一個顯式的整個分區：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS json_object
       FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 5}  |
| {"key": 5}  |
+-------------+

若要返回特定的鍵值（如最小或最大鍵值），可以使用LIMIT。舉例來説：

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
+-------------+

10、MAX()：返回最大值

-- 語法
-- 可以接受字符串參數，它返回最大字符串值。
-- 如果沒有匹配的行，或者 expr 為 NULL， MAX（） 返回 NULL.
-- 用在窗口函數的話，不能與DISTINCT一起用
MAX([DISTINCT] expr) [over_clause]

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

11、MIN()：返回最小值

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， MIN（） 返回 NULL.
-- 用在窗口函數的話，不能與DISTINCT一起用
MIN([DISTINCT] expr) [over_clause]

12、STD()：返回總體標準差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， STD（） 返回 NULL.
STD(expr) [over_clause]

13、STDDEV()：返回總體標準差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， STDDEV（） 返回 NULL.
STDDEV(expr) [over_clause]

14、STDDEV_POP()：返回總體標準差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， STDDEV_POP（） 返回 NULL.
STDDEV_POP(expr) [over_clause]

15、STDDEV_SAMP()：返回樣本標準差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， STDDEV_SAMP（） 返回 NULL.
STDDEV_SAMP(expr) [over_clause]

16、SUM()：求和

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， SUM（） 返回 NULL.
SUM([DISTINCT] expr) [over_clause]

17、VAR_POP()：返回總體標準方差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， VAR_POP（） 返回 NULL.
VAR_POP(expr) [over_clause]

18、VAR_SAMP()：樣本方差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， VAR_SAMP（） 返回 NULL.
VAR_SAMP(expr) [over_clause]

19、VARIANCE()：返回總體標準方差

-- 語法
-- 如果沒有匹配的行，或者 expr 為 NULL， VARIANCE（） 返回 NULL.
VARIANCE(expr) [over_clause]

本文章為轉載內容，我們尊重原作者對文章享有的著作權。如有內容錯誤或侵權問題，歡迎原作者聯繫我們進行內容更正或刪除文章。

huatechinfo 博客

huatechinfo 博客

博客 / 詳情