funciones que se pueden utilizar en las cláusulas SELECT y WHERE de la sentencia
SELECT. Mediante estos operadores y funciones construimos expresiones a nivel
de fila. Por ejemplo, en la siguiente sentencia:
se parte de la tabla FACTURAS y se seleccionan las filas que cumplen la condición
de la cláusula WHERE. A continuación, se toma el valor de la fecha de cada fila
seleccionada, se extrae el mes y se muestra éste sin repeticiones.
En este apartado se muestra cómo se pueden realizar operaciones a nivel de
columna, teniendo en cuenta todas las filas de una tabla (sin cláusula WHERE)
o bien teniendo en cuenta sólo algunas de ellas (con cláusula WHERE). Además,
se muestra cómo las funciones de columna se pueden aplicar sobre grupos de
filas cuando se hace uso de la cláusula GROUP BY. Este uso se hace necesario
cuando los cálculos a realizar no son sobre todas las filas de una tabla o sobre
un subconjunto, sino que se deben realizar repetidamente para distintos grupos
de filas.
4.6.1. Funciones de columna
En ocasiones es necesario contar datos: ¿cuántos clientes hay en Castellón?
O también hacer cálculos sobre ellos: ¿a cuánto asciende el IVA cobrado en la
factura 3752? SQL proporciona una serie de funciones que se pueden utilizar en
la cláusula SELECT y que actúan sobre los valores de las columnas para realizar
diversas operaciones como, por ejemplo, sumarlos u obtener el valor máximo
o el valor medio, entre otros. Las funciones de columna más habituales son las
que se muestran a continuación:
COUNT(*) Cuenta filas.
COUNT(columna) Cuenta valores no nulos.
SUM(columna) Suma los valores de la columna.
MAX(columna) Obtiene el valor máximo de la columna.
MIN(columna) Obtiene el valor mínimo de la columna.
AVG(columna) Obtiene la media de los valores de la columna.
Si no se realiza ninguna restricción en la cláusula WHERE de una sentencia
SELECT que utiliza funciones de columna, éstas se aplican sobre todas las filas
de la tabla especificada en la cláusula FROM. Sin embargo, cuando se realiza
una restricción mediante WHERE, las funciones se aplican sólo sobre las filas que
la restricción ha seleccionado.
A continuación, se muestran algunos ejemplos:
-- cantidad media por línea de factura
SELECT AVG(cant)
FROM
lineas_fac;
-- cantidad media por línea de factura del artículo
SELECT AVG(cant)
FROM
lineas_fac
WHERE
codart = ’TLFXK2’;
-- se puede hacer varios cálculos a la vez
SELECT SUM(cant) AS suma, COUNT(*) AS lineas
FROM
lineas_fac;
La función COUNT() realiza operaciones distintas dependiendo de su argu-
mento:
COUNT(*)
Cuenta filas.
COUNT(columna)
Cuenta el número de valores no nulos en la
columna.
COUNT(DISTINCT columna)
Cuenta el número de valores distintos y no
nulos en la columna.
A continuación, se muestra su uso mediante un ejemplo. Se ha creado una
tabla P que contiene los datos de una serie de piezas:
SELECT * FROM P;
y se ha ejecutado la siguiente sentencia:
SELECT COUNT(*) AS cuenta1, COUNT(color) AS cuenta2,
COUNT(DISTINCT color) AS cuenta3
FROM
P;
El resultado de ejecutarla será el siguiente:
cuenta1 | cuenta2 | cuenta3
---------+---------+---------
6 | 5 | 3
A la vista de los resultados se puede decir que cuenta1 contiene el número de
piezas, cuenta2 contiene el número de piezas con color y cuenta3 contiene el
número de colores de los que hay piezas.
Las funciones de columna (SUM, MAX, MIN, AVG) ignoran los nulos, es de-
cir, los nulos no son tenidos en cuenta en los cálculos. Según esto, se plantea la
siguiente pregunta: ¿coincidirá siempre el valor de media1 y media2 al ejecutar
la siguiente sentencia?
SELECT AVG(dto) AS media1, SUM(dto)/COUNT(*) AS media2
FROM
lineas_fac;
La respuesta es negativa, ya que en media1 se devuelve el valor medio de los
descuentos no nulos, mientras que en media2 lo que se devuelve es el valor me-
dio de los descuentos (interpretándose los descuentos nulos como el descuento
cero).
Como se ha visto, la función AVG calcula la media de los valores no nulos
de una columna. Si la tabla de la cláusula FROM es la de artículos, la media
es por artículo; si la tabla de la cláusula FROM es la de facturas, la media es
por factura. Cuando se quiere calcular otro tipo de media se debe hacer el
cálculo mediante un cociente. Por ejemplo, el número medio de facturas por
mes durante el año pasado se obtiene dividiendo el número de facturas del año
pasado entre doce meses:
SELECT COUNT(*)/12 AS media_mensual
FROM
facturas
WHERE
EXTRACT(year FROM fecha) =
EXTRACT(year FROM CURRENT_DATE)-1;
Es importante tener en cuenta que la función COUNT devuelve un entero
y que las operaciones entre enteros devuelven resultados enteros. Es decir, la
operación SELECT 2/4; devuelve el resultado cero. Por lo tanto, es conveniente
multiplicar uno de los operandos por 1.0 para asegurarse de que se opera
con números reales. En este caso, será necesario redondear los decimales del
resultado a lo que sea preciso:
SELECT ROUND(COUNT(*)*1.0/12,2) AS media_mensual
FROM
facturas
WHERE
EXTRACT(year FROM fecha) =
EXTRACT(year FROM CURRENT_DATE)-1;
4.6.2. Cláusula GROUP BY
La cláusula GROUP BY forma grupos con las filas que tienen en común los
valores de una o varias columnas. Sobre cada grupo se pueden aplicar las fun-
ciones de columna que se han estado utilizando hasta ahora (SUM, MAX, MIN,
AVG, COUNT), que pasan a denominarse funciones de grupo. Estas funciones,
utilizadas en la cláusula SELECT, se aplican una vez para cada grupo.
La siguiente sentencia cuenta cuántas facturas tiene cada cliente el año
pasado:
SELECT codcli, COUNT(*)
FROM
facturas
WHERE
EXTRACT(year FROM fecha) =
EXTRACT(year FROM CURENT_DATE)-1
GROUP
BY codcli;
El modo en que se ejecuta la sentencia se explica a continuación. Se toma la
tabla de facturas (FROM) y se seleccionan las filas que cumplen la restricción
(WHERE). A continuación, las facturas se separan en grupos, de modo que en
un mismo grupo sólo hay facturas de un mismo cliente (GROUP BY codcli),
con lo cual hay tantos grupos como clientes hay con facturas del año pasado.
Finalmente, de cada grupo se muestra el código del cliente y el número de
facturas que hay en el grupo (son las facturas de ese cliente): COUNT(*).
62
63
4.6.3. Cláusula HAVING
En la cláusula HAVING, que puede aparecer tras GROUP BY, se utilizan las
funciones de grupo para hacer restricciones sobre los grupos que se han forma-
do. La sintaxis de la sentencia SELECT, tal y como se ha visto hasta el momento,
es la siguiente:
SELECT
[ DISTINCT ] { * | columna [ , columna ] }
FROM
tabla
[ WHERE condición_de_búsqueda ]
[ GROUP BY columna [, columna ]
[ HAVING condición_para_el_grupo ] ]
[ ORDER BY columna [ ASC | DESC ]
[,columna [ ASC | DESC ] ];
En las consultas que utilizan GROUP BY se obtiene una fila por cada uno de
los grupos producidos. Para ejecutar la cláusula GROUP BY se parte de las filas
de la tabla que cumplen el predicado establecido en la cláusula WHERE y se
agrupan en función de los valores comunes en la columna o columnas especifi-
cadas. Mediante la cláusula HAVING se realiza una restricción sobre los grupos
obtenidos por la cláusula GROUP BY, y se seleccionan aquellos que cumplen el
predicado establecido en la condición.
Evidentemente, en la condición de la cláusula HAVING sólo pueden aparecer
restricciones sobre columnas por las que se ha agrupado y también funciones de
grupo sobre cualquier otra columna de la tabla. Lo mismo sucede en la cláusula
SELECT: sólo es posible especificar de manera directa columnas que aparecen
en la cláusula GROUP BY y también funciones de grupo sobre cualquier otra
columna. Cuando en las cláusulas SELECT o HAVING aparecen columnas que
no se han especificado en la cláusula GROUP BY y que tampoco están afectadas
por una función de grupo, se produce un error.
4.6.4. Ejemplos
Ejemplo 4.4 Se quiere obtener el importe medio por factura, sin tener en
cuenta los descuentos ni el IVA.
El importe medio por factura se calcula obteniendo primero la suma del impor-
te de todas las facturas y dividiendo después el resultado entre el número total
de facturas. La suma del importe de todas las facturas se obtiene sumando
el importe de todas las líneas de factura. El importe de cada línea se calcu-
la multiplicando el número de unidades pedidas (cant) por el precio unitario
(precio).
Por lo tanto, la solución a este ejercicio es la siguiente:
SELECT ROUND(SUM(cant*precio)/COUNT(DISTINCT codfac),2)
AS importe_medio
FROM
lineas_fac;
Se ha redondeado a dos decimales porque el resultado es una cantidad en euros.
Ejemplo 4.5 Se quiere obtener la fecha de la primera factura del cliente cuyo
código es el 210, la fecha de su última factura (la más reciente) y el número
de días que han pasado entre ambas facturas.
Como se ha comentado antes, algunas funciones de columna se pueden utilizar
también sobre las fechas. En general, las funciones MIN y MAX pueden usarse
sobre todo aquel tipo de datos en el que haya definida una ordenación: tipos
numéricos, cadenas y fechas.
Ambas funciones sirven, por lo tanto, para obtener la fecha de la primera y
de la última factura. Restando ambas fechas se obtiene el número de días que
hay entre ambas.
SELECT MIN(fecha) AS primera, MAX(fecha) AS ultima,
MAX(fecha) - MIN(fecha) AS dias
FROM
facturas
WHERE
codcli = 210;
Ejemplo 4.6 Se quiere obtener un listado con los clientes que tienen más de
cinco facturas con 18% de IVA, indicando cuántas de ellas tiene cada uno.
Para resolver este ejercicio se deben tomar las facturas (tabla FACTURAS) y
seleccionar aquellas con 18% de IVA (WHERE). A continuación, se debe agru-
par las facturas (GROUP BY) de manera que haya un grupo para cada cliente
(columna codcli). Una vez formados los grupos, se deben seleccionar aquellos
que contengan más de cinco facturas (HAVING). Por último, se debe mostrar
(SELECT) el código de cada cliente y su número de facturas.
SELECT codcli, COUNT(*) AS facturas
FROM
facturas
WHERE
iva = 18
GROUP BY codcli
HAVING COUNT(*) > 5;
Ejemplo 4.7 Se quiere obtener un listado con el número de facturas que hay
en cada año, de modo que aparezca primero el año con más facturas. Además,
para cada año se debe mostrar el número de clientes que han hecho compras y
en cuántos días del año se han realizado éstas.
SELECT EXTRACT(year FROM fecha) AS año,
COUNT(*) AS nfacturas,
COUNT(DISTINCT codcli) AS nclientes,
COUNT(DISTINCT codven) AS nvendedores,
COUNT(DISTINCT fecha) AS ndias
FROM
facturas
GROUP BY EXTRACT(year FROM fecha)
ORDER BY nfacturas DESC;
-- nfacturas es el nombre que se ha dado a COUNT(*)
Como se ve en el ejemplo, es posible utilizar expresiones en la cláusula
GROUP BY. El ejemplo también muestra cómo se puede hacer referencia a los
nombres con que se renombran las expresiones del SELECT, en la cláusula ORDER
BY. Esto es así porque la cláusula ORDER BY es la única que se ejecuta tras el
SELECT.
Ejemplo 4.8 De los clientes cuyo código está entre el 240 y el 250, mostrar
el número de facturas que cada uno tiene con cada IVA distinto.
SELECT codcli, COALESCE(iva,0) AS iva, COUNT(*) AS facturas
FROM
facturas
WHERE
codcli BETWEEN 240 AND 250
GROUP BY codcli, COALESCE(iva,0);
Para resolver el ejercicio, se han agrupado las facturas teniendo en cuenta
dos criterios: el cliente y el IVA. De este modo, quedan en el mismo grupo las
facturas que son de un mismo cliente y con un mismo tipo de IVA. Puesto
que en la base de datos con que se trabaja se debe interpretar el IVA nulo
como cero, se ha utilizado la función COALESCE. Si no se hubiera hecho esto,
las facturas de cada cliente con IVA nulo habrían dado lugar a un nuevo grupo
(distinto del de IVA cero), ya que la cláusula GROUP BY no ignora los nulos
sino que los toma como si fueran todos un mismo valor.
4.6.5. Algunas cuestiones importantes
A continuación se plantean algunas cuestiones que es importante tener en
cuenta cuando se realizan agrupaciones:
Cuando se utilizan funciones de grupo en la cláusula SELECT sin que haya
GROUP BY, el resultado de ejecutar la consulta tiene una sola fila.
A diferencia del resto de funciones que proporciona SQL, las funciones
de grupo sólo se utilizan en las cláusulas SELECT y HAVING, nunca en la
cláusula WHERE.
La sentencia SELECT tiene dos cláusulas para realizar restricciones: WHERE
y HAVING. Es muy importante saber situar cada restricción en su lugar: las
restricciones que se deben realizar a nivel de filas, se sitúan en la cláusula
WHERE; las restricciones que se deben realizar sobre grupos (normalmente
involucran funciones de grupo), se sitúan en la cláusula HAVING.
El modificador DISTINCT puede ser necesario en la cláusula SELECT de
una sentencia que tiene GROUP BY sólo cuando las columnas que se mues-
tren en la cláusula SELECT no sean todas las que aparecen en la cláusula
GROUP BY.
Una vez formados los grupos mediante la cláusula GROUP BY (son grupos
de filas, no hay que olvidarlo), del contenido de cada grupo sólo es posible
conocer el valor de las columnas por las que se ha agrupado (ya que
dentro del grupo, todas las filas tienen dichos valores en común), por lo
que sólo estas columnas son las que pueden aparecer, directamente, en las
cláusulas SELECT y HAVING. Además, en estas cláusulas, se pueden incluir
funciones de grupo que actúen sobre las columnas que no aparecen en la
cláusula GROUP BY.
4.7. Subconsultas
No hay comentarios:
Publicar un comentario