BigQuery — Nested WITH clause ( WITH is like a Subquery )

BIGQUERY.md

BigQuery — Nested WITH clause ( WITH is like a Subquery )

Query Example

WITH result AS (
   WITH example AS ( SELECT * FROM `dataset.table` )
   SELECT * FROM example
)

SELECT * FROM result

Public Dataset Query Example

WITH result AS (
   WITH games_wide_limitted AS ( SELECT * FROM `bigquery-public-data.baseball.games_wide` LIMIT 100 )
   SELECT * FROM games_wide_limitted
)

SELECT * FROM result

Deeper nest

WITH result AS (
   WITH reg_season AS (
     WITH  year_2016 AS (
       SELECT * FROM `bigquery-public-data.baseball.games_wide` WHERE year = 2016
     )
     SELECT * FROM year_2016 WHERE seasonType = 'REG'
   )
   SELECT * FROM reg_season
)

SELECT * FROM result

Links

• Qiita—Japanese
• Medium—Imported