The BigQuery update introduces the general availability (GA) of WITH expressions in GoogleSQL queries, allowing the creation of temporary variables within a query. This feature enables defining one or more variables that can be referenced in subsequent expressions inside the same WITH expression, ultimately returning the value of a final result expression.
Syntax and Usage
The syntax is:
WITH(variable_assignment[, ...], result_expression)
- variable_assignment: Defines a variable as
variable_name AS expression. Each variable name must be unique within the WITH expression. - result_expression: Uses all previously defined variables and its value is returned by the WITH expression.
Variables can reference those defined before them but can only be assigned once. Each variable's expression is evaluated only once.
Important Rules and Limitations
- Variables created in WITH expressions cannot be used as arguments in analytic or aggregate functions (e.g.,
WITH(a AS ..., SUM(a))causes an error). - Variables can store results of aggregate or analytic functions but cannot be passed as inputs to such functions.
- WITH expressions differ from WITH clauses, which define temporary tables.
Examples
- Concatenation of variables:
SELECT WITH(
a AS '123',
b AS CONCAT(a, '456'),
c AS '789',
CONCAT(b, c)
) AS result;
Result: '123456789'
- Volatile expression evaluated once:
SELECT WITH(a AS RAND(), a - a);
Result: 0.0
- Storing aggregate results in variables:
SELECT WITH(
s AS SUM(input),
c AS COUNT(input),
s/c
)
FROM UNNEST([1.0, 2.0, 3.0]) AS input;
Result: 2.0
- Error when using variables as aggregate function arguments:
SELECT WITH(diff AS a - b, AVG(diff))
FROM UNNEST([
STRUCT(1 AS a, 2 AS b),
STRUCT(3 AS a, 4 AS b),
STRUCT(5 AS a, 6 AS b)
]);
This produces an error because variables like diff cannot be used inside aggregate or analytic function arguments.
- Using WITH expression alongside WITH clause:
WITH my_table AS (
SELECT 1 AS x, 2 AS y
UNION ALL
SELECT 3 AS x, 4 AS y
UNION ALL
SELECT 5 AS x, 6 AS y
)
SELECT WITH(
a AS SUM(x),
b AS COUNT(x),
a/b
) AS avg_x, AVG(y) AS avg_y
FROM my_table
WHERE x > 1;
Result:
| avg_x | avg_y |
|---|---|
| 4 | 5 |
This example shows how WITH expressions can be combined with WITH clauses to compute temporary variables and aggregate values in a single query.
The introduction of WITH expressions in BigQuery enhances query flexibility by enabling temporary variable creation and reuse within queries, while maintaining clear restrictions to avoid misuse in aggregate or analytic function arguments.
