Consultas Avanzadas
## Objetivos de Aprendizaje
- Dominar UNION e INTERSECT
- Usar expresiones CASE
- Trabajar con funciones de ventana
- Entender Expresiones de Tabla Comunes
## UNION
### Combinar Resultados
```sql
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales'
UNION
SELECT first_name, last_name
FROM contractors
WHERE department = 'Sales';
```
### UNION ALL
```sql
-- Incluye duplicados
SELECT department FROM employees
UNION ALL
SELECT department FROM contractors;
```
### UNION vs UNION ALL
| Tipo | Duplicados | Rendimiento |
|------|------------|-------------|
| UNION | Eliminados | Más lento |
| UNION ALL | Mantenidos | Más rápido |
## INTERSECT
### Solo Filas Comunes
```sql
SELECT email FROM employees
INTERSECT
SELECT email FROM contractors;
```
## EXCEPT / MINUS
### Diferencia
```sql
-- Filas en primera, no en segunda
SELECT email FROM employees
EXCEPT
SELECT email FROM contractors;
```
## Expresión CASE
### CASE Simple
```sql
SELECT
first_name,
salary,
CASE department
WHEN 'Sales' THEN salary * 1.10
WHEN 'Engineering' THEN salary * 1.15
ELSE salary
END AS adjusted_salary
FROM employees;
```
### CASE Buscado
```sql
SELECT
first_name,
salary,
CASE
WHEN salary < 50000 THEN 'Low'
WHEN salary >= 50000 AND salary < 80000 THEN 'Medium'
WHEN salary >= 80000 THEN 'High'
ELSE 'Unknown'
END AS salary_bracket
FROM employees;
```
### CASE en WHERE
```sql
SELECT *
FROM employees
WHERE CASE
WHEN department = 'Sales' THEN salary > 50000
WHEN department = 'Engineering' THEN salary > 70000
ELSE TRUE
END;
```
### CASE para Pivote
```sql
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive
FROM accounts;
```
## Funciones de Ventana
### Cláusula OVER
```sql
SELECT
first_name,
salary,
AVG(salary) OVER () AS avg_salary
FROM employees;
```
### PARTITION BY
```sql
SELECT
department,
first_name,
salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
```
### ROW_NUMBER
```sql
SELECT
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
first_name,
salary
FROM employees;
```
### RANK y DENSE_RANK
```sql
SELECT
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
first_name,
salary
FROM employees;
```
### LAG y LEAD
```sql
SELECT
first_name,
hire_date,
LAG(hire_date) OVER (ORDER BY hire_date) AS prev_hire,
LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire
FROM employees;
```
### FIRST_VALUE y LAST_VALUE
```sql
SELECT
department,
first_name,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY hire_date
) AS first_salary_in_dept
FROM employees;
```
## Expresiones de Tabla Comunes (CTE)
### CTE Básico
```sql
WITH dept_avg AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
e.first_name,
e.salary,
d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;
```
### Múltiples CTEs
```sql
WITH
high_earners AS (
SELECT *
FROM employees
WHERE salary > 80000
),
recent_hires AS (
SELECT *
FROM employees
WHERE hire_date > '2023-01-01'
)
SELECT *
FROM high_earners
INTERSECT
SELECT *
FROM recent_hires;
```
### CTE Recursivo
```sql
-- Jerarquía organizacional
WITH RECURSIVE org_chart AS (
SELECT
employee_id,
first_name,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.first_name,
e.manager_id,
oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart;
```
## Agregación Condicional
### Patrón SUM/CASE
```sql
SELECT
COUNT(*) AS total,
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;
```
### AVG con Condición
```sql
SELECT
AVG(CASE WHEN department = 'Sales' THEN salary END) AS avg_sales_salary,
AVG(CASE WHEN department = 'Engineering' THEN salary END) AS avg_eng_salary
FROM employees;
```
## PIVOT / Tabla Cruzada
### PIVOT en MySQL
```sql
SELECT
SUM(CASE WHEN quarter = 'Q1' THEN sales END) AS Q1,
SUM(CASE WHEN quarter = 'Q2' THEN sales END) AS Q2,
SUM(CASE WHEN quarter = 'Q3' THEN sales END) AS Q3,
SUM(CASE WHEN quarter = 'Q4' THEN sales END) AS Q4
FROM quarterly_sales;
```
### PIVOT en SQL Server
```sql
SELECT *
FROM (SELECT year, quarter, sales) AS src
PIVOT (SUM(sales) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])) AS pvt;
```
## Resumen
- **UNION/UNION ALL**: Combinar conjuntos de resultados
- **INTERSECT/EXCEPT**: Operaciones de conjunto
- **CASE**: Lógica condicional en consultas
- **Funciones de ventana**: LAG, LEAD, ROW_NUMBER, RANK, SUM/AVG sobre particiones
- **CTE**: Subconsultas nombradas con cláusula WITH
- **CTE recursivo**: Para datos jerárquicos
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →