PostgreSQL按日期范围拼接带日期后缀表的实现咨询

1. 动态SQL实现（推荐） #

因为表名是动态生成的，最直接的方式是构造包含所有目标表的UNION ALL查询，再与你的时间序列关联。以下是具体实现步骤：

步骤1：生成目标表名列表

先从日期范围中提取所有涉及的年月（格式YYYYMM），生成对应的表名：

SELECT format('quantity.business_%s', to_char(d, 'YYYYMM')) AS table_name
FROM generate_series(
  date_trunc('month', timezone(@Timezone, @StartDate::date)),
  date_trunc('month', timezone(@Timezone, @EndDate::date)),
  '1 month'::interval
) AS d;

这段代码会输出日期范围内所有月份对应的表名，比如你示例中的quantity.business_202205、quantity.business_202206、quantity.business_202207。

步骤2：构造完整动态SQL

将这些表用UNION ALL拼接，再和你的时间序列、业务表关联。可以用PL/pgSQL函数封装，或者直接构造字符串执行：

DO $$
DECLARE
  v_start_date DATE := @StartDate::date;
  v_end_date DATE := @EndDate::date;
  v_timezone TEXT := @Timezone;
  v_table_list TEXT;
  v_sql TEXT;
BEGIN
  -- 生成所有需要拼接的表的UNION ALL语句
  SELECT string_agg(format('SELECT businessid, demanddate FROM %s', table_name), ' UNION ALL ')
  INTO v_table_list
  FROM (
    SELECT format('quantity.business_%s', to_char(d, 'YYYYMM')) AS table_name
    FROM generate_series(
      date_trunc('month', timezone(v_timezone, v_start_date)),
      date_trunc('month', timezone(v_timezone, v_end_date)),
      '1 month'::interval
    ) AS d
  ) AS tables;

  -- 构造完整查询SQL
  v_sql := format('
    SELECT bp.*, dd, dmd.*
    FROM bp -- 替换为你的基础业务表名
    CROSS JOIN generate_series(
      timezone(%L, %L::date),
      timezone(%L, %L::date) + interval ''1 day - 1 second'',
      ''30 minutes''::interval
    ) AS dd
    JOIN (%s) AS dmd
      ON dmd.businessid = bp.businessid AND dmd.demanddate = dd;
  ', v_timezone, v_start_date, v_timezone, v_end_date, v_table_list);

  -- 执行动态SQL
  EXECUTE v_sql;
END $$;

注意：确保所有business_YYYYMM表的结构完全一致（字段名、类型、顺序相同），否则UNION ALL会报错；如果bp表有过滤条件，需在查询中补充。

2. 替代思路：分区表重构 #

如果你的业务场景长期需要按年月查询这类表，建议将分散的表重构为PostgreSQL分区表，从架构层面简化查询：

• 创建一个主表quantity.business，按demanddate的年月范围做分区
• 将现有的business_202201等表转换为分区表的子分区
• 查询时直接操作主表，PostgreSQL会自动扫描对应的分区，无需手动拼接表名

示例代码：

-- 创建主表
CREATE TABLE quantity.business (
  businessid INT,
  demanddate TIMESTAMPTZ,
  -- 其他业务字段...
) PARTITION BY RANGE (demanddate);

-- 将现有表转换为分区（以202205为例）
ALTER TABLE quantity.business_202205
ATTACH TO quantity.business
FOR VALUES FROM ('2022-05-01 00:00:00+00') TO ('2022-06-01 00:00:00+00');

-- 查询时直接用主表，自动匹配分区
SELECT bp.*, dd, dmd.*
FROM bp
CROSS JOIN generate_series(
  timezone(@Timezone, @StartDate::date),
  timezone(@Timezone, @EndDate::date) + interval '1 day - 1 second',
  '30 minutes'::interval
) AS dd
JOIN quantity.business dmd
  ON dmd.businessid = bp.businessid AND dmd.demanddate = dd;

对原有代码的优化说明 #

你原来的单表代码固定了表名，无法适配多表场景。动态SQL方案通过UNION ALL将多表合并为逻辑表，完美适配你的需求；分区表则是更长期的优化方案，减少维护成本。

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