How can i find 'inteval' in sql?

For example:

If i have in table records like:

id    timestamp    flag
a1    1000         false
a1    2000         false
a1    3500         true
a1    4000         true
a2    1000         false
a2    6000         true
a3    1000         false
a4    2000         true

I want find 'last interval' when flag is true. if no interval, max.

I want result like this.

id    interval
a1    1500
a1    500
a2    5000
a4    9999999

I will run this query in hive with sql. Is it possible? or not?

I can't find how to do it.

sql can find 'most near' row?

Solution

I find window function udf in hive.

https://cwiki.apache.org/confluence/display/hive/languagemanual+windowingandanalytics#LanguageManualWindowingAndAnalytics-PARTITIONBYwithonepartitioningcolumn,noORDERBYorwindowspecification

'lag' work for me.

select id, `interval`
from(
    SELECT *, (`timestamp` - LAG(`timestamp`, 1, -999999) OVER (PARTITION BY id ORDER BY `timestamp`)) as `interval`
    FROM test_table)
as first
where flag == 'true'

upper code perfectly work.