MySql 中所有的聚合函数

1. COUNT()

   • 计算行数

   1SELECT COUNT(*) AS total_rows FROM your_table;

2. SUM()

   • 求和

   1SELECT SUM(column_name) AS total_value FROM your_table;

3. AVG()

   • 求平均值

   1SELECT AVG(column_name) AS average_value FROM your_table;

4. MAX()

   • 求最大值

   1SELECT MAX(column_name) AS max_value FROM your_table;

5. MIN()

   • 求最小值

   1SELECT MIN(column_name) AS min_value FROM your_table;

6. GROUP_CONCAT()

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

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

7. STDDEV() 和 STDDEV_POP()

   • 计算标准差

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

8. VARiance() 和 VARIANCE_POP()

   • 计算方差

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

9. COALESCE()

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

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

10. BIT_OR() 和 BIT_AND()

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

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

11. JSON_ARRAYAGG()

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

    1SELECT JSON_ARRAYAGG(column_name) AS json_array FROM your_table;

1. MEDIAN()

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

   1SELECT MEDIAN(column_name) AS median_value FROM your_table;

2. 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;