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
Post a Comment