获取指定表最新3个备份表的Snowflake查询语句需求

基于备份序号筛选 #

如果你的备份表命名规则是原表名_BKP+数字（比如EMP_BKP1），可以通过提取序号并排序来获取最新3个：

-- 替换schema和目标表名后执行
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'
  AND TABLE_NAME LIKE 'EMP_BKP%'
  -- 过滤出_BKP后是数字的表，避免匹配非标准命名的表
  AND TRY_TO_NUMBER(RIGHT(TABLE_NAME, LENGTH(TABLE_NAME) - LENGTH('EMP_BKP'))) IS NOT NULL
ORDER BY TRY_TO_NUMBER(RIGHT(TABLE_NAME, LENGTH(TABLE_NAME) - LENGTH('EMP_BKP'))) DESC
LIMIT 3;

通用化版本（支持动态指定原表） #

用Snowflake变量实现灵活切换目标表：

-- 设置目标表名
SET target_table = 'EMP';

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'
  AND TABLE_NAME LIKE CONCAT($target_table, '_BKP%')
  AND TRY_TO_NUMBER(RIGHT(TABLE_NAME, LENGTH(TABLE_NAME) - LENGTH(CONCAT($target_table, '_BKP')))) IS NOT NULL
ORDER BY TRY_TO_NUMBER(RIGHT(TABLE_NAME, LENGTH(TABLE_NAME) - LENGTH(CONCAT($target_table, '_BKP')))) DESC
LIMIT 3;

基于创建时间筛选（如果序号不代表新旧） #

如果备份表的序号和创建顺序不一致，改用表的创建时间排序：

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'YOUR_SCHEMA_NAME'
  AND TABLE_NAME LIKE 'EMP_BKP%'
ORDER BY CREATED DESC
LIMIT 3;

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