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 post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
