select * from ( select col1 as old_st,col2 as person,lead(col2) over (partition by col2 order by timestamp) next_st from main_table ) m where next_st is not null
This will give you old street to new street in one row. You can then join to lookup table. On Fri, Aug 5, 2016 at 12:48 PM, Divya Gehlot <divya.htco...@gmail.com> wrote: > based on the time stamp column > > On 5 August 2016 at 10:43, ayan guha <guha.a...@gmail.com> wrote: > >> How do you know person1 is moving from street1 to street2 and not other >> way around? Basically, how do you ensure the order of the rows as you have >> written them? >> >> On Fri, Aug 5, 2016 at 12:16 PM, Divya Gehlot <divya.htco...@gmail.com> >> wrote: >> >>> Hi, >>> I am working with Spark 1.6 with scala and using Dataframe API . >>> I have a use case where I need to compare two rows and add entry in the >>> new column based on the lookup table >>> for example : >>> My DF looks like : >>> col1 col2 newCol1 >>> street1 person1 >>> street2 person1 area1 >>> street3 person1 area3 >>> street5 person2 >>> street6 person2 area5 >>> street7 person4 >>> street9 person4 area7 >>> >>> loop up table looks like >>> street1 -> street2 - area1 >>> street2 -> street 3 - area3 >>> street5 -> street6 - area5 >>> street 7-> street 9 - area 7 >>> >>> if person moving from street 1 to street 2 then he is reaching area 1 >>> >>> >>> Would really appreciate the help. >>> >>> Thanks, >>> Divya >>> >>> >>> >>> >> >> >> -- >> Best Regards, >> Ayan Guha >> > > -- Best Regards, Ayan Guha
