Salut,
Je veux partager ce script avec vous.
Salim.
Solution avec Model clause
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
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 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 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.
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
Partager