BigQuery如何基于ref_table条目动态生成指定格式的聚合SQL语句

实现思路 #

核心逻辑是从ref_table读取所有id和对应分类名称的映射关系，批量拼接CASE WHEN计算片段，再组装成完整的查询语句执行，全程不需要硬编码分类值，ref_table新增条目后重新执行即可自动生成最新的查询。
前提假设：ref_table包含两个字段，id（对应Transaction_table的id字段）、color_name（对应分类名称，如Red、Blue、Green）。

不同数据库实现示例 #

MySQL 版本 #

-- 拼接CASE WHEN字段片段
SET @sql = NULL;
SELECT GROUP_CONCAT(
  CONCAT('sum(case when id = ', id, ' then val end)/sum(val) as `', color_name, '_percent`')
  SEPARATOR ',\n    '
) INTO @sql
FROM ref_table;

-- 组装完整SQL
SET @full_sql = CONCAT('SELECT ', @sql, ' FROM Transaction_table;');

-- 查看生成的SQL（可选）
SELECT @full_sql;
-- 执行动态SQL
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 版本 #

如果只是执行不需要返回结果，用匿名块即可：

DO $$
DECLARE
    v_sql text;
BEGIN
    -- 拼接字段片段
    SELECT string_agg(
        format('sum(case when id = %s then val end)/sum(val) as %I_percent', id, color_name),
        ',\n    '
    ) INTO v_sql
    FROM ref_table;

    -- 组装完整SQL
    v_sql := 'SELECT ' || v_sql || ' FROM Transaction_table;';
    
    -- 打印生成的SQL（可选）
    RAISE NOTICE '%', v_sql;
END $$;

如果需要返回查询结果，封装成函数使用：

CREATE OR REPLACE FUNCTION get_color_percents()
RETURNS SETOF RECORD AS $$
DECLARE
    v_sql text;
BEGIN
    SELECT string_agg(
        format('sum(case when id = %s then val end)/sum(val) as %I_percent', id, color_name),
        ',\n    '
    ) INTO v_sql
    FROM ref_table;

    v_sql := 'SELECT ' || v_sql || ' FROM Transaction_table;';
    RETURN QUERY EXECUTE v_sql;
END $$ LANGUAGE plpgsql;

SQL Server 版本 #

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX)

-- 拼接字段片段
SELECT @cols = STRING_AGG(
    CONCAT('sum(case when id = ', id, ' then val end)/sum(val) as ', QUOTENAME(color_name + '_percent')),
    ',\n    '
)
FROM ref_table

-- 组装完整SQL
SET @sql = 'SELECT ' + @cols + ' FROM Transaction_table'

-- 查看生成的SQL（可选）
PRINT @sql
-- 执行动态SQL
EXEC sp_executesql @sql

注意事项 #

• 所有示例都内置了特殊字符转义逻辑，避免color_name包含空格、特殊字符、中文时出现语法错误，同时降低SQL注入风险
• 其他数据库的实现逻辑完全一致，只需替换对应数据库的字符串聚合函数、动态SQL执行语法即可
• 如果需要对小数精度做限制，可以在除法计算外层套对应数据库的ROUND函数处理

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