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.
CREATE TEMP FUNCTION mileage_band(km INT64) AS (...);has a name, a list of parameters with a type each, and a body, which is one expression in brackets. It ends with a semicolon and goes before the first statement that calls it. ADECLAREstill comes first in the script, before theCREATE TEMP FUNCTION.- A call is
mileage_band(odometer.value_normalized_km). BigQuery checks the number and the type of the arguments, and refuses a call that does not fit the parameters. It cannot check what a number means: a parameter namedkmaccepts anyINT64, miles included. - The body sees only its own parameters. A column of the calling query is not visible inside it, so everything the body needs goes in as an argument.
- A function can take several parameters, separated by commas, and every call passes them in the same order. Calls with different arguments are what a function is good for: the body is written once and only the arguments change.
- The name of a temporary function is not case sensitive, so
MILEAGE_BAND(km)callsmileage_band. - A function is a name for an expression. It does not change a single row: the rows are the same as with the expression written inline. BigQuery plans the body as part of the query, so a SQL function is not a performance tool either way.
- The function is for whoever reads the query next, which is why this lesson asks for one. Reach for it when the same expression appears in several places, or with different arguments, and you want one body to read and to change.
- A temporary function lives inside one query. A script with several statements is one query, so the function lasts to the end of the script and no further. The next script defines it again.
- A call hides its body. Anyone who reads
mileage_band(km)sees a name, so the name has to be honest and the body has to be right for every value the column holds.
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.