[11g] Sum et Null

Version imprimable

[11g] Sum et Null

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:

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 ?

16/02/2012, 10h27
mnitu

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

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

1 2 3 4 5 select case count(*) when count(n) then sum(n) end as res from test;
Edit : grilled by mnitu !

Salut,

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

Code:

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

16/02/2012, 15h11
Glutinus

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 ^^
16/02/2012, 15h34
Rams7s

Code:

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.
16/02/2012, 16h34
doc malkovich
bon bin merci pour vos réponses :calim2:

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

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

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+
16/02/2012, 16h38
pacmann

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 :)
16/02/2012, 16h49
mnitu

Citation:

Envoyé par Rams7s

Code:

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.

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

16/02/2012, 22h08
Waldar

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 !

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

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

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

17/02/2012, 16h59
laurentschneider

pour le sport

Code:

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