A variation on aggregate Window functions
with the difference that the Window ranges from the beginning to the end of
the partition, so each row in the partition is reported with an aggregate over
the whole partition.

These functions are often used to aggregate more than one item e.g. For each
region, find the sales for cities that contribute 10 % or more to regional sales.
This involves aggregating sales per (region) and also Sales per (Region,City).

Syntax

SELECT Aggregate_function() ([ALL | DISTINCT] {<value expression1> | *}) OVER ([PARTITION BY <value expression2> [,…]]) e.g. SELECT * FROM (SELECT year(date), product, SUM(sales) As sum_sales,MAX(SUM(sales)) OVER (PARTITION BY product) As max_salesFROM SalesTable GROUP BY year(date), product) WHERE sum_sales = max_sales;

**RATIO_TO_REPORT**

Compute the ratio of a value to the sum of a set of values (in a partition window)

This allows *shares* to be calculated in one step without requiring a second
SELECT to calculate the total.

Syntax

RATIO_TO_REPORT (expr) OVER ([PARTITION BY value/expr]) e.g SELECT product_id, SUM(sales) RATIO_TO_REPORT (SUM(sales)) OVER () As MyRatio FROM sales GROUP BY product_id

*“Well the hours are good, but now you come to mention it, most of the actual minutes are pretty lousy” - Douglas Adams*

**Related**

Analytic features

Oracle SQL Functions