如何筛选指定日期范围内的同结构周备份表用于union运算？

首先先明确你给出的示例的筛选逻辑：无后缀的user_details作为当前全量表默认保留，带8位日期后缀的表除了匹配指定日期范围内的，还需包含范围结束后最近的一个周度备份（对应你示例中的user_details_20211126），下面分不同场景给出可直接落地的方案：

场景1：Hive/Spark SQL 大数据环境 #

直接通过系统元数据表筛选符合要求的表名，再拼接Union查询即可：

-- 1. 筛选符合规则的表名
SELECT table_name
FROM information_schema.tables
WHERE table_schema = '替换为你的库名'
  AND (
    table_name = 'user_details' -- 匹配无后缀全量表
    OR table_name RLIKE '^user_details_[0-9]{8}$' -- 匹配带日期后缀的周表
  )
  AND (
    table_name = 'user_details'
    -- 匹配日期范围内的周表
    OR DATE_FORMAT(STR_TO_DATE(REGEXP_EXTRACT(table_name, '([0-9]{8})', 1), 'yyyyMMdd'), 'yyyy-MM-dd') BETWEEN '2021-11-13' AND '2021-11-20'
    -- 匹配范围结束后最近的一个周表（不需要可删除这段条件）
    OR DATE_FORMAT(STR_TO_DATE(REGEXP_EXTRACT(table_name, '([0-9]{8})', 1), 'yyyyMMdd'), 'yyyy-MM-dd') = (
        SELECT MIN(DATE_FORMAT(STR_TO_DATE(REGEXP_EXTRACT(table_name, '([0-9]{8})', 1), 'yyyyMMdd'), 'yyyy-MM-dd'))
        FROM information_schema.tables
        WHERE table_schema = '替换为你的库名'
          AND table_name RLIKE '^user_details_[0-9]{8}$'
          AND DATE_FORMAT(STR_TO_DATE(REGEXP_EXTRACT(table_name, '([0-9]{8})', 1), 'yyyyMMdd'), 'yyyy-MM-dd') > '2021-11-20'
    )
);

拿到筛选后的表名后，直接拼接为Union查询语句即可：

SELECT * FROM user_details
UNION ALL
SELECT * FROM user_details_20211126
UNION ALL
SELECT * FROM user_details_20211119

场景2：MySQL 环境 #

逻辑和上述方案完全一致，仅调整对应函数即可：

• 正则提取函数替换为REGEXP_SUBSTR(table_name, '[0-9]{8}')
• 日期转换格式符调整为STR_TO_DATE(xxx, '%Y%m%d')、DATE_FORMAT(xxx, '%Y-%m-%d')

场景3：脚本预处理生成SQL（Python示例） #

如果不想编写复杂的SQL，可以通过脚本先过滤表名再生成执行语句，灵活度更高：

import pymysql
from datetime import datetime

# 配置数据库连接
conn = pymysql.connect(host='数据库地址', user='用户名', password='密码', database='库名')
cursor = conn.cursor()

# 查询所有符合前缀的表
cursor.execute("SHOW TABLES LIKE 'user_details%'")
all_tables = [item[0] for item in cursor.fetchall()]

# 配置筛选日期范围
start_dt = datetime.strptime('2021-11-13', '%Y-%m-%d')
end_dt = datetime.strptime('2021-11-20', '%Y-%m-%d')

valid_tables = []
out_range_later_tables = []

for table in all_tables:
    if table == 'user_details':
        valid_tables.append(table)
        continue
    # 提取日期后缀
    date_suffix = table.split('_')[-1]
    if len(date_suffix) != 8 or not date_suffix.isdigit():
        continue
    table_dt = datetime.strptime(date_suffix, '%Y%m%d')
    if start_dt <= table_dt <= end_dt:
        valid_tables.append(table)
    elif table_dt > end_dt:
        out_range_later_tables.append((table_dt, table))

# 加入范围结束后最近的周表（不需要可删除这段）
if out_range_later_tables:
    out_range_later_tables.sort()
    valid_tables.append(out_range_later_tables[0][1])

# 生成Union查询语句
union_sql = " UNION ALL ".join([f"SELECT * FROM `{t}`" for t in valid_tables])
print(union_sql)
# 如需直接执行可加：cursor.execute(union_sql)

注意：如果你的场景不需要包含范围外的后续周表，删除对应逻辑即可，筛选逻辑可根据实际业务规则灵活调整。

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