MySql 中所有的聚合函数_mysql 聚合函数列表

COUNT()

• 计算行数

1SELECT COUNT(*) AS total_rows FROM your_table;

SUM()

• 求和

1SELECT SUM(column_name) AS total_value FROM your_table;

AVG()

• 求平均值

1SELECT AVG(column_name) AS average_value FROM your_table;

MAX()

• 求最大值

1SELECT MAX(column_name) AS max_value FROM your_table;

MIN()

• 求最小值

1SELECT MIN(column_name) AS min_value FROM your_table;

GROUP_CONCAT()

• 将一列的值连接为字符串

1SELECT GROUP_CONCAT(column_name SEPARATOR ',') AS concatenated_values FROM your_table;

STDDEV() 和 STDDEV_POP()

• 计算标准差

1SELECT STDDEV(column_name) AS std_deviation, STDDEV_POP(column_name) AS pop_std_deviation FROM your_table;

VARiance() 和 VARIANCE_POP()

• 计算方差

1SELECT VARIANCE(column_name) AS variance, VARIANCE_POP(column_name) AS pop_variance FROM your_table;

COALESCE()

• 返回参数列表中第一个非NULL的值，虽然不是严格意义上的聚合函数，但在聚合查询中经常用于处理NULL值

1SELECT COALESCE(column1, column2, 'default_value') AS non_null_value FROM your_table;

BIT_OR() 和 BIT_AND()

• 计算比特位的OR和AND操作

1SELECT BIT_OR(bit_column) AS bitwise_or, BIT_AND(bit_column) AS bitwise_and FROM your_table;

JSON_ARRAYAGG()

• （MySQL 5.7.22+）将一列转换为JSON数组

1SELECT JSON_ARRAYAGG(column_name) AS json_array FROM your_table;

MEDIAN()

• 计算中位数，MySQL 8.0.2+ 版本引入。

1SELECT MEDIAN(column_name) AS median_value FROM your_table;

RANK()、DENSE_RANK() 和 ROW_NUMBER()

• 这三个窗口函数（Window Function）虽然不属于传统意义上的聚合函数，但在SQL查询中常用于根据特定字段对行进行排名或编号。

1SELECT column_name, RANK() OVER (ORDER BY score DESC) rank,
2       DENSE_RANK() OVER (ORDER BY score DESC) dense_rank,
3       ROW_NUMBER() OVER (ORDER BY score DESC) row_number
4FROM your_table;