如何获取PostgreSQL表中每月1日各条目的最新状态？

要查询每月1日对应条目的状态，核心是找到每月1日之前最后一次更新的状态，可以通过生成日期序列结合横向关联（LATERAL JOIN）实现：

完整SQL语句 #

SELECT 
    dates.month_start,
    t.id,
    t.status
FROM (
    -- 生成目标时间段内的每月1日序列
    SELECT s::date AS month_start
    FROM generate_series('2024-07-01'::date, CURRENT_DATE, INTERVAL '1 month') s
) dates
LEFT JOIN LATERAL (
    -- 对每个月1日，找到该日期前最新的状态记录
    SELECT id, status
    FROM test
    WHERE datechange < dates.month_start
    ORDER BY datechange DESC
    LIMIT 1
) t ON true
ORDER BY dates.month_start;

逻辑说明 #

1. 生成日期序列：用generate_series生成从2024-07-01到当前日期的所有每月1日，转换为date类型确保只保留日期部分。
2. 匹配最新状态：通过LEFT JOIN LATERAL，针对每个月1日，从test表中筛选出时间早于该月1日的记录，按datechange倒序排列后取第一条，即为该月1日生效的状态。
3. 排序输出：最终结果按月份升序排列，清晰展示各月1日的状态。

验证结果 #

执行后会得到符合需求的结果：

• 2024-07-01 对应的状态为12
• 2024-08-01 对应的状态为33
• 后续月份会根据表中记录自动匹配对应状态

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