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

Question

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!

score 0 · Accepted Answer · answered Dec 22 '15 at 23:56

0

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';

answered Dec 22 '15 at 23:56

Gordon Linoff

1,242,037
58
646
786

This looks like what I am looking for but when I run it I can en error due to "Unsupported complex type: MAP". Any ideas of what this could be? – manjam Dec 23 '15 at 16:15
I figured it out. The field is a map field which is not supported for windowing functions. Putting it into a sub query fixes that and then your answer works. Thanks! – manjam Dec 23 '15 at 23:03

score 0 · Answer 2 · answered Dec 23 '15 at 00:09

0

Lead and Lag functions of Oracle will help you to achieve your expected results.

Examples

answered Dec 23 '15 at 00:09

Avi

1,115
8
20
30

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

2 Answers2