Database Reference
In-Depth Information
ORDER BY population DESC) AS pop_rank
FROM [bigquery-e2e:reference.zip_codes]
WHERE population > 0)
WHERE pop_rank <= 3
ORDER BY state, population DESC
Without the outer
ORDER BY
clause, the ordering of the rows returned is
undefined; therefore, you need to explicitly specify the order you would like
rather than relying on the order specified in the
OVER
clause.
Queries can also use multiple window functions in a single
SELECT
clause.
There is a restriction that all the window functions in the query use the same
partitioning and ordering clause. This restriction is nonstandard, so it is
likely to be lifted as the service enhances support for the feature.
SELECT state, zip, fraction FROM (
SELECT state,
zip,
RATIO_TO_REPORT(population)
OVER (PARTITION BY state
ORDER BY population DESC) AS fraction,
RANK() OVER (PARTITION BY state
ORDER BY population DESC) AS pop_rank
FROM [bigquery-e2e:reference.zip_codes]
WHERE population > 0)
WHERE pop_rank <= 3
ORDER BY state, fraction DESC
This query would be challenging to write in SQL without the support for
window functions.
If you need to perform an aggregation before applying the window function,
you need to mix in an aggregation operation. However, the
GROUP BY
clause
does not play well with window functions. Like with
ORDER BY,
the trick
is to use a subquery, but this time as an inner query that feeds the window
function query.
SELECT
primary_city,
RATIO_TO_REPORT(city_total) OVER() AS fraction
