patternsqlMinor
Get total amount of time between records when changed state
Viewed 0 times
totalamountrecordstimegetbetweenstatewhenchanged
Problem
I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown.
So the data is stored like this:
and so on.
The example above shows
What I need
Need to calculate the total amount of time for the whole breakdown for
What I've done
Currently I can define a breakdown time for each device separately (device_1 = 30mins, device_2 = 30mins). For this I transformed the table using
So the datetime difference between timestamp and lead_timestamp in sum gives me the total breakdown time for a device.
It is the same device list for any owner. The data snapsho
So the data is stored like this:
device_owner device_id timestamp status
owner1 device_1 2001-01-01 09:00 0
owner1 device_2 2001-01-01 09:15 0
owner1 device_1 2001-01-01 09:30 1
owner1 device_2 2001-01-01 09:45 1and so on.
The example above shows
- device_1 was down from 09:00 to 09:30 (total 30 mins),
- device_2 was down from 09:15 to 09:45 (total 30 mins)
What I need
Need to calculate the total amount of time for the whole breakdown for
owner_1 will be 45 mins (from 09:00 to 09:45) instead of 60 mins (30 + 30).device_owner total_breakdown_min
owner_1 45What I've done
Currently I can define a breakdown time for each device separately (device_1 = 30mins, device_2 = 30mins). For this I transformed the table using
LEAD over partition by device_owner, device_id and date part from timestamp:device_owner device_id timestamp status lead_timestamp lead_status
owner1 device_1 2001-01-01 09:00 0 2001-01-01 09:30 1
owner1 device_1 2001-01-01 09:30 1 NULL NULL
owner1 device_2 2001-01-01 09:15 0 2001-01-01 09:45 1
owner1 device_2 2001-01-01 09:45 1 NULL NULLSo the datetime difference between timestamp and lead_timestamp in sum gives me the total breakdown time for a device.
;WITH LeadStatus AS
(
SELECT
D.*,
lead_status = LEAD(D.status) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC),
lead_timestamp = LEAD(D.datetime) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC)
FROM
#DeviceStatus AS D
)
SELECT * FROM LeadStatusIt is the same device list for any owner. The data snapsho
Solution
We can use conditional aggregation and an
We now use
Now we use conditional aggregation and the
From here on we can now use
We can now simply use
```
SELECT device_owner,
sum(datediff(minute,
maxts,
mints)) total_breakdown_min
FROM (SELECT device_owner,
max(timestamp) mints,
min(timestamp) maxts
FROM (SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
times
OVER clause to get the number of broken devices for each row in devicestatus.SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus;We now use
lag() to get the previous number of broken devices for each row.SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1;Now we use conditional aggregation and the
OVER clause once again to get an identifier for each period where at least one device of a user was down.SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1) x2;From here on we can now use
GROUP BY to get the minimum and maximum timestamp of the periods where at least one device of a user was down.SELECT device_owner,
max(timestamp) mints,
min(timestamp) maxts
FROM (SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1) x2) x3
GROUP BY device_owner,
broken_period#;We can now simply use
datediff(), sum() and GROUP BY to calculate the total downtime for each device owner.```
SELECT device_owner,
sum(datediff(minute,
maxts,
mints)) total_breakdown_min
FROM (SELECT device_owner,
max(timestamp) mints,
min(timestamp) maxts
FROM (SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
times
Code Snippets
SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus;SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1;SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1) x2;SELECT device_owner,
max(timestamp) mints,
min(timestamp) maxts
FROM (SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1) x2) x3
GROUP BY device_owner,
broken_period#;SELECT device_owner,
sum(datediff(minute,
maxts,
mints)) total_breakdown_min
FROM (SELECT device_owner,
max(timestamp) mints,
min(timestamp) maxts
FROM (SELECT device_owner,
timestamp,
sum(CASE
WHEN previous_broken# = 0
THEN 1
ELSE
0
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken_period#
FROM (SELECT device_owner,
timestamp,
broken#,
lag(broken#,
1,
0) OVER (PARTITION BY device_owner
ORDER BY timestamp) previous_broken#
FROM (SELECT device_owner,
timestamp,
sum(CASE status
WHEN 0
THEN 1
WHEN 1
THEN -1
END) OVER (PARTITION BY device_owner
ORDER BY timestamp) broken#
FROM devicestatus) x1) x2) x3
GROUP BY device_owner,
broken_period#) x4
GROUP BY device_owner;Context
StackExchange Database Administrators Q#211041, answer score: 3
Revisions (0)
No revisions yet.