A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function
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 CASE SUM(CASE WHEN id < 10 THEN 0 ELSE 1 END) WHEN 0 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.*, CASE SUM(CASE WHEN title LIKE '%a' THEN 0 ELSE 1 END) OVER(PARTITION BY author_id) WHEN 0 THEN 1 ELSE 0 END FROM book;
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!
Reference: | A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog. |