How-to: Analytic Reporting Functions

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_sales
      FROM 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