Analytical Functions: Defining Custom Window Frames

ROWS BETWEEN n1 PRECEDING and n2 FOLLOWING

Let's consider a table shown in the figure above. The table has five columns: "National Pokedex No.," "Pokemon Name," "Type 1," "Type2," and "Total Base Stat". Now, suppose that, for each row, you want to calculate the average total base stats with one preceding to one following rows. One way to achieve this with Oracle SQL is to add a ROWS clause in the OVER clause. For example:

Code lines from 15 to 16 computes the average base stat using an analytical function with rounding to two decimal places. The PARTITION BY clause divides the result set into partitions based on the type_1 column values. The ORDER BY clause sorts the rows within each partition by the id column in an ascending order.  

Then the ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING defines a window of rows to consider for the average calculation. This range includes the current row and its adjacent. So, for example, if you look at the Avg. Base Stat of Metapod, we see that the calculated value is the average of 195, 205, and 395.

The figure shown above clearly illustrates how it works. For the first row in the table, there is no preceding row available. So, it calculates the average with the current and its following. Then it slides down the row partitioning window to the next three. This time, the query calculates the average for the preceding, current, and the following.

Window sliding keep moving on until it encounters the very last row defined by PARTITION BY clause. Then at the very last row, where there is no following is available, the window function calculates the average of the available two rows. For example:

CURRENT ROW and UNBOUNDED

Of course, the number of preceding and following rows are not limited to one. You can specify any number of rows for the two positions, including 0. Entering 0 for either PRECEDING or FOLLOWING means the current row. For example:

Alternatively, you can also use keyword CURRENT ROW. For example:

The keyword UNBOUNDED can be used to specify a range as far as the partition goes. For example:

In the query shown above, the PARTITION BY type_1 divides the result set into partitions based on the type_1 column. The ORDER BY id sorts the rows within each partition by the id column. Then the ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING clause defines a window of rows to consider the average calculation. Here, the window includes the current row and all rows following it. For example, if you look at the row for Vivillon, the calculated average is 411, as the current row value is 411 and there is no following rows.