3 ms·
BigQuery pipe syntax: A much cleaner way to write SQL
- gigatexal 2y agoI've been doing SQL for nearly 15 years I think I can get behind the new syntax. I really like that I can start with the from clause. It's something I really like in things like DuckDB. Examples: -- Standard Syntax SELECT AVG(num_trips) AS avg_trips_per_year, payment_type FROM ( SELECT EXTRACT(YEAR FROM trip_start_timestamp) as year, payment_type, COUNT() AS num_trips FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips` GROUP BY year, payment_type ) GROUP BY payment_type ORDER BY payment_type; Here’s that same query using pipe syntax — no subquery needed! -- Pipe Syntax FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips` |> EXTEND EXTRACT(YEAR FROM trip_start_timestamp) AS year |> AGGREGATE COUNT() AS num_trips GROUP BY year, payment_type |> AGGREGATE AVG(num_trips) AS avg_trips_per_year GROUP BY payment_type ASC;