Salut,

Je veux partager ce script avec vous.

Salim.

Solution avec Model clause
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
WITH t AS
     (SELECT 1 ID, 1 val
        FROM DUAL
      UNION ALL
      SELECT 2 ID, 2 val
        FROM DUAL
      UNION ALL
      SELECT 3 ID, 3 val
        FROM DUAL
      UNION ALL
      SELECT 4 ID, 4 val
        FROM DUAL)
select id,val,cumulative_product
from t
model 
dimension by(id)
measures( val,1 cumulative_product)
(cumulative_product[any]order by id asc=nvl(cumulative_product[cv()-1],1)*val[cv()]);
 
 
        ID        VAL CUMULATIVE_PRODUCT
---------- ---------- ------------------
         1          1                  1
         2          2                  2
         3          3                  6
         4          4                 24
 
 
4 rows selected.
Solution sans model clause( pour des valeurs positives)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
WITH t AS
     (SELECT 1 ID, 1 val
        FROM DUAL
      UNION ALL
      SELECT 2 ID, 2 val
        FROM DUAL
      UNION ALL
      SELECT 3 ID, 3 val
        FROM DUAL
      UNION ALL
      SELECT 4 ID, 4 val
        FROM DUAL)
SELECT ID, val,
       EXP (SUM (LN (val)) OVER (PARTITION BY NULL ORDER BY ID)
           ) cumulative_product
  FROM t;
 
 
        ID        VAL CUMULATIVE_PRODUCT
---------- ---------- ------------------
         1          1                  1
         2          2                  2       -->exp (ln(1)+ln(2)= exp(ln(1))*exp(ln(2))=1*2=2
         3          3         6.00000000 --> exp (ln(1)+ln(2)+ln(3))= exp(ln(1))*exp(ln(2))*exp(ln(3))=1*2*3=6
         4          4         24.0000000 --> exp (ln(1)+ln(2)+ln(3)+ln(4))=exp(ln(1))*exp(ln(2))*exp(ln(3))*exp(ln(4))=1*2*3*4=24
 
 
4 rows selected.
Solution sans model avec des valeurs postives et négatives
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
WITH t AS
     (SELECT 1 AS ID, 1 AS val
        FROM DUAL
      UNION ALL
      SELECT 2, -2
        FROM DUAL
      UNION ALL
      SELECT 3, 3
        FROM DUAL
      UNION ALL
      SELECT 4, 4
        FROM DUAL
      UNION ALL
      SELECT 5, 5
        FROM DUAL
      UNION ALL
      SELECT 6, -4
        FROM DUAL)
SELECT ID, val,
         EXP
            (SUM (LN (ABS (val))) OVER (PARTITION BY NULL ORDER BY ID)
            )
       * CASE
            WHEN MOD
                   (COUNT (CASE
                              WHEN SIGN (val) = -1
                                 THEN 1
                           END) OVER (PARTITION BY NULL ORDER BY ID),
                    2
                   ) = 0
               THEN 1
            ELSE -1
         END cumulative_product
  FROM t;
 
 
        ID        VAL CUMULATIVE_PRODUCT
---------- ---------- ------------------
         1          1                  1
         2         -2                 -2
         3          3         -6.0000000
         4          4         -24.000000
         5          5         -120.00000
         6         -4         480.000000