Discussion :

[Invité]

Bonjour,

je bloque sur une requête SQL. Actuellement j'ai une table avec des articles avec comme données :
- Le code de l'article
- Son parent s'il y a lieu
- Coefficient

j'arrive à récupérer la liste des différents articles en partant d'une branche de l'arborescence.

select A1.CODE_ARTICLE, A1.COEFFICIENT
FROM ARTICLE A1
start with (A1.CODE_ARTICLE IN ('XXXXXXXXX') and A1.COEFFICIENT!=0)
connect by prior A1.CODE_PARENT=A1.CODE_ARTICLE

Par compte j'aimerai récupérer le coefficient total à coté de chaque article. Exemple :
ARTICLE X | COEF 2
|- ARTICLE XX | COEF 3
|----| ARTICLE XXX | COEF 4

Résultat de la requette :
ARTICLE X | 2
ARTICLE XX | 6
ARTICLE XXX | 24

Comment faire cela ? Est-ce faisable avec 1 requête ?

Nebuka

[pacmann]

Salut !

Tu veux faire le produit des coefficients, c'est ça ?
Il n'y a pas de fonctions pour cela... et le WITH recursif n'est prévu que pour une version future d'Oracle (je ne sais plus laquelle, mais c'est Waldar qui le disait). Donc en attendant, comme tes coefs sont positifs, tu peux faire de la transformation ln / exp :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SQL> ed
Wrote file afiedt.sql
 
  1  WITH t as (
  2  SELECT 'TonGroup' as g, level as niv, 2*level as coeff, dbms_random.string('A', 5) as code_article from dual connect by level <= 4
  3  )
  4  select t.*, exp(sum(ln(coeff)) over (partition by g order by niv) ) produit
  5* from t

G NIV COEFF CODE_ PRODUIT
-------- ---------- ---------- ----- -------
TonGroup 1 2 pRsYU 2
TonGroup 2 4 mohwS 8
TonGroup 3 6 pNHzf 48
TonGroup 4 8 PWJxv 384

(Dans le WITH, j'ai essayé de mettre un truc qui ressemble au résultat de ta requête de base)

[GoLDoZ]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

exp(sum(ln(coeff)) over (partition by g order by niv) )

Bien vu le exp(sum(ln)), je n'y avais pas pensé, je le note précieusement.

J'ai juste une question: EXP est "à l'extérieur du OVER" alors que SUM et LN sont "à l'intérieur". Est-ce parce que ce n'est pas une fonction analytique et que SUM en est une?

[pacmann]

C'est à peu près ça... mais je dirais plutôt fonction "d'aggrégation".
sum => fait la somme sur plein de lignes (ici sur une fenêtre)
ln => applique sur une seule valeur, mais sur la ligne unitaire
exp => applique sur une seule valeur, mais sur le résultat du grouppement (la somme)

(Ne te laisses pas troubler par la fonction analytique : tu as le même genre de problèmatiques avec un simple GROUP BY)

[EDIT]
Tiens au passage, la formule générique si les termes ne sont pas positifs :
Faire le produit des valeurs absolues, puis déduire le signe par le nombre de négatifs. (-1^n)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
exp(sum(ln(abs(coeff))) over (partition BY g ORDER BY niv) )
* 
(1 - 2 * mod(sum(case when coeff < 0 then 1 else 0 end), 2))

[GoLDoZ]

C'est à peu près ça... mais je dirais plutôt fonction "d'aggrégation".

C'est exactement cela. Merci pour l'explication et pour la formule générique.

[Waldar]

et le WITH recursif n'est prévu que pour une version future d'Oracle (je ne sais plus laquelle, mais c'est Waldar qui le disait).

C'est Laurent Schneider qui le tenait de Vadim Tropashko (développeur chez Oracle), mais ce n'est plus pour une version future, c'est pour 11gR2 sortie récemment (pas sur toutes les plateformes).

[GoLDoZ]

Pacmann, je reviens sur ta formule générique car elle me pose problème:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
WITH t AS
     (SELECT     'TonGroup' AS g, LEVEL AS niv, 2 * LEVEL AS coeff,
                 DBMS_RANDOM.STRING ('A', 5) AS code_article
            FROM DUAL
      CONNECT BY LEVEL <= 4)
SELECT t.*,
         EXP (SUM (LN (ABS (coeff))) OVER (PARTITION BY g ORDER BY niv))
       * (1 - 2 * MOD (SUM (CASE WHEN coeff < 0 THEN 1 ELSE 0 END), 2))
  FROM t;

ORA-00937: La fonction de groupe ne porte pas sur un groupe simple

Ce qui est logique vu les deux sommes. Mais je n'arrive pas à récupérer le signe d'une autre façon...

[pacmann]

J'ai un peu baclé effectivement : l'ajustement du signe se fait bien entendu sur la même fenêtre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
WITH t AS
     (SELECT     'TonGroup' AS g, LEVEL AS niv, 2 * LEVEL AS coeff,
                 DBMS_RANDOM.STRING ('A', 5) AS code_article
            FROM DUAL
      CONNECT BY LEVEL <= 4)
SELECT t.*,
         EXP (SUM (LN (ABS (coeff))) OVER (PARTITION BY g ORDER BY niv))
       * (1 - 2 * MOD (SUM (CASE WHEN coeff < 0 THEN 1 ELSE 0 END) OVER (PARTITION BY g ORDER BY niv), 2)) 
  FROM t;

Tiens Waldar, je me souvenais avoir lu quelque part qu'il allait y avoir une fonction "intégrée" Eval...
Mais je la trouve plus, donc je me demande si je n'ai pas juste fantasmé.
Ca te dit quelque chose ?

Parce que dans ce cas, on pourrait faire du eval(listagg(...) over(...))

[GoLDoZ]

Arf! Je n'ai pas encore l'habitude d'utiliser ces fonctionnalités. Merci