Database Products with Window Functions Support
This permanently updated list shows all database products with built-in native support for Window Functions. This includes the OVER clause, named windows or window chaining with the WINDOW clause, together with ranking functions, offset navigational functions and other related functions.
Table of Contents
Window Functions Specification
Window functions are heavily documented in the the ANSI/ISO Standard SQL:2003 and the ANSI/ISO Standard SQL:2011. They have been introduced as a new feature in the ANSI/ISO Standard SQL:2003. The new built-in scalar and aggregate functions have been presented as OLAP (on-line analytical processing) extensions.
Relational database vendors usually implement most of these standard-based features:
(1) A WINDOW clause, that can be used to define a generic window definition. Inline OVER clauses can then refer to these window definitions by name.
This is great for reuse, when multiple SELECT items will use the same OVER clauses. Remark that a WINDOW can be also partially defined based on another previously defined WINDOW. The clause can be added before the ORDER BY of the main query.
WINDOW <window_name_1> AS ([<other_window_name>] [<OVER_clause>]) [,<window_name_2> AS …]The WINDOW clause has been introduced by an amendment to the SQL:1999 standard, and can be found fully described in the SQL:2003 standard. Only around half of the known vendors implement this feature.
(2) An OVER clause, that can be added at the end of a SQL SELECT expression. This is described in the SQL standard as part of the WINDOW definition. It may come in three parts:
- A PARTITION BY clause, similar to inline GROUP BY fields.
- An ORDER BY clause, similar to inline ORDER BY fields.
- A ROWS/RANGE clause, to further limit the frame for the current partition.
[PARTITION BY columnRef [COLLATE collation] [, …]]
[ORDER BY expr [ASC | DESC] [NULLS FIRST | NULLS LAST] [, …]]
[ROWS/RANGE <start>|<between> [EXCLUDE {CURRENT ROW | GROUP |TIES | NO OTHERS} ]]
<start> ::= UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW
<between> ::= BETWEEN <bound> AND <bound>
<bound> ::= UNBOUNDED PRECEDING/FOLLOWING | n PRECEDING/FOLLOWING | CURRENT ROW
While the SQL standard seems to allows a ROWS BETWEEN UNBOUNDED FOLLOWING construct, none of the vendors we looked at support this use case. Also be aware that ROWS and RANGE are not synonyms, they may have subtle differences and provide different implementations. ROWS is the common keyword when you refer to rows by relative position.
(3) Ranking functions, which are global functions that can be used with the OVER clause. SQL:2003 introduced the RANK, DENSE_RANK, PERCENT_RANK and CUME_DIST rank function types, alongside the new ROW_NUMBER window function.
(4) Offset navigation functions, which aggregate or point to one or more partition rows relative to the current row. NTILE, LEAD/LAG, FIRST_VALUE/LAST_VALUE and NTH_VALUE have been defined and introduced by the SQL:2008/SQL:2011 standards. SQL:2011 also introduced the so-called nested window function, using a special ROW_NUMBER or a new VALUE_OF function, which has been rarely implemented by any vendor.
(5) Aggregate window functions are extended typical aggregates such as COUNT, SUM/AVG or MIN/MAX that can be also used before an OVER clause.
Vendor-specific aggregates may offer extensions for grouping sets, such as GROUPING and GROUPING_ID. Oracle has LISTAGG, MEDIAN, and FIRST/LAST. SQL Server has COUNT_BIG, APPROX_COUNT_DISTINCT, STRING_AGG and CHECKSUM_AGG.
(6) Statistical functions are often described with or as part of the window functions, but they are rather a category apart. We will skip for now the variance-based statistical functions and the linear regression functions. We will also skip very specific functions such as PERCENTILE_CONT and PERCENTILE_DISC, or RATIO_TO_REPORT.
Window Functions Support Summary
| Product | Support |
|---|---|
| Oracle* | with support since version 8i (1998) |
| PostgreSQL* | with support since version 8.4 (2009) |
| IBM Db2* | with support since version 9 for z/OS (2008) |
| Microsoft SQL Server* | with limited support since SQL Server 2005 extended support in SQL Server 2012 |
| SAP/Sybase SQL Anywhere* | with support since version 12 (2010) |
| Firebird** | with limited support since version 3 (2016) |
| SQLite** | with support since version 3.25 (2018) extended support in version 3.28 (2019) |
| Amazon Redshift* | with support since version 1 released (2013) |
| MySQL* | with support since version 8.0.2 (2018) |
| MariaDB* | with support since version 10.2 (2017) |
| Actian Ingres | with support since version 10.2 (2015) |
| IBM Informix | with support since version 12.10 (2013) |
| SAP/Sybase IQ | with support |
| Google BigQuery | with support |
| Apache Spark/Hive | with support |
| Apache Drill | with support |
| Teradata | with support |
| Cloudera Impala | with support |
| Presto | with support |
| Snowflake | with support |
| SAP/Sybase ASE | no support |
| Microsoft Access | no support |
| Microsoft SQL Server CE | no support |
* – supported by the visual SQL query builder of Data Xtractor.
** – supported by both Data Xtractor and Query Xtractor, for free.
Analytic Functions in Oracle
Oracle Database had window functions support in PL/SQL since its version 8i, around year 2000. They are still called Analytic Functions and they provide one of the most complete sets of window functions.
Oracle has many specific extensions for the aggregate functions, such as LISTAGG, MEDIAN, and FIRST/LAST. The ORDER BY clause accepts NULLS FIRST and NULLS LAST.
Statistical functions are considered part of the package, and most of them can be used with the OVER windowing clause.
Surprisingly enough, the WINDOW clause has never been implemented, so you have to type the OVER clause for each SELECT item using it.
You can try the visual window functions from Data Xtractor for Oracle for seven days for free. See what a rich visual SQL query editor can do for you.
OLAP Functions in IBM Db2
DB2 offered native window functions support starting with version 9 for z/OS, which was first announced in early 2007. They are still called OLAP Functions or Window Analytic Functions.
Their implementation follows closely the SQL Standard, and it is as rich and complete as Oracle’s. However, they do not implement the WINDOW clause.
In Data Xtractor, we offer window function support for the latest Db2 LUW version (for Linux, Unix and Windows).
Window Functions in PostgreSQL
PostgreSQL is also one of the oldest relational databases with window function support, back since the 8.4 version, released in 2009. PostgreSQL is one open-source RDBMS known for its rich implementations of most standard SQL features, and the category of windows functions makes no exception.
The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard’s default, namely RESPECT NULLS. Likewise, the standard’s FROM FIRST or FROM LAST option for nth_value is not implemented: only the default FROM FIRST behavior is supported. (You can achieve the result of FROM LAST by reversing the ORDER BY ordering.)
Note on the PostgreSQL page
PostgreSQL supports also both window naming and window chaining through the WINDOW clause.
Since version 11, PostgreSQL fully implements the RANGE and GROUPS clauses, and the EXCLUDE clause.
Data Xtractor for PostgreSQL can help you define the ROWS clause in a visual manner, in time.
OLAP Functions in SAP/Sybase SQL Anywhere
SQL Anywhere added window functions with version 12, back in 2010. They called them OLAP Functions.
SQL Anywhere started as Watcom SQL back in 1992. In 2006, with version 10, was renamed SQL Anywhere. At some point, it was acquired by Sybase, but SAP bought Sybase in 2010. The latest version is known as SAP SQL Anywhere.
They also implement the WINDOW clause, but they do not seem to support LEAD/LAG offset functions yet.
Data Xtractor has partial support for the window functions implemented in the latest versions of SQL Anywhere.
Window Functions in Microsoft SQL Server
SQL Server was a bit late to the party, but their window function support today in T-SQL (Transact-SQL) is pretty strong. Microsoft added also several new and specific aggregates that can be used with the OVER clause, such as COUNT_BIG, APPROX_COUNT_DISTINCT, STRING_AGG and CHECKSUM_AGG.
Windowing functions were added to the ANSI/ISO Standard SQL:2003 and then extended in ANSI/ISO Standard SQL:2008. Microsoft was late to this game. DB2, Oracle, Sybase, PostgreSQL and other products have had full implementations for years. SQL Server did not catch up until SQL 2012. Better late than never.
To be fair, SQL Server added support for the standard ranking functions – RANK, DENSE_RANK, ROW_NUMBER and NTILE – and a limited OVER clause in SQL Server 2005. No new features in this area in SQL Server 2008. But SQL Server 2012 implemented the full OVER clause, with ORDER BY and window framing. Also, this version saw the addition of the newly added offset functions LEAD/LAG, FIRST_VALUE/LAST_VALUE, CUME_DIST, PERCENT_RANK, PERCENTILE_DISC/CONT. They do not support NTH_VALUE.
Beware Microsoft SQL Server supports the RANGE clause, but with no offset values.
Microsoft SQL-Server window functions are exposed through a visual SQL query editor in our Data Xtractor, that can help you create, understand and run complex queries in no time.
Window Functions in Amazon Redshift
Redshift is a fork from PostgreSQL version 8.0.2, fully released on the marker as a cloud columnar database in 2013. As window functions have been supported by PostgreSQL starting with version 8.4, Amazon implemented their own support over the years, with several notable differences when compared with today’s PostgreSQL versions.
Only the ROWS keyword can be used for frame filtering, the RANGE does not seem allowed. And there is no WINDOW clause implemented in Amazon Redshift.
Data Xtractor supports all Amazon Redshift versions, with their window function vendor-specific implementations.
OLAP Analytic Functions in IBM Informix
Informix had also window functions support added long ago, since version 12.10. in 2013, with other IBM database products such as Db2. They have been called OLAP Analytic Functions, or OLAP Window Functions.
Informix had the largest market share among the relational database systems back in 1997. It was acquired by IBM in 2001.
Window Functions in Actian Ingres
Ingres, the commercial database system from which the open-source PostgreSQL (i.e. “Post-Ingres”) has been forked in the mid-1980s, started supporting window functions with version 10.2, in 2015. In 2011, Ingres Corporation became Actian Corporation. Ingres 11 was released in 2017 and is now known as Actian X Hybrid Database.
Last time we checked, the support for window functions was still a work in progress, as the implementation was not very reliable. It is expected however a much better support in the new Actian’s database products.
Ingres began as a research project at the University of California, Berkeley, in early 1970s. It ended in 1985. Some Ingres programmers founded Sybase, and had the #2 commercial database product for a while, behind Oracle, in the 1980s and 1990s. Informix took over then in 1997. Microsoft licensed Sybase in 1992, and renamed it Microsoft SQL Server.
Analytical Functions in Firebird
Firebird added window functions starting with their version 3, since April 2016. They were also called Analytical Functions and they were just partially implemented at the beginning.
Latest versions offer partial implementations of the OVER clause, with full PARTITION BY and ORDER BY, but no ROWS/RANGE clause. They may be the only ones here with window functions support, but no framing filter. No WINDOW clause yet, as well.
FIRST_VALUE, LAST_VALUE and NTH_VALUE also operate on a window frame. Currently, Firebird always frames from the first to the current row of the partition, not to the last. This is likely to produce strange results for NTH_VALUE and especially LAST_VALUE.
Important to Note (from their site)
Both Data Xtractor and Query Xtractor implement and support Firebird window functions for 100% free, though our visual SQL query editor.
Window Functions in MariaDB
MariaDB added its own window functions support just lately, in May 2017, with version 10.2. It happened in parallel with but independently of MySQL, adding a similar support in the same period of time. Specific MySQL/MariaDB aggregates, such as BIT_AND/OR/XOR can be also used before an OVER clause.
Unlike MySQL, no WINDOW clause implementation yet.
Our visual SQL query editor helps you create complex MariaDB window functions and understand what you’re doing.
Window Functions in MySQL
MySQL added only recently rich window functions support, in 2018. It started with version 8.0.2 (just beware the version 8.0.0 does not have this support). Until then, you were able to emulate a few constructs with GROUP_CONCAT. BIT_AND/OR/XOR can be also used before an OVER clause.
They also implemented the WINDOW clause.
Amazon Aurora – which is based on MySQL – still uses older versions of MySQL at this time that do not support window functions. It is however expected Amazon will gradually upgrade to the latest versions and support.
Data Xtractor added support for the latest implementation of window functions in MySQL. Use our rich visual SQL query editor to create complex analytic queries in no time.
Window Functions in SQLite
SQLite added rich window function support just lately, since September 2018, starting with their 3.25 version, inspired from PostgreSQL. They added other related features in version 3.28, since April 2019.
Window function support was first added to SQLite with release version 3.25.0 (2018-09-15). The SQLite developers used the PostgreSQL window function documentation as their primary reference for how window functions ought to behave. Many test cases have been run against PostgreSQL to ensure that window functions operate the same way in both SQLite and PostgreSQL.
In SQLite version 3.28.0 (2019-04-16), windows function support was extended to include the EXCLUDE clause, GROUPS frame types, window chaining, and support for “
PRECEDING” and “ FOLLOWING” boundaries in RANGE frames. History note, from their site
If you used the System.Data.SQLite assembly for your .NET projects, just beware it took a while to come up with these latest features, in March 2019.
They may be the only vendors we know (other than PostgreSQL v11) that actually implemented the standard SQL EXCLUDE clause.
Support for latest SQLite implementation of window functions is included for free in both Data Xtractor and Query Xtractor.
Analytical Functions in SAP/Sybase IQ
Sybase IQ had good window functions support.
Analytic Functions in Google BigQuery
BigQuery is among the few database products with a proper implementation of the alternative RANGE clause.
The window frame clause is disallowed for RANK, as it is for all numbering functions. The WINDOW clause is implemented as well.
Window Functions in Apache Spark/Hive
Hive has Spark SQL with implementations of window functions. Window functions belong to Window functions group in Spark’s Scala API.
SQL Window Functions in Apache Drill
Drill has some limited window function implementations. The ROWS/RANGE framing clause does not allow n PRECEDING/FOLLOWING constructs. There is no WINDOW clause implementation, and some standard SQL analytic functions are missing.
Window Functions in Teradata
Teradata has its own specific implementation of the window functions, with some notable differences.
Analytic Functions in Cloudera Impala
Impala added support for window functions in version 2.0.0 (CDH 5.2.0). They look like very standard.
Window Functions in Presto
Presto is a distributed SQL query engine for big data, but they also included window function support. Their architecture allows to query data sources such as MySQL, Cassandra, MongoDB, Hadoop, or Kafka.
All these databases can take advantage of this abstract layer and look like they have native window support, even when it is actually emulated through Presto.
Window Functions in Snowflake
Snowflake is a data warehouse with support for the most common standardized version of SQL: ANSI. They also support window functions, along with data warehousing operations, like create, update, insert, etc.
Snowflake supports two types of window frames: cumulative and sliding.
Notable Databases with no Window Functions Support
Here are a few database products that do not provide native window function support:
- SAP/Sybase ASE – but many other SAP relational database products, such as SQL Anywhere and Sybase IQ, have built-in support for window functions.
- Microsoft Access – not added and very unlikely they will ever add window functions support.
- Microsoft SQL Server CE – this SQL Server version has been already abandoned by Microsoft a while ago, but you can still use it.
Do you know about other databases with native built-in support for window functions?
Please let us know. We will include them in the list.
