A temporary function

A temporary function gives an expression a name, so you can write mileage_band(km) instead of repeating a CASE. The name makes the expression look finished, but the name does not say what the body does with a value its author never thought of.

How it works

CREATE TEMP FUNCTION is a statement of the script. It runs where it is written, and every statement after it can call the name in as many places as it likes, with different arguments. The function lasts as long as the script. The rows are the same as with the expression written out inline, so a function gives you one place to read and one place to change, and nothing else.

Common mistake

A function written for the values its author had in mind does something with the values nobody had in mind, and whatever that is appears in the report. Check a new function against the awkward values: a boundary, a zero and a missing one.

Worked example

CREATE TEMP FUNCTION share_of(part INT64, total INT64) AS (
  ROUND(SAFE_DIVIDE(part, total), 3)
);

SELECT
  make,
  COUNT(*) AS tests,
  share_of(COUNTIF(test_result = 'PASSED'), COUNT(*)) AS pass_rate,
  share_of(COUNTIF(test_result = 'FAILED'), COUNT(*)) AS fail_rate
FROM `learn.mot.fact_mot_test`
WHERE make IN ('BMW', 'FORD', 'HONDA')
  AND completed_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY make
ORDER BY make;

share_of takes two numbers and returns the rounded share of one in the other. The ROUND and the SAFE_DIVIDE are written once, in the body.

The query calls it twice, once with the passed tests and once with the failed tests. If the rounding changes, it changes in one place.

Write the two divisions out inline and the rows are the same. The function adds a name and a shorter query to read.

The body sees only part and total. It cannot read make or test_result, so the counts go in as arguments.

The call shows only the name share_of. To learn what it returns for a total of zero, you have to open the body.

All BigQuery lessons