Chapter 13: Exploiting SQL Functions

From mathematical and statistical computations to string and date-time manipulations, respectively, to different types of aggregations, rankings, and groupings, SQL built-in functions are quite handy in many scenarios. There are different categories of functions depending on their goal and usage and, as you'll see, jOOQ has accorded major attention to their support. Based on these categories, our agenda for this chapter follows these points:

• Regular functions
• Aggregate functions
• Window functions
• Aggregates as window functions
• Aggregate functions and ORDER BY
• Ordered set aggregate functions (WITHIN GROUP)
• Grouping, filtering, distinctness, and functions
• Grouping sets

Let's get started!

Technical requirements

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter13.

Regular functions

Being a SQL user, you've probably worked with a lot of regular or common SQL functions such as functions for dealing with NULL values, numeric functions, string functions, date-time functions, and so on. While the jOOQ manual represents a comprehensive source of information structured as a nomenclature of all the supported SQL built-in functions, we are trying to complete a series of examples designed to get you familiar with the jOOQ syntax in different scenarios. Let's start by talking about SQL functions for dealing with NULL values.

Just in case you need a quick overview about some simple and common NULL stuff, then quickly check out the someNullsStuffGoodToKnow() method available in the bundled code.

SQL functions for dealing with NULLs

SQL provides several functions for handling NULL values in our queries. Next, let's cover COALESCE(), DECODE(), IIF(), NULLIF(), NVL(), and NVL2() functions. Let's start with COALESCE().

COALESCE...

Aggregate functions

The most common aggregate functions (in an arbitrary order) are AVG(), COUNT(), MAX(), MIN(), and SUM(), including their DISTINCT variants. I'm pretty sure that you are very familiar with these aggregates and you've used them in many of your queries. For instance, here are two SELECT statements that compute the popular harmonic and geometric means for sales grouped by fiscal year. Here, we use the jOOQ sum() and avg() functions:

// Harmonic mean: n / SUM(1/xi), i=1…n
ctx.select(SALE.FISCAL_YEAR, count().divide(
     sum(inline(1d).divide(SALE.SALE_))).as("harmonic_mean"))
   .from(SALE).groupBy(SALE.FISCAL_YEAR).fetch();

And here, we compute the geometric mean:

// Geometric mean: EXP(AVG(LN(n)))
ctx.select(SALE.FISCAL_YEAR, exp(avg(ln(SALE.SALE_)))
            .as("geometric_mean"))
   .from(SALE).groupBy...

Window functions

Window functions are extremely useful and powerful; therefore, they represent a must-know topic for every developer that interacts with a database via SQL. In a nutshell, the best way to quickly overview window functions is to start from a famous diagram representing a comparison between an aggregation function and a window function that highlights the main difference between them, as represented here:

Figure 13.3 – Aggregate functions versus window functions

As you can see, both the aggregate function and the window function calculate something on a set of rows, but a window function doesn't aggregate or group these rows into a single output row. A window function relies on the following syntax:

window_function_name (expression) OVER (
    Partition Order Frame
)

This syntax can be explained as follows:

Obviously, window_function_name represents the window function name, such as ROW_NUMBER(), RANK(...

Aggregates as window functions

Aggregate functions can be used as window functions as well. For instance, let's use the SUM() aggregate function as a window function for computing the sum of the successfully transferred amount per customer until each caching date, as illustrated in the following screenshot:

Figure 13.22 – Sum of the transferred amount until each caching date

The jOOQ query can be expressed like this:

ctx.select(BANK_TRANSACTION.CUSTOMER_NUMBER,   
  BANK_TRANSACTION.CACHING_DATE,
  BANK_TRANSACTION.TRANSFER_AMOUNT, BANK_TRANSACTION.STATUS,
  sum(BANK_TRANSACTION.TRANSFER_AMOUNT).over()
   .partitionBy(BANK_TRANSACTION.CUSTOMER_NUMBER)
   .orderBy(BANK_TRANSACTION.CACHING_DATE)
   .rowsBetweenUnboundedPreceding().andCurrentRow().as("result"))
  .from(BANK_TRANSACTION)
  .where(BANK_TRANSACTION.STATUS.eq("...

Aggregate functions and ORDER BY

Certain aggregate functions output significantly different results depending on their input order. By default, this ordering is not specified, but it can be controlled via an optional ORDER BY clause as an argument. So, in the presence of ORDER BY on these aggregate function calls, we can fetch ordered aggregated results. Let's see how we can use such functions in jOOQ and start with a category of functions having their names suffixed with AGG, such as ARRAY_AGG(), JSON_ARRAYAGG(), XML_AGG(), MULTISET_AGG() (covered in Chapter 8, Fetching and Mapping), and so on.

FOO_AGG()

For instance, ARRAY_AGG() is a function that aggregates data into an array and, in the presence of ORDER BY, it aggregates data into an array conforming to the specified order. Here is an example of using ARRAY_AGG() to aggregate EMPLOYEE.FIRST_NAME in descending order by EMPLOYEE.FIRST_NAME and LAST_NAME:

ctx.select(arrayAgg(EMPLOYEE.FIRST_NAME).orderBy(
  ...

Ordered set aggregate functions (WITHIN GROUP)

Ordered set aggregate functions allow operations on a set of rows sorted with ORDER BY via the mandatory WITHIN GROUP clause. Commonly, such functions are used for performing computations that depend on a certain row ordering. Here, we can quickly mention hypothetical set functions such as RANK(), DENSE_RANK(), PERCENT_RANK(), or CUME_DIST(), and inverse distribution functions such as PERCENTILE_CONT(), PERCENTILE_DISC(), or MODE(). A particular case is represented by LISTAGG(), which is covered at the end of this section.

Hypothetical set functions

A hypothetical set function calculates something for a hypothetical value (let's denote it as hv). In this context, DENSE_RANK() computes the rank of hv without gaps, while RANK() does the same thing but with gaps. CUME_DIST() computes the cumulative distribution of hv (the relative rank of a row from 1/n to 1), while PERCENT_RANK() computes the percent rank of hv (the relative...

Grouping, filtering, distinctness, and functions

In this section, grouping refers to the usage of GROUP BY with functions, filtering refers to the usage of the FILTER clause with functions, and distinctness refers to aggregate functions on distinct values.

Grouping

As you already know, GROUP BY is a SQL clause useful for arranging rows in groups via one (or more) column given as an argument. Rows that land in a group have matching values in the given columns/expressions. Typical use cases apply aggregate functions on groups of data produced by GROUP BY.

Important Note

Especially when dealing with multiple dialects, it is correct to list all non-aggregated columns from the SELECT clause in the GROUP BY clause. This way, you avoid potentially indeterminate/random behavior and errors across dialects (some of them will not ask you to do this (for example, MySQL), while others will (for example, Oracle)).

jOOQ supports GROUP BY in all dialects, therefore here is an example...

Grouping sets

For those not familiar with grouping sets, let's briefly follow a scenario meant to quickly introduce and cover this notion. Consider the following screenshot:

Figure 13.24 – Two queries using a grouping set each

The groupBy(SALE.EMPLOYEE_NUMBER) construction from the left-hand side (respectively, groupBy(SALE.FISCAL_YEAR) from the right-hand side) is referred to as a grouping set. A grouping set can contain none (empty grouping set), one, or more columns. In our case, both grouping sets contain one column.

Getting a unified result set of these two result sets containing the aggregated data of both grouping sets can be done via the UNION ALL operator, as illustrated here:

Figure 13.25 – Union grouping sets

But, as you can see, even for only two grouping sets, this query is quite lengthy. Moreover, it needs to resolve two SELECT statements before combining their results into a single result set. Here...

Summary

Working with SQL functions is such fun! They truly boost the SQL world and allow us to solve so many problems during data manipulation. As you saw in this chapter, jOOQ provides comprehensive support to SQL functions, covering regular and aggregate functions to the mighty window functions, ordered set aggregate functions (WITHIN GROUP), and so on. While we're on this topic, allow me to recommend the following article as a great read: https://blog.jooq.org/how-to-find-the-closest-subset-sum-with-sql/. In the next chapter, we tackle virtual tables (vtables).