Oracle OLAP Functions and PostgreSQL Window Functions

Feature compatibility	AWS SCT / AWS DMS automation level	AWS SCT action code index	Key differences
		OLAP Functions	GREATEST and LEAST functions might get different results in PostgreSQL. CONNECT BY isn’t supported by PostgreSQL, workaround 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.

PostgreSQL Usage

PostgreSQL refers to ANSI SQL analytical functions as “Window Functions”. They provide the same core functionality as SQL Analytical Functions and Oracle extended OLAP functions. Window functions in PostgreSQL operate on a logical “partition” or "window" of the result set and return a value for rows in that “window”.

From a database migration perspective, you should examine PostgreSQL Window Functions by type and compare them with the equivalent Oracle OLAP functions to verify compatibility of syntax and output.

Note

Even if a PostgreSQL window function provides the same functionality of a specific Oracle OLAP function, the returned data type may be different and require application changes.

PostgreSQL provides support for two main types of window functions:

• Aggregation functions.

• Ranking functions.

PostgreSQL Window Functions by Type

Function type	Related functions
Aggregate	avg, count, max, min, sum, string_agg
Ranking	row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, nth_value

Examples

The Oracle rank() function and the PostgreSQL rank() function provide the same results.

Oracle:

SELECT department_id, last_name, salary, commission_pct,
    RANK() OVER (PARTITION BY department_id
    ORDER BY salary DESC, commission_pct) "Rank"
    FROM employees WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY COMMISSION_PCT Rank
80            Russell   14000  .4             1
80            Partners  13500  .3             2
80            Errazuriz 12000  .3             3

PostgreSQL:

hr=# SELECT department_id, last_name, salary, commission_pct,
    RANK() OVER (PARTITION BY department_id
    ORDER BY salary DESC, commission_pct) "Rank"
    FROM employees WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY    COMMISSION_PCT Rank
80            Russell   14000.00  0.40           1
80            Partners  13500.00  0.30           2
80            Errazuriz 12000.00  0.30           3

Note

The returned formatting for certain numeric data types is different.

Oracle CONNECT BY Equivalent in PostgreSQL

PostgreSQL provides two workarounds as alternatives to Oracle hierarchical statements such as the CONNECT BY function:

• Use PostgreSQL generate_series function.

• Use PostgreSQL recursive views.

Example

PostgreSQL generate_series function.

SELECT "DATE"
  FROM generate_series(timestamp '2010-01-01',
                       timestamp '2017-01-01',
                       interval '1 day') s("DATE");

DATE
---------------------
2010-01-01 00:00:00
2010-01-02 00:00:00
2010-01-03 00:00:00
2010-01-04 00:00:00
2010-01-05 00:00:00
…

For more information, see Window Functions and Aggregate Functions in the PostgreSQL documentation.

Extended Support for Analytic Queries and OLAP

For advanced analytic purposes and use cases, consider using Amazon Redshift as a purpose-built data warehouse cloud solution. You can run complex analytic queries against petabytes of structured data using sophisticated query optimization, columnar storage on high-performance local disks, and massive parallel query run. Most results are returned in seconds.

Amazon Redshift is specifically designed for online analytic processing (OLAP) and business intelligence (BI) applications, which require complex queries against large datasets. Because it addresses very different requirements, the specialized data storage schema and query run engine that Amazon Redshift uses is completely different from the PostgreSQL implementation. For example, Amazon Redshift stores data in columns, also known as a columnar-store database.

Function type	Related functions
Aggregate	AVG, COUNT, CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, MAX, MEDIAN, MIN, NTH_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, RATIO_TO_REPORT, STDDEV_POP, STDDEV_SAMP (synonym for STDDEV), SUM, VAR_POP, VAR_SAMP (synonym for VARIANCE)
Ranking	DENSE_RANK, NTILE, PERCENT_RANK, RANK, ROW_NUMBER

For more information, see Window functions and Overview example for window functions in the Amazon documentation.

Summary

Oracle OLAP function	Returned data type	PostgreSQL window function	Returned data type	Compatible syntax
Count	Number	Count	bigint	Yes
Max	Number	Max	numeric, string, date/time, network or enum type	Yes
Min	Number	Min	numeric, string, date/time, network or enum type	Yes
Avg	Number	Avg	numeric, double, otherwise same datatype as the argument	Yes
Sum	Number	Sum	bigint, otherwise same datatype as the argument	Yes
rank()	Number	rank()	bigint	Yes
row_number()	Number	row_number()	bigint	Yes
dense_rank()	Number	dense_rank()	bigint	Yes
percent_rank()	Number	percent_rank()	double	Yes
cume_dist()	Number	cume_dist()	double	Yes
ntile()	Number	ntile()	integer	Yes
lag()	Same type as value	lag()	Same type as value	Yes
lead()	Same type as value	lead()	Same type as value	Yes
first_value()	Same type as value	first_value()	Same type as value	Yes
last_value()	Same type as value	last_value()	Same type as value	Yes