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.
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.
| 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 |