CODEWITHSUNDEEP
sqlamazon-web-serviceswindow-functionsamazon-athenapresto
belostoky
belostoky

Reputation: 974

Athena (Presto) SQL window functions

say I have the following data in Athena: 

id      ts
uid1    1499672134268
uid1    1499672134292
uid1    1499672136189
uid1    1499672136212
uid1    1499719927907
uid1    1499719927940
uid1    1499719927975
uid1    1499719927999
uid2    1499670000000
uid2    1499670000010
uid2    1499688880010

I want to rank it so:

rank    id      ts
1       uid1    1499672134268
1       uid1    1499672134292
1       uid1    1499672136189
1       uid1    1499672136212
2       uid1    1499719927907
2       uid1    1499719927940
2       uid1    1499719927975
2       uid1    1499719927999
1       uid2    1499670000000
1       uid2    1499670000010
2       uid2    1499688880010

the logic is:
the difference between the min value in the second group and the max value in the first group is larger than x

I am thinking some combination of dense_rank() OVER (PARTITION BY id ... ) could resolve it, but I am a total newbie to window functions in SQL.

Thanks

Upvotes: 2

Views: 21540

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use lag and get the difference from the previous row's ts and reset it using a running sum, when it goes > x. 

select id,ts,1+sum(col) over(partition by id order by ts) as rnk
from (select id,ts
      ,case when ts-lag(ts,1,ts) over(partition by id order by ts) > 3000 then 1 else 0 end as col
      from tbl
     ) t

Replace 3000 (x) in the case expression with the desired value.

Upvotes: 3

Related Questions