Índice de contenidos
- 1. Antes de comenzar
- 2. Evoluciones del estándar
- 3. CTE. Common Table Expressions. WITH
- 4. CTE Recursivas. WITH… LEVEL
- 5. CUBE, ROLLUP y GROUPING SETS
- 6. Funciones de Ventana
- 7. Insertar o actualizar mediante MERGE
- 8. Conclusiones
- 9. Enlaces y referencias
[teaser img=»https://www.adictosaltrabajo.com/wp-content/uploads/2018/03/sql.jpg»]
El objetivo de este post es dar un repaso a las funcionalidades menos conocidas de SQL.
[/teaser]
1. Antes de comenzar
Para los ejemplos, se va a usar la BBDD de ejemplo de https://github.com/datacharmer/test_db
Que tiene el siguiente modelo:
Esta base de datos de ejemplo está hecha para MySQL, pero te costará muy poquito adaptarla al gestor que utilices. Para este ejemplo, se ha adaptado de forma que se ha recreado en un DB2 y el cliente de BBDD que he utilizado en este artículo es DBeaver v3.5.8
2. Evoluciones del estándar
El lenguaje SQL ha sufrido muchas revisiones. La última en 2016.
- SQL-86
- SQL-89
- SQL-92
- SQL-1999
- SQL-2003
- SQL-2008
- SQL-2011
- SQL-2016
La mayoría de gestores de base de datos implementan el último estándar, aunque cada uno tiene sus matices. Sin embargo, el uso mayoritario que se sigue haciendo se corresponde con el de SQL-92.
3. CTE. Common Table Expressions. WITH
SQL-1999 introduce las tablas comunes y consultas recursivas a través de la construcción WITH.
A cierto modo, WITH es como si crease una vista al vuelo y luego se utilizase en una query. En la BBDD que estamos manejando de ejemplo en este artículo, los directores actuales de cada departamento se pueden obtener de la siguiente manera.
|
1 2 3 4 |
SELECT EMP_NO, DEPT_NO FROM DEPT_MANAGER WHERE FROM_DATE < CURRENT_DATE AND TO_DATE > CURRENT_DATE |
Si guardamos el resultado de esta query en una variable, podemos usarla a continuación y hacer cosas del estilo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
WITH managers AS ( SELECT EMP_NO, DEPT_NO FROM DEPT_MANAGER WHERE FROM_DATE < CURRENT_DATE AND TO_DATE > CURRENT_DATE ) SELECT M.EMP_NO, E.FIRST_NAME, E.LAST_NAME, E.GENDER, D.DEPT_NO, D.DEPT_NAME FROM managers M INNER JOIN EMPLOYEES E ON M.EMP_NO=E.EMP_NO INNER JOIN DEPARTMENTS D ON M.DEPT_NO=D.DEPT_NO; |
En este caso, esa consulta se podría resolver de forma más óptima con un INNER JOIN, pero ejemplifica el uso que se puede hacer de la cláusula WITH.
4. CTE Recursivas. WITH… LEVEL
En ocasiones nos encontraremos con estructuras jerárquicas que requieren de consultas con cierta recursividad. Lo bueno de las CTE es que las podemos usar dentro de su propia definición, de forma que ahí tenemos la recursividad.
Por ejemplo, imaginemos que tenemos una tabla donde guardamos el linaje por línea materna.
|
1 2 3 4 5 6 |
CREATE TABLE person ( id INT NOT NULL, name VARCHAR(30) NOT NULL, mother INT NOT NULL, PRIMARY KEY (id) ); |
Definimos una primera mujer a la que llamaremos Eva, y a partir de ahí vamos guardando su descendencia haciendo referencia a quien es su madre.
|
1 2 3 4 5 6 7 8 |
insert into person (id, name, mother) values (1, 'Eva', 0), (2, 'Ana', 1), (3, 'María', 1), (4, 'Rocío', 2), (5, 'Carmen', 2), (6, 'Aurora', 3), (7, 'Pilar', 4); |
Y al hacer una consulta sobre esta tabla, deseamos obtener en una columna su linaje materno desde Eva hasta la madre de cada niña.
Pues podemos utilizar una CTE recursiva para hacerlo, indicando el nivel de profundidad (recursividad) que deseamos emplear, en este ejemplo 4.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
WITH RCTE (id, name, mother, family, level) as ( select root.id, root.name, root.mother, CAST('' AS VARCHAR(50)) as family, 1 as level from PERSON root where root.id=1 union all select child.id, child.name, child.mother, concat(concat(parent.family,' > '), parent.name), parent.level +1 as level from RCTE parent, PERSON child where parent.id = child.mother and parent.level < 4 ) select distinct id, name, mother, family from RCTE |
Y este es el resultado de la consulta.
Este tipo de consultas, se suelen utilizar para obtener estructuras jerárquicas.
5. CUBE, ROLLUP y GROUPING SETS
También la especificación SQL-1999 amplía el significado de la cláusula GROUP BY con los operadores CUBE, ROLLUP y GROUPING SETS.
En el ejemplo que tenemos, vamos a obtener un listado de empleados, con su cargo actual y salario. La siguiente consulta nos podría dar este resultado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select e.emp_no, first_name, last_name, gender, title, salary from titles t inner join employees e on t.EMP_NO = e.EMP_NO inner join salaries s on s.EMP_NO = e.EMP_NO AND current_date > s.from_date AND current_date < s.to_date where current_date > t.from_date AND current_date < t.to_date AND e.emp_no<10021; |
E imaginemos que ahora queremos desglosar los sueldos medios por género y por cargo en la empresa. Podemos agrupar por género y cargo con cubo de la siguiente manera.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select gender, title, avg(salary) from titles t inner join employees e on t.EMP_NO = e.EMP_NO inner join salaries s on s.EMP_NO = e.EMP_NO AND current_date > s.from_date AND current_date < s.to_date where current_date > t.from_date AND current_date < t.to_date AND e.emp_no<10021 group by gender, title with cube; |
Fijémonos en los bloques de null.
Las cuatro primeras filas nos dan el sueldo medio por cargo, independientemente del sexo.
La fila cinco nos da el salario medio independientemente del género (null) y del cargo (null). Es decir, nos da el sueldo medio de este grupo.
La fila 6 y 7 nos dan el salario medio desglosado por sexos independientemente del cargo (null)
Y el resto de filas nos dan el salario medio desglosado por cargo y género.
La opción WITH ROLLUP nos da el mismo resultado, pero sin tener en cuenta las cuatro primeras filas.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select gender, title, avg(salary) from titles t inner join employees e on t.EMP_NO = e.EMP_NO inner join salaries s on s.EMP_NO = e.EMP_NO AND current_date > s.from_date AND current_date < s.to_date where current_date > t.from_date AND current_date < t.to_date AND e.emp_no<10021 group by gender, title with rollup; |
La primera fila nos da el salario medio de esos empleados.
La fila 2 y 3 el salario medio desglosado por género.
Y de la 5 a la 11, el salario medio, desglosado por género y cargo que ocupa.
En el caso de grouping sets, saca la información desglosada por uno y otro campo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select gender, title, avg(salary) from titles t inner join employees e on t.EMP_NO = e.EMP_NO inner join salaries s on s.EMP_NO = e.EMP_NO AND current_date > s.from_date AND current_date < s.to_date where current_date > t.from_date AND current_date < t.to_date AND e.emp_no<10021 group by grouping sets (gender, title); |
6. Funciones de Ventana
Las funciones de ventana fueron introducidas en SQL2003 y posteriormente ampliadas en SQL2008 y nos proporcionan una forma de tratar datos calculados referidos a una ventana de nuestra query.
Por ejemplo, volviendo a la query de nuestro ejemplo que sacaba datos de los empleados, ahora queremos una columna adicional, que de esos datos nos devuelva el salario medio desglosado por género.
Esa columna sería
|
1 |
avg(salary) over (partition by gender) as avg_salary_by_gender |
y la consulta quedaría así:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select e.emp_no, first_name, last_name, gender, title, salary, avg(salary) over (partition by gender) as avg_salary_by_gender from titles t inner join employees e on t.EMP_NO = e.EMP_NO inner join salaries s on s.EMP_NO = e.EMP_NO AND current_date > s.from_date AND current_date < s.to_date where current_date > t.from_date AND current_date < t.to_date AND e.emp_no < 10021; |
Y el resultado sería:
Igual que se calcula la media, se puede calcular también el max, min, sum, etc… así como otras funciones numéricas.
Algunas estrategias de optimización suelen hacer este tipo de queries y por fuera otra query que se queda sólo con un subconjunto de estos datos. Por ejemplo, en este caso sería muy fácil obtener los empleados que cobran por encima de la media según su género.
También se utiliza con frecuencia en combinación con rownumber() para paginar resultados.
Por ejemplo, queremos obtener los empleados ordenados por apellido y nombre, la segunda página, con un tamaño de página 20.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT emp_no, first_name, last_name, gender, title, salary FROM ( SELECT e.emp_no, first_name, LAST_name, gender, title, salary, rownumber() OVER (ORDER BY last_name, first_name) AS rownumber FROM titles t INNER JOIN employees e ON t.EMP_NO = e.EMP_NO INNER JOIN salaries s ON s.EMP_NO = e.EMP_NO AND CURRENT_DATE > s.from_date AND CURRENT_DATE < s.to_date WHERE CURRENT_DATE > t.from_date AND CURRENT_DATE < t.to_date ) WHERE ROWNUMBER >= 21 AND ROWNUMBER < 40 |
Y el resultado quedaría
7. Insertar o actualizar mediante MERGE
Una situación que nos encontramos con frecuencia es que si tenemos un registro en la BBDD debemos actualizarlo, y si no, debemos insertarlo.
La forma habitual es hacer el select del registro y si no nos devuelve resultados, lo insertamos, y si nos devuelve resultados, actualizamos sólo los campos afectados.
En SQL2003 se introdujo la sentencia MERGE que luego se ampliaría en SQL2008 y que nos permite esto mismo en una sentencia:
|
1 2 3 4 5 |
MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1, column2 = value2, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (value1, value2, ...); |
8. Conclusiones
El SQL es más que las sentencias propias de un CRUD. Y cada gestor de BBDD implementa parte o la totalidad del estándar añadiendo sus particularidades. Pero antes que esas particularidades, conveniente conocer el estándar para sacar más partido al lenguaje.
9. Enlaces y referencias
- Puedes descargar este tutorial en PDF:
Autentia – 20180320 – El SQL menos conocido
hola, las consultas del numeral 5. CUBE, ROLLUP y GROUPING SETS no corresponden con el resultado obtenido: las columnas e.emp_no, first_name, last_name no deberían estar y salary debería estar precedida por avg()
Pues tienes toda la razón. Las capturas de pantalla no se corresponden con las consultas. Lo voy a corregir. Muchas gracias por el apunte
Corrí el ejemplo del parentesco y me manda un error de que Los tipos de la parte de delimitador y la parte recursiva de la columna «family» y la consulta recursiva RCTE no coinciden. Perdón, soy nueva usando recursividad y por más que le busco, no hallo por qué ese error
Hola Claudia,
¿qué gestor estás usando? piensa que no todos los gestores implementan exactamente el estándar. Los que probablemente más se acercan al estándar son PostgreSQL y Db2.
Dependiendo del gestor, tendrá sus matices. De hecho, por ejemplo, Oracle usa otro sistema «CONNECT BY PRIOR», etc…