13
Wed, Nov
5 New Articles

TechTip: Implement a Rolling Average User-Defined Function in DB2 for i

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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:

 

Order Date

Amount

Sales Rolling Avg

1/1/2013

           21,053.08

                       21,053.08

1/2/2013

            19,620.61

                         20,336.84

1/3/2013

           24,015.02

                       21,562.90

1/4/2013

            22,131.96

                         21,705.16

1/5/2013

           18,399.34

                       21,044.00

1/6/2013

            16,020.84

                         20,206.80

1/7/2013

           22,121.94

                       20,480.39

1/8/2013

            20,045.42

                         20,336.44

 

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:

 

 

ORDER_DATE

AMOUNT

RUNNING_TOTAL

1/1/2013

21053.08

21053.08

1/2/2013

19620.61

40673.69

1/3/2013

24015.02

64688.71

1/4/2013

22131.96

86820.67

1/5/2013

18399.34

105220.01

1/6/2013

16020.84

121240.85

1/7/2013

22121.94

143362.79

1/8/2013

20045.42

163408.21

 

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.

 

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: