我有一個記錄某些機器的三種狀態的應用程式。我需要報告機器每天從 00:00:00 到 23:59:59 處于每個狀態的時間。
我需要幫助來構建一個 postgresql 查詢，以獲取白天發生某些事件的整個時間間隔。
例如，如下所示，資料顯示在前一天 23:50:00 到第二天 01:00:00 之間。
| device_id varchar(50) | 值 int4 | 日期時間時間戳 |
|---|---|---|
| 設備1 | 0 | 2022-23-04 23:50:00 |
| 設備1 | 0 | 2022-24-04 00:10:00 |
| 設備1 | 0 | 2022-24-04 00:15:00 |
| 設備1 | 0 | 2022-24-04 00:20:00 |
| 設備1 | 1 | 2022-24-04 00:25:00 |
| 設備1 | 1 | 2022-24-04 00:30:00 |
| 設備1 | 1 | 2022-24-04 11:00:00 |
| 設備1 | 0 | 2022-24-04 21:00:00 |
| 設備1 | 1 | 2022-25-04 01:00:00 |
我正在計算狀態變化之間的持續時間，我也在表格中插入，我只是在求和，但它給了我以下結果：
全部的：
- 狀態 = 0 - 04:35
- 狀態 = 1 - 20:35
- 兩者之和 = 25:10:00
我需要的查詢不應該考慮屬于不是 24/04/2021 的其他日期的時間部分，并且必須給我：
2022 年 4 月 24 日總天數：
- 狀態 = 0 - 03:25
- 狀態 = 1 - 20:35
- 兩者之和 = 24:00:00
在每一天的每一天，我都需要知道機器在每個狀態下停留的時間百分比并構建一個餅圖。
有沒有辦法進行符合此需求的查詢？
提前感謝大家的幫助。
@shawnt00 答案有效。現在我正在嘗試弄清楚如何組織資料，以便查詢結果如下：
| 設備編號 | state_0 | state_1 | state_9 |
|---|---|---|---|
| 設備編號 | 時間跨度_狀態_0 | 時間跨度_狀態_1 | 時間跨度_狀態_9 |
對于給定的示例，應該如下所示，我添加了第二個設備只是為了擴充示例:)
| 設備編號 | state_0 | state_1 | state_9 |
|---|---|---|---|
| 設備1 | 03:25 | 20:35 | 00:00 |
| 設備2 | X | 是 | Z |
附件。
獲勝者馬丁斯
uj5u.com熱心網友回復：
我認為您只需要使用lead()/lag()一些case運算式來檢測午夜的跨度。無需加入：
with data as (
select *,
cast(date_trunc('day', Date_time) as date) as dt,
lag(Date_time) over (partition by device_id order by Date_time) as last_Date_time,
lead(Date_time) over (partition by device_id order by Date_time) as next_Date_time
from T
)
select device_id, dt as "date", Value,
coalesce(sum(
case when date_trunc('day', next_Date_time) > date_trunc('day', Date_time)
then date_trunc('day', Date_time interval '1 day') - Date_time
else coalesce(next_date_time - Date_time, interval '0 seconds') end
case when date_trunc('day', last_Date_time) < date_trunc('day', Date_time)
then Date_time - date_trunc('day', Date_time)
else interval '0 seconds' end
), interval '0 seconds') as timespan2
from data
group by device_id, dt, Value
order by device_id, dt, Value;
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=ab32fee1615b637f9f2f844aa1bf5064
我對所有 PostGres 日期函式都不太熟悉，因此可能有一種更簡潔的方法來計算時間。
uj5u.com熱心網友回復：
下面的查詢會起作用。
select
std.state,
sum(case
when std.rk = 1 then std.time_diff std.time_diff_start
when std.trunc_state_start = std.trunc_state_end then std.time_diff
when std.trunc_state_start <> std.trunc_state_end then std.time_diff_end
else std.time_diff
end)
from
(
select
a.state,
date_trunc('day', a.date_time) as trunc_state_start,
date_trunc('day', b.date_time) as trunc_state_end,
b.date_time - a.date_time as time_diff,
a.date_time - date_trunc('day', a.date_time) as time_diff_start,
date_trunc('day', b.date_time) - a.date_time as time_diff_end,
rank() over(order by a.date_time) rk
from
(select ds.*, rank() over(order by date_time) rk from devicestat ds) a
inner join
(select ds.*, rank() over(order by date_time) rk from devicestat ds) b
on
a.rk 1 = b.rk
where
date_trunc('day', a.date_time) = '2022-04-24') std
group by
std.state;
本身之間的連接使我很容易計算狀態開始和結束時間之間的時間差。剩下的就是如何計算一天的開始和結束之間的邊界差異。我認為有很多方法可以做到這一點，但這就是我想到的。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/464765.html
標籤：sql 数据库 PostgreSQL
上一篇：生成列使用的SQL更新列