PostgreSQL：计算在每个状态下花费的累计时间

fumotvh3 于 2023-02-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(144)

给定一个带有时间戳的状态更改表，如何计算在每个状态下花费的总时间？
例如：

CREATE TABLE states (state text, dt timestamp without time zone);
INSERT INTO states (state, dt) VALUES ('blue',  '2023-02-21T00:00:00');
INSERT INTO states (state, dt) VALUES ('green', '2023-02-21T01:00:00');
-- redundant info - "still in this state" - should be handled correctly
INSERT INTO states (state, dt) VALUES ('green', '2023-02-21T02:00:00');
INSERT INTO states (state, dt) VALUES ('orange', '2023-02-21T03:00:00');
-- state with no end (no next state) should be handled correctly
INSERT INTO states (state, dt) VALUES ('red', '2023-02-21T05:00:00');

我想要一个生成"1小时在蓝色状态，2小时在绿色状态"等的查询。

postgresql

来源：https://stackoverflow.com/questions/75522333/postgresql-calculate-cumulative-time-spent-in-each-state

1条答案

按热度按时间

mklgxw1f1#

使用窗口查询

select 
    state, 
    sum(
      -- if there is no lead (next row), assume that state continues
      -- until the present
      COALESCE(lead_dt, NOW()) - dt
    ) duration
from  ( 
    select
        t.*,
        lead(dt) over(order by dt) lead_dt
    from states t
) t
group by state
order by state;

这就产生了（在我写这篇文章的时候）：

state  |    duration
--------+-----------------
 blue   | 01:00:00
 green  | 02:00:00
 orange | 02:00:00
 red    | 04:58:43.250921

当然，运行这个函数的日期和时间越晚，在最后一个状态中花费的时间就越长，因为它的结束时间是NOW()。
（优点：基于this answer，用于SQL Server。）

赞(0）回复(0）举报 2023-02-22

我来回答

PostgreSQL：计算在每个状态下花费的累计时间

1条答案

使用窗口查询

相关问题

热门标签

最新问答