[11g] Sum et Null

**doc malkovich** · 16/02/2012, 10h03

Bonjour,

La fonction sum() ne prend pas en compte les null s'il y a au moins une valeur renseignée prise en compte.
Je dois faire le contraire, s'il y a au moins une valeur null dans le périmètre je dois renvoyer null, y a t-il un moyen simple de faire ça ? un paramètre de la fonction ? ou faut-il compter le nombre de null ?

Merci

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
with test as
  ( select 252 n from dual union all
    select 43 n from dual union all
    select null n from dual ) 
select
  sum(n)
from test
;
-- resultat=295
-- comment avoir null simplement ?

**mnitu** · 16/02/2012, 10h27

Il n'y pas de paramètre magique. Soit comparer count(*) avec count(n) soit tester si existe valeur null.

**Waldar** · 16/02/2012, 10h28

Oui en effet à la va-vite c'est une idée :

Code :

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

1
2
3
4
5
select case count(*)
         when count(n)
         then sum(n)
       end as res
  from test;

Edit : grilled by mnitu !

**pacmann** · 16/02/2012, 14h38

Salut,

Pour rigoler, mais ça va pas te plaire je pense :

Code :

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

1
2
3
4
5
6
7
 
    WITH test AS
    ( SELECT 252 n FROM dual union ALL
    SELECT 43 n FROM dual union ALL
    SELECT null n FROM dual ) 
    SELECT greatest(1, sign(min(n) keep(dense_rank first order by n nulls first))) * sum(n)
    FROM test

**Glutinus** · 16/02/2012, 15h11

Waouh, au lieu de 5 minutes de code, tu as droit à 10 minutes de code et 15 minutes de commentaires + 45 minutes de justification au CP que c'est la bonne solution ^^

**Rams7s** · 16/02/2012, 15h34

Code :

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

SELECT SUM(n) FROM tmp WHERE 1 NOT IN (SELECT 1 FROM tmp WHERE n IS NULL AND ROWNUM=1);

Comme ca on peut eventuellemt utiliser un index pour savoir tres vite si il va falloir renvoyer null ou pas.

**doc malkovich** · 16/02/2012, 16h34

bon bin merci pour vos réponses

s'il fallait voter je prendrais celle de waldar, qui est la plus simple à expliquer je trouve, désolé pacmann mais j'ai mal à la tête juste à essayer de décortiquer ta formule

Pour ma part j'étais parti sur l'utilisation minimale de caractères, j'étais arrivé à :

Code :

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

1
2
3
4
5
6
7
 
   WITH test AS
    ( SELECT 252 n FROM dual union ALL
    SELECT 43 n FROM dual union ALL
    SELECT null n FROM dual ) 
    SELECT nullif(min(nvl2(n,1,0)),0)*sum(n)
    FROM test

Mais comme dirait Glutinus c'est pas évident à expliquer à un CP ou dans la doc ...
a+

**pacmann** · 16/02/2012, 16h38

Moi perso c'était vraiment juste pour rigoler, je vote aussi pour Waldar bien sûr

Par contre la tienne me semble boiteuse, vu que n = 0 pour toutes les lignes me semble retourner null sur ta formule ?

[EDIT] oubliez, chuis con, j'avais pas vu le 2 du nvl

**mnitu** · 16/02/2012, 16h49

Envoyé par Rams7s

Code :

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

SELECT SUM(n) FROM tmp WHERE 1 NOT IN (SELECT 1 FROM tmp WHERE n IS NULL AND ROWNUM=1);

Comme ca on peut eventuellemt utiliser un index pour savoir tres vite si il va falloir renvoyer null ou pas.

Pourriez-vous essayer de nous démontrer tout ça via un exemple, SVP.

**Rams7s** · 16/02/2012, 17h12

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
TMP@MINILHC >drop table tmp;
 
Table dropped.
 
Elapsed: 00:00:00.07
TMP@MINILHC >create table tmp as select level as n from dual connect by level<1e6;
 
Table created.
 
Elapsed: 00:00:02.03
TMP@MINILHC >insert into tmp values (null);
 
1 row created.
 
Elapsed: 00:00:00.13
TMP@MINILHC >create index tmp_ind on tmp(n,1);
 
Index created.
 
Elapsed: 00:00:04.98
TMP@MINILHC >set autotrace on
TMP@MINILHC >SELECT SUM(n) FROM tmp WHERE 1 NOT IN (SELECT 1 FROM tmp WHERE n IS NULL AND ROWNUM=1);
 
    SUM(N)
----------
 
 
Elapsed: 00:00:03.48
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3592827287
 
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    13 |   454   (2)| 00:00:06 |
|   1 |  SORT AGGREGATE      |         |     1 |    13 |            |          |
|*  2 |   FILTER             |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL | TMP     |   967K|    11M|   444   (3)| 00:00:06 |
|*  4 |    FILTER            |         |       |       |            |          |
|*  5 |     COUNT STOPKEY    |         |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| TMP_IND |    18 |   234 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter( NOT EXISTS (<not feasible>)
   4 - filter(1=1)
   5 - filter(ROWNUM=1)
   6 - access("N" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
         38  recursive calls
          0  db block gets
        273  consistent gets
        286  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
TMP@MINILHC >delete from tmp where n is null;
 
1 row deleted.
TMP@MINILHC >SELECT SUM(n) FROM tmp WHERE 1 NOT IN (SELECT 1 FROM tmp WHERE n IS NULL AND ROWNUM=1);
 
    SUM(N)
----------
5.0000E+11
 
Elapsed: 00:00:11.24
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3592827287
 
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    13 |   454   (2)| 00:00:06 |
|   1 |  SORT AGGREGATE      |         |     1 |    13 |            |          |
|*  2 |   FILTER             |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL | TMP     |   967K|    11M|   444   (3)| 00:00:06 |
|*  4 |    FILTER            |         |       |       |            |          |
|*  5 |     COUNT STOPKEY    |         |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| TMP_IND |    18 |   234 |    10   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter( NOT EXISTS (<not feasible>)
   4 - filter(1=1)
   5 - filter(ROWNUM=1)
   6 - access("N" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1549  consistent gets
       1239  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Edit: Si l'index est cache, c'est encore plus mieux

**Waldar** · 16/02/2012, 22h08

Attention ce n'est pas le même résultat, la requête que j'ai proposée (la même que celle évoquée par mnitu) renvoie une ligne qui contient la valeur nulle et si je ne dis pas de bêtise celle de Rams7s ne renvoie pas de ligne.

Edit : après vérification, j'ai bien dit une bêtise !

**mnitu** · 16/02/2012, 22h09

L'index tmp_ind est un index basé sur une fonction.

De plus je vous propose un exemple un peu plus réaliste

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
 
ConnectÚ Ó :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
mni@DIANA> create table tmp as
  2  select rownum as id,
  3         object_name as some_text,
  4         object_type as some_type,
  5         case
  6           when mod(rownum,10) = 0 Then Null
  7           Else trunc(dbms_random.value(1,10))
  8         end As n
  9    from all_objects
 10  Where rownum <= 70000
 11  /
 
Table crÚÚe.
 
mni@DIANA>
mni@DIANA> CREATE INDEX tmp_ind ON tmp(some_type,n,1)
  2  /
 
Index crÚÚ.
 
mni@DIANA>
mni@DIANA> exec dbms_stats.gather_table_stats(user,'TMP')
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
mni@DIANA>
mni@DIANA> set autotrace on
mni@DIANA>
mni@DIANA> SELECT case count(*)
  2           when count(n)
  3           then sum(n)
  4         end AS res
  5    FROM tmp
  6  where some_type = 'TABLE'
  7  /
 
       RES
----------
 
 
 
--Plan d'exécution
----------------------------------------------------------
Plan hash value: 444391475
 
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    12 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |    12 |            |          |
|*  2 |   INDEX RANGE SCAN| TMP_IND |  1842 | 22104 |     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SOME_TYPE"='TABLE')
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
mni@DIANA>
mni@DIANA> SELECT  Sum(n)
  2    FROM tmp t
  3  where some_type = 'TABLE'
  4    and 1 not in (Select 1 from tmp t1 where t1.some_type = t.some_type and n is null And rownum = 1)
  5  /
 
    SUM(N)
----------
 
 
 
--Plan d'exécution
----------------------------------------------------------
Plan hash value: 2092370764
 
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    12 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |         |     1 |    12 |            |          |
|*  2 |   INDEX RANGE SCAN   | TMP_IND |    92 |  1104 |     8   (0)| 00:00:01 |
|*  3 |    FILTER            |         |       |       |            |          |
|*  4 |     COUNT STOPKEY    |         |       |       |            |          |
|*  5 |      INDEX RANGE SCAN| TMP_IND |     2 |    24 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("SOME_TYPE"='TABLE')
       filter( NOT EXISTS (<not feasible>)
   3 - filter(1=1)
   4 - filter(ROWNUM=1)
   5 - access("T1"."SOME_TYPE"=:B1 AND "N" IS NULL)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

**laurentschneider** · 17/02/2012, 16h59

pour le sport

Code :

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

SELECT nanvl(sum(nvl(n,binary_double_nan)),null) FROM test

[11g] Sum et Null

SQL Oracle

Discussions similaires

Partager

Partager