基本上，我有一張這樣的桌子
| ID | 時間戳 | 狀態 | 分鐘 |
|---|---|---|---|
| 1 | 7/12/2021, 17:38 | 離開 | 14.54 |
| 1 | 7/12/2021, 17:53 | 跑步 | 8.39 |
| 1 | 7/12/2021, 18:01 | 離開 | 8.12 |
| 1 | 2021 年 9 月 12 日，00:04 | 跑步 | 這里很有價值 |
該表按 id 排序，然后按時間戳升序排列。每個 id 代表一臺機器，在第一行，例如，從 2021 年 7 月 12 日 17:38 到 2021 年 7 月 12 日 17:53（第二行） ，機器關閉了 14.54 分鐘。17:53 機器開始運行8.39 分鐘，直到 2021 年 7 月 12 日 18:01 等...
然后我要做的是總結所有的分鐘并按天和狀態分組，所以我知道每臺機器每天關閉和運行的時間。問題是，當連續時間戳來自不同的連續日期時，我得到的那一天的總和值是錯誤的。在上表的示例中，我會得到關閉狀態下總分鐘數的總和將是 14.54 8.39 8.12 big value here。
理想情況下應該發生的是與此類似的組（僅考慮關閉狀態）
| ID | 日 | 狀態 | 分鐘 |
|---|---|---|---|
| 1 | 2021 年 7 月 12 日 | 離開 | 22.66 |
| 1 | 2021 年 8 月 12 日 | 離開 | 1440 |
| 1 | 2021 年 9 月 12 日 | 離開 | 4 |
我在想也許可以人為地在結果表上添加時間戳，例如“17/12/2021 24:00”，這樣我在分組時總是可以正確地切割每一天的間隔。
你能幫我么？真的很重要！！謝謝
uj5u.com熱心網友回復：
像這樣的東西：
with Cal as (
select cast(DayStarts as date) as Date, DayStarts, DayStarts interval '1 day' - interval '1 second' as DayEnds
from (
select CAST('2021-12-07' AS timestamp(0)) (n || ' day')::INTERVAL as DayStarts
from generate_series(0, 10) n
) Cal1
),
MyTbl as (
select
Tbl.id
, tbl.timestamp as StartTS
, tbl.state
, timestamp (tbl.minutes * interval '1 minute') as EndTS
from Tbl
)
select
C.Date
, T.State
, sum(extract(Epoch from (least(C.DayEnds, T.EndTS)
-
greatest(C.DaySTarts, T.StartTS) interval '1 second'))/60) as minutes
from Cal C
inner join
MyTbl T
on C.DayEnds>=T.StartTS
and C.DaySTarts<=T.EndTS
group by C.Date, T.State
order by C.Date, T.State
Cal 是日歷，從 2021 年 12 月 7 日開始為接下來的 10 天生成，它回傳一個日期列，以及日期的第一個/最后一個時刻（時間戳）。Tbl 是您的表，MyTbl 是通過將分鐘添加到時間戳列中加上一個結束時間戳 (EndTs)。然后我們加入這些表以尋找“重疊”；然后對于每個重疊，我們計算重疊的分鐘數，然后按日期和狀態匯總。
沒有正確包括每天的最后一秒，但如果四舍五入到分鐘就可以了。將其更改為使用 < NextDate 應該不難，以使其完全準確。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/418790.html
標籤：