Oracle OLAP Functions and MySQL Window Functions - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL Usage
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle OLAP Functions and MySQL Window Functions

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Four star feature compatibility

Four star automation level

N/A

GREATEST and LEAST functions might get different results in MySQL. CONNECT BY isn’t supported by MySQL, a workaround is available.

Oracle Usage

Oracle OLAP functions extend the functionality of standard SQL analytic functions by providing capabilities to compute aggregate values based on a group of rows. You can apply the OLAP functions to logically partitioned sets of results within the scope of a single query expression. OLAP functions are usually used in combination with Business Intelligence reports and analytics. They can help boost query performance as an alternative to achieving the same result using more complex non-OLAP SQL code.

Common Oracle OLAP Functions

Function type Related functions

Aggregate

average_rank, avg, count, dense_rank, max, min, rank, sum

Analytic

average_rank, avg, count, dense_rank, lag, lag_variance, lead_variance_percent, max, min, rank, row_number, sum, percent_rank, cume_dist, ntile, first_value, last_value

Hierarchical

hier_ancestor, hier_child_count, hier_depth, hier_level, hier_order, hier_parent, hier_top

Lag

lag, lag_variance, lag_variance_percent, lead, lead_variance, lead_variance_percent

OLAP DML

olap_dml_expression

Rank

average_rank, dense_rank, rank, row_number

For more information, see OLAP Functions and Functions in the Oracle documentation.

MySQL Usage

Some Oracle OLAP functions are aggregative functions in Aurora MySQL. For more information, see Single-Row and Aggregate Functions.

You can replace other OLAP functions with window functions, which are currently not available in Aurora MySQL.

Aurora MySQL version 5.7 does not support window functions.

Note

Amazon Relational Database Service (Amazon RDS) for MySQL version 8 supports window functions that for each row from a query perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition several existing aggregate functions now can be used as window functions such as SUM() and AVG(). For more information, see Window Functions in the MySQL documentation.

Migration Considerations

As a temporary workaround, rewrite the code to remove the use of Window Functions, and revert to using more traditional SQL code solutions.

In most cases, you can find an equivalent SQL query, although it may be less optimal in terms of performance, simplicity, and readability. See the examples below for migrating window functions to code that uses correlated subqueries.

Note

You may want to archive the original code and then reuse it in the future when Aurora MySQL is upgraded to version 8. The documentation for version 8 indicates the window function syntax is ANSI compliant and will be compatible with Oracle PL\SQL syntax.

For more information, see Window Functions in the MySQL documentation.

Examples

The following examples demonstrate ANSI SQL compliant subquery solutions:

Create and populate an OrderItems table.

CREATE TABLE OrderItems(
OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL,
Quantity SMALLINT NOT NULL,
PRIMARY KEY(OrderID, Item));

INSERT INTO OrderItems (OrderID, Item, Quantity)
  VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100),
    (3, 'M8 Washer', 200);

Rank items based on ordered quantity. This is a workaround for the window ranking function.

SELECT Item, Quantity,
(SELECT COUNT(*) FROM OrderItems
  AS OI2 WHERE OI.Quantity > OI2.Quantity) + 1
    AS QtyRank
FROM OrderItems AS OI;

Calculate the grand total. This is a workaround for a partitioned window aggregate function.

SELECT Item, Quantity,
(SELECT SUM(Quantity) FROM OrderItems
  AS OI2 WHERE OI2.OrderID = OI.OrderID)
  AS TotalOrderQty
FROM OrderItems AS OI;

For more information, see Window Function Descriptions in the MySQL documentation.