Emulate the window aggregate functions available on other database platforms.

A friend recently asked me how to convert a particular Oracle query to SQL Server 2005. The query contained a column expression that looked like this (modified and simplified):

SELECT AVG(AMOUNT)

OVER(ORDER BY TDATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

FROM ORDER_SUMMARY

ORDER BY AMOUNT DESC

The simplified ORDER_SUMMARY table looks like this:

CREATE TABLE ORDER_SUMMARY (

TDATE DATE NOT NULL PRIMARY KEY,

AMOUNT DECIMAL(9,4))

If you're not familiar with it, in this case the AVG (AVERAGE) aggregate function has a "windowing" clause that specifies the rows to be included in the calculation. Because the windowing clause controls the input to the function, a GROUP BY is unnecessary and the calculation is done row by row. In Oracle parlance, aggregate functions used in this manner are referred to as "analytic functions."

Looking closely at the windowing clause, "ROWS BETWEEN 6 PRECEDING AND CURRENT ROW" is limiting the average calculation to the current row of the result set and six rows prior to the current row. How does the database engine know which six "preceding" rows to include? The ORDER BY clause specified in the function instructs the calculation to sort the result set rows in a specific way (only for the purpose of the calculation). So even though the result is ordered by the AMOUNT column in descending order (shown on the statement's ORDER BY), the analytic function keeps a copy of the pertinent result set columns sorted by TDATE (and yes, this feature can make your queries resource hogs!) It is this alternatively sorted set that the function uses to consistently find the six rows preceding the current row. Ultimately, assuming there are no missing dates in the result set, this analytic function effectively creates a one-week rolling average. Pretty cool for almost no code!

If this Oracle query was being migrated to SQL Server 2012 or DB2 LUW, there wouldn't be any problems because these database engines support this functionality. But SQL Server 2005 doesn't support it. DB2 for i doesn't yet support this functionality either (but hopefully soon). Nevertheless, a user-defined function (UDF) can be written to simulate the functionality of the rolling average (or other windowed calculations). This solution can be used in DB2 for i (or SQL Server 2005 if modified) to simulate the rolling average created by the Oracle AVG analytic function demonstrated above.

A potential solution to mimicking this query using DB2 for i syntax is shown below:

A

/*

For performance reasons, this table should have a PK constraint or index. Since it's in QTEMP with a small number of rows, we'll ignore it for now

*/

CREATE TABLE QTEMP.ORDER_SUMMARY (

ORDER_DATE DATE         NOT NULL,

AMOUNT     DECIMAL(9,2) NOT NULL

)

;

INSERT INTO QTEMP.ORDER_SUMMARY VALUES

('1/1/2013', 21053.08),

('1/2/2013', 19620.61),

('1/3/2013', 24015.02),

('1/4/2013', 22131.96),

('1/5/2013', 18399.34),

('1/6/2013', 16020.84),

('1/7/2013', 22121.94),

('1/8/2013', 20045.42)

;

B

CREATE OR REPLACE FUNCTION dev.ROLLING_AVG

(@ORDER_DATE DATE,

@PRECEDING_DAYS INT)

RETURNS DECIMAL(9,2)

LANGUAGE SQL

SET OPTION COMMIT=*NONE

BEGIN

RETURN (

      SELECT AVG(AMOUNT)

      FROM QTEMP.ORDER_SUMMARY

      WHERE ORDER_DATE BETWEEN @ORDER_DATE - @PRECEDING_DAYS DAYS

                         AND @ORDER_DATE

);

END

;

C

SET PATH=DEV;

;

SELECT D.*,dev.ROLLING_AVG(ORDER_DATE,6) SALES_ROLLING_AVG

FROM QTEMP.ORDER_SUMMARY D

ORDER BY ORDER_DATE

;

At A, a temporary "Order Summary" table is created and populated with fictitious data. It's intended to contain the daily summary for each day's total sales amount (stored in the "amount" column). The goal is to report the weekly rolling average of this table's amount column.

At B, a user-defined scalar function called ROLLING_AVG is built to emulate the analytic average from Oracle. In this simple scenario, all that's required is logic to take an average of the correct subset (aka window) of rows. The Oracle function will take an average of the current row and the prior six rows. As long as there's data present for each day, the Oracle code and DB2 for i code will work the same even though the DB2 function window selects its data based on a date range.

However, if days are missing in the middle of the result set, the Oracle calculation will use the six prior rows in the set. The sample function shown here cannot do that unless it's redesigned to operate by relative record number on the temp table (assuming the table is ordered properly). So use caution when emulating a windowed function because there may be a subtle difference in how the rows are selected for the calculation. A little more on this problem will be discussed in a bit.

For developers not yet on IBM i 7.1, you'll need to remove the "OR REPLACE" text from the CREATE FUNCTION statement.

Coming to portion C of the script, the SQL path is set so that it can locate the new function in the DEV schema. Finally, the query is run to show the results of the rolling average function.

The formatted results are shown below:

In the first row, the simulated analytic function doesn't have any preceding rows, so the rolling average is only itself. On row two, the rolling average is created using its amount and the prior row's amount. It's not until the seventh row that it performs a full week's average. On row eight, the average is taken from row dates 1/2/13 through 1/8/13.

As already mentioned, an exact emulation of an Oracle analytic function that uses a windowing clause would generally require making a sorted copy of the result set and storing it in a temp table. Further, the user-defined function would have to use the relative record number to find the appropriate spot in the result set. An alternative to using relative record number is to create an additional column on the result set that has a unique row number (populated by the ROW_NUMBER function, a sequence, or an identity column). Using this kind of numbering scheme will get the exact position within the cursor result set and can be used to accurately emulate relative row position selection.

Analytic functions are powerful and contain many features worth reviewing. The DB2 9.7 LUW documentation can be found here within the OLAP functions documentation. The terminology differs from Oracle (e.g., window partition clause vs. windowing subclause), but the concepts are the same. There are many cool ways to show values from other rows in almost any query using functions like LEAD and LAG and to window the input in any number of ways. Take time to learn how to use them. They should eventually be available in DB2 for i.

For one more popular example, a windowed function is often used to create a running total column using the familiar SUM function:

   SELECT *,

     SUM(AMOUNT) OVER(ORDER BY ORDER_DATE

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL

      FROM #ORDER_SUMMARY

ORDER BY ORDER_DATE

The window that the function creates always starts at the beginning of the result set (UNBOUNDED PRECEDING) and ends with the current row (with the rows sorted by order date). The results are shown here:

If you throw in a PARTITION subclause, you can even make the window start over at a change in a specified column or expression. For example, the running total can be made to start over with each new month:

   SELECT *,

     SUM(AMOUNT) OVER(PARTITION BY YEAR(ORDER_DATE),MONTH(ORDER_DATE)

       ORDER BY ORDER_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RUNNING_TOTAL

      FROM #ORDER_SUMMARY

ORDER BY ORDER_DATE

To simulate this functionality in DB2 for i, all you need to do is write a user-defined function that SUMs the amount column from the start of the result set through the latest date. To emulate the PARTITION BY feature shown above, use the beginning date of the current month and year as the starting row for the SUM instead of the beginning of the result set.

In conclusion, this brief intro to window aggregate functions illustrates a useful tool in any developer's toolbox. Studying them will tune your thinking as an SQL developer to learn to think in terms of windows within a result set. This knowledge is definitely useful in DB2 for i even though the "window" functions have to be implemented manually using temporary sorted data and user-defined functions.