Window Functions Comparison Between PostgreSQL 8.4, SQL Server 2008, Oracle, IBM DB2

PostgreSQL 8.4 has ANSI SQL:2003 window functions support. These are often classified under the umbrella terms of basic Analytical or Online Application Processing (OLAP) functions. They are used most commonly for producing cumulative sums, moving averages and generally rolling calculations that need to look at a subset of the overall dataset (a window frame of data) often relative to a particular row. For users who use SQL window constructs extensively, this may have been one reason in the past to not to give PostgreSQL a second look. While you may not consider PostgreSQL as a replacement for existing projects because of the cost of migration, recoding and testing, this added new feature is definitely a selling point for new project consideration.

If you rely heavily on windowing functions, the things you probably want to know most about the new PostgreSQL 8.4 offering are:

To make this an easier exercise we have curled thru the documents of the other database vendors to distill what the SQL Windowing functionality they provide in their core product. If you find any mistakes or ambiguities in the below please don't hesitate to let us know and we will gladly amend.

For those who are not sure what this is and what all the big fuss is about, please read our rich commentary on the topic of window functions.

Window functions

There are two kinds of window constructs and they are very similar.

To our knowledge, PostgreSQL is the first open source database to introduce ANSI-2003 SQL Windowing support. Firebird database has window functions on their todo, but not released yet. We don't see this feature on MySQL's roadmap.

Both Oracle and IBM DB2 had windowing support before Microsoft SQL Server and PostgreSQL.

Below we have outlined the basic syntactic structure of each of the databases we will be discussing

Oracle 11G
 (rank or agg window) Function([arguments]) OVER
	   ([PARTITION BY value/expr, ...]
		   [ORDER BY [SIBLINGS] expr [ASC|DESC], ...
			  [ROWS | RANGE windowing_clause]])
	windowing_clauses:
	   INTERVAL 'nn' DAY PRECEDING
	   INTERVAL 'nn' SECONDS FOLLOWING
	   INTERVAL 'nn' MONTH PRECEDING
	   BETWEEN x PRECEDING AND y FOLLOWING
	   BETWEEN x PRECEDING AND y PRECEDING
	   BETWEEN CURRENT ROW AND y FOLLOWING
	   BETWEEN x PRECEDING AND CURRENT ROW
	   BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING
	   BETWEEN UNBOUNDED PRECEDING AND y FOLLOWING
	   column BETWEEN current.column +/- n AND current.column +/- m
	   UNBOUNDED PRECEDING | FOLLOWING
	   value/expr PRECEDING | FOLLOWING
	   CURRENT ROW

	   --where x and y are integers denoting position row of record relative to current
	   --where n and m are value ranges in a column
			
IBM DB2 V. 9
Function([arguments]) OVER
	   ([PARTITION BY value/expr, ..]
		   [ORDER BY expr [ASC|DESC]
			  [ROWS | RANGE windowing_clause]])
	windowing clauses:
	   BETWEEN x PRECEDING AND y FOLLOWING
	   BETWEEN x PRECEDING AND y PRECEDING
	   BETWEEN CURRENT ROW AND y FOLLOWING
	   BETWEEN x PRECEDING AND CURRENT ROW
	   BETWEEN x PRECEDING AND UNBOUNDED FOLLOWING
	   BETWEEN UNBOUNDED PRECEDING AND y FOLLOWING
	   UNBOUNDED PRECEDING | FOLLOWING
	   value/expr PRECEDING | FOLLOWING
	   CURRENT ROW

	   --where x and y are integers position row of record relative to current
	   
SQL Server 2008

SQL Server does not allow order by with aggregate functions and does not support window framing clauses so you can't do running sums and totals though you can accomplish similar things with grouping and ROLLUP and CUBE constructs.

Ranking_Window_function([arguments])
		 OVER
	   ([PARTITION BY value/expr, ...]
		   [ORDER BY expr [ASC|DESC], ... )
----------------------------------------------
Agg_Window_function ([arguments])
		OVER ( [ PARTITION BY value_expression , ...  ] )

		frame clauses:
			Does not support frame clauses

		
PostgreSQL 8.4

PostgreSQL does support the window framing clauses but limited set than Oracle and IBM DB2. So you can't specify specific number ranges or row ranges. PostgreSQL supports definition of named windows and reusing these.

Function([arguments]) OVER
	   ([PARTITION BY value/expr, ..]
		   [ORDER BY expr [ASC|DESC]
			  [ROWS | RANGE frame_clause]])
		----------------------------------------------
Function([arguments]) OVER named_window

	frame clauses:
	   RANGE UNBOUNDED PRECEDING
	   RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	   ROWS UNBOUNDED PRECEDING
	   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
	   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Below is a break down of key window pieces and what is supported or not supported in each.

ItemOracle 11GIBM DB2 V.9SQL Server 2008PostgreSQL 8.4
Introduced inOracle 8iDB2 6?SQL Server 20058.4
Built-in Window supported functions
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
ROW_NUMBER
DENSE_RANK (DENSERANK)
RANK
ROW_NUMBER (ROWNUMBER)
DENSE_RANK
NTILE
RANK
ROW_NUMBER
CUME_DIST
DENSE_RANK
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTH_VALUE
NTILE
PERCENT_RANK
RANK
ROW_NUMBER
Window aggregates with any aggregate (SUM, AVERAGE, COUNT) etc.YesYesYesYes
Custom Window Functions
(ability to define custom window range like functions)
YesNoNoYes - but only C functions currently supported
Basic Frame Clauses (no numbers)YesYesNoYes
Frame Clauses (with numbered ranges and rows)YesYesNoNo
Frame Clauses (between specific field values)YesNoNoNo
Ability to chain multiple frame clausesYesNoNoNo
ORDER BY within a range/rank functionYesYesYesYes
ORDER BY within an aggregation
e.g. SUM(*) OVER(PARTITION BY ..ORDER BY ..)
YesYesNoYes
Interval frame ranges for datesYesNoNoNo
Named windows - ability to name a window expression and reference it multiple times in the same queryNoNoNoYes