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:
- What SQL window functionality is supported?
- How does PostgreSQL 8.4 offering compare to that of the database you are currently using?
- Is the subset of functionality you use supported?
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.
- Window rank type functions. There are a couple of built-in functions defined for these in the ANSI SQL specs. Most common are ROW_NUMBER() and RANK().
- Window aggregates. These are window constructs that use an aggregate function such as SUM, COUNT, AVG and aggregate data a window frame of data relative to the current record or some
defined partition the current record belongs in. These are useful for doing rollup sums, moving averages, running totals, full totals that would appear in each child record of the grouping.
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.
| Item | Oracle 11G | IBM DB2 V.9 | SQL Server 2008 | PostgreSQL 8.4 |
|---|
| Introduced in | Oracle 8i | DB2 6? | SQL Server 2005 | 8.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. | Yes | Yes | Yes | Yes |
Custom Window Functions
(ability to define custom window range like functions) | Yes | No | No | Yes - but only C functions currently supported |
| Basic Frame Clauses (no numbers) | Yes | Yes | No | Yes |
| Frame Clauses (with numbered ranges and rows) | Yes | Yes | No | No |
| Frame Clauses (between specific field values) | Yes | No | No | No |
| Ability to chain multiple frame clauses | Yes | No | No | No |
| ORDER BY within a range/rank function | Yes | Yes | Yes | Yes |
ORDER BY within an aggregation
e.g. SUM(*) OVER(PARTITION BY ..ORDER BY ..) | Yes | Yes | No | Yes |
| Interval frame ranges for dates | Yes | No | No | No |
| Named windows - ability to name a window expression and reference it multiple times in the same query | No | No | No | Yes |
PostGIS in Action
About the Authors
Consulting
Tracked: Jul 12, 22:27