GoogleSQL features worth knowing

These features make BigQuery SQL shorter and safer than plain ANSI SQL.

GoogleSQL follows the SQL standard closely and adds conveniences you'll see in most BigQuery code.

SELECT * EXCEPT (col1, col2) drops columns; SELECT * REPLACE (UPPER(name) AS name) swaps one column's expression.
QUALIFY filters on window functions, e.g. latest row per key: QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts DESC) = 1.
COUNTIF(cond), LOGICAL_AND, LOGICAL_OR, ANY_VALUE, MAX_BY(x, y), MIN_BY, STRING_AGG(x, ', ').
IF(cond, a, b), IFNULL(x, 0), COALESCE, NULLIF.
SAFE_DIVIDE(a, b) returns NULL instead of a division-by-zero error. Plain / always returns FLOAT64; use DIV(a, b) for integer division.
GROUP BY ALL groups by every non-aggregated select expression. You can also group and order by select-list aliases.
WITH CTEs, all window functions, UNNEST and set operations (UNION ALL, UNION DISTINCT, EXCEPT DISTINCT: GoogleSQL requires the ALL or DISTINCT keyword).
Date functions take the part as an argument: DATE_TRUNC(d, MONTH), DATE_DIFF(a, b, DAY), TIMESTAMP_SUB(ts, INTERVAL 1 HOUR), FORMAT_DATE('%Y-%m', d).

Pipe syntax is an alternative way to write queries as a top-down sequence of steps: FROM t |> WHERE ... |> AGGREGATE COUNT(*) AS n GROUP BY x |> ORDER BY n DESC. Each |> operator applies to the result so far, which reads in execution order.

Example

SELECT corpus,
       COUNT(*)                         AS words,
       COUNTIF(word_count = 1)          AS hapax,
       SAFE_DIVIDE(COUNTIF(word_count = 1), COUNT(*)) AS hapax_share,
       MAX_BY(word, word_count)         AS top_word
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus LIKE 'h%'
GROUP BY ALL
QUALIFY RANK() OVER (ORDER BY COUNT(*) DESC) <= 2;   -- top 2 corpora

-- The same kind of question in pipe syntax
FROM `bigquery-public-data.samples.shakespeare`
|> WHERE word_count > 100
|> AGGREGATE COUNT(*) AS frequent_words GROUP BY corpus
|> ORDER BY frequent_words DESC
|> LIMIT 3;

Remember

SELECT * EXCEPT / REPLACE and GROUP BY ALL cut boilerplate
QUALIFY filters window results; COUNTIF and MAX_BY shorten aggregates
SAFE_DIVIDE avoids division errors; / always returns FLOAT64
Pipe syntax (|>) writes queries as ordered steps

Watch out for

Writing UNION instead of UNION ALL or UNION DISTINCT, which GoogleSQL rejects.

Check yourself

What does SAFE_DIVIDE(10, 0) return?

Official docs: docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks