Search code examples
sqlhivewindowing

HIVE: Get all records where preceding record (by timestamp) is a specific value

I am doing pathing analysis where I need to see where one page leads to. How do I write a query that gets me all the records that have a preceding record of a specific value.

For example: 

col1 timestamp
a    1   
b    2
a    3
c    4
b    5
e    6

I would want to return only c and b

I am trying to use windowing functions to do this but I have no experience with them and have completely failed :-(

Thanks for the answers!

Solution

  • You would use the lag() function . . . and a subquery:

    select t.*
from (select t.*, lag(col1) over (order by timestamp) as prev_col1
      from t
     ) t
where prev_col1 = 'a';