Funciones de agregación, nulos y matemáticas.

El tratamiento que hace Oracle de los valores nulos junto al comportamiento de las funciones de agregación puede llevar a ciertas confusiones a la hora de efectuar cálculos.

Uno puede pensar que las sumas y restas son operaciones conmutativas, es decir, si tenemos tres valores numéricos como columnas de una tabla (id1, id2, id3) el sumatorio de la operación en cada fila, sum((id1 – (id2 + id3)), debe ser igual a la misma operación sobre los tres sumatorios, sum(id1) – (sum(id2) + sum(id3)). Pero esto puede muy bien no ser así:

sql> create table a (id1 number, id2 number, id3 number);

Tabla creada.

sql> insert into a values (1,null,null);

1 fila creada.

sql>  insert into a values (null,2,null) ;

1 fila creada.

sql> insert into a values (null,null,3);

1 fila creada.

sql> insert into a values (4,4,4);

1 fila creada.

sql>  SELECT SUM(RESULT_TMP) RESULT
2     FROM ( SELECT id1 - (id2 + id3) RESULT_TMP
3              FROM a);

RESULT
----------
        -4

sql> SELECT SUM(id1) - (SUM(id2) + SUM(id3)) RESULT
2    FROM a;

RESULT
----------
        -8

sql>

¡Pero cómo es posible! Yo sé que las sumas y restas son conmutativas…

Y lo son. La explicación está en que los valores nulos hacen que el resultado de una operación que los contenga arroje nulo como resultado, mientras que las funciones de agregación (sum() en este caso, pero también count(), por ejemplo) tienen en cuenta los valores no nulos, obviando los nulos.

En el ejemplo anterior, la primera ‘SELECT’ hace algo asi como:

  1  - ( NULL + NULL) = NULL
NULL - (   2  + NULL) = NULL
NULL - ( NULL +   3 ) = NULL
  4  - (   4  +   4 ) =  -4
                      ______
Resultado:               -4

Mientras que la segunda hace:

  1  + NULL + NULL + 4  =   5
NULL +   2  + NULL + 4  =   6
NULL + NULL +   3  + 4  =   7
                       ______
Resultado: 5 - ( 6 + 7) =  -8

Así que: tranquilidad. Oracle respeta la conmutatividad de las sumas, sólo que hay que tener en cuenta los nulos:

sql>  SELECT SUM(RESULT_TMP) RESULT
2     FROM ( SELECT NVL(id1,0) - (NVL(id2, 0) + NVL(id3, 0)) RESULT_TMP
3              FROM a);

RESULT
----------
        -8

sql>

Saludos.

Carlos.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: