Window functions in H2

[Lukas Eder]

Hello,

I have seen that you put window functions such as RANK(),
DENSE_RANK(), etc. on the roadmap. Is that something you will
implement in the near future? And if you do, to what extent?

I know, this hasn't been an important topic in discussions around H2.
Nevertheless, I find this a very inspiring topic and apart from
Postgres, H2 might be one of the first open-source databases actually
implementing windows functions - apart, of course, from the major
commercial ones, such as DB2, Oracle, SQL Server, and Sybase.

Cheers
Lukas

[Shammat]

> I know, this hasn't been an important topic in discussions around H2.
> Nevertheless, I find this a very inspiring topic and apart from
> Postgres, H2 might be one of the first open-source databases actually
> implementing windows functions

Firebird 3.0 will have support for windowing functions (http://
tracker.firebirdsql.org/browse/CORE-1688)

So if H2 wants to be the "second", it needs to be done before Firebird
3.0 is released ;)

[Lukas Eder]

> Firebird 3.0 will have support for windowing functions (http://
> tracker.firebirdsql.org/browse/CORE-1688)
>
> So if H2 wants to be the "second", it needs to be done before Firebird
> 3.0 is released ;)

OK, true. I just wanted to make this feature a bit more
"interesting" ;-)
To be fair, Derby also has initial (far from complete) support for the
ROW_NUMBER() function.

[Shammat]

Yes, but it does not even support PARTITION BY so it isn't really a
windowing function.

[Lukas Eder]

OK, anyway. I'm curious about H2 though...

[Thomas Mueller]

Hi,

> I have seen that you put window functions such as RANK(),
> DENSE_RANK(), etc. on the roadmap. Is that something you will
> implement in the near future? And if you do, to what extent?

The more people request it, the high the priority. But I would like to
understand the use case, so what is your use case?

Regards,
Thomas

[Lukas Eder]

Hi Thomas,

Sure! Probably the most important and obvious use case is the
calculation of ROW_NUMBER() OVER (...) as described also in this
thread:
http://groups.google.com/group/h2-database/browse_thread/thread/55342d429221974a

Apart from that, in my daily work with databases (especially Oracle,
which is quite advanced in this field), I also have these use cases:

- Calculate the total number of records along with a query, where
grouping is not an option (to prevent running the same long running
query twice):
SELECT *, COUNT(*) OVER (PARTITION BY 1) ...

- Calculate some total sums along with a query, where grouping is not
an option (to prevent running the same long running query twice):
SELECT *, SUM(amount) OVER (PARTITION BY 1)
SELECT *, SUM(amount) OVER (PARTITION BY account)

- Calculate running totals. This is a rather advanced usage example of
window functions that nicely depicts their full power:
SELECT booked_at, amount,
SUM(amount) OVER (PARTITION BY 1
ORDER BY booked_at
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS total
FROM transactions

Besides from using these features directly, I also provide support for
them in my database abstraction library jOOQ:
http://sourceforge.net/apps/trac/jooq/wiki/Manual/DSL/FUNCTIONS

Cheers
Lukas

[Daniel Gredler]

Hi guys,

I'm thinking of taking a crack at implementing window / analytic functions like ROW_NUMBER, RANK and DENSE_RANK -- but it's a little daunting. Does anyone have any hints, pointers or advice? We have one use case for this, but I'm afraid that scratching this itch may be more painful than just allowing it to continue to itch :-)

Take care,

Daniel

[Noel Grandin]

Hi

I had a brief bash at this, but it's a little painful.
Most DB's implement window functions by translating them into an additional joins.
If you google around for how to translate window functions to joins you'll find the strategy somewhere.

But the hard part, is that we have limited support for query transformation.
You'd need to hook into the optimize() method call on the Prepared subclasses, and teach them how to transform the
Select such that the window function is an extra join.

Regards Noel

[Lukas Eder]

I can see RANK() and DENSE_RANK() being emulated, but how do you emulate ROW_NUMBER() with joins? Do you remember what strategy you were looking at at the time you had a look at the implementation?

[Noel Grandin]

On 2013-11-28 08:37, Lukas Eder wrote:
> I can see RANK() and DENSE_RANK() being emulated, but how do you emulate ROW_NUMBER() with joins? Do you remember what
> strategy you were looking at at the time you had a look at the implementation?

I think I was only looking at RANK and DENSE_RANK, I hadn't considered ROW_NUMBER, so I have no idea how that would need
to be implemented.