I have the following dataset showing the status of certain tools.
id || Toolid || time || message || timeToComplete
===========================================================
1 || 1 || 1578294000 || running || 153
2 || 1 || 1578294153 || assistance || null
3 || 1 || 1578294204 || done || null
4 || 1 || 1578294264 || running || 208
5 || 1 || 1578294472 || assistance || null
6 || 1 || 1578294524 || done || null
7 || 2 || 1578294584 || running || 127
8 || 2 || 1578294711 || assistance || null
9 || 2 || 1578294772 || done || null
I need this dataset to calculate the response time, but I can't find a query that succesfully substracts the rows of message=done minus message=assistance.
The output needs to look like this:
Toolid || time || timeToComplete || responseTime
================================================
1 || 1578294000 || 153 || 51
1 || 1578294264 || 208 || 52
2 || 1578294584 || 127 || 61
Another challenge is that the assistance-message and done-message aren't always exactly 1 apart. They do always arrive in the same order from the tools (running-assistance-done).
Can someone help me with the necessary query?
I think you want conditional aggregation -- but you need a grouping. You can calculate this by adding up the number of "running" messages for each row:
select toolid,
min(case when message = 'running' then time end) as time,
min(timeToComplete) as timeToComplete,
(min(case when message = 'done' then time end) -
min(case when message = 'assistance' then time end)
) as responseTime
from (select t.*,
sum(case when message = 'running' then 1 else 0 end) over (partition by toolid order by id) as grp
from t
) t
group by toolid, grp;