Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
1 septembre 2009 2 01 /09 /septembre /2009 14:17
Je ne sais pas trop pourquoi, mais je me sens toujours très fier quand j'arrive à éliminer tous mes CASE ... WHEN ... THEN ... END de mes expressions.
Surtout quand mon expression n'implique que des entiers relatifs !
Pour les petits malins, il ne s'agit pas de les remplacer par des DECODE, mais de se restreindre aux opérateurs "arithmétiques" usuels :
+, -, *, /, mod, partie entière, valeur absolue
Bien entendu, on voit tout de suite que "partie entière" et "valeur absolue" contiennent un CASE ... WHEN .. END, en quelques sortes : et c'est bien ce qu'on va tenter d'exploiter...
Avant de commencer, réglons la question de l'utilité : aucune. J'irais même plus loin :
N'APPLIQUEZ PAS CES METHODES AU TRAVAIL, VOUS SERIEZ LICENCIES POUR FAUTE PROFESSIONNELLE !

Afin d'arriver à une méthode générique de transformation du CASE, nous suivrons les étapes suivantes :
0) Périmètre et jeux de données
1) Le sélecteur simple : CASE x WHEN a THEN b ELSE 0 END
2) La condition non exacte : CASE WHEN x >= a THEN y ELSE 0 END
3) Composition des conditions unitaires : CASE WHEN x > a AND y > b THEN z ELSE 0 END
4) Branchement de diverses conditions : CASE WHEN Cond1 THEN y WHEN Cond2 THEN z ... END

0) Périmètre et jeux de données
Les valeurs "élémentaires" sont entières, éventuellement négatives
Les opérateurs permis autorisés sont : +, -, *, /, mod (modulo), trunc noté aussi [x], abs noté aussi |x|)
Les expressions de CASE que l'on transformera seront :
>=, <=, =, BETWEEN, IN, NOT IN. Avec les connecteurs AND, OR et NOT

Pour les jeux de données, nous créons la table contenant toutes les quadruplets formés de nombres entiers dans l'intervalle [-10, 10]

CREATE TABLE ME_MANQUE_UNE_CASE AS
WITH t AS (
SELECT 10 - level + 1 as nb FROM DUAL
CONNECT BY level <= 21)
select a.nb as a, b.nb as b, c.nb as c, d.nb as d
FROM t a
  CROSS JOIN t b
  CROSS JOIN t c
  CROSS JOIN t d


1) Le sélecteur simple : CASE x WHEN a THEN b ELSE 0 END
C'est la base de tout !
Comment distinguer la valeur "a" d'une variable "x" parmi toutes les autres valeurs ?
D'instinct, on remarque que l'on peut généraliser le problème par la translation :
x -> x - a
... ce qui revient à poser la question : quelle expression renvoit 1 quand x vaut 0, 0 sinon ?
(soit CASE x WHEN 0 THEN 1 ELSE 0 END)

La première idée, c'est de se dire que  1 / x est strictement compris entre 0 et 1, et on aimerait en prendre la partie entière supérieure.
Mais la division par 0, c'est généralement interdit.

Tout en restant dans cet esprit (ramener sur l'intervalle et prendre la partie entière pour générer une "discontinuité" en 0), on a envie de tenter :
x / (x + 1)

Car on cherche l'expression qui ne dépend que de x est qui lui est strictement supérieure. Cela ne marche pas très bien pour les nombres négatifs...
Et on en vient tout naturellement à :
f(x) = [1 - |x| / (|x| + 1)], qui renvoit 1 quand x vaut 0, 0 sinon


Et donc on réécrit :
f(x) = CASE x WHEN a THEN b ELSE 0 END
En :
f(x) = [1 - |x - a| / (|x - a| + 1)] * b


Petit test sur la table :

SQL> SELECT trunc(1 - abs(a - 5) / (abs(a - 5) + 1)) * 2 as nw,
  2     CASE a WHEN 5 THEN 2 ELSE 0 END as old,
  3     a.a
  4  FROM me_manque_une_case a
  5  WHERE a = 5 and b = 1 and c = 3 and d = 4
  6  /

        NW        OLD          A
---------- ---------- ----------
         2          2          5


2) La condition non exacte : CASE WHEN x >= a THEN y ELSE 0 END

A partir de maintenant, nous allons principalement nous ramener au cas basique étudié en 1)
La fonction créée se décompose tout naturellement en une fonction de sélection qui renvoit 1 ou 0, et une constante multiplicative.
Toujours la même démarche : essayer de ramener au cas le plus général en composant / substituant / translatant... puis résoudre !
x > a <=> x - a >= 0
Comment caractérise-t-on les nombres positifs ?
Ce sont tout ceux qui sont égaux à leur valeur absolue !
x >= 0 <=> x - |x| = 0

D'où la transformation finale :
f(x) = CASE WHEN x >= a THEN y ELSE 0 END
f(x) = x - a - |x-a|
f(x) = y * [1 - |x - a - |x-a|| / (|x - a - |x-a|| + 1)]

Petit exemple :

SQL> select * from (
  2  select 5 * trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1)) as reecr,
  3  CASE WHEN a >= 2 THEN 5 ELSE 0 END as orig
  4  , a.*, row_number() over (partition by a order by null) rk
  5  from me_manque_une_case a
  6  where a in (1, 3, -1)
  7  )
  8  where rk = 1
  9  /

     REECR       ORIG          A          B          C          D         RK
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          0         -1         10         10         10          1
         0          0          1         10         10         10          1
         5          5          3         10         10         10          1


Nous savons maintenant écrire pour une condition élémentaire la fonction de sélection f :
f(x) = 1 si cond
       0 sinon
Tout naturellement, la fonction g associée à NOT(cond) est :     
g(x) = 1 - f(x)
(Je ne vous fais pas l'affront de vous expliquer pourquoi)

3) Composition des conditions unitaires : CASE WHEN x > a AND y > b THEN z ELSE 0 END

L'ensemble des conditions logiques, prédicats est "structuré", "algébrique".
(Vous noterez que j'utilise tout le temps des doubles quotes pour pallier mon manque de précision dans le vocabulaire :))
Je veux dire par là que la composition de prédicats par l'intermédiaire d'opérateurs logiques constitue également des prédicats, et que réciproquement, d'une certaine manière, tout prédicat peut être décomposé en prédicats simples reliés par ces opérateurs.
Ansi, si nous arrivons maintenant à formaliser ces opérations (c'est à dire écrire leur fonction Fop(c1, c2), nous pourrons considérer que l'objectif est atteint.

Soit f(x) et g(x) deux fonctions de sélection, telles que :
f1(x) = f(x) * y = CASE WHEN cond1(x) THEN y ELSE 0 END
g1(x) = g(x) * z = CASE WHEN cond2(x) THEN z ELSE 0 END

La fonction h de sélection pour les opérattions logiques basiques :
cond1 OR cond2 : h(x) = f(x) + g(x) - f(x) * g(x)
cond1 AND cond2 : h(x) = f(x) * g(x)
NOT cond1 : h(x) = 1 - f(x)

On peut étendre a présent les tests de base à IN, NOT IN et BETWEEN :
x IN (a1, a2, ..., an) <=> x = a1 OR x = a2 OR ...
x BETWEEN a AND b <=> x >= a AND x <= b

Allez, on applique tout ça !


select * from (
select trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1)) *
(trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) + trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1)) - trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) * trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1))) *
(1 - trunc(1 - abs(d - 5) / (abs(d - 5) + 1))) * 5 as reecr,
CASE WHEN a >= 2 AND (b = 6 OR c = -1) AND d <> 5  THEN 5 ELSE 0 END as orig,
a.*
from me_manque_une_case a
)
where reecr <> orig
/


Aucun résultat : les deux écritures sont strictement équivalentes.

4) Branchement de diverses conditions : CASE WHEN Cond1 THEN y WHEN Cond2 THEN z ... END

Jusqu'à présent, nous n'avons réalisé qu'un seul branchement.
Allez, un dernier petit effort !
CASE WHEN cond1 THEN a WHEN cond2 THEN b ELSE c END
Pour implémenter une succession de branchements, nous devons retranscrire de la manière suivante :
Soient f et g les fonctions de sélectios de cond1 et cond2.
cond1
NOT cond1 AND cond2
NOT cond1 AND not cond2
Donc :
f(x) * a + (1 - f(x)) * g(x) * b + (1 - f(x)) * (1 - g(x)) * c

Et donc, la dernière illustration :

SQL> select * from (
  2  select trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1))
  3  +
  4  (1 - trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1))) *
  5  (trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) + trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1))
  6      - trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) * trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1))) * 2
  7  +
  8  (1 - trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1))) *
  9  (1-(trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) + trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1)) -
 10        trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) * trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1)))) * 3
 11  as reecr,
 12  CASE WHEN a >= 2 THEN 1 WHEN (b = 6 OR c = -1) THEN 2 ELSE 3 END as orig,
 13  a.*
 14  from me_manque_une_case a
 15  order by dbms_random.random
 16  )
 17  --where reecr <> orig
 18  where rownum <= 20;

     REECR       ORIG          A          B          C          D
---------- ---------- ---------- ---------- ---------- ----------
         2          2         -7         -8         -1         -5
         1          1          9          4         -6          7
         1          1          8          7          7         -2
         3          3          0         -5         -7          1
         3          3         -2         -5          6         -4
         3          3         -8          5         -4         -6
         1          1          9         -7         -6         -8
         3          3          1        -10          6         -2
         3          3         -4         -8         -2         -9
         1          1          7         10         -9         -5
         1          1         10          6         -6         -3
         3          3         -7          3         -8         -4
         1          1          7          9          3         -8
         3          3          1          5         -2          2
         3          3         -8          8          2          1
         3          3         -1          3         -4          1
         3          3         -3         -2          5         -2
         3          3          0         -7          4          8
         1          1          2         -8          6         -8
         3          3         -3        -10        -10         -6

20 ligne(s) sÚlectionnÚe(s).



=> Sous performant, absolument illisible, inutile... Vive le Cosmetic SQL !

Partager cet article

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

commentaires

Waldar 08/09/2009 22:59

Un petit cadeau quant à ce post :
http://www.waldar.org/blog/200909/tsql-challenge-13-break-batch-pacmann-arithmetics-case

Pacman 22/09/2009 13:37



Héhé merci Waldar, c'est très joli !
Franchement, tu as vraiment les bons réflexes :)



GoLDoZ 04/09/2009 11:13

Excellente gymnastique, bravo!

Pacman 22/09/2009 13:35


Merci, merci, c'est vrai que je me suis bien amusé en le faisant :)