假設有一個網管系統監控server運作情況,每分鐘紀錄一次狀態(up/down)
為了簡化起見,這裡只以一台server為例.
要找出每段 down的:
起始時間（down_start）
結束時間（down_end）
持續時長（duration）
連續 down 的記錄筆數（record_count）

--
create table server_status (
  recorded_at timestamp primary key
, status      text not null check (status in ('up', 'down'))
);

insert into server_status (recorded_at, status) values
-- 正常運作
('2025-01-01 10:00:00', 'up'),
('2025-01-01 10:01:00', 'up'),
('2025-01-01 10:02:00', 'up'),
-- 第一次停機 (3 分鐘)
('2025-01-01 10:03:00', 'down'),
('2025-01-01 10:04:00', 'down'),
('2025-01-01 10:05:00', 'down'),
-- 恢復
('2025-01-01 10:06:00', 'up'),
('2025-01-01 10:07:00', 'up'),
-- 第二次停機 (1 分鐘, 短暫)
('2025-01-01 10:08:00', 'down'),
-- 恢復
('2025-01-01 10:09:00', 'up'),
('2025-01-01 10:10:00', 'up'),
('2025-01-01 10:11:00', 'up'),
('2025-01-01 10:12:00', 'up'),
-- 第三次停機 (5 分鐘)
('2025-01-01 10:13:00', 'down'),
('2025-01-01 10:14:00', 'down'),
('2025-01-01 10:15:00', 'down'),
('2025-01-01 10:16:00', 'down'),
('2025-01-01 10:17:00', 'down'),
-- 恢復
('2025-01-01 10:18:00', 'up'),
('2025-01-01 10:19:00', 'up'),
('2025-01-01 10:20:00', 'up');

---
-- 先來做編號,依照紀錄時間,分兩種,第一種是全部的編號,第二種是依狀態編號

select *
     , row_number() over(order by recorded_at) as rn_all
     , row_number() over(partition by status order by recorded_at) as rn_status
  from server_status;
  
     recorded_at     | status | rn_all | rn_status
---------------------+--------+--------+-----------
 2025-01-01 10:00:00 | up     |      1 |         1
 2025-01-01 10:01:00 | up     |      2 |         2
 2025-01-01 10:02:00 | up     |      3 |         3
 2025-01-01 10:03:00 | down   |      4 |         1
 2025-01-01 10:04:00 | down   |      5 |         2
 2025-01-01 10:05:00 | down   |      6 |         3
 2025-01-01 10:06:00 | up     |      7 |         4
 2025-01-01 10:07:00 | up     |      8 |         5
 2025-01-01 10:08:00 | down   |      9 |         4
 2025-01-01 10:09:00 | up     |     10 |         6
 2025-01-01 10:10:00 | up     |     11 |         7
 2025-01-01 10:11:00 | up     |     12 |         8
 2025-01-01 10:12:00 | up     |     13 |         9
 2025-01-01 10:13:00 | down   |     14 |         5
 2025-01-01 10:14:00 | down   |     15 |         6
 2025-01-01 10:15:00 | down   |     16 |         7
 2025-01-01 10:16:00 | down   |     17 |         8
 2025-01-01 10:17:00 | down   |     18 |         9
 2025-01-01 10:18:00 | up     |     19 |        10
 2025-01-01 10:19:00 | up     |     20 |        11
 2025-01-01 10:20:00 | up     |     21 |        12
(21 筆資料)

-- 再來利用 rn_all, rn_status 計算後分組.
-- 當 down 記錄是連續的，rn_all 和 rn_down 都各自 +1，差值不變。一旦被 up 記錄中斷
-- rn_all 繼續增長但 rn_down 停住，差值就改變了，形成新的 group

with rn as (
select *
     , row_number() over(order by recorded_at) as rn_all
     , row_number() over(partition by status order by recorded_at) as rn_status
  from server_status
)
select recorded_at
     , rn_all - rn_status as grp
  from rn
 where status = 'down'
 order by recorded_at;

     recorded_at     | grp
---------------------+-----
 2025-01-01 10:03:00 |   3
 2025-01-01 10:04:00 |   3
 2025-01-01 10:05:00 |   3
 2025-01-01 10:08:00 |   5
 2025-01-01 10:13:00 |   9
 2025-01-01 10:14:00 |   9
 2025-01-01 10:15:00 |   9
 2025-01-01 10:16:00 |   9
 2025-01-01 10:17:00 |   9
(9 筆資料)

-- 分組之後就簡單了, min(), max(), count() 搭配使用

with rn as (
select *
     , row_number() over(order by recorded_at) as rn_all
     , row_number() over(partition by status order by recorded_at) as rn_status
  from server_status
), grps as (
select recorded_at
     , rn_all - rn_status as grp
  from rn
 where status = 'down'
)
select min(recorded_at) as down_start
     , max(recorded_at) as down_end
     , max(recorded_at) - min(recorded_at)
       + interval '1 minute' as duration
     , count(*) as record_count
  from grps
 group by grp
 order by down_start;
 
     down_start      |      down_end       | duration | record_count
---------------------+---------------------+----------+--------------
 2025-01-01 10:03:00 | 2025-01-01 10:05:00 | 00:03:00 |            3
 2025-01-01 10:08:00 | 2025-01-01 10:08:00 | 00:01:00 |            1
 2025-01-01 10:13:00 | 2025-01-01 10:17:00 | 00:05:00 |            5
(3 筆資料)

與趣味SQL 260217 最長連續登入天數, 其實是一樣的手法.傳送門