Database Reference
In-Depth Information
and expressing operations that rely on the order. In fact, even the
ORDER BY
clause is not supported by the calculus but is nevertheless present in SQL. As
the use of relational databases for data analysis grew, it became necessary to
support these kinds of operations. Initially developers implemented custom
solutions using programming extensions available in individual databases.
Eventually database providers added SQL extensions to support these types
of operations, and these extensions were standardized as window functions
in ANSI SQL 99. Arguably, their syntax and usage is fairly awkward because
they do not fit neatly into the underlying framework. Nevertheless, they
enable a range of useful operations. BigQuery supports these window
functions, and this section discusses how to use them.
The main property of window function expressions is the capability to define
a sequence (in some cases just a set) of values over which the function
should operate.
window-function OVER ([PARTITION BY fields]
[ORDER BY fields])
When the
OVER
clause is empty, the window covers all the rows in the query
result. The partition clause is optional and divides the rows into separate
windows over which the function is applied; much like a grouping clause but
it does not combine rows. Window functions that operate over an ordered
sequence require the
ORDER BY
clause to specify the ordering within each
window. The window function can appear only as a column in a
SELECT
clause; for example, it cannot be used in an arithmetic expression or in a
WHERE
clause. You can always use a subquery if you need to operate on the
result of a window function.
We will start with the simplest possible window function clause and work
toward more complex usage.
SELECT zip, RATIO_TO_REPORT(population) OVER() AS
population_fraction
FROM [bigquery-e2e:reference.zip_codes]
WHERE primary_city = 'Seattle'
This query computes the fraction of the population that resides in a ZIP code
associated with Seattle. The fraction is computed with respect to the sum of
the population field across all rows returned by the query.
