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 On Thursday, May 19, 2011 3:36:59 AM UTC-4, Lukas Eder wrote: > > 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 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
