Mastering Oracle SQL

Windowing Functions

The ranking functions described thus far are quite useful when comparing items within a fixed window of time, such as “last year” or “second quarter.” But what if we want to perform computations using a window that slides as we progress through the data set? Oracle’s windowing functions allow aggregates to be calculated for each row in a result set based on a specified window. The aggregation window can be defined in one of three ways:

• By specifying a set of rows: “From the current row to the end of the partition.”

• By specifying a time interval: “For the 30 days preceeding the transaction date.”

• By specifying a range of values: “All rows having a transaction amount within 5% of the current row’s transaction amount.”

To get started, we generate a window that fills the entire partition, and then we see how the window can be detached from one or both ends of the partition so that it floats with the current row. All of the examples will be based on the following query, which calculates total monthly sales for the Mid-Atlantic region:

            SELECT month, SUM(tot_sales) monthly_sales
            FROM orders
            WHERE year = 2001 
              AND region_id = 6
            GROUP BY month
            ORDER BY 1month;

     MONTH MONTHLY_SALES
---------- -------------
         1        610697
         2        428676
         3        637031
         4        541146
         5        592935
         6        501485
         7        606914
         8        460520
         9        392898
        10        510117
        11        532889
        12        492458

First, we will sum the monthly sales for the entire result set by specifying an “unbounded” window. Note the ROWS BETWEEN clause in the following example: ...