截至每月 1 日的每个项目的最新状态

怎么样

SELECT S.d,
       (SELECT tab.status
        FROM tab
        WHERE tab.datechange <= s.d
        ORDER BY tab.datechange DESC
        LIMIT 1)
FROM generate_series('2024-07-01', now(), interval '1 month') as s(d);

索引datechange应该会使这个查询表现良好。

（我的回答详细阐述了Laurenz Albe 的回答，他首先发表并值得称赞；它添加了不同的id时间表）

-- Each id gets its own timeline, starting with the debuting month of the first measured status: 
with timelines as
(
    select id, generate_series(date_trunc('month', min(dateChange)), now(), interval '1 month') d
    from tab
    group by id
)
-- Then for each month, lookup the last value it had before (well, the first value it had going backwards):
SELECT S.d,
       (SELECT tab.status
        FROM tab
        WHERE tab.id = s.id and tab.datechange <= s.d
        ORDER BY tab.datechange DESC
        LIMIT 1)
from timelines S;

这里有一个SQLFiddle 来演示它。

查看示例

with cte as(
  select * 
    ,lead(dateChange,1,date_trunc('month',dateChange)+ interval '1 month')
          over(partition by id order by dateChange) nextChange
  from test
)
select ids.id, reportdt, status, datechange, nextchange ,firstDt
from generate_series(cast('2024-07-01' as date),now(),interval '1 month') reportDt
left join (select id,min(dateChange) firstDt 
            from test 
            where dateChange>='2024-01-07'
            group by id
      )ids on firstDt<=reportDt
left join cte c on c.id=ids.id 
  and c.datechange<=reportDt and c.nextChange>reportDt 
order by ids.id, reportDt
  ;

小提琴