Work with multi-statement queries in BigQuery

Multi-statement queries

A multi-statement query consists of one or more SQL statements separated by semicolons.

Multi-statement queries can also include procedural language statements, which let you use variables or implement control flow with your SQL statements.

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 20 AS x)
SELECT (x*x) AS result FROM Numbers;   # the result is 400

-- Query 1: Find the total number of bike hires
SELECT COUNT(*) AS total_bike_hires
FROM `bigquery-public-data.london_bicycles.cycle_hire`;   # the result is 83434866

-- Query 2: Find the average duration of bike hires
SELECT AVG(duration) AS average_duration
FROM `bigquery-public-data.london_bicycles.cycle_hire`;   # the result is 1307

Each statement will be executed sequentially, and you’ll get the result of each query independently. The result should look like this

Status	End time	SQL	Stages completed	Bytes processed	Action
✅	21:11 [2:1]	SELECT COUNT(*) AS total_bike_hires FROM `bigquery-public-data.london_bicycles.cycle_hire`	2	0 B	VIEW RESULTS
✅	21:11 [6:1]	SELECT AVG(duration) AS average_duration FROM `bigquery-public-data.london_bicycles.cycle_hire`	2	636.41 MB	VIEW RESULTS

Use temporary tables

A temporary table in BigQuery is a table that exists temporarily for the duration of a query. It is not saved in your dataset or project and is automatically deleted when the query finishes executing. Temporary tables are useful for breaking complex queries into smaller, more manageable parts or for storing intermediate results.

WITH personnel AS (
  SELECT 'John' AS name, 22 AS age
  UNION ALL
  SELECT 'Tim' AS name, 38 AS age
  UNION ALL
  SELECT 'Martin' AS name, 55 AS age
)

-- Main query that references the CTE
SELECT *
FROM personnel;

The WITH clause is used to define a temporary named result set called personnel.
Inside the personnel, we use UNION ALL to combine multiple SELECT statements, each generating a row of data.
Finally, the main query selects all columns from the personnel.

The result looks like this

Row	name	age
1	John	22
2	Tim	38
3	Martin	55

Use subqueries

Subqueries, also known as nested queries, are queries that are embedded within another query’s WHERE clause, FROM clause, HAVING clause, or SELECT list. They allow you to perform operations on the results of another query.

WITH station_avg_hires AS (
  SELECT 
    start_station_name,
    AVG(num_bikes_hired) AS avg_bikes_hired_per_day
  FROM (
    SELECT 
      start_station_name,
      DATE(start_date) AS hire_date,
      COUNT(*) AS num_bikes_hired
    FROM 
      `bigquery-public-data.london_bicycles.cycle_hire`
    GROUP BY 
      start_station_name,
      hire_date
  )
  GROUP BY 
    start_station_name
)                          # Notice, there is no semicolon in between

SELECT 
  start_station_name,
  avg_bikes_hired_per_day
FROM 
  station_avg_hires
WHERE 
  avg_bikes_hired_per_day = (
    SELECT 
      MAX(avg_bikes_hired_per_day)
    FROM 
      station_avg_hires
  );

The first statement is to create a temp table called station_avg_hires, we calculate the average number of bikes hired per day for each station.
Then we use a subquery in the WHERE clause to filter only the stations with the highest average bikes hired per day.

The result looks like this

Row	start_station_name	avg_bikes_hired_per_day
1	Hyde Park Corner, Hyde Park	234.77385529535135

Share on Social Media