[Info] Produit cumulative en sql

Version imprimable

Salut,

Je veux partager ce script avec vous.

Salim.

Solution avec Model clause

Code:

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:

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:

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

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:

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 !

24/07/2009, 15h41
salim11

Citation:

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

Citation:

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:

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:

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
       ...................................................................
       ....................................................................

Salut Salim11

Je te propose encore une solution

Code:

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>

Salut mnitu;

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

Salim.

Code:

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>

Oui, j'ai triché un peu :oops: :hola: mais je trouve que la solution est assez élégante est simple:

Code:

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

30/07/2009, 17h34
salim11

Citation:

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.
30/07/2009, 17h45
mnitu

Citation:

Envoyé par salim11

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

Salim.

C’est surement une question des goûts. :lol:

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

Code:

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:

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:

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:

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 :)

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:

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>