如何在Redshift中对多个同结构表执行相同查询并合并结果

方案1：通过系统表生成拼接SQL手动执行 #

你可以直接查询Redshift系统视图svv_tables获取所有符合命名规则的表，批量生成合并查询语句后手动执行，无需依赖数据库变量能力。
生成SQL语句的代码：

SELECT LISTAGG(
  'SELECT Customer_ID, Balance FROM ' || table_name || ' LIMIT 10',
  ' UNION ALL '
) AS exec_sql
FROM svv_tables
WHERE table_schema = '替换为你的表所属schema名称'
  AND table_name LIKE 'Events_%'
  AND REGEXP_SUBSTR(table_name, 'Events_([0-9]+)', 1, 1, 'e') ~ '^[0-9]+$';

执行上述语句后会输出完整的多表UNION ALL查询代码，复制该代码，在开头添加CREATE TABLE 你的目标结果表名 AS 后直接执行，即可生成合并后的结果表。

提示：若表数量过多触发LISTAGG长度上限，可添加OVERFLOW ERROR TRUNCATE参数扩展长度，或按表名后缀数字范围分批次生成SQL再拼接。

方案2：创建Redshift存储过程自动执行 #

Redshift支持存储过程内声明变量，若你需要定期重复执行该需求，可直接创建存储过程一次性完成全流程操作。
存储过程定义代码：

CREATE OR REPLACE PROCEDURE sp_merge_events_top10(target_table VARCHAR(128))
LANGUAGE plpgsql
AS $$
DECLARE
  v_full_sql TEXT := '';
  rec RECORD;
BEGIN
  -- 遍历所有符合规则的事件表拼接查询逻辑
  FOR rec IN SELECT table_name FROM svv_tables
             WHERE table_schema = '替换为你的表所属schema名称'
               AND table_name LIKE 'Events_%'
               AND REGEXP_SUBSTR(table_name, 'Events_([0-9]+)', 1, 1, 'e') ~ '^[0-9]+$'
  LOOP
    v_full_sql := v_full_sql || 'SELECT Customer_ID, Balance FROM ' || rec.table_name || ' LIMIT 10 UNION ALL ';
  END LOOP;
  -- 移除末尾多余的UNION ALL关键字
  v_full_sql := RTRIM(v_full_sql, ' UNION ALL');
  -- 拼接建表语句
  v_full_sql := 'CREATE TABLE ' || target_table || ' AS ' || v_full_sql;
  -- 执行动态SQL
  EXECUTE v_full_sql;
END;
$$;

存储过程调用方式：

CALL sp_merge_events_top10('替换为你的目标结果表名');

方案3：使用SQL Workbench客户端变量能力 #

SQL Workbench本身支持客户端侧变量定义和循环逻辑，不需要依赖Redshift数据库端的变量支持，直接在客户端执行以下代码即可：

-- 先获取所有符合规则的表名存入本地变量
WBCMDVAR -name=event_table_list -value=(SELECT LISTAGG(table_name, ',') FROM svv_tables WHERE table_schema = '替换为你的表所属schema名称' AND table_name LIKE 'Events_%');
-- 批量执行合并查询
WbExec -script=
  CREATE TABLE 替换为你的目标结果表名 AS 
  @[foreach table=${event_table_list} separator="UNION ALL"]
    SELECT Customer_ID, Balance FROM ${table} LIMIT 10
  @[/foreach];

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