Subject | Improving CTE with ANSI SQL2003 Window Functions |
---|---|

Author | Philippe Makowski |

Post date | 2008-04-08T14:46:28Z |

Hello,

Adriano dos Santos Fernandes [08-02-09 00.15] :

I asked this because I think it would be useful for statistical calculation such as

median, quartiles ...

If we could have at least OVER PARTITION BY clause with our current aggregate functions

plus the ROW_NUMBER( ) function it would be very useful

Now we have no easy way to do this (with Fb2.1 I simulate some with execute

block,temporary table and CTE, but it is not quite easy to maintain and with lots of data,

insert into temporary table cost a lot of time.

Adriano dos Santos Fernandes [08-02-09 00.15] :

> Window functions (from Oracle Analytic Functions POV, which I know moreyes I ask for this new feature in Jira (CORE-1688)

> than the standard) is a complex thing.

>

> There are many functions and many clauses.

>

> But I think it is easy to implement our current aggregate functions as

> window functions with simple OVER PARTITION BY clause...

>

> I recommend you to start specifying what would be the more important

> functions and clauses, and how are you going to use.

>

I asked this because I think it would be useful for statistical calculation such as

median, quartiles ...

If we could have at least OVER PARTITION BY clause with our current aggregate functions

plus the ROW_NUMBER( ) function it would be very useful

Now we have no easy way to do this (with Fb2.1 I simulate some with execute

block,temporary table and CTE, but it is not quite easy to maintain and with lots of data,

insert into temporary table cost a lot of time.