如何基于time列批量计算table1中多列的分组加权平均值？

绝对可以批量搞定！手动写一堆sum(varX*time)/sum(time)不仅效率低，还容易写错，我们可以借助数据库的元数据和动态SQL来自动生成这些计算逻辑，下面给你几种主流数据库的实现方案：

MySQL/MariaDB 实现方案 #

通过查询系统元数据表获取符合规则的列，自动拼接动态SQL执行：

-- 1. 生成所有目标列的加权平均表达式
SET @sql = NULL;
SELECT GROUP_CONCAT(
    CONCAT('sum(', column_name, '*time)/sum(time) AS ', column_name)
    SEPARATOR ', '
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = DATABASE() -- 自动取当前连接的数据库
  AND table_name = 'table1'
  AND column_name REGEXP '^var[0-9]+$'; -- 匹配var1、var2这类格式的列

-- 2. 组装完整的建表SQL
SET @sql = CONCAT(
    'DROP TABLE IF EXISTS table2; CREATE TABLE table2 AS SELECT ID, ',
    @sql,
    ' FROM table1 GROUP BY ID'
);

-- 3. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 实现方案 #

利用string_agg和PL/pgSQL的动态执行能力，同时用format函数避免SQL注入风险：

DO $$
DECLARE
    col_expression_list TEXT;
BEGIN
    -- 获取所有目标列的加权平均表达式
    SELECT string_agg(
        format('sum(%I * time)/sum(time) AS %I', column_name, column_name),
        ', '
    ) INTO col_expression_list
    FROM information_schema.columns
    WHERE table_schema = 'public' -- 替换为你的实际schema名称
      AND table_name = 'table1'
      AND column_name ~ '^var[0-9]+$'; -- 正则匹配目标列

    -- 执行动态创建表的SQL
    EXECUTE format(
        'DROP TABLE IF EXISTS table2; CREATE TABLE table2 AS SELECT ID, %s FROM table1 GROUP BY ID',
        col_expression_list
    );
END $$;

SQL Server 实现方案 #

使用STRING_AGG（2017及以上版本支持）拼接表达式，配合动态SQL执行：

DECLARE @sql NVARCHAR(MAX);

-- 生成目标列的加权平均表达式列表
SELECT @sql = STRING_AGG(
    CONCAT('SUM(', QUOTENAME(column_name), '*time)/SUM(time) AS ', QUOTENAME(column_name)),
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' -- 替换为你的实际schema名称
  AND TABLE_NAME = 'table1'
  AND column_name LIKE 'var[0-9]%'; -- 或者用正则：column_name REGEXP '^var[0-9]+$'（SQL Server 2016+支持）

-- 组装并执行完整SQL
SET @sql = N'DROP TABLE IF EXISTS table2; SELECT ID, ' + @sql + N' INTO table2 FROM table1 GROUP BY ID';

EXEC sp_executesql @sql;

注意事项 #

• 要确保time列没有0值，否则会触发除以0的错误。可以修改为SUM(CASE WHEN time <> 0 THEN time ELSE 0 END)作为分母，或者在查询时过滤掉time=0的行。
• 正则表达式可以根据你的实际列名格式调整，比如如果列名是var_1、var_2，就把正则改成^var_[0-9]+$。
• 执行动态SQL需要对应权限，确保你的数据库账号有查询元数据和创建表的权限。

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