Various Meanings of SQL’s PARTITION BY Syntax – Java, SQL and jOOQ.
For SQL beginners, there’s a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meaning is similar to that of GROUP BY, namely to group/partition data sets by some grouping/partitioning criteria.
For example, when querying the Sakila database:
SELECT actor_id, film_id
FROM film_actor
Something like the below might appear:
|actor_id|film_id| |--------|-------| |1 |1 | |2 |3 | |10 |1 | |20 |1 | |1 |23 | |1 |25 | |30 |1 | |19 |2 | |40 |1 | |3 |17 | |53 |1 | |19 |3 | |2 |31 |
And we could partition the data like this for the ACTOR_ID = 1 partition:
|actor_id|film_id|
|--------|-------|
+--> |1 |1 |
All ACTOR_ID = 1 | |2 |3 |
| |10 |1 |
| |20 |1 |
+--> |1 |23 |
+--> |1 |25 |
|30 |1 |
|19 |2 |
|40 |1 |
|3 |17 |
|53 |1 |
|19 |3 |
|2 |31 |
For the ACTOR_ID = 2 partition:
|actor_id|film_id|
|--------|-------|
|1 |1 |
All ACTOR_ID = 2 +--> |2 |3 |
| |10 |1 |
| |20 |1 |
| |1 |23 |
| |1 |25 |
| |30 |1 |
| |19 |2 |
| |40 |1 |
| |3 |17 |
| |53 |1 |
| |19 |3 |
+--> |2 |31 |
How can we use these partitions in SQL, specifically? What do they mean? In short:
A partition separates a data set into subsets, which don’t overlap.
Window partitions
The first thing we can do is the window PARTITION clause, which we use when calculating window functions. For example, we might calculate:
SELECT
actor_id,
film_id,
COUNT(*) OVER (PARTITION BY actor_id)
FROM film_actor
If we’re assuming that we’re seeing the entire data set (the actual table has more rows), then the following result would be displayed:
|actor_id|film_id|count|
|--------|-------|-----|
|1 |1 |3 |
|2 |3 |2 |
|10 |1 |1 |
|20 |1 |1 |
|1 |23 |3 |
|1 |25 |3 |
|30 |1 |1 |
|19 |2 |2 |
|40 |1 |1 |
|3 |17 |1 |
|53 |1 |1 |
|19 |3 |2 |
|2 |31 |2 |
In other words, we’re “counting rows over the partition“. It works almost like GROUP BY, where we count rows from the group, though the GROUP BY clause transforms the result set and the projectable columns, making non-grouped columns unavailable:
SELECT actor_id, COUNT(*)
FROM film_actor
GROUP BY actor_id
Resulting in:
|actor_id|count|
|--------|-----|
|1 |3 |
|2 |2 |
|10 |1 |
|20 |1 |
|30 |1 |
|19 |2 |
|40 |1 |
|3 |1 |
|53 |1 |
If you will, the partition contents are now collapsed such that each partition key / group key only appears once in the result set. This difference makes window functions vastly more powerful than ordinary aggregate functions and grouping.
See also our article about GROUP BY for more details.
MATCH_RECOGNIZE partitions
MATCH_RECOGNIZE is part of the SQL standard, invented by Oracle, and the envy of all other RDBMS (though some have started adopting it). It combines the power of regular expressions, pattern matching, data generation and SQL. It might be sentient, who knows.
For example, let’s look at customers who make small payments within a small amount of time. Look at this beauty. Just look!
SELECT
customer_id,
payment_date,
payment_id,
amount
FROM payment
MATCH_RECOGNIZE (
-- Partition the data set by customer_id
PARTITION BY customer_id
-- Order each partition by payment_date
ORDER BY payment_date
-- Return all the matched rows
ALL ROWS PER MATCH
-- Match rows with 3 occurrences of event "A" in a row
PATTERN (A 3)
-- Define the event "A" as...
DEFINE A AS
-- Being a payment whose amount is less than 1
A.amount < 1
-- And whose payment date is less than 1 day after
-- the previous payment
AND A.payment_date - prev(A.payment_date) < 1
)
ORDER BY customer_id, payment_date
Whew! This uses so many fancy keywords this cheap blog’s syntax highlighter here can’t even remotely keep up!
The result is:
|CUSTOMER_ID|PAYMENT_DATE |PAYMENT_ID|AMOUNT| |-----------|-----------------------|----------|------| |72 |2005-08-18 10:59:04.000|1961 |0.99 | |72 |2005-08-18 16:17:54.000|1962 |0.99 | |72 |2005-08-19 12:53:53.000|1963 |0.99 | |152 |2005-08-20 01:16:52.000|4152 |0.99 | |152 |2005-08-20 19:13:23.000|4153 |0.99 | |152 |2005-08-21 03:01:01.000|4154 |0.99 | |207 |2005-07-08 17:14:14.000|5607 |0.99 | |207 |2005-07-09 01:26:22.000|5608 |0.99 | |207 |2005-07-09 13:56:56.000|5609 |0.99 | |244 |2005-08-20 11:54:01.000|6615 |0.99 | |244 |2005-08-20 17:12:28.000|6616 |0.99 | |244 |2005-08-21 09:31:44.000|6617 |0.99 |
So, we can confirm that for each of those groups of 3 payments, there are:
- Amounts less than 1.
- Consecutive dates less than 1 day apart.
- Groups are per customer, which is again the partition.
Want to learn more about MATCH_RECOGNIZE? I think this article explains it much better than anything else on the web. You can play around with it for free using the Oracle XE 21c, e.g. available on Docker by Gerald Venzl.
MODEL partitions
Even more arcane than MATCH_RECOGNIZE is the Oracle-specific MODEL or SPREADSHEET clause. Every complex application should have at least one MODEL query just to keep your coworkers wondering. An example can be found in our previous article. In short, you can do anything you could otherwise do in a spreadsheet software, such as MS Excel. I’ll give another example here, without a deep dive into how it works:
SELECT
customer_id,
payment_date,
payment_id,
amount
FROM (
SELECT *
FROM (
SELECT p.*, 0 AS s, 0 AS n
FROM payment p
)
MODEL
-- We again partition our data set by customer_id
PARTITION BY (customer_id)
-- The "spreadsheet dimension" is the row number ordered
-- by payment date, within a partition
DIMENSION BY (
row_number () OVER (
PARTITION BY customer_id
ORDER BY payment_date
) AS rn
)
-- Measures is what we want to project, including
-- o Table columns
-- o Additional calculated values
MEASURES (payment_date, payment_id, amount, s, n)
-- These rules are the spreadsheet formulae
RULES (
-- S is the sum of previous amounts that are smaller than 1
-- and whose payment dates are less than 1 day apart
s[any] = CASE
WHEN amount[cv(rn)] < 1
AND payment_date[cv(rn)] - payment_date[cv(rn) - 1] < 1
THEN coalesce(s[cv(rn) - 1], 0) + amount[cv(rn)]
ELSE 0
END,
-- N is the number of consecutive amounts with these properties
n[any] = CASE
WHEN amount[cv(rn)] < 1
AND payment_date[cv(rn)] - payment_date[cv(rn) - 1] < 1
THEN coalesce(n[cv(rn) - 1], 0) + 1
ELSE 0
END
)
) t
-- Filter out only those rows where we had more than 3
-- consecutive events
WHERE n >= 3
ORDER BY customer_id, rn
Drop one of these into your production code base on Friday before deployment, and you’ll be everyone’s darling, guaranteed.
Anyway, MATCH_RECOGNIZE was a bit nicer, I think. The result is:
|CUSTOMER_ID|PAYMENT_DATE |PAYMENT_ID|AMOUNT| |-----------|-----------------------|----------|------| |72 |2005-08-19 12:53:53.000|1963 |0.99 | |152 |2005-08-21 03:01:01.000|4154 |0.99 | |207 |2005-07-09 13:56:56.000|5609 |0.99 | |244 |2005-08-21 09:31:44.000|6617 |0.99 | |244 |2005-08-21 19:39:43.000|6618 |0.99 | |252 |2005-07-28 02:44:25.000|6800 |0.99 | |377 |2005-07-07 12:24:37.000|10211 |0.99 | |425 |2005-08-01 12:37:46.000|11499 |0.99 | |511 |2005-07-11 18:50:55.000|13769 |0.99 |
If you’re in for a thrill, try modifying my query to return the usual triple rows that form a group, just like in the MATCH_RECOGNIZE example, and leave your solution in the comments. It’s definitely doable!
Partitioned tables
At least Oracle and PostgreSQL support table partitioning on a storage level, probably others, too. The feature helps tame your storage trouble by separating data into separate physical tables, while transparently pretending you have a single logical table in your application, and by introducing other kinds of trouble.
The typical example is partitioning data sets by date ranges, e.g. that’s what’s documented in PostgreSQL.
CREATE TABLE payment (
customer_id int not null,
amount numeric not null,
payment_date date not null
)
PARTITION BY RANGE (payment_date);
Now, we cannot use this table yet, because it only exists logically. It doesn’t know yet how to store data physically:
INSERT INTO payment (customer_id, amount, payment_date)
VALUES (1, 10, DATE '2000-01-01');
This produces:
SQL Error [23514]: ERROR: no partition of relation “payment” found for row
Detail: Partition key of the failing row contains (payment_date) = (2000-01-01).
So, let’s create some physical storage for a certain date range, e.g.:
CREATE TABLE payment_2000
PARTITION OF payment
FOR VALUES FROM (DATE '2000-01-01') TO (DATE '2000-12-31');
Now, the insert works. This interpretation of PARTITION again matches the window function one, where we partition our data set into subsets, which are clearly separated without overlaps.
Weird one: Outer Join Partitions
The next partitioning feature is part of the SQL standard, but I’ve only seen it being implemented in Oracle, so far, which has had it forever: partitioned outer joins. They’re not trivial to explain, and regrettably, their partitions have nothing to do with the window partitions. They’re more like CROSS JOIN syntax sugar (or vinegar, depending on your tastes).
Think about them this way, you can use partitioned outer joins to fill gaps in otherwise sparse data. Let’s look at an example:
SELECT
f.film_id,
f.title,
c.category_id,
c.name,
count(*) OVER ()
FROM film f
LEFT OUTER JOIN film_category fc
ON f.film_id = fc.film_id
LEFT OUTER JOIN category c
ON fc.category_id = c.category_id
ORDER BY f.film_id, c.category_id
This query produces the category per film. If a category does not appear with a film, there’s no record in the result:
|FILM_ID|TITLE |CATEGORY_ID|NAME |COUNT(*)OVER()| |-------|----------------|-----------|-----------|--------------| |1 |ACADEMY DINOSAUR|6 |Documentary|1000 | |2 |ACE GOLDFINGER |11 |Horror |1000 | |3 |ADAPTATION HOLES|6 |Documentary|1000 | |4 |AFFAIR PREJUDICE|11 |Horror |1000 | |5 |AFRICAN EGG |8 |Family |1000 | |6 |AGENT TRUMAN |9 |Foreign |1000 | |7 |AIRPLANE SIERRA |5 |Comedy |1000 | |8 |AIRPORT POLLOCK |11 |Horror |1000 | |9 |ALABAMA DEVIL |11 |Horror |1000 | |10 |ALADDIN CALENDAR|15 |Sports |1000 |
As you can see, we have 1000 films, and because the Sakila database is so boring, every film only has 1 category, even if the many-to-many relationship would allow for more than one assignment.
What happens if we add a PARTITION BY clause to one of the outer joins?
SELECT
f.film_id,
f.title,
c.category_id,
c.name,
count(*) OVER ()
FROM film f
LEFT OUTER JOIN film_category fc
ON f.film_id = fc.film_id
LEFT OUTER JOIN category c
PARTITION BY (c.category_id) -- Magic here
ON fc.category_id = c.category_id
ORDER BY f.film_id, c.category_id
I won’t show the entire result, but as you can see with the window function result, we now have 16000 rows in total, not 1000. That’s because we have 1000 films x 16 categories, so a cross product with blank category names (but not blank category IDs) in case there’s no match, if you will:
|FILM_ID|TITLE |CATEGORY_ID|NAME |COUNT(*)OVER()| |-------|----------------|-----------|-----------|--------------| |1 |ACADEMY DINOSAUR|1 | |16000 | |1 |ACADEMY DINOSAUR|2 | |16000 | |1 |ACADEMY DINOSAUR|3 | |16000 | |1 |ACADEMY DINOSAUR|4 | |16000 | |1 |ACADEMY DINOSAUR|5 | |16000 | |1 |ACADEMY DINOSAUR|6 |Documentary|16000 | |1 |ACADEMY DINOSAUR|7 | |16000 | |1 |ACADEMY DINOSAUR|8 | |16000 | |1 |ACADEMY DINOSAUR|9 | |16000 | |1 |ACADEMY DINOSAUR|10 | |16000 | |1 |ACADEMY DINOSAUR|11 | |16000 | |1 |ACADEMY DINOSAUR|12 | |16000 | |1 |ACADEMY DINOSAUR|13 | |16000 | |1 |ACADEMY DINOSAUR|14 | |16000 | |1 |ACADEMY DINOSAUR|15 | |16000 | |1 |ACADEMY DINOSAUR|16 | |16000 | |2 |ACE GOLDFINGER |1 | |16000 | |2 |ACE GOLDFINGER |2 | |16000 | |2 |ACE GOLDFINGER |3 | |16000 | |2 |ACE GOLDFINGER |4 | |16000 | |2 |ACE GOLDFINGER |5 | |16000 | |2 |ACE GOLDFINGER |6 | |16000 | |2 |ACE GOLDFINGER |7 | |16000 | |2 |ACE GOLDFINGER |8 | |16000 | |2 |ACE GOLDFINGER |9 | |16000 | |2 |ACE GOLDFINGER |10 | |16000 | |2 |ACE GOLDFINGER |11 |Horror |16000 | |2 |ACE GOLDFINGER |12 | |16000 | |2 |ACE GOLDFINGER |13 | |16000 | |2 |ACE GOLDFINGER |14 | |16000 | |2 |ACE GOLDFINGER |15 | |16000 | |2 |ACE GOLDFINGER |16 | |16000 |
In a way, this is useful whenever you want to create a report based on sparse data, and generate records for those gaps. A similar query without PARTITION BY would be on using CROSS JOIN
SELECT
f.film_id,
f.title,
c.category_id,
NVL2(fc.category_id, c.name, NULL) AS name,
count(*) OVER ()
FROM film f
CROSS JOIN category c
LEFT JOIN film_category fc
ON fc.film_id = f.film_id
AND fc.category_id = c.category_id
ORDER BY f.film_id, c.category_id;
I must say, I haven’t found these partitioned outer join very useful or intelligible in the past, and I’m not convinced that other RDBMS are really lacking some important feature here, despite this being standard SQL.
So far, jOOQ doesn’t emulate the feature in other RDBMS yet.
