Team Fly

Page 353

Analytic partitioning is the division of the results of an analytic function into groups within which the analytic function operates. This is accomplished using the partition by clause of the analytic function. Do not confuse this partitioning with data partitioning discussed earlier in this chapter. Analytic partitioning can be used with any of the analytic functions we have discussed so far.

An analytic window is a subset of an analytic partition in which the values of each row depend on the values of other rows in the window. There are two types of windows: physical and logical. A physical window is defined by a specified number of rows. A logical window is defined by the order by values.

Windowing functions can only be used in the select and order by clauses. They can be used to calculate the following:

Moving sum

Moving average

Moving min/max

Cumulative sum

Statistical functions

Let's look at an example of a moving sum function. The following shows the listing and results for calculating the moving sum from our SALES table by product category for a six-month period:

select b.prod_category,
       to_char(a.time_id, 'yyyy-mm'),
       sum(a.quantity_sold),
       sum(sum(a.quantity_sold)) over (partition by b.prod_category
                                order by to_char(a.time_id, 'yyyy-mm')
                                rows unbounded preceding) as cume_sum
from sales a, products b
where a.prod_id = b.prod_id
and b.prod_category_id between 202 and 204
and to_char(a.time_id, 'yyyy-mm') between '2001-01' and '2001-06'
group by b.prod_category, to_char(a.time_id, 'yyyy-mm')
order by b.prod_category, to_char(a.time_id, 'yyyy-mm');

PROD_CATEGORY                  TO_CHAR SUM(A.QUANTITY_SOLD)   CUME_SUM
------------------------------ ------- -------------------- ----------
Hardware                       2001-01                  281        281

Team Fly