Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
4 décembre 2013 3 04 /12 /décembre /2013 16:49

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...

 

Partager cet article

Published by Pacman - dans SQL cosmétique
commenter cet article

commentaires

Laurent Schneider 05/12/2013 14:16

Ils sont partis sur le principe que ceux qui connaissent les bits connaissance aussi les équivalences suivante :)


SQL> WITH n AS (SELECT 131 x, 102 y FROM DUAL),
hex
AS (SELECT x, y, SUBSTR (UTL_RAW.CAST_from_binary_integer (x), 7) xx,
SUBSTR (UTL_RAW.CAST_from_binary_integer (y), 7) yy
FROM n)
SELECT x,
'0x'||xx "HEX(X)",
y,
'0x'||yy "HEX(Y)",
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_xor (xx, yy)) x_xor_y,
x+y-bitand(x,y)*2,
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_and (xx, yy)) x_and_y,
bitand(x,y),
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_or (xx, yy)) x_or_y,
x+y-bitand(x,y),
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_complement (xx)) x_compl,
255-x,
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_complement (yy)) y_compl,
255-y
FROM hex

X HEX(X) Y HEX(Y) X_XOR_Y X+Y-BITAND(X,Y)*2 X_AND_Y BITAND(X,Y) X_OR_Y X+Y-BITAND(X,Y) X_COMPL 255-X Y_COMPL 255-Y
---- ------ ---- ------ -------- ----------------- -------- ----------- -------- --------------- -------- ---------- -------- ----------
131 0x83 102 0x66 229 229 2 2 231 231 124 124 153 153

Pacman 05/12/2013 14:58



Oui c'est certain, et on pourrait également continuer de noter {{a}, {a,b}} à la place de (a,b) :)


Ca me rappelle aussi l'exercice que j'avais fait pour supprimer les CASE :)


http://pacmann.over-blog.com/article-35540693.html



Laurent Schneider 05/12/2013 11:26

On peut aussi utiliser XOR, AND et complément


SQL> WITH n AS (SELECT 131 x, 102 y FROM DUAL),
hex
AS (SELECT x, y, SUBSTR (UTL_RAW.CAST_from_binary_integer (x), 7) xx,
SUBSTR (UTL_RAW.CAST_from_binary_integer (y), 7) yy
FROM n)
SELECT x,
y,
'0x'||xx "HEX(X)",
'0x'||yy "HEX(Y)",
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_xor (xx, yy)) x_xor_y,
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_and (xx, yy)) x_and_y,
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_or (xx, yy)) x_or_y,
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_complement (xx)) x_compl,
UTL_RAW.CAST_TO_BINARY_INTEGER (UTL_RAW.bit_complement (yy)) y_compl
FROM hex

X Y HEX(X) HEX(Y) X_XOR_Y X_AND_Y X_OR_Y X_COMPL Y_COMPL
---- ---- ------ ------ -------- -------- -------- -------- --------
131 102 0x83 0x66 229 2 231 124 153


Et pour ceux qui aiment le XOR booléen, il y a en même une qui n'est pas documentée :)

SQL> exec if xor(true,false) then raise_application_error(-20001,'');end if
ORA-20001:

Pacman 05/12/2013 11:58



Ah ouais, pas mal UTL_RAW... mais un peu lourd quand même.


Dommage qu'ils n'aient pas implémenté les autres fonctions comme ils l'ont fait pour bitand !