SQL:2003 window functions in jOOQ
I have become a very dedicated user of Stackoverflow, where the most difficult questions find qualified answers. I also love using it as a means of "feeling" what the average Java developer struggles with in the field of RDBMS, JDBC, and the various database abstraction frameworks in Java in order to make my database abstraction library jOOQ evolve in the right direction. JPA and its controversial Criteria API are some of the hottest topics as most developers struggle constructing even simple SQL statements with that verbose API. In the mid-term future, we'll finally see JPA having added support for stored procedures (read also my previous article about on that topic), but progress is slow.
Today I want to cover something that seems to have gone almost unnoticed in the community, and in those frameworks. Nevertheless, I find this a highly fascinating topic, as it allows for delegating a lot of calculation and work to the RDBMS quite elegantly: The SQL:2003 standard Window Functions and how I integrated this in jOOQ
Window functions are very useful for calculations related to the current row of a cursor. The simplest of all window functions is ROW_NUMBER(), which quite obviously calculates the current row number from the beginning of iteration over the cursor. An example:
select row_number() over (), last_name
This results in something like
The examples in this article follow the Postgres syntax and run on Postgres 9.0
Note the quite peculiar syntax using the over (...) clause. Within that clause, you can add additional parameters to the window function, any of these:
- PARTITION BY ... divides the rows into groups, almost like the SELECT's GROUP BY clause
- ORDER BY ... orders the assignment of rows to the window function, like the SELECT's ORDER BY clause
- ROWS ... limits the ordered assignment, like some RDBMS's LIMIT .. OFFSET clauses (or FETCH FIRST / NEXT, etc)
This allows for great functionality like the summing up of a running totals
-- get transactions and the running total for every transaction
-- going back to the beginning
SELECT booked_at, amount,
SUM(amount) OVER (PARTITION BY 1
ORDER BY booked_at
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS total
The above window function reads in English:
For every record (booked_at, amount), add the sum of amounts of all previously booked transactions.
The potential is great, as most RDBMS have started supporting many of these functions, and they can be freely combined with any other SQL expressions.
jOOQ (Java Object Oriented Querying) is a database abstraction library, that tries to fill a lot of gaps that major ORMs leave open still today. It is not really a competing product with ORMs like Hibernate or JPA/CriteriaQuery in general, as the paradigm is quite different. While ORMs emphasise on the "Object", which is "persisted" to any arbitrary RDBMS, jOOQ likes to see things in a way that the RDBMS is a source of data for a Java application. This means that SQL and the relational data model is the primary way of thinking, which cannot be fully represented in an object-oriented world. See also this very interesting article I recently came across.
To support the idea of jOOQ emphasising on "SQL" the way SQL works, I'd like to give this rather extensive example:
-- get all authors' first and last names, and the number
-- of books they've written in German, if they have written
-- more than five books in German in the last three years
-- (from 2011), and sort those authors by last names
-- limiting results to the second and third row
SELECT T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME, COUNT(*)
JOIN T_BOOK ON T_AUTHOR.ID = T_BOOK.AUTHOR_ID
WHERE T_BOOK.LANGUAGE = 'DE'
AND T_BOOK.PUBLISHED > '2008-01-01'
GROUP BY T_AUTHOR.FIRST_NAME, T_AUTHOR.LAST_NAME
HAVING COUNT(*) > 5
ORDER BY T_AUTHOR.LAST_NAME ASC NULLS FIRST
This query translates into jOOQ's fluent API:
create.select(TAuthor.FIRST_NAME, TAuthor.LAST_NAME, create.count())
Window functions in jOOQ
jOOQ has matured in the last 6 months and in the latest release finally added support for window functions (which are currently supported in DB2, Oracle, Postgres, SQL Server, and Sybase). With jOOQ, the previous example of a running total involving window functions can be written in a fluent way like this:
// jOOQ uses a central factory class to create executable
// queries with
Factory create = new PostgresFactory(connection);
// BOOKED_AT and AMOUNT are fields in TRANSACTIONS,
// Which is a generated class representing the underlying
create.select(BOOKED_AT, AMOUNT, AMOUNT.sumOver()
The above statement will execute and format the query to an ASCII table that might look something like this
|2011-01-05| 1000| 1250|
|2011-01-04| 180| 250|
|2011-01-03| 20| 70|
|2011-01-02| -50| 50|
|2011-01-01| 100| 100|
jOOQ is growing and getting a bigger and bigger community every day. Window functions are just one more very powerful SQL standard. When you make a choice for one or the other RDBMS, you shouldn't be limited in taking advantage of your RDBMS's great functionality set, just because your database access layer is an ORM that does not support advanced SQL. If you want to get back to SQL and all the nice functionality SQL has, jOOQ may be becoming a better and better choice.