Utilisez les prédicats MULTISET pour comparer des ensembles de données - Java, SQL et jOOQ.

Utilisez les prédicats MULTISET pour comparer des ensembles de données – Java, SQL et jOOQ.

Les questions qui pourraient être un peu plus difficiles à résoudre en utilisant le SQL ordinaire sont des questions du genre :

Quels films ont les mêmes acteurs qu’un film X donné ?

Comme toujours, nous utilisons la base de données sakila pour cet exemple. Quel serait un moyen possible de résoudre ce problème avec SQL (par exemple, PostgreSQL, pour être précis) ? La requête suivante donne un aperçu des acteurs par film :

SELECT
  film_id,
  array_agg(actor_id ORDER BY actor_id) actors
FROM film_actor
GROUP BY film_id

Il produit quelque chose comme ceci :

|film_id|actors                            |
|-------|----------------------------------|
|1      |{1,10,20,30,40,53,108,162,188,198}|
|2      |{19,85,90,160}                    |
|3      |{2,19,24,64,123}                  |
|4      |{41,81,88,147,162}                |
|5      |{51,59,103,181,200}               |
|6      |{21,23,62,108,137,169,197}        |
|...    |...                               |

Notez qu’en SQL, les tableaux se comportent comme des listes, c’est-à-dire qu’ils conservent leur ordre, donc l’ordre explicite du tableau est important pour pouvoir comparer les acteurs entre eux. Maintenant, nous voulons trouver tous les films qui partagent le même jeu d’acteurs, à partir de ce qui précède :

WITH t AS (
  -- Previous query
  SELECT
    film_id,
    array_agg(actor_id ORDER BY actor_id) actors
  FROM film_actor
  GROUP BY film_id
)
SELECT 
  array_agg(film_id ORDER BY film_id) AS films,
  actors
FROM t
GROUP BY actors
ORDER BY count(*) DESC, films

Le résultat est maintenant :

|films   |actors                            |
|--------|----------------------------------|
|{97,556}|{65}                              |
|{1}     |{1,10,20,30,40,53,108,162,188,198}|
|{2}     |{19,85,90,160}                    |
|{3}     |{2,19,24,64,123}                  |
|{4}     |{41,81,88,147,162}                |
|{5}     |{51,59,103,181,200}               |
|{6}     |{21,23,62,108,137,169,197}        |
|...     |...                               |

Ainsi, comme nous pouvons le voir, il n’y a que 2 films qui partagent le même ensemble d’acteurs, et ces films sont FILM_ID IN (97, 556). (La base de données Sakila est un peu ennuyeuse car les ensembles de données sont générés).

Utilisation des comparaisons MULTISET

Bien que ce qui précède soit déjà assez cool, dans cet article, j’aimerais présenter une fonctionnalité moins connue du jOOQ 3.15 MULTISET support, à savoir le fait qu’ils peuvent être comparés entre eux.

Et comme c’est la nature de la norme SQL MULTISETl’ordre n’est pas pertinent, nous n’avons donc pas à ajouter d’éléments explicites ORDER BY clause pour une telle comparaison. En fait, ce n’est pas 100% hors de propos. Toi peut commander un MULTISET à des fins de projection, la commande sera donc maintenue par jOOQ. Mais lorsque vous les utilisez dans des prédicats, jOOQ remplacera votre ORDER BY clause.

En utilisant jOOQ, on peut écrire :

ctx.select(FILM.FILM_ID, FILM.TITLE)
   .from(FILM)
   .where(
       multiset(
           select(FILM_ACTOR.ACTOR_ID)
           .from(FILM_ACTOR)
           .where(FILM_ACTOR.FILM_ID.eq(FILM.FILM_ID))
       ).eq(multiset(
           select(FILM_ACTOR.ACTOR_ID)
           .from(FILM_ACTOR)
           .where(FILM_ACTOR.FILM_ID.eq(97L))
       ))
   )
   .orderBy(FILM_ID)
   .fetch();

C’est un peu moins efficace qu’une requête du formulaire précédent car il accède au FILM_ACTOR table de deux sous-requêtes, même si une seule d’entre elles est corrélée. Utilisation de la valeur par défaut JSONB émulation, la requête suivante est générée :

SELECT film.film_id, film.title
FROM film
WHERE (
  SELECT coalesce(
    jsonb_agg(jsonb_build_array(v0) ORDER BY t.v0),
    jsonb_build_array()
  )
  FROM (
    SELECT film_actor.actor_id AS v0
    FROM film_actor
    WHERE film_actor.film_id = film.film_id
  ) AS t
) = (
  SELECT coalesce(
    jsonb_agg(jsonb_build_array(v0) ORDER BY t.v0),
    jsonb_build_array()
  )
  FROM (
    SELECT film_actor.actor_id AS v0
    FROM film_actor
    WHERE film_actor.film_id = 97
  ) AS t
)
ORDER BY film.film_id

j’ai promis que non ORDER BY clause était nécessaire pour MULTISET, et cela est toujours vrai pour le code jOOQ. Cependant, dans les coulisses, jOOQ doit ordonner les tableaux JSON par leur contenu pour s’assurer que deux MULTISET les valeurs sont les mêmes, quel que soit leur ordre.

Le résultat est les deux mêmes identifiants que le résultat précédent :

+-------+--------------+
|film_id|title         |
+-------+--------------+
|     97|BRIDE INTRIGUE|
|    556|MALTESE HOPE  |
+-------+--------------+

Comparer MULTISET_AGG, à la place

Si vous préférez utiliser des jointures et GROUP BY générer l’acteur du film MULTISET, vous pouvez également le faire avec jOOQ. Cette fois, nous utilisons :

Jointures implicites pour simplifier l’accès aux FILM.TITLE depuis FILM_ACTOR
UN MULTISET prédicat dans le HAVING clause, en utilisant MULTISET_AGG

Voici la version jOOQ :

ctx.select(FILM_ACTOR.FILM_ID, FILM_ACTOR.film().TITLE)
   .from(FILM_ACTOR)
   .groupBy(FILM_ACTOR.FILM_ID, FILM_ACTOR.film().TITLE)
   .having(multisetAgg(FILM_ACTOR.ACTOR_ID).eq(multiset(
        select(FILM_ACTOR.ACTOR_ID)
        .from(FILM_ACTOR)
        .where(FILM_ACTOR.FILM_ID.eq(97L))
    )))
   .orderBy(FILM_ACTOR.FILM_ID)
   .fetch();

Le support SQL généré ressemble à ceci :

SELECT film_actor.film_id, alias_75379701.title
FROM film_actor
  JOIN film AS alias_75379701
    ON film_actor.film_id = alias_75379701.film_id
GROUP BY film_actor.film_id, alias_75379701.title
HAVING jsonb_agg(
  jsonb_build_array(film_actor.actor_id) ORDER BY film_actor.actor_id
) = (
  SELECT coalesce(
    jsonb_agg(jsonb_build_array(v0) ORDER BY t.v0),
    jsonb_build_array()
  )
  FROM (
    SELECT film_actor.actor_id AS v0
    FROM film_actor
    WHERE film_actor.film_id = 97
  ) AS t
)
ORDER BY film_actor.film_id

Remarquez comment la jointure implicite est développée automatiquement, alors que la HAVING le prédicat utilise à nouveau l’habituel JSONB émulation pour MULTISET et MULTISET_AGG.

Alternatives

Dans les exemples ci-dessus, nous avons comparé MULTISET expressions qui projettent des colonnes uniques, en d’autres termes, Result<Record1<Long>> types de collection imbriqués. Rien ne vous empêche d’ajouter plus de colonnes à l’équation. jOOQ s’assurera toujours que votre type de requête vérifie et que le SQL généré est correct.

Une alternative à l’utilisation MULTISET utiliserait ARRAY_AGG et ARRAY (maintenant il faut ORDER BY explicitement, encore une fois). Avec jOOQ :

ctx.select(FILM_ACTOR.FILM_ID, FILM_ACTOR.film().TITLE)
   .from(FILM_ACTOR)
   .groupBy(FILM_ACTOR.FILM_ID, FILM_ACTOR.film().TITLE)
   .having(arrayAgg(FILM_ACTOR.ACTOR_ID)
        .orderBy(FILM_ACTOR.ACTOR_ID).eq(array(
            select(FILM_ACTOR.ACTOR_ID)
            .from(FILM_ACTOR)
            .where(FILM_ACTOR.FILM_ID.eq(97L))
            .orderBy(FILM_ACTOR.ACTOR_ID)
    )))
    .orderBy(FILM_ACTOR.FILM_ID)
    .fetch();

Avec SQL :

SELECT film_actor.film_id, film.title
FROM film_actor
  JOIN film
    ON film_actor.film_id = film.film_id
GROUP BY film_actor.film_id, film.title
HAVING array_agg(film_actor.actor_id ORDER BY film_actor.actor_id) = 
  ARRAY (
    SELECT film_actor.actor_id
    FROM film_actor
    WHERE film_actor.film_id = 97
    ORDER BY film_actor.actor_id
  )
ORDER BY film_actor.film_id

InMy.Live