[Info] Produit cumulative en sql

**salim11** · 24/07/2009, 14h28

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

**Waldar** · 24/07/2009, 15h15

Hello salim,

Je m'en étais servi de la seconde méthode pour calculer les factoriels pour le NoCOUG challenge.

J'avais rajouté un arrondi pour éviter les erreurs de précisions induis par LN et de EXP :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
WITH fact AS
(
SELECT 0 AS l, 1 AS f
FROM DUAL
UNION ALL
SELECT
    LEVEL AS l,
    ROUND( EXP( SUM (LN (LEVEL)) OVER (ORDER BY LEVEL ASC))) AS f
FROM DUAL
CONNECT BY LEVEL <= 83
)
select * from fact

Au-dessus de 83 ça plante par contre, et côté arrondi ce n'est pas toujours parfait !

**salim11** · 24/07/2009, 15h41

Envoyé par Waldar

Hello salim,
Au-dessus de 83 ça plante par contre, et côté arrondi ce n'est pas toujours parfait !

Salut waldar,

Oui, t'as raison.

Salim

**salim11** · 27/07/2009, 20h33

Envoyé par Waldar

Hello salim,

Je m'en étais servi de la seconde méthode pour calculer les factoriels pour le NoCOUG challenge.

J'avais rajouté un arrondi pour éviter les erreurs de précisions induis par LN et de EXP :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
WITH fact AS
(
SELECT 0 AS l, 1 AS f
FROM DUAL
UNION ALL
SELECT
    LEVEL AS l,
    ROUND( EXP( SUM (LN (LEVEL)) OVER (ORDER BY LEVEL ASC))) AS f
FROM DUAL
CONNECT BY LEVEL <= 83
)
select * from fact

Au-dessus de 83 ça plante par contre, et côté arrondi ce n'est pas toujours parfait !

Salut Waldar,

On peut remédier à cette situation:

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
 
DROP TABLE DATA;
 
CREATE TABLE DATA
(
  ID   NUMBER,
  val  BINARY_DOUBLE
);
 
INSERT INTO DATA
   (SELECT 0 AS l, 1 AS f
      FROM DUAL
    UNION ALL
    SELECT     LEVEL AS l, LEVEL f
          FROM DUAL
    CONNECT BY LEVEL <= 300);
 
 
SELECT ID AS l, EXP (SUM (LN (val)) OVER (ORDER BY ID ASC)) val
  FROM DATA;
 
       ...................................................................
       ...................................................................
       ...................................................................
       ...................................................................
       163                                         2.00440157654583E291
       164                                         3.28721858553507E293
       165                                          5.4239106661326E295
       166                                         9.00369170577961E297
       167                                         1.50361651486524E300
 
         L                                                          VAL
---------- ------------------------------------------------------------
       168                                         2.52607574497356E302
       169                                          4.2690680090055E304
       170                                         7.25741561530971E306
       171                                                          INF
       172                                                          INF
       173                                                          INF
       174                                                          INF
       175                                                          INF
       ...................................................................
       ....................................................................

**mnitu** · 30/07/2009, 16h38

Salut Salim11

Je te propose encore une solution

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
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0 
Connected as mni
 
SQL> 
SQL> WITH t AS
  2       (SELECT 1 ID, 1 val
  3          FROM DUAL
  4        UNION ALL
  5        SELECT 2 ID, 2 val
  6          FROM DUAL
  7        UNION ALL
  8        SELECT 3 ID, 3 val
  9          FROM DUAL
 10        UNION ALL
 11        SELECT 4 ID, 4 val
 12          FROM DUAL)
 13  SELECT id,val,cumulativeproduct(val) over(order by id)
 14  FROM t
 15  /
 
        ID        VAL CUMULATIVEPRODUCT(VAL)OVER(ORD
---------- ---------- ------------------------------
         1          1                              1
         2          2                              2
         3          3                              6
         4          4                             24
 
SQL>

**salim11** · 30/07/2009, 17h09

Salut mnitu;

Bizarre, il ne reconnait pas cette fonction, est ce que c’est une fonction non documentée d’oracle.

Salim.

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
 
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
 
SQL> select id,cumulativeproduct(val)over(order by id) from data;
select id,cumulativeproduct(val)over(order by id) from data
          *
ERREUR à la ligne 1 :
ORA-00904: "CUMULATIVEPRODUCT" : identificateur non valide
 
 
SQL>

**mnitu** · 30/07/2009, 17h25

Oui, j'ai triché un peu

mais je trouve que la solution est assez élégante est simple:

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
 
create type CumulativeProductImpl as object
(
  prod NUMBER,  
  static function ODCIAggregateInitialize(sctx IN OUT CumulativeProductImpl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT CumulativeProductImpl, 
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN CumulativeProductImpl, 
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT CumulativeProductImpl, 
    ctx2 IN CumulativeProductImpl) return number
);
/
create or replace type body CumulativeProductImpl is 
static function ODCIAggregateInitialize(sctx IN OUT CumulativeProductImpl) 
return number is 
begin
  sctx := CumulativeProductImpl(1);
  return ODCIConst.Success;
end;
 
member function ODCIAggregateIterate(self IN OUT CumulativeProductImpl, value IN number) return number is
begin
  self.prod := self.prod * value;
  return ODCIConst.Success;
end;
 
member function ODCIAggregateTerminate(self IN CumulativeProductImpl, returnValue OUT number, flags IN number) return number is
begin
  returnValue := self.prod;
  return ODCIConst.Success;
end;
 
member function ODCIAggregateMerge(self IN OUT CumulativeProductImpl, ctx2 IN CumulativeProductImpl) return number is
begin
  self.prod := self.prod * ctx2.prod;
  return ODCIConst.Success;
end;
end;
/
 
CREATE FUNCTION CumulativeProduct (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING CumulativeProductImpl;
/

A voir User-Defined Aggregates

**salim11** · 30/07/2009, 17h34

Envoyé par mnitu

mais je trouve que la solution est assez élégante est simple:

A voir User-Defined Aggregates

Sincèrement, je la trouve ni simple ni élégante.

Salim.

**mnitu** · 30/07/2009, 17h45

Envoyé par salim11

Sincèrement, je la trouve ni simple ni élégante.

Salim.

C’est surement une question des goûts.

**skuatamad** · 18/10/2011, 22h44

Une autre solution à partir de la 11GR2 à base de WITH recursif :

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
SQL>   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
),
       t_rn as (
select t.id, t.val,
       row_number() over (order by id) as rn /*Pour le cas général où les id ne se suivent pas */
  from t
),
       recursif_cumulative_produit (rn, id, val, cumulative_product) as (
select rn, id, val, val
  from t_rn
 where rn = 1
 union all
select t_rn.rn, t_rn.id, t_rn.val, t_rn.val*rcp.cumulative_product
  from t_rn
  join recursif_cumulative_produit rcp on t_rn.rn = rcp.rn + 1
)
select id, val, cumulative_product
  from recursif_cumulative_produit
/
 
        ID        VAL CUMULATIVE_PRODUCT
---------- ---------- ------------------
         1          1                  1
         2          2                  2
         3          3                  6
         4          4                 24
 
SQL>

Et aussi une astuce avec sys_connect_by_path et une fonction PL/SQL (donc un peu en dehors des règles de bases mais dans le soucis de faire de ce post une source complète sur le sujet)

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION cumulative_product(p_product IN VARCHAR2)
RETURN NUMBER IS
 TOTAL NUMBER ;
BEGIN
  EXECUTE IMMEDIATE 'BEGIN :b1 := ' || p_product || ' ; END;' USING OUT TOTAL ;
  RETURN TOTAL;
end;
/

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
SQL> 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
),
       t_rn as (
select t.id, t.val,
       row_number() over (order by id) as rn /*Pour le cas général où les id ne se suivent pas */
  from t
)
 select id, val,
        cumulative_product(substr(sys_connect_by_path(val,'*'),2)) as cumulative_product
   from t_rn
  start with rn = 1
connect by prior rn + 1 = rn
/
 
        ID        VAL CUMULATIVE_PRODUCT
---------- ---------- ------------------
         1          1                  1
         2          2                  2
         3          3                  6
         4          4                 24
 
SQL>

PS : Pour bénéficier du cache de sous-requête scalaire (si tant est que c'est nécessaire...) il faudrait passer par une sous-requête suplémentaire :

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
SQL> 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
),
       t_rn as (
select t.id, t.val,
       row_number() over (order by id) as rn /*Pour le cas général où les id ne se suivent pas */
  from t
),
        t2 as (
 select id, val,
        substr(sys_connect_by_path(val,'*'),2) as cumulative_connect
   from t_rn
  start with rn = 1
connect by prior rn + 1 = rn
)
 select id, val,
        (select cumulative_product(cumulative_connect) from dual) as cumulative_product
   from t2
/
        ID        VAL CUMULATIVE_PRODUCT
---------- ---------- ------------------
         1          1                  1
         2          2                  2
         3          3                  6
         4          4                 24
 
SQL>

PS2 : Concernant la fonction PL/SQL il me semble que c'est la bonne approche pour limiter le parse.
Mais si vous avez des commentaires à ajouter, vous êtes bien venu

**skuatamad** · 30/08/2013, 17h48

Concernant la solution PL/SQL, son gros défaut est la surcharge du shared pool (lire les commentaires du blog de tom kyte ci-dessous).

Par contre, à partir de la 11G, on peut évaluer la multiplication avec xmlquery :
Evaluating an expression, like a calculator...

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
SQL> WITH t AS (
  2  SELECT 1 ID, 1 val FROM DUAL UNION ALL
  3  SELECT 2 ID, 2 val FROM DUAL UNION ALL
  4  SELECT 3 ID, 3 val FROM DUAL UNION ALL
  5  SELECT 4 ID, 4 val FROM DUAL
  6  ),
  7         t_rn AS (
  8  SELECT t.id, t.val,
  9         row_number() over (ORDER BY id) AS rn /*Pour le cas général où les id ne se suivent pas */
 10    FROM t
 11  )
 12   SELECT id, val,
 13          xmlquery(substr(sys_connect_by_path(val,'*'),2) returning content).getNumberVal() AS cumulative_product
 14     FROM t_rn
 15    start WITH rn = 1
 16  connect BY prior rn + 1 = rn
 17  /
 
              ID              VAL CUMULATIVE_PRODUCT
---------------- ---------------- ------------------
               1                1                  1
               2                2                  2
               3                3                  6
               4                4                 24
 
SQL>

[Info] Produit cumulative en sql

SQL Oracle

Discussions similaires

Partager

Partager