Using OLAP Analytic Features

Online Analytical Processing (OLAP) GROUP BY enhancements allow application developers to write complex SQL statements to generate valuable results:

• To generate multiple groupings of data in a single SELECT statement, use GROUP BY GROUPING SETS.

• To create a sparse multi-dimensional result set that contains multiple levels of aggregation, use GROUP BY ROLLUP.

• To create a multi-dimensional cube as a result set, use GROUP BY CUBE.

These features are standard with ANSI SQL-99 and are supported by SAP HANA.

Window framing is an important SQL construct for OLAP operations. It enables users to perform the following tasks:

• Divide query result sets into groups of rows called partitions.

• Determine subsets of rows to aggregate with respect to the current row.

Window framing is standard with ANSI SQL:2003 and SAP HANA supports a variety of aggregate functions within window framing.

Window frames have the following three components:

• Partitioning: the division of a user-specified result set (input rows) using the PARTITION BY clause.

• Ordering: the arrangement of results (rows) within each partition using the WINDOW ORDER clause.

• Framing: the definition of a moving frame of rows using the BETWEEN clause.

These components are defined within a SELECT statement using the OVER() clause.

Window partitioning enables the user to divide user-specified input rows using a PARTITION BY clause.

The partition is defined by one or more value expressions separated by commas. The expression list can consist of items such as columns, operations, and functions. The partition data is implicitly sorted and the default sort order is ascending.

Partitioning is optional. If a window partition clause is not specified, the input rows are treated as a single partition.

The following is an example of the syntax to use with the PARTITION BY clause:

1
PARTITION BY <WINDOW PARTITION EXPRESSION LIST>

The following is an example of the syntax to use with the PARTITION BY clause within a SELECT statement:

12
SELECT ... 
RANK() OVER (PARTITION BY customer_key ...

The following is an example of the syntax to use with the WINDOW ORDER clause:

12345
SELECT product_id, SUM(amount), 
       RANK() OVER ( ORDER BY sum(amount) desc) AS RANK
    FROM TRAINING.SALES_DATA
GROUP BY product_id
ORDER BY RANK

This query returns the following result:

In this example, the data is sorted as follows:

• Select rows from the SALES_DATA table.

• Group rows by PRODUCT_ID.

• Calculate the rank of each group based on the SUM(AMOUNT) aggregate.

• Order the rows by SUM(AMOUNT) descending.

• List the PRODUCT_ID, SUM(AMOUNT), and RANK().

The following is an example of the syntax to use with window ordering and partitioning:

12345
SELECT customer_id, product_id,  SUM(amount), 
       RANK() OVER (PARTITION BY customer_id ORDER BY SUM(amount) desc) AS RANK
    FROM TRAINING.SALES_DATA
GROUP BY customer_id, product_id
ORDER BY customer_id, rank

This query returns the following result:

In this example, the data is sorted as follows:

• Select rows from the SALES_DATA table.

• Partition data by CUSTOMER_ID.

• When the CUSTOMER_ID value changes, reset the RANK calculation.

• Within each partition, calculate the rank of each month based on the SUM(AMOUNT).

• Within each partition, order the rows by SUM(AMOUNT), in descending order.

• List the CUSTOMER_ID, revenue, and RANK.

• Order the rows by CUSTOMER_ID and RANK.

The following is an example of the syntax to use to return a cumulative total:

1234
SELECT product_id, amount, 
       SUM(amount) OVER (ORDER BY product_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "CUMULATIVE TOTAL"
    FROM TRAINING.SALES_DATA
ORDER BY product_id;

This query returns the following result:

This example calculates the cumulative total of amounts for all products. For each row, the amount column is summed from the first row (unbounded preceding) to the current row. For example, the cumulative total for row 2 (8,339.4) is calculated by adding the amount for row 2 (1,017) to the amount for row 1 (7,322.4).

The following is an example of the syntax to use to return a cumulative total with partitioning:

12345
SELECT customer_id, product_id, amount, 
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY product_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
       AS "CUMULATIVE TOTAL"
    FROM TRAINING.SALES_DATA
ORDER BY customer_id, product_id;

This query returns the following result:

This example calculates the cumulative total for the first three months on a customer-by-customer basis. When the customer ID value increments, the cumulative total is reset.

The following is an example of the syntax to use to return a moving average:

1234
SELECT order_id, amount, 
       ROUND(AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS "MOVING AVG"
    FROM TRAINING.BII_ORDERS
ORDER BY order_id;

This query returns the following result:

This example calculates the moving average of the amounts for the previous two orders and the current order. This is a three-order moving average.

For rows 1 and 2, there are not yet three orders to average together. In row 2, the average of row 1 and 2 is calculated. It is only from row 3 on that the average over three rows is calculated.

The following is an example of the syntax to use to return a difference:

12345
SELECT stock, period, stock_price, 
       stock_price – sum(stock_price) OVER (ORDER BY period ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)) AS "DIFFERENCE"
    FROM TRAINING.STOCK_PRICES
WHERE stock = ‘SAP’
ORDER BY stock, period;

This query returns the following result:

This example displays the current stock price and the difference between the current price and the previous price.

The difference is calculated by subtracting the stock price of the previous row from the stock price of the current row. A positive value indicates an increase and a negative value indicates a decrease.

In row 1, as there is no previous row value to subtract, a null value (?) is displayed in the DIFFERENCE column.