WITH in BigQuery

sql google-bigquery common-table-expression
cshin9 picture cshin9 · May 24, 2016 · Viewed 28.9k times · Source

Does BigQuery support the WITH clause? I don't like formatting too many subqueries.

For example:

WITH alias_1 AS (SELECT foo1 c FROM bar)
, alias_2 AS (SELECT foo2 c FROM bar a, alias_1 b WHERE b.c = a.c)
SELECT * FROM alias_2 a;

Answer

Mikhail Berlyant picture Mikhail Berlyant · May 24, 2016

Recently introduced BigQuery Standard SQL does support WITH clause
See more about WITH clause

See also how to Enabling Standard SQL

Related questions

Cannot access field in Big Query with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>>

I'm trying to run a query using Standard SQL Dialect (ie not Legacy SQL) on BigQuery. My query is: SELECT date, hits.referer FROM `refresh.ga_sessions_xxxxxx*` LIMIT 1000 But keep getting the error Error: Cannot access field referer on …

Read More
BigQuery SQL WHERE Date Between Current Date and -15 Days

I am trying to code the following condition in the WHERE clause of SQL in BigQuery, but I am having difficulty with the syntax, specifically date math: WHERE date_column between current_date() and current_date() - 15 days This seems …

Read More
How can I extract date from epoch time in BigQuery SQL

I have date stored in Epoch Time and I want to extract Date from it. I tried the code below and I get null as output. date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as Extracted_date_Epoch Ex …

Read More