A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function

Posted on by lukaseder

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

And, of course PostgreSQL!

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!

jOOQ: the best way to write SQL in Java

Published by lukaseder

I made jOOQ

11 thoughts on “A True SQL Gem You Didn’t Know Yet: The EVERY() Aggregate Function”

      2. I ended up implementing this as an improvement:

        SELECT 
  min(CASE WHEN id < 10 THEN 1 ELSE 0 END) AS bool_and, -- a.k.a. EVERY()
  max(CASE WHEN id < 10 THEN 1 ELSE 0 END) AS bool_or
FROM book;

        I’ll also fix the article, because the original solution was not correctly aggregating empty sets to NULL

        Reply

    1. 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.

      Reply

  3. 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.

    Reply

    1. 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)

      In comparisons of boolean values, True is greater than False

      Can’t say I really like this, but it does make sense.

      Reply

Leave a Reply