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).


SELECT Aggregate_function() 
   ([ALL | DISTINCT] {<value expression1> | *}) 
    OVER ([PARTITION BY <value expression2> [,…]])


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;

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.


     ([PARTITION BY value/expr])

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


Analytic features
Oracle SQL Functions

Copyright © 1999-2018
Some rights reserved