New OLAP Aggregate Functions In DB2 For i, Part 2

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:

RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY CustomerId)

-- 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)