如何批量计算表中全部数值列的mean、stddev及分位数？

当然有办法解决这个重复劳动的问题！手动写30列的统计函数不仅耗时，还容易出错。下面针对主流SQL数据库给出具体的实现方案，你可以根据自己使用的数据库选择：

PostgreSQL 实现 #

PostgreSQL可以利用系统表information_schema.columns获取表的列信息，再通过动态SQL拼接并执行统计语句：

-- 1. 先拼接出需要执行的统计SQL语句
WITH cols AS (
  SELECT column_name
  FROM information_schema.columns
  WHERE table_name = 'your_table_name' -- 替换成你的表名
    AND table_schema = 'public' -- 替换成你的schema（如果有的话）
    AND data_type IN ('integer', 'numeric', 'double precision') -- 筛选数值类型列
)
SELECT format(
  'SELECT date, %s FROM your_table_name GROUP BY date;',
  string_agg(
    format('avg(%I) AS avg_%I, stddev(%I) AS stddev_%I, percentile_cont(0.5) WITHIN GROUP (ORDER BY %I) AS median_%I',
           column_name, column_name, column_name, column_name, column_name, column_name),
    ', '
  )
) AS dynamic_sql
FROM cols;

-- 2. 将上面查询返回的SQL语句复制出来执行，或者用EXECUTE直接运行（需要权限）

MySQL 实现 #

MySQL同样可以通过information_schema.columns获取列信息，结合GROUP_CONCAT拼接SQL：

-- 生成统计SQL语句
SELECT CONCAT(
  'SELECT date, ',
  GROUP_CONCAT(
    CONCAT(
      'AVG(`', column_name, '`) AS avg_', column_name,
      ', STDDEV(`', column_name, '`) AS stddev_', column_name,
      ', PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `', column_name, '`) AS median_', column_name
    ) SEPARATOR ', '
  ),
  ' FROM your_table_name GROUP BY date;'
) AS dynamic_sql
FROM information_schema.columns
WHERE table_schema = 'your_database_name' -- 替换成你的数据库名
  AND table_name = 'your_table_name' -- 替换成你的表名
  AND data_type IN ('int', 'decimal', 'float', 'double'); -- 筛选数值类型列

执行上面的查询后，复制返回的SQL语句运行即可。如果是MySQL 8.0+，也可以用预处理语句直接执行动态SQL。

BigQuery 实现 #

BigQuery支持EXECUTE IMMEDIATE来执行动态SQL，步骤如下：

DECLARE dynamic_sql STRING;

-- 拼接统计SQL
SET dynamic_sql = (
  SELECT CONCAT(
    'SELECT date, ',
    STRING_AGG(
      CONCAT(
        'AVG(', column_name, ') AS avg_', column_name,
        ', STDDEV(', column_name, ') AS stddev_', column_name,
        ', PERCENTILE_CONT(0.5) OVER (PARTITION BY date ORDER BY ', column_name, ') AS median_', column_name
      ), ', '
    ),
    ' FROM `your_project.your_dataset.your_table` GROUP BY date;'
  )
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table' -- 替换成你的表名
    AND data_type IN ('INT64', 'NUMERIC', 'FLOAT64') -- 筛选数值类型列
);

-- 执行动态SQL
EXECUTE IMMEDIATE dynamic_sql;

通用工具方案（适合所有数据库） #

如果不想写动态SQL，也可以用Python脚本快速生成统计语句：

import pandas as pd
from sqlalchemy import create_engine

# 连接数据库（以PostgreSQL为例，其他数据库修改连接字符串）
engine = create_engine('postgresql://user:password@host:port/dbname')

# 获取表的列信息
df = pd.read_sql("SELECT column_name FROM information_schema.columns WHERE table_name='your_table_name' AND data_type IN ('integer', 'numeric', 'double precision')", engine)

# 生成统计列的SQL片段
stats = []
for col in df['column_name']:
    stats.append(f"avg({col}) AS avg_{col}, stddev({col}) AS stddev_{col}, percentile_cont(0.5) WITHIN GROUP (ORDER BY {col}) AS median_{col}")

# 拼接完整SQL
sql = f"SELECT date, {', '.join(stats)} FROM your_table_name GROUP BY date;"
print(sql)

运行脚本后，复制输出的SQL语句执行即可。

注意：分位数函数在不同数据库中可能略有差异，比如有些数据库用PERCENTILE_DISC代替PERCENTILE_CONT，你可以根据需求替换。另外，记得替换代码中的表名、数据库名、schema等信息。

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