Pagination des JOIN via jOOQ et DENSE

Travailler avec DENSE_RANK()

DENSE_RANK() est une fonction de fenêtre qui attribue un rang à chaque ligne d’une partition ou d’un ensemble de résultats sans écart dans les valeurs de classement. Un exemple simple est montré ici :

Supposons que nous voulions classer les employés (EMPLOYEE) dans les bureaux (OFFICE) par leur salaire (EMPLOYEE.SALARY). Exprimer cela via jOOQ et DENSE_RANK() peut être fait comme suit :

ctx.select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME, EMPLOYEE.SALARY,
           OFFICE.CITY, OFFICE.COUNTRY,
           OFFICE.OFFICE_CODE, 
           denseRank().over().partitionBy(OFFICE.OFFICE_CODE)
              .orderBy(EMPLOYEE.SALARY.desc()).as("salary_rank"))
   .from(EMPLOYEE)
   .innerJoin(OFFICE)
   .on(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE))
   .fetch();

Un fragment de sortie ressemble à ceci (notez que les employés ayant le même salaire obtiennent le même rang) :

Ensuite, utilisons DENSE_RANK() pour sélectionner le salaire le plus élevé de chaque bureau, y compris les doublons. Cette fois, utilisons le QUALIFY clause également. Le code est illustré dans l’extrait suivant :

select(EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME,
       EMPLOYEE.SALARY, OFFICE.CITY, OFFICE.COUNTRY,
       OFFICE.OFFICE_CODE)
  .from(EMPLOYEE)
  .innerJoin(OFFICE)
  .on(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE))
  .qualify(denseRank().over().partitionBy(OFFICE.OFFICE_CODE)
           .orderBy(EMPLOYEE.SALARY.desc()).eq(1))
  .fetch();

Avant d’aller plus loin, voici une belle lecture intitulée « La différence entre ROW_NUMBER(), RANK() et DENSE_RANK() ». Vous pouvez consulter ces exemples dans le code groupé DenseRank.

Pagination des JOIN via DENSE_RANK()

Supposons que nous voulions paginer les bureaux (OFFICE) avec des employés (EMPLOYEE). Si nous appliquons un offset classique ou une pagination keyset au JOIN entre OFFICE et EMPLOYEE, le résultat est susceptible d’être tronqué. Par conséquent, un bureau peut être récupéré avec seulement un sous-ensemble de ses employés. Par exemple, alors que nous pensons qu’une page de résultats de taille 3 contient trois bureaux avec tous leurs employés, nous obtenons à la place un seul bureau avec trois employés (même si ce bureau a plus d’employés). La figure suivante révèle ce que nous attendons par rapport à ce que nous obtenons d’une page de taille 3 (bureaux) :

Pour obtenir un jeu de résultats comme celui du côté gauche de la figure précédente, on peut s’appuyer sur le DENSE_RANK() fonction de fenêtre, qui attribue un numéro séquentiel à différentes valeurs de a au sein de chaque groupe bcomme indiqué dans la requête jOOQ suivante :

Map<Office, List<Employee>> result = ctx.select().from(
  select(OFFICE.OFFICE_CODE, OFFICE...,
         EMPLOYEE.FIRST_NAME, EMPLOYEE...,
         denseRank().over().orderBy(OFFICE.OFFICE_CODE, OFFICE.CITY).as("rank"))
  .from(OFFICE)
  .join(EMPLOYEE)
  .on(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE)).asTable("t"))
  .where(field(name("t", "rank")).between(start, end))
  .fetchGroups(Office.class, Employee.class);

Le start et end les variables représentent la gamme de bureaux définie via DENSE_RANK(). La figure suivante devrait clarifier cet aspect où start = 1 et end = 3 (la page suivante des trois bureaux est entre start = 4 et end = 6):

Voici une version plus compacte de la requête précédente, utilisant la QUALIFY clause:

Map<Office, List<Employee>> result = 
  ctx.select(OFFICE.OFFICE_CODE, OFFICE...,
             EMPLOYEE.FIRST_NAME, EMPLOYEE...)
  .from(OFFICE)
  .join(EMPLOYEE)
  .on(OFFICE.OFFICE_CODE.eq(EMPLOYEE.OFFICE_CODE))
  .qualify(denseRank().over().orderBy(OFFICE.OFFICE_CODE, OFFICE.CITY)
           .between(start, end))
  .fetchGroups(Office.class, Employee.class);

Vous pouvez consulter l’exemple complet nommé DenseRankPagination pour MySQL. Le revenu Map<Office, List<Employee>> est sérialisé en JSON via un contrôleur Spring Boot classique.

Il y a des chances que vous ne connaissiez pas QUALIFY clause, voici donc un bref aperçu.

La clause QUALIFICATION

Certaines bases de données (par exemple, Snowflake) prennent en charge une clause nommée QUALIFY. Via cette clause, nous pouvons filtrer (appliquer un prédicat) les résultats des fonctions de fenêtre. Principalement, un SELECT … QUALIFY La clause est évaluée après le calcul des fonctions de fenêtre, donc après les fonctions de fenêtre (étape 6) et avant DISTINCT (étape 8) :

La syntaxe de QUALIFY est QUALIFY <predicate>et dans la capture d’écran suivante, vous pouvez voir comment cela fait la différence (cette requête renvoie tous les 10 produits de la PRODUCT tableau via le ROW_NUMBER() fonction fenêtre) :

En utilisant le QUALIFY clause, nous éliminons la sous-requête et le code est moins verbeux. Même si cette clause a un support natif médiocre parmi les fournisseurs de bases de données, jOOQ l’émule pour tous les dialectes pris en charge. Cool, non ? !

Explorez plus loin dans mon livre, Masterclass jOOQ.

Pagination des JOIN via jOOQ et DENSE_RANK()

Leave A Reply Cancel Reply