Using Window Functions
What are Window Functions?
Window functions enable users to perform calculations against partitions (i.e. subgroups or sections) of a result set, typically a table or the results from another query. Unlike traditional aggregation functions, which return only a single value for each group defined in the query, window functions return a single value for each input row.
How are window functions helpful?
Window functions increase the efficiency and reduce the complexity of queries that analyze partitions (windows) of a data set by providing an alternative to more complex SQL concepts, e.g. derived queries. Common use cases include:
- Ranking results within a specific window (e.g. per-group ranking)
- Accessing data from another row in a specific window (e.g. period-over-period reporting)
- Aggregation within a specific window (e.g. running totals):
How to use Window Functions
Window functions are initiated with the
OVER clause, and are configured using three concepts:
- window partition (
PARTITION BY) - groups rows into partitions
- window ordering (
ORDER BY) - defines the order or sequence of rows within each window
- window frame (
ROWS) - defines the window by use of an offset from the specified row
function (expression) OVER ( [ PARTITION BY expression_list ] [ ORDER BY order_list ] [ ROWS frame_clause ])
- expression_list: comma separated list of expressions, typically column names
- order_list: comma separated list of expressions, typically column names
- frame_clause: define the offset using
Ranking: per-group ranking
SELECT campaign , month , total_cost , rank() OVER (PARTITION BY month ORDER BY month DESC, total_cost DESC) as "rank" FROM Cost_by_Month LIMIT 8
Previous Row: period-over-period analysis
SELECT campaign , month , total_cost , LAG(total_cost, 1) OVER (ORDER BY campaign, month) as "previous total cost" FROM Cost_by_Month WHERE campaign = 'TV' LIMIT 4
Aggregation Within a Window: running totals
SELECT campaign, month, monthly_cost , sum(monthly_cost) OVER (PARTITION BY campaign ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as "running cost total" FROM Cost_by_Month
Documentation & Function List:
Please note that subtle differences in usage and syntax exist from database to database. For information specific to your implementation, please use the source-specific documentation provided below.