Trucs et Astuces SQL

– CLAUSE ORDER BY STATIQUE
– Pivot SQL (V5R4)
– ROLLUP, CUBE et GROUPING SETS (V6R1)
– Les nouvelles vues SYS___STAT (V5R4 & V6R1)

• CLAUSE ORDER BY STATIQUE

Vous faites sans aucun doute du SQL dans vos programmes. On vous a toujours conseillé de faire du SQL Statique versus Dynamique pour une question de performances. Il est difficile de se passer du Dynamique pour fabriquer notre clause WHERE à la volée et généralement de même pour la clause ORDER BY. Bob COSY nous propose de conditionner la clause ORDER BY et ainsi conserver notre requête en Statique. Voir figure 1

• Pivot SQL (V5R4)

Un internaute demande s’il est possible dans DB2 à partir de la table ci-dessous, de faire une requête afin d’obtenir plusieurs lignes dans une même colonne, c’est-à-dire un pivot. Voir figure 2.

Les Common Table [removed]V5R3), les fonctions OLAP (V5R4) ainsi que la récursivité (V5R4) toutes trois combinées nous permettent de résoudre la requête.

Commençons pas à pas, je demande d’abord un niveau de classement sur FRUIT avec une rupture par NAME en faisant appel à la fonction OLAP Row_Number() . Voir figure 3.

Puis je rajoute de la récursivité en prenant au départ les lignes de niveau 1 et en y ajoutant le niveau supérieur tout en concaténant les libellés. Voir figure 4.

Les deux premières lignes  résultantes proviennent de la requête avec la clause « Where Niv = 1 », les autres sont issues de la récursivité.

Seulement voilà, du résultat précédent, seuls les niveaux les plus hauts m’intéressent pour chaque rupture de la colonne NAME. Voir figure 5.

Je finalise ma requête en récupérant le MAX() du niveau de chacun et en jointant le résultat pour obtenir mon libellé. (nb :  un max() de libelle aurait aussi marché).
Une autre solution aurait été la création d’une UDF.

• ROLLUP, CUBE et GROUPING SETS (V6R1)

La V6R1 nous enrichit de quelques fonctions OLAP supplémentaires très intéressantes. Nous utilisons souvent les fonctions d’agrégation comme SUM, COUNT, MIN/MAX, AVG etc…afin d’extraire des totaux de nos données. Mais quid des sous-totaux ? Ces fonctions permettent justement de calculer des sous-totaux de lignes agrégées avec la clause GROUP BY.

ROLLUP

ROLLUP  permet de calculer les sous-totaux et totaux pour les colonnes que vous lui présentez selon la hiérarchie que vous lui demandez. Voir figure 6.

Les lignes marquées d’un représentent les sous-totaux et totaux automatiquement ajoutés par ROLLUP par rapport à un simple GROUP BY, soit un total des effectifs par service et sexe quel que soit le job, un total par service et un total général.

Pour résumer un GROUP BY ROLLUP(A, B, C) calcule les totaux suivants :(A, B , C) > Idem GROUP BY
(A, B, null)
(A, null, null)
(null, null, null) > Total Général

CUBE

CUBE  va encore plus loin que ROLLUP, car il fonctionne d’une façon multidimensionnelle et  renvoie toutes les combinaisons possibles de sous-totaux.  Voir figure 7.

Les lignes présentées ici sont ajoutées au résultat du ROLLUP, on peut y voir le total des jobs quels que soient le sexe et/ou le département, le nombre de femmes ou d’hommes de l’entreprise etc… en résumé tous les totaux et sous-totaux croisés.
(A, B , C)
(A, B, null)
(A, null, null)
(null, null, null)
(null, B, C)
(null, B, null)
(A, null, C)
(null, null, C)

GROUPING SETS

Si l’on veut soi-même choisir les sous-totaux sur les agrégats plutôt que d’obtenir tous les sous-totaux croisés comme peut l’offrir le puissant CUBE, dans ce cas il vous faudra utiliser la fonction OLAP Grouping Sets. Voir figure 8.

Attention, remarquez bien, que nous avons obtenu que les sous-totaux demandés et pas les lignes provenant d’un GROUP BY classique.
Pour ce faire il aurait fallu avoir la clause :
GROUPING SETS (
(DEPTNAME, SEX, JOB),
(DEPTNAME, SEX),
(SEX, JOB) )

On peut combiner l’ensemble de ces fonctions OLAP à la suite, du genre :
GROUP BY GROUPING SETS ((A, B), B), CUBE (C, D), ROLLUP (E, A)
Ce n’est pas interdit, mais cela peut pénaliser vos performances.
Le HAVING fonctionne de la même manière et s’applique à l’ensemble de ces fonctions.

Nouvelle fonction de colonne GROUPING()
Que vous utilisiez l’agrégat GROUP BY, ou l’une de ces trois nouvelles fonctions OLAP, vous pouvez tester si un champ possède la valeur Null (ce qui impliquerait un sous-total ou total) avec la fonction de colonne GROUPING(Nom du Champ). Exemple :
Select A, B, Count(*) as Nb, Grouping(A)
FROM MaTable Group BY CUBE(A, B)
La fonction renverra la valeur 1 si le champ A est Null, ou 0 dans le cas contraire.

Select A, B, Count(*) as Nb, Grouping(A) + Grouping(B) as Niv
FROM MaTable Group BY CUBE(A, B)
Dans l’exemple ci-dessus, si :
–    Niv = 0 > il s’agit d’une ligne détail
–    Niv = 1 > il s’agit d’un sous-total
–    Niv = 2 > il s’agit du total général

• Les nouvelles vues SYS___STAT (V5R4 & V6R1)

Le catalogue base de données « IBM i » c’est-à-dire les différentes références croisées de DB2 (QSYS2/SYS*), est enrichi à chaque nouvelle version. La V6R1 n’est pas en reste avec la livraison de vues SQL statistiques très riches en information dont certaines sont d’ores et déjà disponibles en V5R4 par PTF. Elles vous permettront d’obtenir des statistiques sur vos tables (fichiers) et leur contenu, index (logiques) ainsi que sur vos partitions (membres).