6. Consultas Avanzadas con SQL¶
Este tema es una continuación de lo ya visto en Consultando con SQL. Aprenderás, usando el SGBD Relacional PostgreSQL, a:
Conocer en detalle la sintaxis de SELECT en Postgres
Usar expresiones de tablas basadas en combinaciones de tablas
Conocer aspectos más avanzados de consultas SQL
Esfuerzo Necesario
El curso está organizado en 8 sesiones de clase. Cada clase (sesión) implica una dedicación de entre 2 y 4 horas.
La dedicación depende del conocimiento previo, motivación y capacidad de aprendizaje del estudiante para esa sesión en concreto.
Video Clase
6.1. Consultas SQL con una sola tabla¶
Video Clase
Repasa lo que ya hemos visto del lenguaje SQL (Consultando con SQL)
Las consultas se componen generalmente de:
Lo que se selecciona (select_list). Campos, cálculos, constantes o funciones que devuelven valores
El origen de la consulta (table_expression). Tablas básicas, relacionadas (JOIN), subconsultas, etc
En qué orden lo muestra (sort_especification). Ordenamiento de los campos resultado de las consultas
La Sintaxis SQL SELECT en Postgresql (sql-select) incluye varios conceptos:
expression (sql-expressions). Puede ser muchas cosas: constantes, cálculos, nombres de campos/columnas, funciones, etc
condition. Es una expresión cuyo resultado es boolean (t/f)
grouping_element. Campos para funciones de agrupamiento
output_name / table_name / function_name / alias_name . Nombres de objetos o alias a objetos
from_item. Origen de datos, sea una tabla o varias tablas. Puede no haber ningún origen de datos.
join_type. Tipos de combinaciones de tablas
Las consultas pueden ser desde muy simples hasta más complejas (y lleva tiempo diseñarlas).
-- Una muy simple select 1 as Ejemplo; -- Una más compleja: SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit FROM products p LEFT JOIN sales s USING (product_id) WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks' GROUP BY product_id, p.name, p.price, p.cost HAVING sum(p.price * s.units) > 5000;
Ejercicios SQL
Para los ejercicios de repaso trabajas sobre Base de datos Hubway, en su versión Postgres.
Usa el listado de Ejercicios para usar SQL pero ahora usando la sintaxis SQL de Postgres.
6.2. Construyendo una consulta en SQL¶
Video Clase
Definimos una selección de columnas (value expressions) para seleccionar la información a mostrar (queries-select-lists)
Define qué campos (o columnas) se van a seleccionar
Incluye expresiones de valor, o sea que el resultado es un valor
22 o count(campo) o tabla1.campo2 o ...
Es muy útil poner nombres a las columnas (para ayudar en la visualización u operar dentro de la consulta)
La selección es sobre un origen de datos «table expression», es decir una expresión que da como resultado una tabla ( queries-table-expressions). Por ejemplo:
Una tabla completa p.ej.
databasename.schemaname.tablaejemplo
o parcial (sólo unos campos)Una expresión, que de como resultado una tabla p.ej.
tabla1 cross join tabla2 where tabla1.id = tabla2.id and tabla3.edad > 50
Un conjunto de valores (que da como resultado una tabla), p.ej
values (1,'ejemplo'), (2,'otro')
Es muy útil usar alias en las tablas (AliaS): permite usarlas en la consulta
select t1.id from ejemplo AS t1;
Se calcula la tabla (temporal) sobre la que se seleccionarán las columnas:
FROM table_reference [, table_reference [, …]]. Una (o más) tablas base o procesadas que generan la tabla (virtual) para consultar.
WHERE search_condition. Condición lógica que deben cumplir los registros
GROUP BY. Definición de la expresión a partir de la cual se agrupan (grouping_elements)
HAVING. Condición sobre los datos agregados
Y finalmente se puede modificar cómo se mostrará la búsqueda:
Ordenándola (ORDER BY)
Limitándola (LIMIT) y Ajustándola (OFFSET)
Ejercicios SQL
Para los ejercicios de repaso trabajas sobre Base de datos Hubway, en su versión Postgres.
Usa el listado de Ejercicios para usar SQL pero ahora usando la sintaxis SQL de Postgres.
6.3. Combinando consultas¶
Video Clase
Se usan los operadores conjunto (queries-union)
UNION
INTERSECT
EXCEPT
Las consultas tienen que ser «union compatible»:
Igual número de columnas
Tipos compatibles (no tienen porque ser 100% iguales) para los valores de las columnas
Uso del operador ALL:
Modifica el comportamiento (devuelve los duplicados, si los hubiere)
UNION ALL.
INTERSECT ALL.
EXCEPT ALL.
Usa paréntesis para organizar las consultas y evitar malentendidos (o errores de sintaxis)
SELECT nombre FROM clientes UNION SELECT nom FROM vecinos LIMIT 10; (SELECT nombre FROM clientes UNION SELECT nom FROM vecinos) LIMIT 10; -- Es igual que la anterior SELECT nombre FROM clientes UNION (SELECT nom FROM vecinos LIMIT 10); -- Es diferente a las dos primeras
Uniendo Consultas
Crea una tabla nueva (tabla1) que sea el resultado de unir dos consultas de dos campos, en concreto:
Todos los campos
Sólo los comunes
Sólo los que no son comunes
Crea una tabla nueva que sea el resultado de unir dos consultas con 4 campos, en concreto:
Todos los campos
Sólo los comunes
Sólo los que no son comunes
6.4. Consultando el valor NULL¶
Video Clase
El valor NULL
La columna no tiene ningún valor (es desconocido)
Es diferente de que sea una cadena vacía (y no se distingue a simple vista)
Utilidad del valor null
Insertar valores nulos en una tabla cuando no estemos claros (mejor que cadena vacia)
Uso en las restricciones del modelo de datos:
Restricción NOT NULL: el campo no puede tener valores nulos
Restricción DEFAULT expression: valor predeterminado que nos interese si no se aporta
create table ejemplo ( n integer NOT NULL DEFAULT 0, s varchar(10) DEFAULT 'Vacío' );
Nos permite detectar información en la que, probablemente haya que actuar.
Es necesario saber qué valores nulos hay en la tabla
Uso de IS NULL y de IS NOT NULL
Hay que decidir si se permiten o no (restricción NOT NULL)
Influyen en las consultas, y hay que tener cuidado con ellos
Detectando valores NULL
Trabajas, en la versión Postgresql de la BD Base de datos Hubway.
¿Hay valores nulos?
¿En qué campos están?
¿Cómo modificas el diseño físico para que no se acepten valores nulos?
¿Puedes actualizar todos los (miles de) registros para que la BD quede en un estado «más fiable»?
¿Qué decisiones vas a tomar para actualizar esos datos?
¿Qué hacemos con los valores nulos de start_station y end_station ?
¿Qué hacemos con los valores nulos de bike_number?
¿Y los de sub_type?
¿Y los de birth_date?
¿Y los de gender?
6.5. Combinación de Tablas (JOIN)¶
Video Clase
La cláusula JOIN (sql_join):
Combina varias tablas que …
Están relacionadas a través de una condición (o varias)
Es muy útil cuando hay FK (pero no es obligatorio usar las FK )
Pueden ser varias tablas relacionadas entre sí, no sólo dos
Combina tablas añadiendo campos, mientras que UNION/EXCEPT/INTERSECT unen registros sobre los mismos campos (ojo a la diferencia)
Hay diferentes tipos de JOIN:
(INNER) JOIN -> sql_join_inner
LEFT OUTER JOIN -> sql_join_left
RIGHT OUTER JOIN -> sql_join_right
FULL OUTER JOIN -> sql_join_full
Un caso especial: SELF JOIN -> sql_join_self
Ojo a los campos nulos (NULL) que se generan al combinar tablas donde no puede asignar valores.
Algunos ejemplos de JOIN:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; -- Útil usar alias -- A veces es más claro con where y condición (ojo al rendimiento) -- En este caso la combinación es CROSS JOIN (tablas separadas por comas) SELECT O.OrderID, C.CustomerName, O.OrderDate FROM Orders O, Customer AS C WHERE O.CustomerID = C.CustomerID;
-- Combinación de todos los campos de todas las tablas -- Los campos que no se "cruzan" tienen valor NULL SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName;
Trabajando con combinación de tablas (JOIN)
Haz los ejercicios de consulta multitabla en Ejercicios para usar SQL
6.6. Seleccionando los campos a mostrar¶
Video Clase
Se usan «value expressions», es decir, cualquier tipo de cálculo, que como resultado, da un valor (sql-expressions).
Lo básico:
Todos los campos (*)
Seleccionar campos concretos de la tabla/s separados por comas
Usando Etiquetas (Alias) de columnas: AS (opcional)
SELECT * FROM Customers; SELECT ContactName, City FROM Customers; SELECT C.ContactName AS Contacto, City Ciudad FROM Customers C;
-
Mismo nombre en diferentes tablas (tabla1.* vs tabla2.*)
Constantes (números, cadenas, fechas)
Funciones (que devuelven valores)
Y … más (sql-expressions)
Uso de DISTINCT vs ALL
ALL. Todos los valores seleccionados (comportamiento predeterminado)
DISTINCT. Solo los valores distintos
de toda la fila
o sobre algunos campos vía DISTINCT on (expression)
SELECT city FROM Customers; SELECT DISTINCT city FROM Customers; SELECT count(city) FROM Customers; SELECT count(distinct city) FROM Customers;
Concatenación de campos:
Permite «crear informes sencillos»
Usa el operador concatenación
||
SELECT (CustomerName || ' tiene como contacto a ' || ContactName || ' y vive en ' || City) AS Informe FROM Customers;
Ejercicios SQL
Haz los ejercicios de Ejercicios de SQL sobre BD Northwind (W3Schools)
6.7. Usando Subconsultas¶
Video Clase
Se usan consultas dentro de consultas (subquery)
Como expresiones de valor: el resultado de la consulta es un valor
SELECT codigo FROM Pedidos WHERE precio > (SELECT max(precio) FROM OtrosPedidos);
Como expresiones de tabla: el resultado de la consulta es una tabla
Y también como uso de expresiones de consulta (subquery expressions : functions-subquery)
Devuelven true/false como resultado de una operación con una subconsulta.
EXISTS (subquery): Devuelve true/false si existe al menos un resultado (o no)
SELECT col1 FROM tab1 WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2); -- Se usa select 1, para no malgastar tiempo de proceso seleccionando campos -- sólo necesita saber que existe algún resultado
expression IN (subquery): como argumento al operador IN (o NOT IN).
La subconsulta no devuelve un valor, sino una tabla de una columna (table expression)
SELECT nombre FROM editoriales WHERE codigo IN (select codigoeditorial from libros where autor='Richard Bach');
Es equivalente a SOME/ANY: expression operator ANY (subquery)
SELECT nombre FROM editoriales WHERE codigo ANY (select codigoeditorial from libros where autor='Richard Bach');
Y otro caso especial es expression operator ALL (subquery)
Si la expression se cumple para todos los valores de la columna resultado de la subconsulta
NOT IN es equivalente a <> ALL
Ejercicios SQL
Haz los ejercicios de Ejercicios de SQL sobre BD Northwind (W3Schools)
6.8. Tablas de constantes y CTE con WITH¶
Video Clase
Uso de VALUES (queries-values): tabla estática y constante:
Campos separados por comas
Organizados en registros usando paréntesis
Registros separados con comas
El tipo de datos de cada campo se adecúa al valor
Se usa como una tabla normal (table_expression)
Útil añadirle una etiqueta de tabla (y campos)
Ejemplos de uso:
SELECT ejemplo.numero FROM ( VALUES (1, 'uno'), (2, 'dos'), (3, 'tres') ) AS ejemplo (numero,texto); INSERT INTO films (code, title, did, date_prod, kind) VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); UPDATE employees SET salary = salary * v.increase FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase) WHERE employees.depno = v.depno AND employees.sales >= v.target;
Uso de WITH (queries-with):
También se denominan CTE (Common Table Expressions)
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
Son temporales, y sólo se usan en el contexto de esa consulta
Su principal objetivo es simplificar la consulta (más legible y mejor estructurada) cuando es muy compleja
Se usa no solo con SELECT (también sirven con INSERT, DELETE, UPDATE)
Construcción (WITH Clause ):
Defines las consultas temporales (p.ej cte_sales y shipper_cte)
Vinculadas a una consulta (o modificación de datos)
Usas los nombres CTE en la consulta principal
-- Primera CTE: se prepara la tabla cte_sales WITH cte_sales AS (SELECT EmployeeID, COUNT(OrderID) as Orders, ShipperID FROM Orders GROUP BY EmployeeID, ShipperID), -- Segunda CTE: shipper_cte (que usa la primera, cte_sales) shipper_cte AS (SELECT * FROM cte_sales WHERE ShipperID=2 or ShipperID=3) -- Consulta usando la segunda CTE (que a su vez usa la primera) SELECT ShipperID, AVG(Orders) average_order_per_employee FROM shipper_cte GROUP BY ShipperID;
Organizando y entendiendo consultas complejas
Tomando como referencia este excelente artículo (ojo, que es un poco complejo), la idea es entender:
Los diferentes elementos de la Consulta Original : consulta compleja con muchos elementos
El uso de WITH y su uso en la Consulta Refactorizada
Y una última consulta mejorada y ampliando la idea original