我正在嘗試查找處于高 (1) 狀態的事件的持續時間。狀態被記錄到一個表中，如下所示
time |state|
------- -----
01:00:00| 0|
01:01:00| 1|
01:02:35| 1|
01:03:20| 0|
01:04:00| 0|
01:05:00| 0|
01:06:00| 1|
01:07:00| 1|
01:08:00| 1|
01:09:10| 0|
01:10:00| 0|
01:11:00| 1|
01:12:45| 0|
我正在尋找的結果是這樣的：
starttime | endtime | duration |
------------- ------------ -------------
01:01:00 | 01:03:20 | 00:02:20 |
01:06:00 | 01:09:10 | 00:03:10 |
01:11:00 | 01:12:45 | 00:01:45 |
與這篇文章幾乎相同，但不完全相同
不太確定如何完成這個..任何幫助將不勝感激
uj5u.com熱心網友回復：
嘗試這個 ：
WITH list AS
( SELECT time AS starttime
, state
, CASE
WHEN state = 0
THEN min(time) FILTER (WHERE state = 1) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
ELSE min(time) FILTER (WHERE state = 0) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
END AS endtime
FROM test
)
SELECT min(starttime)
, endtime
, endtime - min(starttime) AS duration
FROM list
WHERE state = 1
GROUP BY endtime
dbfiddle中的測驗結果
uj5u.com熱心網友回復：
您必須使用視窗函式（此處為結果）。
with y as (
select x.time, lead(time,1) over (), lead(time,1) over () - x.time,state,previous_state
from (select *,lag(state,1) over (order by time) previous_state from t1) x
where (x.state = 1 and x.previous_state = 0) or (x.state = 0 and x.previous_state = 1)
order by time
)
select * from y where state = 1 and previous_state = 0
uj5u.com熱心網友回復：
這可能是間隙和孤島問題，我們需要使用LAG視窗函式 &CASE WHEN來確定邏輯condition列上的組（事件處于高 (1) 狀態。）
然后ROW_NUMBER用來做一組連續數。
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY condition ORDER BY time) -
ROW_NUMBER() OVER ( ORDER BY time) grp
FROM (
SELECT *,
(CASE WHEN state = 1 OR LAG(state,1,state) OVER(ORDER BY TIME) = 1 THEN 1 END) condition
FROM T
) t1
)
SELECT MIN(time) starttime ,
MAX(time) endtime ,
MAX(time) - MIN(time) duration
FROM CTE
WHERE condition IS NOT NULL
GROUP BY grp
ORDER BY 1
sqlfiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/441848.html
標籤：sql PostgreSQL