I have a table like:

select it.name as target_app, hsu.value as req_rate
from items it 
inner join hosts hs on hs.hostid = it.hostid
inner join history hsu on hsu.itemid = it.itemid
where it.name like '% - Hop%' and to_timestamp(hsu.clock) BETWEEN NOW() - INTERVAL '1 MINUTES' AND NOW() 
order by it.name

How can get a table like this? :

CodePudding user response：

You can try to use LEAD window function.

SELECT *
FROM (
    select it.name as target_app, 
           LEAD(it.name) OVER(ORDER BY it.name) the_2nd_hop
           hsu.value as req_rate
    from items it 
    inner join hosts hs on hs.hostid = it.hostid
    inner join history hsu on hsu.itemid = it.itemid
    where it.name like '% - Hop%' and to_timestamp(hsu.clock) BETWEEN NOW() - INTERVAL '1 MINUTES' AND NOW() 
) t1
WHERE the_2nd_hop IS NOT NULL
order by target_app