Redshift中如何高效获取两大主更新的最新次更新数据？

问题背景 #

我在Redshift中有一张可通过SQL访问的表，该表会定期新增数据。数据更新分为月度的主更新（major updates）和更短周期的次更新（minor updates），通过id字段追踪。表包含id、val1、val2三列，id格式为NNaaMM：

• NN为两位数字，标记主更新版本，从00开始递增；
• aa为固定的两位分组标识；
• MM为两位数字，标记次更新版本，从00开始递增。

需求是获取最近两个主更新版本各自对应的最新次更新数据，示例结果如下：

目前使用的SQL冗长且缓慢，可读性差，不利于维护：

WITH prefixes AS (
    SELECT SUBSTRING(id,1,3) AS prefix  
    FROM table
    WHERE id LIKE '%aa%'
    GROUP BY prefix
    ORDER BY prefix DESC
    LIMIT 2),

id_max AS (
    SELECT t.id AS id
    FROM prefixes 
    JOIN table t
    ON t.id LIKE (SELECT MAX(prefix) FROM prefixes)+'%'
    GROUP BY id
    ORDER BY id DESC
    LIMIT 1),

id_min AS (
    SELECT t.id AS id
    FROM prefixes 
    JOIN table t
    ON t.id LIKE (SELECT MIN(prefix) FROM prefixes)+'%'
    GROUP BY id
    ORDER BY id DESC
    LIMIT 1)

(SELECT *, 'current' AS which
FROM table
WHERE id = (SELECT MAX(id) FROM id_max))

UNION ALL

(SELECT *, 'previous' AS which
FROM table
WHERE id= (SELECT MAX(id) FROM id_min))

求更简洁高效、易读的SQL写法。

优化方案 #

可以通过拆分id字段提取主版本号，结合窗口函数筛选出最近两个主版本，再找到每个主版本下的最新次版本数据，整体逻辑更清晰，性能也更优：

优化后的SQL #

WITH parsed_data AS (
    -- 拆分id，提取主版本、分组、次版本
    SELECT 
        id,
        val1,
        val2,
        SUBSTRING(id, 1, 2) AS major_version, -- 提取NN部分作为主版本
        SUBSTRING(id, 5, 2) AS minor_version  -- 提取MM部分作为次版本
    FROM your_table_name
    WHERE id LIKE '%aa%' -- 过滤固定分组标识的记录
),
ranked_majors AS (
    -- 对主版本按版本号降序排名，取最近的两个主版本
    SELECT 
        major_version,
        ROW_NUMBER() OVER (ORDER BY major_version DESC) AS major_rank
    FROM parsed_data
    GROUP BY major_version
    ORDER BY major_rank
    LIMIT 2
),
latest_minors AS (
    -- 找到每个目标主版本下的最新次版本id
    SELECT 
        pd.id,
        rm.major_rank
    FROM parsed_data pd
    JOIN ranked_majors rm ON pd.major_version = rm.major_version
    QUALIFY ROW_NUMBER() OVER (PARTITION BY pd.major_version ORDER BY pd.minor_version DESC) = 1
)
-- 关联原表获取数据并标记版本类型
SELECT 
    t.id,
    t.val1,
    t.val2,
    CASE lm.major_rank 
        WHEN 1 THEN 'current'
        WHEN 2 THEN 'previous'
    END AS which
FROM your_table_name t
JOIN latest_minors lm ON t.id = lm.id
ORDER BY lm.major_rank, t.val1;

优化思路说明 #

1. 拆分id字段：直接提取主版本（NN）和次版本（MM），避免使用模糊匹配LIKE带来的性能损耗，同时让逻辑更直观。
2. 窗口函数筛选：
   • 用ROW_NUMBER()对主版本排名，快速锁定最近的两个主版本；
   • 再通过QUALIFY（Redshift原生支持）结合窗口函数，直接筛选每个主版本下的最新次版本，无需多次子查询和分组。
3. 简化关联逻辑：最终只需一次关联即可获取目标数据，减少查询层级，提升可读性和执行效率。

额外优化建议 #

如果表数据量较大，建议给id字段建立索引，或者单独存储拆分后的major_version、minor_version字段并建立索引，进一步提升查询性能。

内容的提问来源于stack exchange，提问作者Jānis Lazovskis