Статьи

Драгоценный камень SQL, о котором вы еще не знали: агрегатная функция EVERY ()

Декабрь 5, 2018

Мы только что добавили поддержку агрегатной функции EVERY() ( # 1391 ) в jOOQ и хотели бы воспользоваться возможностью, чтобы сообщить вам об этом истинном геме SQL, который может пригодиться EVERY(now and then) (каламбур предназначен ).

Давайте предположим, что в нашей таблице четыре книги:

1
2
3
4
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');

Теперь вопрос:

EVERY() ID меньше 10?

Мы спросим:

1
2
SELECT EVERY(id < 10)
FROM book

И ответ:

1
2
3
every
-----
true

EVERY() ли каждая EVERY() книга для каждого автора буквой «а»?

Мы спросим:

1
2
3
SELECT author_id, EVERY(title LIKE '%a')
FROM book
GROUP BY author_id

И ответ:

1
2
3
4
author_id   every
-----------------
1           false
2           true

Замечательный!

Как и со всеми агрегатными функциями, мы можем даже использовать их как оконную функцию !

1
2
3
4
SELECT
  book.*,
  EVERY(title LIKE '%a') OVER (PARTITION BY author_id)
FROM book

Который будет производить:

1
2
3
4
5
6
id  author_id   title          every
------------------------------------
1   1           1984           false
2   1           Animal Farm    false
3   2           O Alquimista   true
4   2           Brida          true

Кто поддерживает EVERY()

Ну, стандарт SQL имеет это:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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

И, конечно же, PostgreSQL !

Но если ваша база данных не PostgreSQL, не беспокойтесь. EVERY() можно эмулировать в базе данных EVERY() с помощью выражений SUM() и CASE . Вот как эмулировать первый запрос:

1
2
3
4
5
6
7
8
-- 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;

Или как оконные функции

01
02
03
04
05
06
07
08
09
10
11
12
13
-- 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;

И, как всегда в этом блоге, мы рады заключить, что грядущий jOOQ 3.6 теперь будет обрабатывать EVERY(emulation) для вас, поэтому вы можете написать:

1
2
3
4
5
6
DSL.using(configuration)
   .select(BOOK.fields())
   .select(every(BOOK.TITLE.like("%a"))
           .over(partitionBy(BOOK.AUTHOR_ID)))
   .from(BOOK)
   .fetch();

Веселитесь с этой новой функцией!

jooq-The-лучший способ к записи-SQL-в-Java-маленький

Ссылка: Истинный драгоценный камень SQL, о котором вы еще не знали: агрегатная функция EVERY () от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ .
40
Share: