如何在Redshift中查询指定时间点状态为available的书籍ID列表

核心思路 #

对于每本图书，找到指定查询时间之前最后一次状态变更的记录，判断该记录的状态是否为available，即可得到目标书籍ID列表。

方法一：使用窗口函数ROW_NUMBER() #

这是通用且可靠的方案，能处理同一图书在同一时间有多条状态记录的边缘情况：

-- 替换your_table_name为实际表名，替换指定查询时间为你需要的时间点
WITH latest_status AS (
    SELECT 
        book_id,
        status,
        -- 按图书分组，按状态生效时间倒序排序，标记最新记录为1
        ROW_NUMBER() OVER (
            PARTITION BY book_id 
            ORDER BY status_valid_from DESC
        ) AS rn
    FROM your_table_name
    WHERE status_valid_from <= '2024-05-20 12:00:00'
)
SELECT DISTINCT book_id
FROM latest_status
WHERE rn = 1 AND status = 'available';

方法二：使用MAX()聚合关联 #

如果业务上不会出现同一图书在同一时间有多个状态记录的情况，这个方案更简洁：

WITH book_latest_valid_time AS (
    SELECT 
        book_id,
        -- 获取每本图书在查询时间前的最后状态生效时间
        MAX(status_valid_from) AS latest_valid_time
    FROM your_table_name
    WHERE status_valid_from <= '2024-05-20 12:00:00'
    GROUP BY book_id
)
-- 关联原表获取对应状态
SELECT DISTINCT t.book_id
FROM your_table_name t
JOIN book_latest_valid_time lt 
    ON t.book_id = lt.book_id 
    AND t.status_valid_from = lt.latest_valid_time
WHERE t.status = 'available';

动态传入查询时间 #

如果需要多次查询不同时间点，可以用Redshift会话变量实现动态传参：

-- 设置查询时间变量
SET query_time = '2024-05-20 12:00:00';

WITH latest_status AS (
    SELECT 
        book_id,
        status,
        ROW_NUMBER() OVER (
            PARTITION BY book_id 
            ORDER BY status_valid_from DESC
        ) AS rn
    FROM your_table_name
    WHERE status_valid_from <= CURRENT_SETTING('query_time')::TIMESTAMP
)
SELECT DISTINCT book_id
FROM latest_status
WHERE rn = 1 AND status = 'available';

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