[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

[Info] Produit cumulative en sql

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager