Amazon Redshift中按id筛选最大value所属nested_id全量时序数据的SQL实现

开发者阿泽

2026-10-3

实现方案

Amazon Redshift支持标准SQL窗口函数，用窗口函数是最简洁高效的实现方式，不需要嵌套多层子查询，两种可行写法如下：

写法1：用RANK窗口函数（兼容多nested_id同最大value的场景）

WITH ranked_nested AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY id ORDER BY value DESC) AS value_rnk
    FROM mytable
    WHERE id IN (1, 2)
),
target_nested AS (
    SELECT DISTINCT id, nested_id
    FROM ranked_nested
    WHERE value_rnk = 1
)
SELECT t.id, t.nested_id, t.date, t.value
FROM mytable t
INNER JOIN target_nested tn
    ON t.id = tn.id 
    AND t.nested_id = tn.nested_id
WHERE t.id IN (1, 2)
ORDER BY t.id, t.date;

逻辑说明：

先按id分组，给组内所有行按value倒序排名，最大value的行排名为1
提取每个id下排名为1的所有nested_id（如果多个nested_id的value同为最大值，会全部返回）
关联回原表即可拿到目标nested_id的全量时序数据

写法2：用FIRST_VALUE窗口函数（更精简）

SELECT id, nested_id, date, value
FROM (
    SELECT 
        *,
        FIRST_VALUE(nested_id) OVER (
            PARTITION BY id 
            ORDER BY value DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS target_nested_id
    FROM mytable
    WHERE id IN (1, 2)
) t
WHERE nested_id = target_nested_id
ORDER BY id, date;

逻辑说明：