Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
12 août 2009 3 12 /08 /août /2009 10:33
Nous inaugurons aujourd'hui cette section, dont l'objectif est... hmmm... aucun.

Nous allons d'ailleurs le prouver tout de suite, car le sujet du jour est : dessiner un coeur avec une requête SQL.
C'est dédié à mon ami Vodiem, un grand spécialiste du cosmetic SQL.

Bien sûr, il serait possible de faire des select de caractères ligne par ligne... mais on va se compliquer un peu la tâche, car c'est aussi cela le cosmetic SQL.
Donc en généralisant, il s'agit de dessiner une courbe quelconque en une requête.

Pour cela, on commence par décomposer la courbe en portions de courbes représentant des fonctions usuelles (Droite, Cosinus, Sinus, ...)
Dans le cas précis du coeur, on peut considérer par exemple qu'il s'agit de deux arcs de sinus / cosinus, plus deux segments de droite.

Il faut donc formaliser la représentation d'une fonction y = f(x) dans un repère orthonormé. Une fois cela fait, il faudra coller ensemble les morceaux (plus facile à dire qu'à faire...)

1) Représentation d'une fonction
Faisons le parallèle avec notre repère Euclidien habituel :
La coordonnée "y" est gérée par le numéro de ligne décroissant.
La coordonnée x est gérée par le positionnement d'un caractère grâce à LPAD.

Cependant, une différence notable : on génère ligne par ligne les "x", donc l'équation entrée dans le LPAD correspond à :
x = f(y)

Exemple :

select x
from (
select lpad('o', 2 * level) as x, level as y
from dual
connect by level <= 10
)
order by y desc
/


X
-----------------------
                   o
                 o
               o
             o
           o
         o
       o
     o
   o
 o

10 rows selected.


En somme, pour afficher la courbe correspond à l'équation y = f(x), il faut que f soit inversible.
Cependant, toutes les fonctions ne sont pas inversibles. Par contre, une grande partie d'entre elles sont décomposables en intervalles sur lesquels elles sont strictement monotones.
Par exemple,  f: x-> sinus(x) entre [0, pi/2] et [pi/2, pi].

with p as (
            select 'o' as pt, 20 as sc from dual)
,intv as (            
            select 1 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 2 as id, 5 as ymaxi, 4 as ymini from dual
            )
,intvsc as (
            select pt, sc, sc * ymaxi as ymax, sc * ymini as ymin, ymini, ymaxi, id
            from p cross join intv)
, intvm as (
            select max(ymax) as ymax from intvsc)
,t as (
select ymax - level  as y --, ymax, level
from intvm
connect by level <= ymax
union all select ymax from intvm
)
, res1 as (
select case   when id = 1 then asin((y /*+ ymini-4*/- ymin)/sc) * sc                                                       
               when id = 2 then (3.141592  - asin((y - ymin)/sc)) * sc               
      end as x            
            , y
            , pt
            , id
from t join intvsc on t.y between intvsc.ymin and intvsc.ymax
)
, res2 as (
select x, y, coalesce(lag(x, 1) over(partition by y order by x, id), 0) as xprev, pt, id
, row_number() over(partition by y, x order by id) as dedoubl
from res1
)
select substr(REPLACE(REPLACE(XMLAgg(XMLElement("x", coalesce(lpad(pt, x - xprev), pt)) order by x),'</x>', ' '),'<x>',' '),2) AS x
from res2
where dedoubl = 1
group by y
order by y desc
;

X
------------------------------------------------------------------
                              o  o
                        o             o
                     o                   o
                   o                       o
                 o                          o
               o                             o
              o                                o
             o                                   o
           o                                      o
          o                                        o
         o                                          o
        o                                             o
       o                                               o
      o                                                 o
     o                                                   o
    o                                                     o
   o                                                       o
  o                                                         o
 o                                                           o
o                                                             o
o                                                               o

21 rows selected.


Les sections de la requête traitent les problématiques rencontrées :
- intv : décrit les intervalles sur lesquels les fonctions sont définies
- intvsc : il s'agit de recadrer l'intervalle.
    D'une part, on lève la difficulté de gérer des coordonnées négatives (soit en abscisse, soit en ordonnée) en réalisant une translation des bornes.
    D'autre, on applique un facteur d'échelle sc(ale) (dans cet exemple 20) : du fait que la granularité des coordonnées est forcément de "1" (soit le numéro de ligne en ordonnée, soit le décalage "LPAD" en abscisse), il faut pouvoir "zoomer".
- t : on traduit l'union des intervalles de définition par la génération "à la volée" de lignes par CONNECT BY LEVEL
- res1 : pour chaque intervalle et chaque fonction s'appliquant dessus, on génère la coordonnée x = f(y) correspondante
    A noter l'utilisation de arcsin, la fonction réciproque de sinus, ainsi que la composition x = arc(g(y)), qui matérialise les translations et homothéties décrites plus haut
- On arrive aux parties finales, les plus sensibles : recoller les morceaux ! (res2 et SELECT final)
    Pour cela, il faut aggréger les lignes qui correspondent à une même ordonnée y. Plusieurs méthodes sont possibles.
    Ici, la solution retenue (Merci Waldar qui me l'a fait découvrir !), est celle de l'aggrégation XML : on représente d'abord les différentes valeurs sous forme de feuilles XML (XMLElement), puis on les concatène (XMLAgg), puis on nettoie les balises dans le résultat.
    Deux problèmes supplémentaires sont traités par l'intermédiaire de res2 (et des fonctions analytiques) : le fait qu'il s'agit plus d'une juxtaposition que d'une aggrégation, dans la mesure où il faut retrancher les LPAD de l'élément précédent ; Et le fait que si deux points sont "confondus", l'aggrégation simple les affichera tous les deux...

Maintenant qu'on sait comment ça marche, il ne reste plus qu'à tracer le coeur en entier !

with p as (
            select chr(3) as pt, 10 as sc from dual)
,intv as (            
            select 1 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 2 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 3 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 4 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 5 as id, 4 as ymaxi, 0 as ymini from dual union all
            select 6 as id, 4 as ymaxi, 0 as ymini from dual
            )
,intvsc as (          
            select pt, sc, sc * ymaxi as ymax, sc * ymini as ymin, ymini, ymaxi, id
            from p cross join intv)
, intvm as (
            select max(ymax) as ymax from intvsc)
,t as (
select ymax - level  as y
from intvm
connect by level <= ymax
union all select ymax from intvm
)
, res1 as (
select case    when id = 1 then asin((y - ymin)/sc) * sc                                                       
               when id = 2 then (3.141592  - asin((y - ymin)/sc)) * sc
               when id = 3 then (3.141592  + asin((y - ymin)/sc)) * sc                                        
               when id = 4 then (6.283185  - asin((y - ymin)/sc)) * sc
               when id = 5 then (-3.141592 * y / 4  /sc + 3.141592  ) * sc
               when id = 6 then (3.141592 * y / 4  /sc + 3.141592  ) * sc               
      end as x            
            , y
            , pt
            , id
from t join intvsc on t.y between intvsc.ymin and intvsc.ymax
)
, res2 as (
select x, y, coalesce(lag(x, 1) over(partition by y order by x, id), 0) as xprev, pt, id
, row_number() over(partition by y, x order by id) as dedoubl
from res1
)
select substr(REPLACE(REPLACE(XMLAgg(XMLElement("x", coalesce(lpad(pt, x - xprev), pt)) order by x),'</x>', ' '),'<x>',' '),2) AS x
from res2
where dedoubl = 1
group by y
order by y desc


X
----------------------------------------------------------------------
              ♥  ♥                                ♥  ♥
          ♥          ♥                       ♥          ♥
        ♥             ♥                   ♥             ♥
      ♥                ♥                ♥                ♥
     ♥                   ♥             ♥                   ♥
    ♥                     ♥           ♥                     ♥
   ♥                        ♥         ♥                        ♥
  ♥                          ♥       ♥                          ♥
 ♥                            ♥     ♥                            ♥
♥                              ♥   ♥                              ♥
♥                                ♥                                ♥  ♥
♥                                                              ♥
♥                                                            ♥
 ♥                                                           ♥
  ♥                                                         ♥
  ♥                                                       ♥
   ♥                                                      ♥
    ♥                                                    ♥
     ♥                                                   ♥
      ♥                                                 ♥
      ♥                                                ♥
       ♥                                              ♥
        ♥                                            ♥
         ♥                                           ♥
         ♥                                         ♥
          ♥                                        ♥
           ♥                                      ♥
            ♥                                     ♥
             ♥                                   ♥
             ♥                                 ♥
              ♥                                ♥
               ♥                              ♥
                ♥                             ♥
                 ♥                           ♥
                 ♥                          ♥
                  ♥                        ♥
                   ♥                      ♥
                    ♥                     ♥
                    ♥                   ♥
                     ♥                  ♥
                      ♥                ♥
                       ♥               ♥
                        ♥             ♥
                        ♥           ♥
                         ♥          ♥
                          ♥        ♥
                           ♥       ♥
                            ♥     ♥
                            ♥    ♥
                             ♥  ♥
                              ♥


Voilà !
C'est très imparfait, mais ça s'explique :
- Il faut soigner les intersections entre les intervalles de définition
- Il faut ajuster la fonction d'aggrégation : on remarque en effet que plus on colle de morceaux, plus la courbe devient irrégulière.
En fait, lpad('o', f(y)) correspond à x = f(y) + 1/ sc...

Je vous laisse paufiner ces détails.
Quoi qu'il en soit, à présent, vous pourrez faire tomber le coeur de votre jolie DBA en lui envoyant cette requête !
(Petit conseil : faites un script qui balance la requête en boucle afin de faire clignoter ses écrans de surveillance. Au mieux, vous lancez en EXECUTE IMMEDIATE en faisant varier le paramètre sc pour faire craquer le shared pool :))

Partager cet article

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

commentaires

pacmann 28/09/2010 15:04


En fait le WITH sert ici uniquement à factoriser la requête : tu peux directement remplacer les expression dans les FROM respectifs (ce qui devient assez infâme du coup) :

select substr(REPLACE(REPLACE(XMLAgg(XMLElement("x", coalesce(lpad(pt, x - xprev), pt)) order by x),'', ' '),'',' '),2) AS x
from (
select x, y, coalesce(lag(x, 1) over(partition by y order by x, id), 0) as xprev, pt, id
, row_number() over(partition by y, x order by id) as dedoubl
from (
select case when id = 1 then asin((y - ymin)/sc) * sc
when id = 2 then (3.141592 - asin((y - ymin)/sc)) * sc
when id = 3 then (3.141592 + asin((y - ymin)/sc)) * sc
when id = 4 then (6.283185 - asin((y - ymin)/sc)) * sc
when id = 5 then (-3.141592 * y / 4 /sc + 3.141592 ) * sc
when id = 6 then (3.141592 * y / 4 /sc + 3.141592 ) * sc
end as x
, y
, pt
, id
from (
select ymax - level as y
from (
select max(ymax) as ymax from (
select pt, sc, sc * ymaxi as ymax, sc * ymini as ymin, ymini, ymaxi, id
from (
select 'a' as pt, 10 as sc from dual) p cross join (
select 1 as id, 5 as ymaxi, 4 as ymini from dual union all
select 2 as id, 5 as ymaxi, 4 as ymini from dual union all
select 3 as id, 5 as ymaxi, 4 as ymini from dual union all
select 4 as id, 5 as ymaxi, 4 as ymini from dual union all
select 5 as id, 4 as ymaxi, 0 as ymini from dual union all
select 6 as id, 4 as ymaxi, 0 as ymini from dual
) intv) intvsc) intvm
connect by level


xavier 25/08/2010 16:30


Salut, ca a l air super,
mais j'ai essayé sous oracle 8i et le WITH n'est pas reconnu.
Cela dit une dba qui bosserait sur Oracle 8i peut ne plus être fort jeune, lol.


Daniel R. 03/01/2010 00:02


Excellent...j'adore le ton de tes articles, et le petit clin d'oeil à la jolie DBA.


Pacman 09/01/2010 11:55


Merci, ça fait plaisir !


scheu 25/08/2009 11:54

Très fun ton article !!! Je ne soupçonnais pas qu'on pouvait faire tout ça en SQL sous Oracle ;-)

Pacman 28/08/2009 15:15


Merci beaucoup !
Je pense que c'est le genre de choses que je peux mettre dans mon CV :

COMPETENCES ORACLE
---------------------------
Dessiner des coeurs