OLAP Aggregation Specification In DB2 For i 7.3

The OVER portion of the aggregation-specification includes a “window partition clause” and it allows the aggregate to be included in the detail results without requiring a GROUP BY. In fact, you can think of the PARTITION BY as analogous to the GROUP BY. The “partition by” in this example instructs DB2 to calculate the SUM of all the rows in the result set by CustomerId and store the temporary result “under the covers.” Then, as the DB2 builds the result set, it shows the SUM for the specific CustomerId on the current row.

If the boss also wanted to see the total annual product sales subtotal for the current product under review, you could give him another column that specifies the SUM aggregate partitioned by the product ID:

SUM(OrderQty*UnitPrice) OVER(PARTITION BY ProductId) 
AS Product_SubTotal

Since each OLAP aggregation has its own PARTITION BY, a single query can now have many aggregations based on different aggregation groups, in contrast to the old-style GROUP BY queries that can only have one aggregation grouping per query.

Now, for a few more examples. To show the product sales by product ID within customer, simply specify both columns in the PARTITION BY:

SUM(OrderQty*UnitPrice) OVER(PARTITION BY CustomerId,ProductId)
AS Customer_Product_SubTotal

To include the annual sales for the entire year as a column specify:

SUM(OrderQty*UnitPrice) OVER(PARTITION BY YEAR(OrderDate)) 
AS Annual_SubTotal

Alternatively, specify a constant in the expression to aggregate an entire result set:

SUM(OrderQty*UnitPrice) OVER(PARTITION BY 1) AS Annual_SubTotal

I should mention that in the order of processing a query, DB2 will process the OLAP aggregate functions prior to subsetting the rows based on a FETCH FIRST clause.

Other aggregate functions such as average work equally well, such as this expression that calculates the average customer sales:

AVG(OrderQty*UnitPrice) OVER(PARTITION BY CustomerId) 
AS Avg_Customer_SubTotal

Or COUNT can be used to show the number of annual order detail lines for each customer:

COUNT(*) OVER(PARTITION BY CustomerId) 
AS Annual_Customer_Order_Lines

The Window Aggregation Group Clause

And now, I’ll show one more trick that DB2 has up its sleeve called the “window aggregation group clause.” This feature will put the analytical value of your queries into overdrive. Consider the query (revised from the first example) that shows a rolling total by customer:

SELECT soh.SalesOrderId,
       soh.OrderDate,
       soh.CustomerId,
       sod.ProductId,
       sod.OrderQty,
       sod.UnitPrice,
       DEC(sod.OrderQty*UnitPrice,11,2) AS SubTotal,
       SUM(OrderQty*UnitPrice) 
       OVER(PARTITION BY CustomerId 
            ORDER BY ProductId 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
            AS Rolling_Customer_Total
  FROM SalesOrderDetail sod
  JOIN SalesOrderHeader soh ON soh.SalesOrderId=sod.SalesOrderId
 WHERE OrderDate BETWEEN '2006-01-01' AND '2006-03-31'
ORDER BY soh.CustomerId,sod.ProductId

The Rolling_Customer_Total column creates a rolling total of a customer’s purchases. The “window aggregation group clause” shown consists of three components:

1. PARTITION BY CustomerId–The SUM aggregation is reset on the change in the CustomerId within the result set.
2. ORDER BY ProductId–Specifies the order of the aggregation across the rows. In a rolling total scenario this ORDER BY should typically match the ORDER BY clause of the entire result set so that the rolling SUM is easily understood.
3. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW–Instructs DB2 that the aggregate is to be calculated based on all rows starting from the beginning of the partition (CustomerId) to the current row. This is how the rolling total is formed.

The abridged results of the query are shown below with the rolling total column bolded along with the first row of each CustomerId partition:

WITH AnnualSales AS (
SELECT soh.OrderDate,
       DAYOFYEAR(soh.OrderDate) AS DayNo,
       SUM(soh.SubTotal) DailyTotal
  FROM SalesOrderHeader soh
 WHERE OrderDate BETWEEN '2006-01-01' AND '2006-12-31'
GROUP BY soh.OrderDate
)
SELECT s.*,
       AVG(DailyTotal) 
       OVER(ORDER BY OrderDate 
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) 
       AS "30DayRollingAvg"s
  FROM AnnualSales s
ORDER BY OrderDate