MySQL中无需逐个指定列，如何计算各列平均值？

要实现无需手动列出每一列就能计算各列平均值，同时支持后续分组、连接等扩展操作，核心解决方案是动态SQL——利用数据库的数据字典自动获取列名，生成计算每个列平均值的SQL语句。

为什么AVG(*)不合法 #

AVG()函数要求传入单个数值类型的列，*代表所有列的集合，不符合函数参数要求，因此语法不合法。

分数据库实现方案 #

以下示例均假设目标表名为your_table_name，且仅计算数值类型列的平均值（避免对字符串、日期等非数值列报错）：

MySQL/MariaDB

SET @table_name = 'your_table_name';
SET @sql = (
  SELECT CONCAT(
    'SELECT ',
    GROUP_CONCAT(CONCAT('AVG(`', column_name, '`) AS `AVG(', column_name, ')`') SEPARATOR ', '),
    ' FROM ', @table_name
    -- 如需分组，添加：' GROUP BY your_group_column'
  )
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = @table_name
    AND data_type IN ('int', 'decimal', 'float', 'double')
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL

DO $$
DECLARE
  table_name text := 'your_table_name';
  sql text;
BEGIN
  SELECT 'SELECT ' || string_agg(
    'AVG(' || quote_ident(column_name) || ') AS "AVG(' || column_name || ')"',
    ', '
  ) || ' FROM ' || quote_ident(table_name)
  -- 如需分组，添加：' GROUP BY your_group_column'
  INTO sql
  FROM information_schema.columns
  WHERE table_schema = current_schema()
    AND table_name = table_name
    AND data_type IN ('integer', 'numeric', 'real', 'double precision');

  EXECUTE sql;
END $$;

SQL Server

DECLARE @table_name NVARCHAR(128) = 'your_table_name';
DECLARE @sql NVARCHAR(MAX);

SELECT @sql = CONCAT(
  'SELECT ',
  STRING_AGG(CONCAT('AVG(', QUOTENAME(column_name), ') AS ', QUOTENAME('AVG(' + column_name + ')')), ', '),
  ' FROM ', QUOTENAME(@table_name)
  -- 如需分组，添加：' GROUP BY your_group_column'
)
FROM sys.columns
WHERE object_id = OBJECT_ID(@table_name)
  AND system_type_id IN (48, 52, 56, 60, 62, 106, 108, 122, 127); -- 对应数值类型的系统ID

EXEC sp_executesql @sql;

扩展性说明 #

• 分组操作：只需在生成的SQL语句末尾添加GROUP BY 分组列名，示例中已给出注释位置。
• 连接操作：修改FROM子句，添加JOIN 其他表 ON 连接条件即可，例如将FROM ', @table_name'改为FROM ', @table_name, ' JOIN other_table ON your_table.id = other_table.table_id'。
• 过滤条件：可在语句末尾添加WHERE 过滤条件实现数据筛选。

注意事项 #

• 上述代码会自动过滤非数值列，避免计算错误；若需包含特定非数值列（如日期类型的平均值，需先转换为数值），可调整data_type或system_type_id的过滤条件。
• 使用quote_ident（PostgreSQL）、QUOTENAME（SQL Server）、反引号（MySQL）处理列名含特殊字符（如空格、关键字）的情况，避免语法错误。

内容的提问来源于stack exchange，提问作者J_Good_Human