SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 240 posts at DZone. You can read more from them at their website. View Full User Profile

SQL:2003 window functions in jOOQ

05.19.2011
| 4178 views |
  • submit to reddit

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

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 
from author

This results in something like

 row_numberlast_name
 1 Orwell
 2 Coelho
 3 Shakespeare
 4 Hesse

 

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:

  1. PARTITION BY ... divides the rows into groups, almost like the SELECT's GROUP BY clause
  2. ORDER BY ... orders the assignment of rows to the window function, like the SELECT's ORDER BY clause
  3. 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
FROM transactions

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

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(*)
FROM T_AUTHOR
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
LIMIT 2
OFFSET 1
FOR UPDATE

This query translates into jOOQ's fluent API:

create.select(TAuthor.FIRST_NAME, TAuthor.LAST_NAME, create.count())
.from(T_AUTHOR)
.join(T_BOOK).on(TBook.AUTHOR_ID.equal(TAuthor.ID))
.where(TBook.LANGUAGE.equal("DE"))
.and(TBook.PUBLISHED.greaterThan(parseDate('2008-01-01')))
.groupBy(TAuthor.FIRST_NAME, TAuthor.LAST_NAME)
.having(create.count().greaterThan(5))
.orderBy(TAuthor.LAST_NAME.asc().nullsFirst())
.limit(1, 2)
.forUpdate();

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
// table.
create.select(BOOKED_AT, AMOUNT, AMOUNT.sumOver()
.partitionByOne()
.orderBy(BOOKED_AT)
.rowsBetweenUnboundedPreceding()
.andCurrentRow().as("total"))
.from(TRANSACTIONS)
.orderBy(BOOKED_AT.desc())
.fetch()
.format();

The above statement will execute and format the query to an ASCII table that might look something like this

|BOOKED_AT |AMOUNT|TOTAL|
+----------+------+-----+
|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|

Conclusion

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.

Reference

The Postgres tutorial on window functions.
A nice overview of Oracle Analytic Functions.
The SQL Server OVER clause documentation.

Published at DZone with permission of Lukas Eder, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)