Overblog
Suivre ce blog
Administration Créer mon blog
10 décembre 2013 2 10 /12 /décembre /2013 16:38

Dans l'exemple type des row generators (par exemple ici), on utilise parfois une construction étrange qui est "CONNECT BY level <= N".


C'est certes étrange et abusé, mais ça se conçoit : "connecte moi avec n'importe quoi et récursivement, mais pas plus loin que le 3ème étage".

 

Cela dit, il y a des moments où on veut générer N lignes pour chaque lignes appartenant à une sélection (de plus d'une ligne cette fois).

 

CREATE TABLE test_rowgen AS                

SELECT 'pacmann' c, 3 n FROM DUAL UNION ALL

SELECT 'robert' c, 2 n FROM DUAL UNION ALL 

SELECT 'squelettor' c, 1 n FROM DUAL       

 

Par exemple ici, on voudrait générer 3 lignes pacmann, deux lignes robert, et 1 ligne squelettor.

 

Et là, si on tente : 

 

SELECT *             

FROM test_rowgen     

CONNECT BY level <= n

 

... on obtient toutes les combinaisons possibles récursivement, tant que le niveau est inférieur à "n", ce qui n'est pas bon. La différence avec le générateur à partir de dual, c'est que la ligne de dual ne peut se connecter qu'avec elle-même, alors que là, on a une tripotée d'autres arbres distincts à générer combinatoirement.

 

Si, pour empêcher de connecter n'importe quoi, on tente de lui préciser qu'il doit se connecter qu'avec lui-même... 

 

SELECT c, n          

FROM test_rowgen     

CONNECT BY level <= n

    AND c = prior c  

 

... on obtient : 

ORA-01436: CONNECT BY loop in user data

 

La boucle est détectée au niveau de l'analyse de la requête, et non au niveau de l'exécution (pour preuve la première requête, une autre preuve un peu plus loin dans cet article)

 

Si on ajoute NOCYCLE, on ne va pas bien loin : 

 

SELECT c, n                  

FROM test_rowgen             

CONNECT BY NOCYCLE level <= n

    AND c = prior c          

 

C                   N

---------- ----------

pacmann             3

robert              2

squelettor          1

 

Bien entendu, NOCYCLE évite l'erreur, mais pour autant, lorsque le cycle est détecté, aucune connexion n'est tentée pour les chemins suspectés d'induire des cycles.

 

Pour cela, des petits génies (dont Laurent Schneider, mais lui seul pourra nous dire s'il l'avait repompé ailleurs à l'époque :)) ont hacké le système et ont tenté : 

 

SELECT c, n                         

FROM test_rowgen                    

CONNECT BY level <= n               

    AND c = prior c                 

    AND prior sys_guid() IS NOT NULL

 

C                   N

---------- ----------

pacmann             3

pacmann             3

pacmann             3

robert              2

robert              2

squelettor          1

 

Alors oui, ça marche... mais qu'est ce que ça veut dire ?

 

Testons de réécrire un peu la requête : 

WITH u AS (                  

    SELECT c, n, sys_guid() t

    FROM test_rowgen         

    )                        

SELECT *                     

FROM  u                      

CONNECT BY level <= n        

    AND c = prior c          

    AND prior t IS NOT NULL  

 

C                   N T                               

---------- ---------- --------------------------------

pacmann             3 ED2ED377E71B437AE0436203650A0428

pacmann             3 ED2ED377E71D437AE0436203650A0428

pacmann             3 ED2ED377E71F437AE0436203650A0428

robert              2 ED2ED377E721437AE0436203650A0428

robert              2 ED2ED377E723437AE0436203650A0428

squelettor          1 ED2ED377E725437AE0436203650A0428

 

Ca marche !

Mais si on force la matérialisation : 

 

WITH u AS (                                  

    SELECT /*+materialize*/c, n, sys_guid() t

    FROM test_rowgen                         

    )                                        

SELECT *                                     

FROM  u                                      

CONNECT BY level <= n                        

    AND c = prior c                          

    AND prior t IS NOT NULL                  

 

... Paf !

ORA-01436: CONNECT BY loop in user data

 

Et moi, les résultats qui varient en fonction du plan d'exécution... hum. (Je dis ça, mais faudrait encore vérifier dans quel ordre les choses se passent...)

 

Par ailleurs la condition "prior sys_guid() is not null" n'empêche en rien les loop...

 

SELECT c, n                                           

FROM test_rowgen                                 

CONNECT BY prior sys_guid() IS NOT NULL

 

... boucle infinie tuée à la main après plusieurs millions de lignes fetched.

 

Donc voilà, cette astuce est démoniaque et plutôt dangereuse.

 

Je propose donc une méthode encore plus nulle, parce que je le vaux bien : 

 

SELECT c,n                                                                     

FROM (                                                                         

    SELECT c, n, coalesce(sum(n-1)                                             

    over(order by c desc rows between unbounded preceding and 1 preceding),0) p

    FROM test_rowgen                                                           

    )                                                                          

CONNECT BY rownum <= n + p                                                     

 

C                   N

---------- ----------

squelettor          1

robert              2

pacmann             3

pacmann             3

robert              2

pacmann             3

 

Pourquoi est-ce que ça marche ?

Parce qu'au moment de connecter, on se base sur rownum pour estimer le nombre de connexion qui ont été faites sur les lignes précédentes, et en déduire combien il faut en faire la ligne présente.


Pourquoi est-ce que c'est lamentable et à ne surtout pas refaire ?

Parce que d'une part, je n'ai pas la moindre idée de ce qu'est sensé être l'exécution de CONNECT BY, et (surtout ?) parce qu'on ne programme pas en fonction de l'algorithme interne d'Oracle, qui peut très bien changer d'un patch à l'autre (l'introduction du hash group by en étant certainement le plus bel exemple)

 

Comme dans tout article nihiliste, il faut finir par la clause MODEL (qui marche, n'est pas officiellement prohibée, mais vous mettra à dos vos collègues et votre hiérarchie) : 

 

SELECT c, n                                     

FROM test_rowgen                                

MODEL                                           

PARTITION BY (c c0)                             

DIMENSION BY (1 i)                              

MEASURES (c,n)                                  

RULES (                                         

    c[FOR i FROM 1 TO n[1] INCREMENT 1] = c[1]  

    , n[FOR i FROM 1 TO n[1] INCREMENT 1] = n[1]

    )                                           

 

C                   N

---------- ----------

robert              2

squelettor          1

robert              2

pacmann             3

pacmann             3

pacmann             3

 

C'est bien la première fois que je trouve MODEL moins obscure qu'une quelconque autre méthode...

Published by Pacman - dans SQL
commenter cet article
5 décembre 2013 4 05 /12 /décembre /2013 10:53

Pour faire suite à mon article précédent, je vais quand même lâcher une solution viable, spécifique à Oracle : 

 

SELECT ville_etp                                                                

FROM t_entrepot                                                                 

WHERE rayon_ryn IN (SELECT rayon_ryn                                            

                    FROM t_rayon)                                               

GROUP BY ville_etp                                                              

HAVING collect(DISTINCT rayon_ryn) = (SELECT collect(rayon_ryn) FROM t_rayon)   

 

VILLE_ETP

---------

MARSEILLE

TOULOUSE 

 

Collect, à partir de 10g, permet de créer un collection par agrégation. 

Dans une collection, il n'y a pas de problématique d'ordre, par contre il peut bien y avoir des doublons (d'où l'ajout du DISTINCT).


Dans cette requête, on vérifie donc que la collection des rayons proposés par un entrepot (en limitant la liste de ces rayons à ceux présent dans la table t_rayon), correspond exactement à la liste des rayons à fournir.

 

Si on veut la correspondance exacte entre les rayons de t_entrepot et ceux de t_rayon, on peut virer la sous-requête IN.

Published by Pacman - dans SQL
commenter cet article
20 septembre 2013 5 20 /09 /septembre /2013 14:27

Il y a des fois, on agit avant de penser.

Au moment même où je validais ma requête, j'attendais un "ORA-00918: column ambiguously defined".

Mais ce n'est pas arrivé.

 

1) Les données de test

 

Précision : on est en version 10.2.0.4.0

 

create table testeuh as 

select 'a' id, 1 val from dual union all

select 'b' id, 2 val from dual;

 

create table testeuh1 as 

select 'a' id, 1 val from dual union all

select 'b' id, 2 val from dual;

 

create table testeuh2 as

select 'a' ida, 'b' idb, 0 val from dual

 

2) Ce qu'il nous arrive en général

 

Quand dans une requête sur plusieurs tables, on ne préfixe pas par l'alias de la table une colonne présente dans plusieurs tables, cela donne : 

 

SELECT val

FROM testeuh2 a

JOIN testeuh b on a.ida = b.id;

 

SELECT val

       *

ERROR at line 1:

ORA-00918: column ambiguously defined

 

Normal, car comment savoir quelle colonne val doit être renvoyée ?

Il faut donc préfixer les colonnes avec l'alias de la table : 

 

SELECT a.val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id;

 

       VAL

----------

         0

 

3) Parce que plus on est de fous, plus on rit

 

SELECT val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id;

 

       VAL

----------

         2

 

=> Ce coup-ci, la requête ne plante pas, et Oracle me renvoie arbitrairement le val de la table testeuh1... comment est-il choisi ?

 

On ne peut pas affirmer grand chose, mais on peut faire joujou : 

 

SELECT val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id

  JOIN testeuh1 d on a.ida = d.id;

 

       VAL

----------

         1

 

=> Quand on rajoute une jointure de plus, ça ne plante toujours pas. De plus, c'est la dernière table citée dans la clause FROM qui remporte la mise.

 

Est-ce que cela dépend du plan d'exécution ?

 

SELECT /*+leading(b a d c)*/val

FROM testeuh2 a

  JOIN testeuh b on a.ida = b.id

  JOIN testeuh1 c on a.idb = c.id

  JOIN testeuh1 d on a.ida = d.id;

 

       VAL

----------

         1

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 162124344

 

---------------------------------------------------------------------------------

| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |          |     1 |    43 |    14  (15)| 00:00:01 |

|*  1 |  HASH JOIN           |          |     1 |    43 |    14  (15)| 00:00:01 |

|*  2 |   HASH JOIN          |          |     1 |    37 |    10  (10)| 00:00:01 |

|*  3 |    HASH JOIN         |          |     1 |    18 |     7  (15)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| TESTEUH  |     2 |    12 |     3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| TESTEUH2 |     1 |    12 |     3   (0)| 00:00:01 |

|   6 |    TABLE ACCESS FULL | TESTEUH1 |     2 |    38 |     3   (0)| 00:00:01 |

|   7 |   TABLE ACCESS FULL  | TESTEUH1 |     2 |    12 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("A"."IDB"="C"."ID")

   2 - access("A"."IDA"="D"."ID")

   3 - access("A"."IDA"="B"."ID")

 

=> Et ben non, la table c est accédée en dernier, mais pour autant val ne vaut pas 2.

 

4) Conclusions

 

a. C'est une bonne pratique de préfixer systématiquement les colonnes (et en plus ça apporte énormément de lisibilité pour toute personne qui lit le code sans connaître le modèle par coeur)

 

b. "Ambiguously" provient probablement du préfixe latin amb-, qui porte l'idée de double selon wiktionary.org. Donc à partir de 3, on n'est plus ambigu, et il ne s'agit donc pas d'un bug

Published by Pacman - dans SQL
commenter cet article
29 août 2013 4 29 /08 /août /2013 15:10

Comment rassembler des intervalles qui se chevauchent ?

Dans ma jeunesse, j'ai vu (et proposé) des solutions tirées par les cheveux.

Et l'autre jour sur OTN, j'ai trouvé la méthode de FrankKulash particulièrement élégante.

 

Petit article hommage pour lui : 

 

1) Les données

 

Robert, Marcel et Squelettor travaillent dans la même équipe. Voici la table non normalisée de leur planning : 

 

CREATE TABLE planning AS

SELECT 'Robert' nom, to_date('20130810 10', 'YYYYMMDD hh24') date_deb, to_date('20130810 14', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Marcel' nom, to_date('20130810 12', 'YYYYMMDD hh24') date_deb, to_date('20130810 20', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Squelettor' nom, to_date('20130810 11', 'YYYYMMDD hh24') date_deb, to_date('20130810 18', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Robert' nom, to_date('20130811 08', 'YYYYMMDD hh24') date_deb, to_date('20130811 10', 'YYYYMMDD hh24') date_fin FROM DUAL

UNION ALL SELECT 'Marcel' nom, to_date('20130811 12', 'YYYYMMDD hh24') date_deb, to_date('20130811 14', 'YYYYMMDD hh24') date_fin FROM DUAL

 UNION ALL SELECT 'Squelettor' nom, to_date('20130811 09', 'YYYYMMDD hh24') date_deb, to_date('20130811 13', 'YYYYMMDD hh24') date_fin FROM DUAL;

 

2) Interval UNION !

 

La requête suivante répond à la question : "quelles sont les périodes disjointes sur lesquelles il y a au moins une personne présente ?"

 

SELECT to_char(min(date_deb), 'DD/MM/YYYY hh24:mi') deb_union, to_char(max(date_fin), 'DD/MM/YYYY hh24:mi') fin_union

FROM (

  SELECT date_deb, date_fin, sum(brk) OVER(ORDER BY date_deb) grp

  FROM (

      SELECT date_deb, date_fin

        , CASE WHEN max(date_fin) OVER(ORDER BY date_deb ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) < date_deb

               THEN 1

               ELSE 0 END brk

      FROM planning)

      )

GROUP BY grp

ORDER BY grp;

 

DEB_UNION        FIN_UNION

---------------- ----------------

10/08/2013 10:00 10/08/2013 20:00

11/08/2013 08:00 11/08/2013 14:00

 

Le raisonnement : pour une ligne donnée, si parmi les lignes précédentes triées par date de début, la plus grande date de fin est strictement inférieure à ma date de début, la ligne courante crée un nouvel intervalle.


Par défaut, la fenête (le périmètre) d'une fonction analytique comprend toutes les lignes de la partition de dates inférieures où égale à la ligne courante.

Le '1 PRECEDING' permet de s'arrêter un cran avant, et on doit préciser le début de la fenête à UNBOUNDED PRECEDING pour indiquer qu'on prend tout depuis le début de la partition.

 

Une fois qu'on a ces flags 'BRK' pour chaque ligne, en faire la somme cumulative sur chaque ligne, permet de compter le nombre de ruptures présentes avant la ligne courante.

Ce nombre 'GRP' est un critère de groupement pour rassembler les intervalles, et il ne reste plus qu'à choper le début et la fin de l'intervalle.

 

3) Interval INTERSECTION !

 

Histoire d'apporter quand même une touche personnelle, j'ai cherché à répondre à une autre question : 

"Quel est le nombre de personnes présentes par tranches horaires distinctes ?"

 

J'ai fait des petits dessins où je projetais des intervalles sur un axe, pour finir par me dire : si on colle toutes les extrêmités d'intervalles les unes aux autres, on peut ensuite considérer chacun des segments minimaux et compter le nombre de "segments" du planning dans lesquels ils sont inclus.

 

SELECT to_char(t.deb_int, 'DAY hh24:mi') deb, to_char(t.fin_int, 'DAY hh24:mi') fin, count(*) nb, LISTAGG(nom, ',') WITHIN GROUP(ORDER BY nom) lstnom

FROM planning p JOIN 

    (SELECT borne_date deb_int, lead(borne_date, 1) OVER(ORDER BY borne_date) fin_int

    FROM planning

    UNPIVOT (borne_date

             FOR type_borne

             IN (date_deb, date_fin))

    GROUP BY borne_date) t

    ON t.deb_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

    AND t.fin_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

GROUP BY t.deb_int, t.fin_int    

ORDER BY t.deb_int

 

DEB                  FIN                          NB LSTNOM

-------------------- -------------------- ---------- -------------------------

SATURDAY  10:00      SATURDAY  11:00               1 Robert

SATURDAY  11:00      SATURDAY  12:00               2 Robert,Squelettor

SATURDAY  12:00      SATURDAY  14:00               3 Marcel,Robert,Squelettor

SATURDAY  14:00      SATURDAY  18:00               2 Marcel,Squelettor

SATURDAY  18:00      SATURDAY  20:00               1 Marcel

SUNDAY    08:00      SUNDAY    09:00               1 Robert

SUNDAY    09:00      SUNDAY    10:00               2 Robert,Squelettor

SUNDAY    10:00      SUNDAY    12:00               1 Squelettor

SUNDAY    12:00      SUNDAY    13:00               2 Marcel,Squelettor

SUNDAY    13:00      SUNDAY    14:00               1 Marcel

 

J'ai profité de ma nouvelle 11gR2 pour utiliser UNPIVOT, mais cela se fait bien sûr également (et entre autres) de la manière artisanale suivante : faire le produit cartésien avec une "table" à deux lignes, puis choisir alternativement la bonne colonne.

 

SELECT to_char(t.deb_int, 'DAY hh24:mi') deb, to_char(t.fin_int, 'DAY hh24:mi') fin, count(*) nb, LISTAGG(nom, ',') WITHIN GROUP(ORDER BY nom) lstnom

FROM planning p JOIN 

    (SELECT CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END deb_int

            , lead(CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END, 1) OVER(ORDER BY CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END) fin_int

    FROM planning

      CROSS JOIN (SELECT 1 n FROM DUAL

                  UNION ALL SELECT 2 FROM DUAL)

    GROUP BY CASE n WHEN 1 THEN date_deb WHEN 2 then date_fin END) t

    ON t.deb_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

    AND t.fin_int between p.date_deb and p.date_fin AND t.fin_int IS NOT NULL

GROUP BY t.deb_int, t.fin_int    

ORDER BY t.deb_int

 

(Et la première fois de ma vie que j'utilise LISTAGG pour mettre dans un même sac Marcel, Robert et Squelettor...)

Published by Pacman - dans SQL
commenter cet article
21 août 2013 3 21 /08 /août /2013 15:19

L'utilisateur : "Salut, je voudrais un filtre à sélection multiple sur les pays  pour mon rapport XYZ... idéalement avec des cases à cocher" Notez que ce n'est pas un rapport XXX, on fait pas ça chez nous.

Moi :            "Désolé, notre framework est pourri, ça va pas être jouable. Par contre si ça te dit, je te laisse saisir ta sélection, par exemple sous forme de liste de code pays sur 2 caractères, séparés par des virgules. En bonus, un bouton pour afficher la liste des correspondances libellé / code pays."

L'utilisateur : "Vendu !"

 

Donc je me mets à transformer des chaînes de caractères représentant des listes en lignes, afin de pouvoir faire des filtres "IN (SELECT ...)"

Sujet classique, juste l'occasion de passer en revue les méthodes, et "expliquer" mes choix.

 

Spliter une chaîne de caractères, ça se fait de manière itérative.

 

1) Implémenter sa boucle avec CONNECT BY level

 

Toujours la même astuce : CONNECT BY level sur une sélection d'une seule ligne, ça génère des lignes à la volée, et elles sont "indexables" par level ou rownum : 

 

define p_str = 'FR,DE,PL,BE'

 

SELECT substr(str,  instr(str, del, 1, level) + 1, instr(str, del, 1, level+1) - instr(str, del, 1, level) - 1)

FROM (select '&p_str' p_str, ',' || '&p_str' || ',' str, ',' del from dual)

CONNECT BY level <= length(p_str) - length(replace(p_str, del, '')) + 1; 

 

SUBSTR(STR,IN

-------------

FR

DE

PL

BE

 

On rajoute un délimiteur en début en fin de liste (mais on pourrait aussi s'embourber dans plein de CASE pour traiter le début et la fin de liste), puis on peut extraire avec substr en s'appuyant sur la position des délimiteurs dans la chaîne.

 

A partir de 10g, on a la version bien plus élégante avec REGEXP_SUBSTR : 

 

SELECT regexp_substr('&p_str',  '[^,]+', 1, level)

FROM dual

CONNECT BY level <= length(regexp_replace('&p_str', '[^,]+')) + 1; 

 

La regexp [^,]+ matche toutes les occurences multiples de caractères qui sont différents de ','... les deux derniers paramètres sont équivalents à INSR, c'est à dire position de départ pour la recherche, et numéro d'occurence à traiter.

 

Pourquoi je n'ai pas choisi cette solution ?

- Parce que CONNECT BY level, c'est du bugusing (CONNECT BY est sensé faire des arbres, pas du café...)

- Parce que ça fait des mochetés à recoder à chaque fois, et que ça n'arrange pas mes collègues non autistes du SQL 

 

2) Implémenter sa boucle avec MODEL

 

A présent, on reprend l'extraction regexp_substr parce que c'est joli, mais on itère avec la clause MODEL

 

SELECT tk 

FROM (

  SELECT 1 n, p_str, p_str tk, length(regexp_replace(p_str, '[^,]+')) + 1 n_item

  FROM (SELECT  '&p_str' p_str FROM dual) t

)

MODEL 

DIMENSION BY (n)

MEASURES (p_str, tk, n_item)

RULES (

tk[FOR n FROM 1 TO n_item[1] INCREMENT 1] = regexp_substr(p_str[1],  '[^,]+', 1, cv(n))

);

 

Cependant, je me demande si utiliser la clause model dans du code de production ne pourrait pas être un motif valable de licenciement...

Y a un gars (qui devrait se reconnaître s'il passe par là), qui disait : "As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, ..."

 

3) XML Table

 

On peut faire plein de trucs assez loufoques avec XMLTable sous Oracle.

Notamment tout simplement : 

 

SELECT *

FROM xmltable('&p_str');

 

C'est tout simple, tout compact, mais j'ai décidé de ne pas l'utiliser parce qu'il y a "XML" dans l'instruction. (Oui oui, c'est purement psychologique)

 

 

4) Pipelined function

 

Une fonction pl/sql, ça sait très bien faire des boucles, et ça sait aussi renvoyer des tableaux.

Et puis, ça peut aussi aussi retourner des lignes au fil de l'eau en étant "pipelined".

C'est tout mignon, on a l'impression de cracher des petites aiguilles empoisonnées avec une sarbacane : 

 

 

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF varchar2(4000);

/

CREATE OR REPLACE FUNCTION split_string2(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

PIPELINED

IS

n_item NUMBER;

   s_str VARCHAR2(32767);

   s_ret VARCHAR2(4000);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

 

FOR i IN 1..n_item LOOP    

s_ret := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

    pipe row(s_ret);

END LOOP;

END split_string2;

/

 

 

SELECT *

FROM table(split_string('&p_str',','))

 

Cela dit, bien qu'étant un grand fan de pipes, je trouve ça stupide de context switch n fois alors que l'exécution de la boucle en elle-même prend un temps négligeable.

 

 

5) Function

 

Bon ben voilà, j'ai choisi au final de faire une petite fonction toute simple (j'ai cependant gardé la méthode d'extraction pourrie à base de substr/instr), qui renvoit tout un tableau : 

 

CREATE OR REPLACE FUNCTION split_string(p_str IN VARCHAR2, p_del IN VARCHAR2) 

RETURN varchar2_ntt

IS

t_ret varchar2_ntt;

n_item NUMBER;

   s_str VARCHAR2(32767);

BEGIN

  s_str := trim(both p_del from p_str);

n_item := length(s_str) - coalesce(length(replace(s_str, p_del, '')), 0) + 1;

  s_str := p_del || trim( both p_del from p_str) || p_del;

t_ret := varchar2_ntt();

  t_ret.extend(n_item);

 

FOR i IN 1..n_item LOOP    

t_ret(i) := substr(s_str, instr(s_str, p_del, 1, i) + 1, instr(s_str, p_del, 1, i + 1) - instr(s_str, p_del, 1, i) -1);

END LOOP;

RETURN t_ret;

END split_string;

/

 

SELECT *

FROM table(split_string2('&p_str',','))

 

Il y a des gens qui vous diront qu'appeler des fonctions PL/SQL dans du SQL c'est mal parce que ça context switch.

Mais ici, dans une requête comme celle-ci, la fonction est à priori appelée une seule fois, donc sincèrement, je m'en cogne.

 

SELECT *

FROM ma_table 

WHERE code_pays IN 

  (SELECT column_value

    FROM table(split_string2('&p_str',',')))

 

 

 

 

Published by Pacman - dans SQL
commenter cet article
18 juillet 2013 4 18 /07 /juillet /2013 10:58

Un des grands problèmes dans ma vie, c'est ma passion pour le NATURAL FULL OUTER JOIN. Parce qu'il y a plein de mots, c'est full, le côté naturel, les joints et tout ça.


Et cette lubie m'a encore piégée hier lorsque je faisais une simple comparaison de données avant / après traitement.


Allez, démonstration par étape : 


1) Deux tables correspondant aux données avant / après


SQL>CREATE TABLE tab_avant AS

  2  SELECT level l

  3  FROM dual

  4  CONNECT BY level <= 5;


Table created.


Elapsed: 00:00:00.09

SQL>CREATE TABLE tab_apres AS

  2  SELECT level + 2 l

  3  FROM dual

  4  CONNECT BY level <= 5;


Table created.


Elapsed: 00:00:00.03


2) Pour comparer, minus ou natural machin : 


La version minus me barbe un peu, parce qu'il faut écrire A - B et B - A : 


SQL>SELECT '+ap', a.*

  2  FROM tab_apres a

  3  MINUS

  4  SELECT '+ap', b.*

  5  FROM tab_avant b

  6  UNION ALL

  7  SELECT '+av', c.*

  8  FROM tab_avant c

  9  MINUS

 10  SELECT '+av', d.*

 11  FROM tab_apres d;


'+A          L

--- ----------

+ap          6

+ap          7

+av          1

+av          2


En revanche, la version natural full outer join me semble beaucoup plus drôle :


SQL>SELECT case when b.rowid is null then '+av' when a.rowid is null then '+ap' end, l

  2  FROM tab_avant a

  3    NATURAL FULL OUTER JOIN tab_apres b

  4  WHERE a.rowid is null OR b.rowid is null;


CAS          L

--- ----------

+av          1

+av          2

+ap          6

+ap          7


Elapsed: 00:00:00.03


"Natural" pour dire qu'on match sur toutes les colonnes de même nom (à n'utiliser que pour rigoler sous peine de se faire virer), outer join parce qu'on prend aussi ce qui ne matche pas, et full parce que c'est outer des deux côtés.


3) Une défaite supplémentaire


Seulement voilà, il y a des choses qui ne sont pas comparables... ajoutons une colonne vide dans chaque table : 


SQL>ALTER TABLE tab_avant ADD c number;


Table altered.


Elapsed: 00:00:00.06

SQL>ALTER TABLE tab_apres ADD c number;


Table altered.


Elapsed: 00:00:00.03


... et relançons la comparaison :


SQL>SELECT case when b.rowid is null then '+av' when a.rowid is null then '+ap' end, l, c

  2  FROM tab_avant a

  3    NATURAL FULL OUTER JOIN tab_apres b

  4  WHERE a.rowid is null OR b.rowid is null;


CAS          L          C

--- ---------- ----------

+av          1

+av          4

+av          3

+av          2

+av          5

+ap          6

+ap          7

+ap          5

+ap          4

+ap          3


10 rows selected.


Patatra, ça devient n'importe quoi. Le "natural" ne doit pas faire oublier qu'on teste "colonne a" = "colonne b" dans la jointure.

Et la comparaison NULL = NULL est toujours aussi peu constructive...


On n'a pas ce problème avec minus : 


SQL>SELECT '+ap', a.*

  2   FROM tab_apres a

  3   MINUS

  4   SELECT '+ap', b.*

  5   FROM tab_avant b

  6   UNION ALL

  7   SELECT '+av', c.*

  8   FROM tab_avant c

  9   MINUS

 10   SELECT '+av', d.*

 11   FROM tab_apres d;


'+A          L          C

--- ---------- ----------

+ap          6

+ap          7

+av          1

+av          2


Elapsed: 00:00:00.04

 

Z'imaginez même pas quels trucs tordus j'étais allé chercher pour expliquer les différences...

Published by Pacman - dans SQL
commenter cet article
4 juillet 2013 4 04 /07 /juillet /2013 16:38

Dans la vie, il y a ceux qui sont géniaux, et ceux qui répètent connement ce que disent les gens géniaux.


Je fais partie de la deuxième catégorie...


Ayant passé mes 32 dernières années (j'ai commencé le SQL Oracle à la sortie de la maternité) à doubler les quotes pour les échapper, et sans que personne ne se moque jamais de moi, je me dois de partager ce que j'ai appris aujourd'hui sur OTN : 


 

SQL> SELECT q'!J'aime les '!' AS e1, q'{Ah c'qu'on rigole avec l'UMP}' AS e2 FROM dual;

 

E1           E2

------------ ----------------------------

J'aime les ' Ah c'qu'on rigole avec l'UMP

 

 

(le caractère après la quote sert de seconde couche de délimiteur, on referme la citation dans le sens inverse. Bref comme < >... dans un sens puis dans l'autre quoi)

 

Merci BluShadow !


Published by Pacman - dans SQL
commenter cet article
1 juillet 2013 1 01 /07 /juillet /2013 10:22

De retour sur Oracle après 3 ans dans le monde du pipo, je me refais un peu la main en participant au forum OTN.

 

Et là, sur une question classique "comment vérifier qu'une chaîne de caractères est un nombre sous Oracle ?", je m'aperçois que les intervenants d'OTN à priori chevronnés sont en fait un peu à la masse par rapport aux amis de developpez.net d'il y a 3-5 ans.

 

Notamment, pour parser une chaîne potentiellement numérique, il ne faut pas simplement vérifier qu'elle est composée de chiffres !

 

Je m'étais amusé à l'époque sur ce même non-blog à faire un is_numeric imbuvable : 

http://pacmann.over-blog.com/article-yet-another-oracle-isnumeric-function-51158452.html

 

Je profite de l'occasion pour enfin compléter quelques points : 

- Quite à simplement vérifier la présence de chiffres uniquement la fonction rtrim fait très bien le boulot, pas besoin de translate + trim !

  => Un grand merci à McM de developpez.net

- Faire une vraie regexp bien touffue, c'est idéal

  => Un grand merci à Bloon de developpez.net

 

SQL> SELECT to_number(c)

  2      , case when regexp_like (c,'^(-|\+)?(\d*|\d+,|,\d+|\d+,\d+)(E(-|\+)?\d+)?$','i')then 1 else 0 end win

  3      , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  4      , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  5  FROM (

  6    SELECT '1' c FROM DUAL UNION ALL

  7    SELECT '+2'FROM DUAL UNION ALL

  8    SELECT '3,4' FROM DUAL UNION ALL

  9    SELECT '-5' FROM DUAL UNION ALL

 10    SELECT '6E78' FROM DUAL UNION ALL

 11    SELECT '9E-5' FROM DUAL UNION ALL

 12    SELECT '3,E6' FROM DUAL UNION ALL

 13    SELECT '-,45E7' FROM DUAL UNION ALL

 14    SELECT '4,' FROM DUAL

 15  );

 

TO_NUMBER(C)        WIN        MCM    LOOSEXP

------------ ---------- ---------- ----------

           1          1          1          1

           2          1          0          0

         3,4          1          0          0

          -5          1          0          0

  6,0000E+78          1          0          0

      ,00009          1          0          0

     3000000          1          0          0

    -4500000          1          0          0

           4          1          0          0

 

9 rows selected.  

 

Voilà, voilà, vous constaterez qu'il est toujours très facile de faire un article en repompant les solutions des autres... mais bon vu comme j'ai galéré à retrouver la regexp, ça vaut le coup de la rediffuser, hein !

Et puis pour s'y remettre, faut bien commencer quelque part...

 

EDIT : 

 

Prise en compte du NLS_NUMERIC_CHARACTERS, un grand merci à notre guess star Laurent.

Pas prise en compte par contre des nombres de R barre, on va dire que ça en rentre pas dans les données 'business"


SQL>   SELECT c

  2         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i') then to_number(c) end tnb

  3         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i')then 1 else 0 end win

  4         , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  5         , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  6     FROM (

  7       SELECT '1' c FROM DUAL UNION ALL

  8       SELECT '+2'FROM DUAL UNION ALL

  9       SELECT '3,4' FROM DUAL UNION ALL

 10       SELECT '-5' FROM DUAL UNION ALL

 11       SELECT '6E20' FROM DUAL UNION ALL

 12       SELECT '9E-5' FROM DUAL UNION ALL

 13       SELECT '3,E6' FROM DUAL UNION ALL

 14       SELECT '-,45E7' FROM DUAL UNION ALL

 15       SELECT 'E7' FROM DUAL UNION ALL

 16       SELECT '4,' FROM DUAL UNION ALL

 17       SELECT '4.' FROM DUAL UNION ALL

 18       SELECT '3.4' FROM DUAL UNION ALL

 19       SELECT '-.45E7' FROM DUAL UNION ALL

 20       SELECT '3.E6' FROM DUAL

 21     )

 22     CROSS JOIN (SELECT substr(value, 1, 1) d

 23                 FROM nls_session_parameters

 24                 where parameter='NLS_NUMERIC_CHARACTERS');

 

C             TNB        WIN        MCM    LOOSEXP

------ ---------- ---------- ---------- ----------

1               1          1          1          1

+2              2          1          0          0

3,4           3,4          1          0          0

-5             -5          1          0          0

6E20   6,0000E+20          1          0          0

9E-5       ,00009          1          0          0

3,E6      3000000          1          0          0

-,45E7   -4500000          1          0          0

E7                         0          0          0

4,              4          1          0          0

4.                         0          0          0

3.4                        0          0          0

-.45E7                     0          0          0

3.E6                       0          0          0

 

14 rows selected.

 

Elapsed: 00:00:00.09

SQL> alter session set nls_numeric_characters = '.,'

  2  /

 

Session altered.

 

Elapsed: 00:00:00.01

SQL>   SELECT c

  2         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i') then to_number(c) end tnb

  3         , case when regexp_like (c,replace('^(-|\+)?(\d+|\d+X|X\d+|\d+X\d+)(E(-|\+)?\d+)?$', 'X', '\'||d),'i')then 1 else 0 end win

  4         , case when rtrim(c, '1234567890') is null then 1 else 0 end mcm

  5         , case when regexp_like (c, '^[[:digit:]]$') then 1 else 0 end loosexp

  6     FROM (

  7       SELECT '1' c FROM DUAL UNION ALL

  8       SELECT '+2'FROM DUAL UNION ALL

  9       SELECT '3,4' FROM DUAL UNION ALL

 10       SELECT '-5' FROM DUAL UNION ALL

 11       SELECT '6E20' FROM DUAL UNION ALL

 12       SELECT '9E-5' FROM DUAL UNION ALL

 13       SELECT '3,E6' FROM DUAL UNION ALL

 14       SELECT '-,45E7' FROM DUAL UNION ALL

 15       SELECT 'E7' FROM DUAL UNION ALL

 16       SELECT '4,' FROM DUAL UNION ALL

 17       SELECT '4.' FROM DUAL UNION ALL

 18       SELECT '3.4' FROM DUAL UNION ALL

 19       SELECT '-.45E7' FROM DUAL UNION ALL

 20       SELECT '3.E6' FROM DUAL

 21     )

 22     CROSS JOIN (SELECT substr(value, 1, 1) d

 23                 FROM nls_session_parameters

 24                 where parameter='NLS_NUMERIC_CHARACTERS');

 

C             TNB        WIN        MCM    LOOSEXP

------ ---------- ---------- ---------- ----------

1               1          1          1          1

+2              2          1          0          0

3,4                        0          0          0

-5             -5          1          0          0

6E20   6.0000E+20          1          0          0

9E-5       .00009          1          0          0

3,E6                       0          0          0

-,45E7                     0          0          0

E7                         0          0          0

4,                         0          0          0

4.              4          1          0          0

3.4           3.4          1          0          0

-.45E7   -4500000          1          0          0

3.E6      3000000          1          0          0

 

14 rows selected.

 

 

Elapsed: 00:00:00.04

 

Published by Pacman - dans SQL
commenter cet article
1 avril 2012 7 01 /04 /avril /2012 14:02

Ne serait-ce que par esprit de contradiction, le premier avril est le parfait moment pour aborder le sujet le moins drôle de l'univers :voir les données avant modifications, et les comparer aux données présentes !

 

0) Un jeu d'essai simple :

 

SQL> CREATE TABLE testflash AS
  2  SELECT level id, mod(level, 5) n
  3  FROM DUAL
  4  CONNECT BY level <= 2000;

Table créée

 

... et quelques modifications :

 

SQL> UPDATE testflash
  2  SET n = 99
  3  WHERE id = 1000;

1 ligne mise à jour.


SQL> INSERT INTO testflash VALUES(2001, 99);

1 ligne créée.

SQL> DELETE FROM testflash WHERE id = 2000;

1 ligne supprimée.

SQL> commit;

Validation effectuée.

 

On vérifie que les modifications sont bien là :

 

SQL> SELECT *
  2  FROM testflash
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000         99
      2001         99

 

 

1) AS OF TIMESTAMP :

 

Par principe, Oracle est capable de livrer une vue dans le passée des données, afin de garantir la consistence de la lecture. C'est fait de manière automatique, par exemple lorsque ta requête ultra longue ne voit pas les modifications qui sont commitées par ailleurs depuis le début de son executions.

 

En 9i FLASHBACK QUERY permet d'exploiter la mécanique pour demander explicitement, à un instant donné, de lire les données du passé. (Je passe les détails parce ça m'intéresse pas plus que ça)

 

Et en 9iR2, on va un peu plus loin en permettant, à l'intérieur d'une requête, de préciser quelles tables doivent être lues dans le passé. C'est la clause AS OF TIMESTAMP ajoutée après le nom de la table :

 

SQL> SELECT *
  2  FROM testflash AS OF TIMESTAMP sysdate - 1/24/12
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000          0
      2000          0

 

Tadam ! Ce sont les données avant modifications.

1/24/12 pour dire que c'était "il y a 5 minutes", mais on peu bien sûr spécifier directement une vraie date (et c'est ce qu'on va faire dans la suite) :

 

SQL> SELECT *
  2  FROM testflash AS OF TIMESTAMP to_date('20120401 13:00', 'yyyymmdd hh24:mi')
  3  WHERE id IN (1000, 2000, 2001);

        ID          N
---------- ----------
      1000          0
      2000          0

 

2) Full outer Compare !

 

Et donc maintenant, pour voir exactement ce qui a changé, une jointure full externe sur la table avant / après permet de faire un diff sympa :


SQL> SELECT CASE WHEN a.id is NULL THEN 'added'
  2              WHEN b.id IS NULL THEN 'removed'
  3     ELSE 'modified' END status,
  4         a.id, a.n, b.id newid, b.n newn
  5  FROM testflash a
  6    FULL OUTER JOIN testflash AS OF TIMESTAMP to_date('20120401 13:00', 'yyyymmdd hh24:mi') b
  7     ON a.id = b.id
  8  WHERE a.id IS NULL OR b.id IS NULL OR a.n <> b.n;

STATUS           ID          N      NEWID       NEWN
-------- ---------- ---------- ---------- ----------
modified       1000         99       1000          0
removed        2001         99
added                                2000          0

 

3) Entre autres

 

Il y a quelques pré-requis :

- ëtre post 9.2 bien sûr

- l'utilisateur doit avoir le grant execute sur le package dbms_flashback

- les données doivent être disponibles, un peu dans l'esprit ORA-1555

- pas possible lorsqu'il y a eu un DDL entre temps sur la table (notamment TRUNCATE !)

 

TIens sinon, la semaine dernière chuis passé OCM !

(On n'échappe pas aux traditions stupides...)

 

Allez, zou, je vais aller m'occuper de ma gamine qui a bu entre temps toute l'eau de Javel...

(Pas la peine d'appeler la DASS, c'est encore un poisson, hein !)

Published by Pacman - dans SQL
commenter cet article
22 janvier 2012 7 22 /01 /janvier /2012 14:23

Connaître la répartition des valeurs, c'est la clef pour briller en société et réussir ses projets.

 

Genre vous avez une ressource payante, et un résultat qui sera fonction croissante de la quantité de ressource que vous allouez. Ce qui vous intéresse, c'est bien entendu la gueule de la courbe de cette fonction, son côté linéaire, logarithmique ou exponentiel, ...

"En investissant 2M dans votre campagne, vous aurez un taux de conversion de 90%. En investissant 3M, vous atteindrez 91%"

 

Pour ce genre de problèmatique, on se tape un peu de connaître la moyenne ou l'écart type d'une population.

Moi ce qui m'excite, ce sont les "n-tile" !

 

En français, on appelle ça des quantiles, avec les exemples les plus connus : quartiles, déciles, centiles, ...

http://fr.wikipedia.org/wiki/Quantile

 

Exemple concret : en ces temps de crise, un pote partageait le lien suivant qui propose un certain nombre de statistiques sur l'assiduité des députés :

http://www.nosdeputes.fr/synthesetri/1

 

On intègre juste les données qui nous intéressent : les semaines d'activité

 

CREATE TABLE t (n number);

 

INSERT INTO t(n)
SELECT to_number(substr(lstval, case level when 1 then 0 else instr(lstval, ';', 1, level - 1) + 1 end, instr(lstval, ';',1 , level) - case level when 1 then 0 else instr(lstval, ';', 1, level - 1) end -1)) ext
FROM (
  SELECT
'42;40;40;39;39;39;39;39;39;39;39;39;38;38;38;38;38;38;38;38;38;38;38;37;37;37;37;37;37;37;37;37;37;37;37;37;37;36;'||
'36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;36;35;35;35;35;35;35;35;35;35;35;35;35;35;35;35;'||
'35;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;34;33;33;33;'||
'33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;33;32;32;32;32;32;32;'||
'32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;32;31;'||
'31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;31;30;30;30;30;30;30;'||
'30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;30;29;29;29;29;29;29;29;29;29;29;29;29;'||
'29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;29;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;'||
'28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;28;27;27;27;27;27;27;27;27;27;27;27;27;'||
'27;27;27;27;27;27;27;27;27;27;27;27;27;27;27;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;26;'||
'26;26;26;26;26;26;26;26;26;25;25;25;25;25;25;25;25;25;25;25;25;25;25;25;25;25;25;25;24;24;24;24;24;24;24;24;24;24;'||
'24;24;24;24;24;24;24;24;24;23;23;23;23;23;23;23;23;23;23;23;23;23;23;23;22;22;22;22;22;22;22;22;22;22;22;22;22;22;'||
'22;21;21;21;21;21;21;21;21;21;21;21;21;21;21;21;21;21;21;20;20;20;20;20;20;20;20;19;19;19;19;19;19;19;18;18;18;18;'||
'18;18;18;17;17;17;17;17;17;17;16;16;16;16;16;15;15;15;15;15;15;15;15;15;14;14;13;13;12;12;12;11;11;11;11;11;11;10;'||
'10;10;9;9;8;8;8;5;5;5;4;3;3;2;1;0;0;' lstval
FROM DUAL
)
CONNECT BY level <= length(lstval) - length(replace(lstval,';',''))

 

Puis découper en déciles :

 

SQL> SELECT buk * 10 as pct, min(n) as nlow, max(n) as nhigh
  2  FROM (
  3     SELECT n, NTILE(10) OVER(ORDER BY n) buk
  4     FROM t
  5  )
  6  GROUP BY buk
  7  ORDER BY buk;

       PCT       NLOW      NHIGH
---------- ---------- ----------
        10          0         18
        20         18         23
        30         23         26
        40         26         28
        50         28         29
        60         29         31
        70         31         32
        80         32         34
        90         34         36
       100         36         42

 

=> NTILE(x) OVER (ORDER BY cols) est une fonction analytique qui pour chaque ligne, donne le x-quantile auquel la ligne appartient, la population étant partitionnée sur les critères "cols"

=> On regroupe les valeurs par quantile pour obtenir les bornes, car c'est ce qui donne de la visibilité sur la répartition.

 

Pensez-y en payant votre soda surtaxé !

Published by Pacman - dans SQL
commenter cet article