google bigquery - SQL (big)query sequencial properties -


we have denormalized data in bigquery:

user,   cities lived, time,  other properites       ld            1942       sf            1902       la            2004       tk            2012 b       sf            1935 b       sd            1972 b       la            1899 c       ... 

what efficient way find users lived in sf before lived in la?

the output list. in above case, "a" listed.

it better if list has both date , city:

a, 1902, sf, 2004, la ... 

you can use join compute result want:

select la_table.user user, la_table.city, la_table.time,     sf_table.city, sf_table.time  (   select user, time, city la yourtable city = "la") la_table join each (   select user, time, city la yourtable city = "sf") sf_table on la_table.user = sf_table.user la_table.time > sf_table.time 

note "each" in join clause, since size of tables going large. see bigquery join reference.


Comments

Popular posts from this blog

php - Wordpress website dashboard page or post editor content is not showing but front end data is showing properly -

javascript - Get parameter of GET request -

javascript - Twitter Bootstrap - how to add some more margin between tooltip popup and element -