Mercredi 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 :))
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 :))
Derniers Commentaires