We’ve just added support for the EVERY() aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy EVERY(now and then) (pun intended).
Let’s assume we have four books in our table:
INSERT INTO book VALUES (1, 1, '1984');
INSERT INTO book VALUES (2, 1, 'Animal Farm');
INSERT INTO book VALUES (3, 2, 'O Alquimista');
INSERT INTO book VALUES (4, 2, 'Brida');
Now the question is:
Is
EVERY()ID lower than 10?
We’ll ask:
SELECT EVERY(id < 10)
FROM book
And the answer is:
every ----- true
Does
EVERY()book for each author end with the letter ‘a’?
We’ll ask:
SELECT author_id, EVERY(title LIKE '%a')
FROM book
GROUP BY author_id
And the answer is:
author_id every ----------------- 1 false 2 true
Wonderful!
As with all aggregate functions, we can even use them as a window function!
SELECT
book.*,
EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
FROM book
Which will produce
id author_id title every ------------------------------------ 1 1 1984 false 2 1 Animal Farm false 3 2 O Alquimista true 4 2 Brida true
Who supports EVERY
Well, the SQL standard has it:
10.9 <aggregate function>
<aggregate function> ::=
COUNT <left paren> <asterisk> <right paren> [ <filter clause> ]
| <general set function> [ <filter clause> ]
| <binary set function> [ <filter clause> ]
| <ordered set function> [ <filter clause> ]
| <array aggregate function> [ <filter clause> ]
<general set function> ::=
<set function type> <left paren> [ <set quantifier> ]
<value expression> <right paren>
<set function type> ::=
<computational operation>
<computational operation> ::=
AVG
| MAX
| MIN
| SUM
| EVERY <-- yes, here! EVERY!
| ANY
| SOME
| COUNT
| STDDEV_POP
| STDDEV_SAMP
| VAR_SAMP
| VAR_POP
| COLLECT
| FUSION
| INTERSECTION
But if your database is not PostgreSQL, don’t worry. EVERY() can be emulated on EVERY() database using SUM() and CASE expressions. Here’s how to emulate the first query:
-- SELECT EVERY(id < 10)
-- FROM book
SELECT MIN(CASE WHEN id < 10 THEN 1 ELSE 0 END)
FROM book;
Or as window functions
-- SELECT
-- book.*,
-- EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
-- FROM book
SELECT
book.*,
MIN(CASE WHEN title LIKE '%a' THEN 1 ELSE 0 END)
OVER(PARTITION BY author_id)
FROM book;
For a more concise emulation, see Stew Ashton’s comments
And, as always on this blog, we’re happy to conclude that the upcoming jOOQ 3.6 will now handle EVERY(emulation) for you, so you can write:
DSL.using(configuration)
.select(BOOK.fields())
.select(every(BOOK.TITLE.like("%a"))
.over(partitionBy(BOOK.AUTHOR_ID)))
.from(BOOK)
.fetch();
Have fun with this new function!
A more concise emulation:
Sorry, I meant the following:
That’s a cool idea, thanks for sharing, Stew!
I ended up implementing this as an improvement:
I’ll also fix the article, because the original solution was not correctly aggregating empty sets to NULL
curious about what those COLLECT and FUSION are…
The standard says “COLLECT (X) is equivalent to FUSION (MULTISET [X]).”.
Unfortunately, this is the definition of FUSION: If FUSION is specified, then the result is the multiset M such that for each value V in the element type of DT, including the null value, the number of elements of M that are identical to V is the sum of the number of identical copies of V in the multisets that are the values of the column in each row of TXA.
So, we aren’t really any smarter ;-)
On a more serious note. These functions aggregate groups into nested collections (“multisets”), similar to what ARRAY_AGG does with arrays.
So, we can forgive those who didn’t take the time to implement them ;-)
There are much more forgivable omissions from the SQL standard by the implementations… ;)
If the DB allows MIN and MAX on BOOLEAN values (with FALSE < TRUE), then EVERY is equivalent to MIN, and SOME/ANY are equivalent to MAX.
There’s no need for a CASE expression to convert BOOLEAN to INTEGER.
SELECT
min(id < 10) AS bool_and, — a.k.a. EVERY()
max(id < 10) AS bool_or
FROM book;
This works on hsqldb and Calcite. I haven’t tried any other DBs.
That’s clever. And I’ll be damned, I was going to point out that no way this is SQL standards compliant, but it is. 8.2 <comparison predicate> General Rules 8)
Can’t say I really like this, but it does make sense.
Truth over falsehoods >>>