Ma grand-mère me disait toujours, "Pacman, n'oublie jamais le côté artistique du SQL, ni le côté obscur de la force".
Et là, force est de constater que depuis un moment, je m'y crois trop : j'ai oublié les valeurs essentielles dans lesquelles mes ancêtres et moi croyons, et dans lesquelles ma propre descendance devra croire : l'absurdité, l'inutilité, la créativité non applicable à la vraie vie.
On va donc aborder aujourd'hui : la division relationnelle avec l'opérateur BITAND !
Pour un vrai article sérieux voire boursoufflé sur la division relationnelle, cherchez sur le net.
Par exemple, l'article de SQLPro (vous savez, le mec qui, quand vous lui posez une question sur le forum developpez.net, commence par vous insulter, puis vous recommande d'acheter ses bouquins...), dont nous allons d'ailleurs reprendre le script de tests :
http://sqlpro.developpez.com/cours/divrelationnelle/
1) La division relationnelle
Il s'agit de repérer dans une table un ensemble de ligne.
C'est comme faire un filtre where, mais sur tout un groupe dont les lignes doivent matcher, partiellement ou complètement, un certain nombre de valeurs.
Ces valeurs cibles étant par exemple répertoriées dans une autre table.
2) Jeu de données
Comme dit, c'est repompé de chez SQLPro. Il s'agit de dépôts (identifiés par leur ville) qui servent des rayons.
Lesquels sont capables de déservir tous les rayons ?
/* table des rayons */
CREATE TABLE T_RAYON
(RAYON_RYN CHAR(16));
/* tables des entrepôts */
CREATE TABLE T_ENTREPOT
(VILLE_ETP CHAR(16),
RAYON_RYN CHAR(16));
/* alimentation de la table des rayons */
INSERT INTO T_RAYON (RAYON_RYN) VALUES ('frais');
INSERT INTO T_RAYON (RAYON_RYN) VALUES ('boisson');
INSERT INTO T_RAYON (RAYON_RYN) VALUES ('conserve');
INSERT INTO T_RAYON (RAYON_RYN) VALUES ('droguerie');
/* alimentation de la table des entrepots */
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'boisson');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'frais');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('PARIS', 'conserve');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'boisson');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'conserve');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('LYON', 'droguerie');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'boisson');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'frais');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'conserve');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('MARSEILLE', 'droguerie');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'boisson');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'frais');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('ANGER', 'droguerie');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'boisson');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'frais');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'conserve');
INSERT INTO T_ENTREPOT (VILLE_ETP, RAYON_RYN) VALUES ('TOULOUSE', 'droguerie');
/* modif de l'état initial */
INSERT INTO T_RAYON VALUES ('conserve');
3) La méthode qui perd
La représentation binaire, c'est comme la représentation "usuelle" décimale, sauf qu'au lieu d'avoir des nombres composés de chiffres de 0 à 9, on a des nombres composés de 0 et de 1.
Par exemple, si on prend le nombre décimal 153, c'est :
- En décimal 1 * 10^2 + 5 * 10^1 + 3 * 10^0 = 153 [decimal]
- En binaire 1 * 2^7 + 0 * 2^6 + 0 * 2^5 + 1 * 2^4 + 1 * 2^2 + 1 * 2^0 = 100111 [binaire]
Pour deux nombres donnés n1 et n2, bitand(n1, n2) est le nombre résultant de l'opération "pour chaque position, si le chiffre correspondant dans n1 et dans n2 vaut 1, on renvoie 1, sinon 0"
Par exemple,
bitand(153 [décimal], 15 [décimal])
= bitand (100111[binaire], 001111[binaire])
= 000111 [décimal]
= 7
Cette représentation colle parfaitement à une liste de flags : si on affecte à :
- frais la valeur 1
- boisson la valeur 2
- conserve la valeur 4
- droguerie la valeur 8
... et qu'on fait la somme, on obtient 15, qui s'écrit 1111 en binaire.
Si ensuite on somme ces valeurs pour chaque dépôt, on pourra chercher quels sont les dépôt de somme n2, tels que bitand(n2, 15) = 15.
En SQL, ça donne :
WITH t AS (
SELECT distinct rayon_ryn, power(2, dense_rank() over(order by rayon_ryn) - 1) flg
FROM t_rayon)
, u as (
SELECT sum(flg) sumflg
FROM t)
SELECT ville_etp
FROM t_entrepot a
JOIN t ON a.rayon_ryn = t.rayon_ryn
CROSS JOIN U
GROUP BY ville_etp, sumflg
HAVING bitand(sum(distinct t.flg), sumflg) = sumflg
Voilà voilà, ne pas appliquer ce genre de méthode au boulot, bien entendu...
