SELECT STRFTIME_UTC_USEC(a.ending_at, "%Y-%m-%d") ending_at1, STRFTIME_UTC_USEC(b.ending_at-60*86400000000, "%Y-%m-%d") starting_at2, STRFTIME_UTC_USEC(b.ending_at, "%Y-%m-%d") ending_at2, a.country, b.country, CORR(a.c, b.c) corr, COUNT(*) cFROM ( SELECT country, date+i*86400000000 ending_at, c, i FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 60) b) bJOIN ( SELECT country, date+i*86400000000 ending_at, c, i FROM [gdelt-bq:sample_views.country_date_matconf_numarts] a CROSS JOIN (SELECT i FROM [fh-bigquery:public_dump.numbers_255] WHERE i < 60) b WHERE country='Egypt' AND date+i*86400000000 = PARSE_UTC_USEC('2011-01-27')) aON a.i=b.iWHERE a.ending_at != b.ending_atGROUP EACH BY ending_at1, ending_at2, starting_at2, a.country, b.countryHAVING (c = 60 AND ABS(corr) > 0.254)ORDER BY corr DESC
Use promo code NEXT1720 to save $300 off general admission
Follow
No comments :
Post a Comment