如何在Amazon Redshift中为每个ID获取指定日期前的最新状态记录

你的问题核心是要为每个ID单独找出在指定日期（2022-02-14）及之前的最新状态记录，但原查询的逻辑有问题——你用了GROUP BY date, status再全局LIMIT 1，这只会返回整个表中最新的一条记录，而不是每个ID各自的最新记录。

在Redshift里，最简洁高效的解决方案是使用窗口函数，或者先分组找出每个ID的最大符合条件日期再关联原表。下面给你两种可行的方法：

方法一：使用ROW_NUMBER()窗口函数（推荐） #

窗口函数可以帮我们按ID分组，给每个组内的记录按日期降序编号，然后取每个组的第一条（即最新日期的记录）：

WITH ranked_records AS (
    SELECT 
        id,
        date,
        status,
        -- 按ID分区，每个分区内按日期从新到旧排序，编号从1开始
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS record_rank
    FROM table1
    WHERE date <= '2022-02-14' -- 过滤指定日期及之前的记录
)
SELECT id, date, status
FROM ranked_records
WHERE record_rank = 1; -- 取每个ID的最新记录

这个方法的优势是逻辑清晰，而且如果后续需要处理更复杂的规则（比如日期相同时按status排序），只需修改ORDER BY的条件即可。

方法二：先找每个ID的最大日期再关联 #

如果你更习惯分组查询的逻辑，可以先找出每个ID在指定日期前的最大日期，再通过关联原表拿到对应的状态：

WITH id_max_dates AS (
    SELECT 
        id,
        MAX(date) AS latest_date -- 找出每个ID符合条件的最大日期
    FROM table1
    WHERE date <= '2022-02-14'
    GROUP BY id
)
SELECT t.id, t.date, t.status
FROM table1 t
JOIN id_max_dates md 
    ON t.id = md.id AND t.date = md.latest_date; -- 关联原表拿到对应状态

这个方法适合对窗口函数不太熟悉的场景，需要注意的是：如果某个ID在同一个最大日期有多个不同状态的记录（你的原数据里没有这种情况），这个查询会返回多条结果，而窗口函数的方法会默认取其中一条（可以通过调整排序规则控制）。

为什么你的原查询不行？ #

你的原查询中，GROUP BY date, status是按日期和状态分组，再加上DISTINCT id并不能实现按ID聚合的效果，最后LIMIT 1只会返回整个结果集中的第一条记录，自然只能拿到一个ID的数据。

备注：内容来源于stack exchange，提问作者nomnom3214