New OLAP Aggregate Functions In DB2 For i, Part 2
July 19, 2016 Michael Sansoterra
In my last article, I promised to tell you about four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE and RATIO_TO_REPORT. These valuable functions can reference data from other rows in a query result set relative to the current row. This tip covers the fourth function in the list, RATIO_TO_REPORT.
The RATIO_TO_REPORT Function
The new RATIO_TO_REPORT function is heaven sent, as it makes it easy for developers to avoid writing an UGLY arithmetic expression to do a simple calculation common in business. RATIO_TO_REPORT simply divides a numeric column or expression value in the current row by a SUM aggregate of the same expression evaluated over the OLAP window. Quite often this result is multiplied by 100 to obtain a percentage.
For example, consider the following query that aggregates the extended sale price by SalesOrderId and CustomerId (in this data set, the customer id has a one-to-one relationship with the sales order ID):
SELECT sod.SalesOrderId,CustomerId, SUM(OrderQty*UnitPrice) AS ExtPrice FROM SalesOrderDetail sod JOIN SalesOrderHeader soh ON soh.SalesOrderId=sod.SalesOrderId AND soh.OrderDate BETWEEN '2005-01-01' AND '2005-12-31' GROUP BY sod.SalesOrderId,CustomerId
It’s great that it’s easy to aggregate the extended sale price for an entire order. But what if management, while reviewing these orders, wants to know what the percentage of the current order’s value is with respect to all of the customer’s orders, or with respect to the overall sales?
The RATIO_TO_REPORT function makes this task trivial:
WITH SalesOrderSummary AS ( SELECT sod.SalesOrderId,CustomerId, SUM(OrderQty*UnitPrice) AS ExtPrice FROM SalesOrderDetail sod JOIN SalesOrderHeader soh ON soh.SalesOrderId=sod.SalesOrderId AND soh.OrderDate BETWEEN '2005-01-01' AND '2005-12-31' GROUP BY sod.SalesOrderId,CustomerId ) SELECT sos.*, SUM(ExtPrice) OVER(PARTITION BY CustomerId) AS Customer_Sales, SUM(ExtPrice) OVER(PARTITION BY 1) AS Overall_Sales, DEC(RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY CustomerId)*100,7,4) AS Percent_Sales_Within_Customer, DEC(RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY 1)*100,7,4) AS Percent_Sales_Overall FROM SalesOrderSummary sos
The abridged results look like this:
Within the abridged results, customer 29897 (rows two and three) has two orders totaling $17702.08. The first RATIO_TO_REPORT function in the query is used to calculate a ratio of the current order value to the entirety of the customer’s orders:
RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY CustomerId)
The second instance calculates the current row’s order amount (ExtPrice) ratio against the entirety of all orders in the result set:
-- The current ExtPrice is the numerator for each row -- and 11336135.38 is the denominator for all rows RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY 1)
The “Customer Sales” and “Total Sales” columns were included just to demonstrate the calculation is done correctly. Basically, DB2 formulates the calculation as the current row extended price/SUM (extended price) where the SUM aggregate is only taken over the requested OLAP window, as delineated by the PARTITION BY.
Thanks to the new OLAP functionality, it’s easy for management to look at an order’s value and see how it relates to the customer’s overall sales and to the total sales.
Hop On The Analytics Train
Once again, the DB2 for i OLAP functions have proven their worth by mining valuable information from OLAP windows within a query result. As a bonus, these functions are incredibly easy to use and can immediately offer management better insight into company data. Finally, many of these OLAP functions appear in other database engines including SQL Server 2012 and later, Oracle, DB2 LUW, etc.