IBM DB2 Analytics – Moving Difference
I can teach you analytics! DB2 is built for analytics, and this week, I will show you the amazing moving difference. The moving difference is one of the most incredible aspects of analytics, but it cannot seem very clear unless it is explained correctly. With power comes incredible responsibility. I won’t disappoint you.
All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the World. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website. I hope to meet you and say thanks in my next class at your company:)
https://coffingdw.com/education/
Moving differences are commonly used in data analysis to understand the rate of change in a series of values over time, which allows us to look for positive or negative trends. They provide insights into the direction and magnitude of change, which can be useful for decision-making and forecasting. Check out the example below.
Let me caution you so you are not confused. A moving difference only compares one row to another row.
The first thing to remember is that these analytics are often referred to as ORDERED ANALYTICS because we have an ORDER BY statement inside the calculation that sorts the data first. Once the data is ordered correctly, the analytic calculates from the first row sorted to the last. In our example below, we first order from PRODUCT_ID ASC and then SALE_DATE ASC. We don’t need the ASC because ascending mode is the default, but I put it there for clarity.
If I have a moving window of four, as in the example below, then the DAILY_SALES of row one will compare to the DAILY_SALES value of row five (four rows apart) and show a positive or negative difference between those two rows.
Notice that the first four rows have null values in the moving difference. Why? Because each of them has something in common: they don’t have a row four rows ahead of them to compare.
Notice that the fifth row has a value of -16409.90. We are only comparing the DAILY_SALES value in row one with the DAILY_SALES value in row five because we have a moving window of four because of the keywords ROWS BETWEEN 4 PRECEDING AND 4 PRECEDING. I have colored those values in blue. Notice that we compared row two with row six (four rows apart), and we did better by 9,799.78 dollars, colored in red.
The example below performs the same moving difference calculation every four rows but uses the LAG command. Remember, both examples only compare the difference between the current row’s DAILY_SALES and the DAILY_SALES value four rows ahead.
The example below has two differences from the previous examples. The first difference is the PARTITION BY PRODUCT_ID statement within the moving difference calculation. PARTITION BY PRODUCT_ID means performing the moving difference calculation within each product ID. In other words, with each product ID break, we will begin the calculation again.
The second change I have made is we have a moving window of two rows because of the ROWS BETWEEN 2 PRECEDING and 2 PRECEDING. Now, we are comparing the current row’s DAILY_SALES value with the DAILY_SALES value two rows ahead.
Notice the null values at the beginning of each product ID because there are not two rows ahead.
I use several tricks to ensure that this is perfect. The first trick is using the TO_CHAR command to format the SALE_DATE to provide the day of the week, which I alias as dow. I then PARTITION BY PRODUCT_ID and again use the TO_CHAR command as a partition to calculate the data by PRODUCT_ID 1000, and then, all of my Mondays are now back to back. These are followed by all of my Tuesdays in a row. I then use a moving window of one because of the keywords ROWS BETWEEN 1 PRECEDING and 1 PRECEDING.
The final trick I use is to put an additional ORDER BY statement at the end of the query where the major sort is PRODUCT_ID, and the minor sort is SALE_DATE.
The moving difference calculation ordered the data by PRODUCT_ID and day of the week. The moving difference calculated Monday to the previous Monday, Tuesday to the previous Tuesday, etc. However, with the addition of the ORDER BY PRODUCT_ID, SALE_DATE at the end, I resorted to it to make the report look nice. However, if you compare the first Monday, we made 97.00 to the second Monday, where we made 62.00, we did worse on the second Monday by 35.00. But we did better on the second Tuesday than the first Tuesday by 17.00.
You can also see that the third Monday compares to the second Monday, and we did better by $27.00.
Did you know that Coffing Data Warehousing is the first company to create software that joins data across all systems? Download your free Nexus trial at www.CoffingDW.com.
Watch how Nexus performs federated queries to join an IBM DB2 and Netezza table to tables from twenty systems in a single query. Watch the video.
To find out more about Nexus, you can watch our 60-second videos here: