Thread: Oracle Analytic Functions for PostreSQL

Hi,
I have found Oracle analytic functions very useful. They are so convenient
to calculate running totals, moving averages, ranks, order data in
buckets... and so on. I have found in the archives a few threads that deal
with the replication of some of those functions on PostgreSQL but I don't
think I have the skills to write by myself such a package (I mean algorithms
can get pretty complicated). It seems that PostgreSQL does not offer the
equivalent (maybe because they do not belong to standard SQL?).  If I am
wrong thanks for letting me know where in the docs I can find details.
Otherwise, are you aware of an open-source project (maybe a corporation)
offering a set of equivalent functions ?
Many thanks
JCR

---

Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.

I'll try to do my best to answer you questions.  But you might get a better response on the
general mailing list.


> It seems that PostgreSQL does not  offer the
> equivalent (maybe because they do not belong to standard SQL?).

From what I understand, PostgreSQL strongly support Standard SQL.

This a quote taken from http://www.postgresql.org/about/

"
PostgreSQL prides itself in standards compliance. Its SQL implementation strongly conforms to the
ANSI-SQL 92/99 standards. It has full support for subqueries (including subselects in the FROM
clause), read-committed and serializable transaction isolation levels. And while PostgreSQL has a
fully relational system catalog which itself supports multiple schemas per database, its catalog
is also accessible through the Information Schema as defined in the SQL standard.
"

> If I am
> wrong thanks for letting me know where in the docs I can find  details.
> Otherwise, are you aware of an open-source project (maybe a  corporation)
> offering a set of equivalent functions ?

I understand that enterpriseDB is a PostgreSQL RDBMS that closly supports Oracle code.  Perhaps it
might have what you are looking for if the standard version of PostgreSQL does not.

http://www.enterprisedb.com/

Regards,

Richard Broersma Jr.

* Richard Broersma Jr <rabroersma@yahoo.com> [2006-04-21 14:13:36 -0700]:

> I'll try to do my best to answer you questions.  But you might get a better response on the
> general mailing list.
>
>
> > It seems that PostgreSQL does not  offer the
> > equivalent (maybe because they do not belong to standard SQL?).
>
> >From what I understand, PostgreSQL strongly support Standard SQL.

I think the OP knows that and is suggesting that might be why PostgreSQL
does not provide the analytic functions he describes.  (i.e. the
functions are not part of the standard).

regards,
Andy

There are many things from SQL:200n that PostgreSQL doesn't support yet.
SQL:200n doesn't have ORA's analytical functions, but it contains some
features that definitely may be useful in creating SQL statements for
data analysis: ROLLUP, CUBE, GROUPING; WITH .. , recursive statements
and so on.

Just for clarity...

On 4/23/06, Andrew Chambers <andychambers2002@yahoo.co.uk> wrote:
> * Richard Broersma Jr <rabroersma@yahoo.com> [2006-04-21 14:13:36 -0700]:
>
> > I'll try to do my best to answer you questions.  But you might get a better response on the
> > general mailing list.
> >
> >
> > > It seems that PostgreSQL does not  offer the
> > > equivalent (maybe because they do not belong to standard SQL?).
> >
> > >From what I understand, PostgreSQL strongly support Standard SQL.
>
> I think the OP knows that and is suggesting that might be why PostgreSQL
> does not provide the analytic functions he describes.  (i.e. the
> functions are not part of the standard).
>
> regards,
> Andy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


--
Best regards,
Nikolay

In an attempt to throw the authorities off his trail, jcxxr@yahoo.com (Jean-Christophe Roux) transmitted:
> Hi, I have found Oracle analytic functions very useful. They are so
> convenient to calculate running totals, moving averages, ranks,
> order data in buckets... and so on. I have found in the archives a
> few threads that deal with the replication of some of those
> functions on PostgreSQL but I don't think I have the skills to write
> by myself such a package (I mean algorithms can get pretty
> complicated). It seems that PostgreSQL does not offer the equivalent
> (maybe because they do not belong to standard SQL?).  If I am wrong
> thanks for letting me know where in the docs I can find details.
> Otherwise, are you aware of an open-source project (maybe a
> corporation) offering a set of equivalent functions ?  Many thanks

There is material in the SQL2003 standard on analytic functions; I'm
not sure if Oracle's such functions are standards-conformant or not.

It is likely that some could be implemented in a near-standard
fashion; it simply hasn't yet happened.
--
"cbbrowne","@","gmail.com"
http://linuxdatabases.info/info/internet.html
"A  great war  leaves  the  country with three   armies  - an  army of
cripples, an army of mourners, and an army of thieves."
-- German proverb