Módulo 37: Consultas avanzadas
Objetivos del módulo
- Comprender las funciones analíticas (window functions) y su diferencia con las funciones de agregado y
GROUP BY - Dominar la cláusula
OVER:PARTITION BY,ORDER BYy la ventanaROWS/RANGE BETWEEN - Usar las funciones de ranking
ROW_NUMBER,RANK,DENSE_RANK,NTILE,PERCENT_RANKyCUME_DIST - Calcular totales acumulados, medias móviles y porcentajes sobre el total con agregados analíticos y
RATIO_TO_REPORT - Acceder a otras filas con
LAG,LEAD,FIRST_VALUE,LAST_VALUEyNTH_VALUE, evitando la trampa de la ventana por defecto - Usar la cláusula
KEEP (DENSE_RANK FIRST/LAST)y resolver el problema del top-N por grupo - Escribir consultas jerárquicas con
START WITH,CONNECT BY PRIOR,LEVEL,SYS_CONNECT_BY_PATH,CONNECT_BY_ROOT,CONNECT_BY_ISLEAF,ORDER SIBLINGS BYyNOCYCLE - Transformar filas en columnas y columnas en filas con
PIVOT,UNPIVOTyCASE - Conocer
MATCH_RECOGNIZEy las funciones SQL/JSON básicas para generar y leer JSON - Combinarlo todo en consultas de tipo informe
1. Funciones analíticas: la idea
📘 Concepto: Una función analítica (analytic function, en el estándar SQL función de ventana o window function) calcula un valor para cada fila a partir de un conjunto de filas relacionadas con ella (su ventana), sin agrupar ni eliminar filas. Se reconocen por la cláusula
OVER (...).
Compara estas dos consultas. Con GROUP BY obtenemos una fila por departamento; los detalles de cada empleado desaparecen:
SELECT dept_id, ROUND(AVG(salario), 2) AS media
FROM empleados
WHERE dept_id IN (40, 60)
GROUP BY dept_id;
DEPT_ID MEDIA
---------- ----------
40 2816,67
60 2237,5
2 filas seleccionadas.
Con una función analítica conservamos todas las filas y añadimos a cada una la media de su departamento:
SELECT dept_id, nombre, salario,
ROUND(AVG(salario) OVER (PARTITION BY dept_id), 2) AS media_dept
FROM empleados
WHERE dept_id IN (40, 60)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO MEDIA_DEPT
---------- --------------------------- ---------- ----------
40 Pedro 4200 2816,67
40 Isabel 2600 2816,67
40 Jorge 1650 2816,67
60 Javier 4000 2237,5
60 Cristina 1750 2237,5
60 Rosa 1600 2237,5
60 Manuel 1600 2237,5
7 filas seleccionadas.
Es lo mismo que en el módulo 31 hacíamos con una subconsulta correlacionada o con una vista en línea agrupada, pero en una sola pasada, más legible y normalmente más eficiente.
💡 Consejo: Analogía:
GROUP BYes como resumir un libro (cada capítulo se convierte en un párrafo); una función analítica es como anotar al margen de cada página un dato del capítulo al que pertenece (“este capítulo tiene 20 páginas”, “esta es la página 7 de 20”). Las páginas siguen ahí.
1.1. Sintaxis de OVER
función(argumentos) OVER (
[PARTITION BY expr1, expr2, ...] -- divide las filas en grupos (particiones)
[ORDER BY expr [ASC|DESC] [NULLS FIRST|LAST], ...] -- orden dentro de cada partición
[ROWS | RANGE BETWEEN inicio AND fin] -- ventana (marco) dentro de la partición
)
| Parte | Qué hace | Si se omite |
|---|---|---|
PARTITION BY | Divide las filas en grupos independientes; la función se reinicia en cada uno | Toda la consulta es una única partición |
ORDER BY | Ordena las filas dentro de cada partición; imprescindible para rankings, acumulados, LAG/LEAD | Sin orden: los agregados se calculan sobre la partición completa |
Ventana (ROWS/RANGE) | Delimita qué filas de la partición se usan para calcular el valor de la fila actual | Si hay ORDER BY: desde el inicio de la partición hasta la fila actual (RANGE); si no hay ORDER BY: la partición entera |
flowchart LR
T["Filas resultantes de<br/>FROM, WHERE,<br/>GROUP BY, HAVING"] --> P["PARTITION BY<br/>(grupos)"]
P --> O["ORDER BY<br/>(orden en cada grupo)"]
O --> V["Ventana ROWS/RANGE<br/>(filas que ve cada fila)"]
V --> F["Valor de la función<br/>para cada fila"]
1.2. OVER () vacío: toda la consulta
Con OVER () la ventana es el resultado completo. Peso de cada salario de INFORMÁTICA sobre el total del departamento:
SELECT nombre, salario,
SUM(salario) OVER () AS total_empresa,
ROUND(100 * salario / SUM(salario) OVER (), 2) AS pct_total,
COUNT(*) OVER () AS num_empleados
FROM empleados
WHERE dept_id = 30
ORDER BY salario DESC;
NOMBRE SALARIO TOTAL_EMPRESA PCT_TOTAL NUM_EMPLEADOS
--------------------------- ---------- ------------- ---------- -------------
Marta 5000 18800 26,6 6
Pablo 3500 18800 18,62 6
Raquel 2800 18800 14,89 6
Iván 2600 18800 13,83 6
Hugo 2500 18800 13,3 6
Sara 2400 18800 12,77 6
6 filas seleccionadas.
Observa que el “total de la empresa” es 18 800, no 67 900: la función analítica trabaja sobre las filas que quedan después del WHERE. (El alias es engañoso a propósito: recuérdalo.)
1.3. PARTITION BY
Varias funciones con la misma partición, en la misma consulta:
SELECT dept_id, nombre, salario,
COUNT(*) OVER (PARTITION BY dept_id) AS emp_dept,
MAX(salario) OVER (PARTITION BY dept_id) AS max_dept,
MAX(salario) OVER (PARTITION BY dept_id) - salario AS hasta_max
FROM empleados
WHERE dept_id IN (20, 50)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO EMP_DEPT MAX_DEPT HASTA_MAX
---------- --------------------------- ---------- ---------- ---------- ----------
20 Luis 4800 6 4800 0
20 Elena 2300 6 4800 2500
20 Carmen 2200 6 4800 2600
20 David 2100 6 4800 2700
20 Sergio 1900 6 4800 2900
20 Lucía 1700 6 4800 3100
50 Laura 4300 3 4300 0
50 Álvaro 2700 3 4300 1600
50 Nuria 2300 3 4300 2000
9 filas seleccionadas.
1.4. ¿Cuándo se calculan? No se pueden usar en WHERE
Las funciones analíticas se evalúan después de WHERE, GROUP BY y HAVING, y antes del ORDER BY final:
| Orden | Fase |
|---|---|
| 1 | FROM y JOIN |
| 2 | WHERE |
| 3 | GROUP BY |
| 4 | HAVING |
| 5 | Funciones analíticas |
| 6 | SELECT (DISTINCT) |
| 7 | ORDER BY |
| 8 | FETCH FIRST / OFFSET |
Por eso no pueden aparecer en el WHERE (cuando se filtra todavía no existen):
SELECT nombre, salario
FROM empleados
WHERE salario > AVG(salario) OVER (PARTITION BY dept_id);
WHERE salario > AVG(salario) OVER (PARTITION BY dept_id)
*
ERROR en línea 3:
ORA-00934: group function is not allowed here
Help: https://docs.oracle.com/error-help/db/ora-00934/
La solución estándar es calcularlas en una vista en línea (o una CTE) y filtrar fuera:
SELECT dept_id, nombre, salario, media_dept
FROM (SELECT dept_id, nombre, salario,
ROUND(AVG(salario) OVER (PARTITION BY dept_id), 2) AS media_dept
FROM empleados)
WHERE salario > media_dept
ORDER BY dept_id;
DEPT_ID NOMBRE SALARIO MEDIA_DEPT
---------- --------------------------- ---------- ----------
20 Luis 4800 2500
30 Marta 5000 3133,33
30 Pablo 3500 3133,33
40 Pedro 4200 2816,67
50 Laura 4300 3100
60 Javier 4000 2237,5
6 filas seleccionadas.
Es el mismo resultado que la subconsulta correlacionada del módulo 31 (sección 8.1), con una sola lectura de la tabla.
Y como se evalúan después del GROUP BY, una función analítica puede operar sobre los agregados de una consulta agrupada. Ranking de departamentos por masa salarial y peso de cada uno:
SELECT dept_id,
SUM(salario) AS masa,
RANK() OVER (ORDER BY SUM(salario) DESC) AS puesto,
ROUND(100 * RATIO_TO_REPORT(SUM(salario)) OVER (), 1) AS pct
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY puesto;
DEPT_ID MASA PUESTO PCT
---------- ---------- ---------- ----------
30 18800 1 28,1
20 15000 2 22,4
50 9300 3 13,9
60 8950 4 13,4
40 8450 5 12,6
10 6500 6 9,7
6 filas seleccionadas.
2. Funciones de ranking
| Función | Qué devuelve | Empates |
|---|---|---|
ROW_NUMBER() | Número de fila 1, 2, 3… | Números distintos (desempate arbitrario si el orden no es único) |
RANK() | Puesto con huecos: 1, 2, 2, 4 | Mismo puesto; el siguiente salta |
DENSE_RANK() | Puesto sin huecos: 1, 2, 2, 3 | Mismo puesto; el siguiente no salta |
NTILE(n) | Número de grupo (1..n) al dividir las filas en n grupos de tamaño casi igual | Los empatados pueden caer en grupos distintos |
PERCENT_RANK() | (rank − 1) / (filas − 1), entre 0 y 1 | Igual para empatados |
CUME_DIST() | Fracción de filas con valor ≤ (o ≥ si es DESC) que el actual, entre 0 y 1 | Igual para empatados |
Las seis sobre los mismos datos, para ver las diferencias:
SELECT nombre, salario,
ROW_NUMBER() OVER (ORDER BY salario DESC) AS row_num,
RANK() OVER (ORDER BY salario DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salario DESC) AS dense,
NTILE(4) OVER (ORDER BY salario DESC) AS cuartil,
ROUND(PERCENT_RANK() OVER (ORDER BY salario DESC), 2) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY salario DESC), 2) AS cume
FROM empleados
ORDER BY salario DESC, nombre;
NOMBRE SALARIO ROW_NUM RNK DENSE CUARTIL PCT_RANK CUME
--------------------------- ---------- ---------- ---------- ---------- ---------- -------- -----
Ana 6500 1 1 1 1 0,00 0,04
Marta 5000 2 2 2 1 0,04 0,08
Luis 4800 3 3 3 1 0,09 0,13
Laura 4300 4 4 4 1 0,13 0,17
Pedro 4200 5 5 5 1 0,17 0,21
Javier 4000 6 6 6 1 0,22 0,25
Pablo 3500 7 7 7 2 0,26 0,29
Raquel 2800 8 8 8 2 0,30 0,33
Álvaro 2700 9 9 9 2 0,35 0,38
Isabel 2600 10 10 10 2 0,39 0,46
Iván 2600 11 10 10 2 0,39 0,46
Hugo 2500 12 12 11 2 0,48 0,50
Sara 2400 13 13 12 3 0,52 0,54
Elena 2300 14 14 13 3 0,57 0,63
Nuria 2300 15 14 13 3 0,57 0,63
Carmen 2200 16 16 14 3 0,65 0,67
David 2100 17 17 15 3 0,70 0,71
Sergio 1900 18 18 16 3 0,74 0,75
Cristina 1750 19 19 17 4 0,78 0,79
Lucía 1700 20 20 18 4 0,83 0,83
Jorge 1650 21 21 19 4 0,87 0,88
Manuel 1600 22 22 20 4 0,91 0,96
Rosa 1600 23 22 20 4 0,91 0,96
Tomás 900 24 24 21 4 1,00 1,00
24 filas seleccionadas.
Fíjate en los empates (Isabel e Iván con 2600; Elena y Nuria con 2300; Manuel y Rosa con 1600):
ROW_NUMBERles da números distintos (10 y 11). Cuál va primero no está garantizado: si necesitas un orden reproducible, añade un criterio de desempate (ORDER BY salario DESC, emp_id).RANKles da el mismo puesto (10) y salta el siguiente (Hugo es 12.º).DENSE_RANKles da el mismo puesto (10) y no salta (Hugo es 11.º). ConDENSE_RANK, el número indica “cuántos salarios distintos hay por encima más uno”.NTILE(4)reparte 24 filas en 4 grupos de 6 (cuartiles). Si no son divisibles, los primeros grupos reciben una fila más.PERCENT_RANKde Isabel: (10 − 1) / (24 − 1) = 0,39.CUME_DISTde Isabel: 11 de 24 empleados cobran 2600 o más = 0,46.
2.1. Ranking dentro de cada grupo
Con PARTITION BY el ranking se reinicia en cada partición:
SELECT dept_id, nombre, salario,
RANK() OVER (PARTITION BY dept_id ORDER BY salario DESC) AS puesto_dept
FROM empleados
WHERE dept_id IN (30, 60)
ORDER BY dept_id, puesto_dept;
DEPT_ID NOMBRE SALARIO PUESTO_DEPT
---------- --------------------------- ---------- -----------
30 Marta 5000 1
30 Pablo 3500 2
30 Raquel 2800 3
30 Iván 2600 4
30 Hugo 2500 5
30 Sara 2400 6
60 Javier 4000 1
60 Cristina 1750 2
60 Rosa 1600 3
60 Manuel 1600 3
10 filas seleccionadas.
3. Top-N por grupo
Uno de los problemas más habituales en SQL: “los 2 empleados mejor pagados de cada departamento”, “el último pedido de cada cliente”, “los 3 productos más vendidos de cada categoría”. FETCH FIRST no sirve porque limita el resultado global, no por grupo. La solución es numerar dentro de cada grupo y filtrar.
3.1. Con ROW_NUMBER (exactamente N por grupo)
SELECT dept_id, nombre, apellidos, salario
FROM (SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salario DESC, emp_id) AS rn
FROM empleados e
WHERE dept_id IS NOT NULL)
WHERE rn <= 2
ORDER BY dept_id, rn;
DEPT_ID NOMBRE APELLIDOS SALARIO
---------- --------------------------- ------------------ ----------
10 Ana García López 6500
20 Luis Martínez Ruiz 4800
20 Elena Navarro Castro 2300
30 Marta Sánchez Gil 5000
30 Pablo Moreno Herrera 3500
40 Pedro Gómez Díaz 4200
40 Isabel Domínguez Vega 2600
50 Laura Fernández Moreno 4300
50 Álvaro Peña Cano 2700
60 Javier López Navarro 4000
60 Cristina Medina Blanco 1750
11 filas seleccionadas.
DIRECCIÓN solo tiene un empleado, por eso aparece una vez. El emp_id en el ORDER BY hace el desempate determinista.
3.2. Con RANK / DENSE_RANK (respetando empates)
Los dos salarios más bajos de cada departamento, incluyendo a todos los empatados:
SELECT dept_id, nombre, salario, dr
FROM (SELECT dept_id, nombre, salario,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salario) AS dr
FROM empleados
WHERE dept_id IS NOT NULL)
WHERE dr <= 2
ORDER BY dept_id, dr, nombre;
DEPT_ID NOMBRE SALARIO DR
---------- --------------------------- ---------- ----------
10 Ana 6500 1
20 Lucía 1700 1
20 Sergio 1900 2
30 Sara 2400 1
30 Hugo 2500 2
40 Jorge 1650 1
40 Isabel 2600 2
50 Nuria 2300 1
50 Álvaro 2700 2
60 Manuel 1600 1
60 Rosa 1600 1
60 Cristina 1750 2
12 filas seleccionadas.
En LOGÍSTICA salen tres personas: Manuel y Rosa comparten el salario más bajo y Cristina tiene el segundo.
| Si quieres… | Usa |
|---|---|
| Exactamente N filas por grupo, desempatando | ROW_NUMBER() ... <= N |
| Los N primeros puestos, con todos los empatados (puede haber huecos) | RANK() ... <= N |
| Los N primeros valores distintos, con todos los empatados | DENSE_RANK() ... <= N |
3.3. El último pedido de cada cliente
El problema que en el módulo 31 resolvimos con una subconsulta correlacionada y MAX:
SELECT cliente_id, pedido_id, fecha_pedido
FROM (SELECT p.*,
ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY fecha_pedido DESC) AS rn
FROM pedidos p)
WHERE rn = 1
ORDER BY cliente_id
FETCH FIRST 5 ROWS ONLY;
CLIENTE_ID PEDIDO_ID FECHA_PEDI
---------- ---------- ----------
1001 5021 15/06/2026
1002 5018 03/03/2026
1003 5015 27/11/2025
1004 5012 08/09/2025
1005 5019 21/04/2026
5 filas seleccionadas.
(Limitamos la salida a cinco filas para no repetir la tabla del módulo 31.)
3.4. QUALIFY: filtrar por una función analítica sin vista en línea
⚠️ Importante: La cláusula
QUALIFYestá disponible en Oracle AI Database 26ai (la versión del contenedor del curso) y no existe en Oracle 19c ni 21c. La alternativa clásica es la vista en línea de los apartados anteriores, que funciona en todas las versiones.
QUALIFY es al resultado de las funciones analíticas lo que HAVING es a los grupos: un filtro que se aplica después de calcularlas. Va detrás de HAVING (si lo hay) y antes del ORDER BY:
SELECT dept_id, nombre, salario
FROM empleados
WHERE dept_id IS NOT NULL
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salario DESC, emp_id) <= 2
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO
---------- --------------------------- ----------
10 Ana 6500
20 Luis 4800
20 Elena 2300
30 Marta 5000
30 Pablo 3500
40 Pedro 4200
40 Isabel 2600
50 Laura 4300
50 Álvaro 2700
60 Javier 4000
60 Cristina 1750
11 filas seleccionadas.
3.5. Top-N global con empates: FETCH FIRST ... WITH TIES
Si el top-N es global (no por grupo), basta con FETCH FIRST (módulo 27). WITH TIES incluye las filas empatadas con la última:
SELECT nombre, salario
FROM empleados
ORDER BY salario
FETCH FIRST 2 ROWS WITH TIES;
NOMBRE SALARIO
--------------------------- ----------
Tomás 900
Rosa 1600
Manuel 1600
3 filas seleccionadas.
4. Agregados como funciones analíticas
SUM, AVG, COUNT, MIN y MAX pueden usarse con OVER. Su comportamiento depende de si hay ORDER BY dentro del OVER:
- Sin
ORDER BY: calculan el agregado de toda la partición (lo que hemos visto hasta ahora). - Con
ORDER BY: calculan un agregado acumulado desde el principio de la partición hasta la fila actual (o la ventana que indiques).
4.1. Totales acumulados, medias móviles y porcentajes
Ventas de cada mes de 2025, acumulado del año, media móvil de 3 meses y peso de cada mes:
WITH ventas_mes AS (
SELECT TRUNC(p.fecha_pedido, 'MM') AS mes,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
AND p.fecha_pedido < DATE '2026-01-01'
GROUP BY TRUNC(p.fecha_pedido, 'MM')
)
SELECT TO_CHAR(mes, 'YYYY-MM') AS mes,
ventas,
SUM(ventas) OVER (ORDER BY mes) AS acumulado,
ROUND(AVG(ventas) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_movil_3,
ROUND(100 * RATIO_TO_REPORT(ventas) OVER (), 1) AS pct_anual
FROM ventas_mes
ORDER BY mes;
MES VENTAS ACUMULADO MEDIA_MOVIL_3 PCT_ANUAL
------- ---------- ---------- ------------- ----------
2025-01 9205,4 9205,4 9205,4 22,5
2025-02 6791,4 15996,8 7998,4 16,6
2025-03 1375,2 17372 5790,67 3,4
2025-04 2539,13 19911,13 3568,58 6,2
2025-05 4772,44 24683,57 2895,59 11,7
2025-06 12656,5 37340,07 6656,02 30,9
2025-09 1658,8 38998,87 6362,58 4,1
2025-10 1555 40553,87 5290,1 3,8
2025-11 358,8 40912,67 1190,87 ,9
9 filas seleccionadas.
SUM(ventas) OVER (ORDER BY mes): suma desde enero hasta el mes actual.AVG(...) OVER (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW): media de la fila actual y las dos anteriores (en enero solo hay una fila; en febrero, dos).RATIO_TO_REPORT(ventas) OVER ():ventas / SUM(ventas) OVER (), es decir, la fracción del total. SQL*Plus muestra,9para 0,9 (omite el cero inicial).
⚠️ Importante: La “media móvil de 3 meses” con
ROWSusa las 3 filas anteriores, no los 3 meses naturales: entre junio y septiembre faltan julio y agosto (sin ventas), y la fila de septiembre promedia mayo, junio y septiembre. Para medias por periodo natural, genera antes un calendario completo (módulo 32, sección 10.6) o usaRANGEcon intervalos (4.3).
4.2. Acumulados por partición
Numeración de los pedidos de cada cliente y total de pedidos:
SELECT cliente_id, pedido_id, fecha_pedido,
COUNT(*) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS n_pedido,
COUNT(*) OVER (PARTITION BY cliente_id) AS total_pedidos
FROM pedidos
WHERE cliente_id IN (1001, 1002)
ORDER BY cliente_id, fecha_pedido;
CLIENTE_ID PEDIDO_ID FECHA_PEDI N_PEDIDO TOTAL_PEDIDOS
---------- ---------- ---------- ---------- -------------
1001 5001 15/01/2025 1 3
1001 5006 22/03/2025 2 3
1001 5021 15/06/2026 3 3
1002 5002 28/01/2025 1 3
1002 5009 19/05/2025 2 3
1002 5018 03/03/2026 3 3
6 filas seleccionadas.
RATIO_TO_REPORT también admite PARTITION BY. Peso de cada salario en LOGÍSTICA dentro del departamento y dentro de la consulta:
SELECT dept_id, nombre, salario,
ROUND(100 * RATIO_TO_REPORT(salario) OVER (PARTITION BY dept_id), 1) AS pct_dept,
ROUND(100 * RATIO_TO_REPORT(salario) OVER (), 1) AS pct_empresa
FROM empleados
WHERE dept_id = 60
ORDER BY salario DESC;
DEPT_ID NOMBRE SALARIO PCT_DEPT PCT_EMPRESA
---------- --------------------------- ---------- ---------- -----------
60 Javier 4000 44,7 44,7
60 Cristina 1750 19,6 19,6
60 Manuel 1600 17,9 17,9
60 Rosa 1600 17,9 17,9
4 filas seleccionadas.
Como el WHERE deja solo el departamento 60, ambos porcentajes coinciden: de nuevo, la ventana opera sobre las filas filtradas.
4.3. La ventana: ROWS frente a RANGE
📘 Concepto: La ventana (window frame) indica qué filas de la partición, relativas a la fila actual, intervienen en el cálculo. Se define con
ROWS(por posición física: “las 2 filas anteriores”) oRANGE(por valor de la expresión delORDER BY: “las filas cuyo valor está entre el actual menos 30 y el actual”).
Límites posibles:
| Límite | Significado |
|---|---|
UNBOUNDED PRECEDING | Primera fila de la partición |
n PRECEDING | n filas (o n unidades de valor con RANGE) antes de la actual |
CURRENT ROW | Fila actual (con RANGE: todas las filas con el mismo valor que la actual) |
n FOLLOWING | n filas (o unidades) después de la actual |
UNBOUNDED FOLLOWING | Última fila de la partición |
Varias ventanas sobre los mismos datos:
SELECT emp_id, salario,
SUM(salario) OVER (ORDER BY emp_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS vecinos,
SUM(salario) OVER (ORDER BY emp_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS resto,
SUM(salario) OVER (ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total
FROM empleados
WHERE dept_id = 30
ORDER BY emp_id;
EMP_ID SALARIO VECINOS RESTO TOTAL
---------- ---------- ---------- ---------- ----------
102 5000 8500 18800 18800
120 3500 11300 13800 18800
121 2800 8900 10300 18800
122 2600 7800 7500 18800
123 2400 7500 4900 18800
124 2500 4900 2500 18800
6 filas seleccionadas.
La diferencia entre ROWS y RANGE se ve con empates en el ORDER BY:
SELECT nombre, salario,
SUM(salario) OVER (ORDER BY salario ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS suma_rows,
SUM(salario) OVER (ORDER BY salario RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS suma_range,
SUM(salario) OVER (ORDER BY salario) AS suma_defecto
FROM empleados
WHERE dept_id IN (40, 60)
ORDER BY salario, nombre;
NOMBRE SALARIO SUMA_ROWS SUMA_RANGE SUMA_DEFECTO
--------------------------- ---------- ---------- ---------- ------------
Manuel 1600 1600 3200 3200
Rosa 1600 3200 3200 3200
Jorge 1650 4850 4850 4850
Cristina 1750 6600 6600 6600
Isabel 2600 9200 9200 9200
Javier 4000 13200 13200 13200
Pedro 4200 17400 17400 17400
7 filas seleccionadas.
Manuel y Rosa cobran lo mismo. Con ROWS, Manuel solo se suma a sí mismo (1600). Con RANGE, “hasta la fila actual” significa “hasta el valor actual”, que incluye a todos los que cobran 1600: 3200 para ambos. Y la ventana por defecto cuando hay ORDER BY es precisamente RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
⚠️ Importante: Para un total acumulado “fila a fila” en presencia de empates, escribe explícitamente
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(abreviable comoROWS UNBOUNDED PRECEDING) y unORDER BYsin empates (añade la clave). Con la ventana por defecto, las filas empatadas reciben el mismo acumulado.
RANGE con un desplazamiento numérico sirve para ventanas por valor, por ejemplo por días sobre una columna DATE (cada unidad es un día). Cuántos pedidos ha habido en los 30 días anteriores a cada pedido de 2026 (incluido él):
SELECT pedido_id, fecha_pedido,
COUNT(*) OVER (ORDER BY fecha_pedido
RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) AS pedidos_ult_30d
FROM pedidos
WHERE fecha_pedido >= DATE '2026-01-01'
ORDER BY fecha_pedido;
PEDIDO_ID FECHA_PEDI PEDIDOS_ULT_30D
---------- ---------- ---------------
5016 12/01/2026 1
5017 09/02/2026 2
5018 03/03/2026 2
5019 21/04/2026 1
5020 30/05/2026 1
5021 15/06/2026 2
5022 01/07/2026 2
5023 08/07/2026 3
5024 10/07/2026 4
9 filas seleccionadas.
El 10/07/2026 la ventana abarca desde el 10/06/2026: pedidos 5021, 5022, 5023 y 5024.
4.4. Ventanas con nombre, GROUPS y EXCLUDE
⚠️ Importante: La cláusula
WINDOW, la unidadGROUPSy la opciónEXCLUDEson ampliaciones recientes del estándar que funcionan en Oracle 26ai (las hemos comprobado en el contenedor del curso); no están disponibles en Oracle 19c.
Si varias funciones usan la misma ventana, puedes definirla una vez con WINDOW y referirte a ella por su nombre:
SELECT nombre, salario,
MIN(salario) OVER w AS minimo,
MAX(salario) OVER w AS maximo,
ROUND(AVG(salario) OVER w, 2) AS media
FROM empleados
WHERE dept_id = 50
WINDOW w AS (PARTITION BY dept_id)
ORDER BY salario DESC;
NOMBRE SALARIO MINIMO MAXIMO MEDIA
--------------------------- ---------- ---------- ---------- ----------
Laura 4300 2300 4300 3100
Álvaro 2700 2300 4300 3100
Nuria 2300 2300 4300 3100
3 filas seleccionadas.
GROUPS cuenta grupos de valores iguales en lugar de filas, y EXCLUDE CURRENT ROW quita la fila actual de la ventana:
SELECT nombre, salario,
SUM(salario) OVER (ORDER BY salario GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS grupos,
SUM(salario) OVER (ORDER BY salario ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
EXCLUDE CURRENT ROW) AS resto_sin_mi
FROM empleados
WHERE dept_id = 60
ORDER BY salario;
NOMBRE SALARIO GRUPOS RESTO_SIN_MI
--------------------------- ---------- ---------- ------------
Rosa 1600 3200 7350
Manuel 1600 3200 7350
Cristina 1750 4950 7200
Javier 4000 5750 4950
4 filas seleccionadas.
Para Cristina, “el grupo anterior y el actual” son {1600, 1600} y {1750}: 4950. resto_sin_mi es el total del departamento menos el salario propio.
5. Acceso a otras filas: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
5.1. LAG y LEAD
LAG(expr [, desplazamiento [, valor_por_defecto]]) devuelve el valor de expr en la fila que está desplazamiento posiciones antes (por defecto 1); LEAD, después. Requieren ORDER BY en el OVER. Días transcurridos entre pedidos consecutivos de un mismo cliente:
SELECT cliente_id, pedido_id, fecha_pedido,
LAG(fecha_pedido) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS pedido_anterior,
fecha_pedido - LAG(fecha_pedido) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS dias_desde_anterior,
LEAD(pedido_id) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido) AS siguiente_pedido
FROM pedidos
WHERE cliente_id IN (1001, 1002, 1003)
ORDER BY cliente_id, fecha_pedido;
CLIENTE_ID PEDIDO_ID FECHA_PEDI PEDIDO_ANT DIAS_DESDE_ANTERIOR SIGUIENTE_PEDIDO
---------- ---------- ---------- ---------- ------------------- ----------------
1001 5001 15/01/2025 (nulo) (nulo) 5006
1001 5006 22/03/2025 15/01/2025 66 5021
1001 5021 15/06/2026 22/03/2025 450 (nulo)
1002 5002 28/01/2025 (nulo) (nulo) 5009
1002 5009 19/05/2025 28/01/2025 111 5018
1002 5018 03/03/2026 19/05/2025 288 (nulo)
1003 5003 03/02/2025 (nulo) (nulo) 5015
1003 5015 27/11/2025 03/02/2025 297 (nulo)
8 filas seleccionadas.
El primer pedido de cada cliente no tiene anterior (NULL) y el último no tiene siguiente. SQL*Plus recorta la cabecera de pedido_anterior al ancho de la columna de fecha.
Variación de las ventas mes a mes en 2026:
WITH ventas_mes AS (
SELECT TRUNC(p.fecha_pedido, 'MM') AS mes,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
AND p.fecha_pedido >= DATE '2026-01-01'
GROUP BY TRUNC(p.fecha_pedido, 'MM')
)
SELECT TO_CHAR(mes, 'YYYY-MM') AS mes,
ventas,
LAG(ventas) OVER (ORDER BY mes) AS mes_anterior,
ventas - LAG(ventas) OVER (ORDER BY mes) AS variacion,
ROUND(100 * (ventas / LAG(ventas) OVER (ORDER BY mes) - 1), 1) AS pct_var
FROM ventas_mes
ORDER BY mes;
MES VENTAS MES_ANTERIOR VARIACION PCT_VAR
------- ---------- ------------ ---------- ----------
2026-01 8945,05 (nulo) (nulo) (nulo)
2026-02 838 8945,05 -8107,05 -90,6
2026-03 2085,25 838 1247,25 148,8
2026-04 1296 2085,25 -789,25 -37,8
2026-05 1663,64 1296 367,64 28,4
2026-06 2462,05 1663,64 798,41 48
2026-07 3414,8 2462,05 952,75 38,7
7 filas seleccionadas.
Con desplazamiento y valor por defecto:
SELECT nombre, salario,
LAG(nombre, 1, '(ninguno)') OVER (ORDER BY salario DESC) AS anterior,
LAG(salario, 1, 0) OVER (ORDER BY salario DESC) AS salario_anterior,
LEAD(nombre, 2) OVER (ORDER BY salario DESC) AS dos_despues
FROM empleados
WHERE dept_id = 30
ORDER BY salario DESC;
NOMBRE SALARIO ANTERIOR SALARIO_ANTERIOR DOS_DESPUES
--------------------------- ---------- ---------- ---------------- ------------------------------
Marta 5000 (ninguno) 0 Raquel
Pablo 3500 Marta 5000 Iván
Raquel 2800 Pablo 3500 Hugo
Iván 2600 Raquel 2800 Sara
Hugo 2500 Iván 2600 (nulo)
Sara 2400 Hugo 2500 (nulo)
6 filas seleccionadas.
5.2. FIRST_VALUE, LAST_VALUE y la trampa de la ventana por defecto
FIRST_VALUE(expr) y LAST_VALUE(expr) devuelven el valor de expr en la primera y la última fila de la ventana. Y ahí está la trampa:
SELECT dept_id, nombre, salario,
FIRST_VALUE(nombre) OVER (PARTITION BY dept_id ORDER BY salario DESC) AS primero,
LAST_VALUE(nombre) OVER (PARTITION BY dept_id ORDER BY salario DESC) AS ultimo_mal,
LAST_VALUE(nombre) OVER (PARTITION BY dept_id ORDER BY salario DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ultimo
FROM empleados
WHERE dept_id IN (40, 50)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO PRIMERO ULTIMO_MAL ULTIMO
---------- --------------------------- ---------- ---------- ---------- ----------
40 Pedro 4200 Pedro Pedro Jorge
40 Isabel 2600 Pedro Isabel Jorge
40 Jorge 1650 Pedro Jorge Jorge
50 Laura 4300 Laura Laura Nuria
50 Álvaro 2700 Laura Álvaro Nuria
50 Nuria 2300 Laura Nuria Nuria
6 filas seleccionadas.
ultimo_mal devuelve el nombre de la propia fila. ¿Por qué? Porque al haber ORDER BY, la ventana por defecto es “desde el principio de la partición hasta la fila actual”: la última fila de esa ventana es siempre la actual. FIRST_VALUE no sufre el problema porque el principio de la ventana sí es el de la partición.
⚠️ Importante: Siempre que uses
LAST_VALUE(oNTH_VALUE, oFIRST_VALUEcon un orden distinto del que crees), escribe la ventana completa:ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Una alternativa sin trampa:FIRST_VALUEcon elORDER BYinvertido.
5.3. NTH_VALUE
NTH_VALUE(expr, n) devuelve el valor de la n-ésima fila de la ventana. El segundo mejor pagado de cada departamento, junto a cada empleado:
SELECT dept_id, nombre, salario,
NTH_VALUE(nombre, 2) OVER (PARTITION BY dept_id ORDER BY salario DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS segundo
FROM empleados
WHERE dept_id IN (40, 50)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO SEGUNDO
---------- --------------------------- ---------- ----------
40 Pedro 4200 Isabel
40 Isabel 2600 Isabel
40 Jorge 1650 Isabel
50 Laura 4300 Álvaro
50 Álvaro 2700 Álvaro
50 Nuria 2300 Álvaro
6 filas seleccionadas.
5.4. IGNORE NULLS
LAG, LEAD, FIRST_VALUE, LAST_VALUE y NTH_VALUE admiten IGNORE NULLS para saltarse los nulos. Muy útil para “rellenar hacia delante” el último valor conocido:
SELECT emp_id, nombre, comision,
LAST_VALUE(comision IGNORE NULLS) OVER (ORDER BY emp_id) AS ultima_comision_conocida,
LAG(comision) OVER (ORDER BY emp_id) AS lag_normal,
LAG(comision) IGNORE NULLS OVER (ORDER BY emp_id) AS lag_ignore
FROM empleados
WHERE dept_id = 20
ORDER BY emp_id;
EMP_ID NOMBRE COMISION ULTIMA_COMISION_CONOCIDA LAG_NORMAL LAG_IGNORE
---------- --------------------------- ---------- ------------------------ ---------- ----------
101 Luis (nulo) (nulo) (nulo) (nulo)
110 Carmen 500 500 (nulo) (nulo)
111 David 800 800 500 500
112 Elena 300 300 800 800
113 Sergio 0 0 300 300
114 Lucía (nulo) 0 0 0
6 filas seleccionadas.
Lucía no tiene comisión, pero ultima_comision_conocida arrastra la de Sergio (0). Aquí la ventana por defecto (“hasta la fila actual”) es justo lo que queremos para LAST_VALUE.
6. KEEP (DENSE_RANK FIRST | LAST)
📘 Concepto: La cláusula
KEEPes una extensión de Oracle que permite a una función de agregado operar solo sobre las filas que quedan primeras (o últimas) según un orden. Responde a preguntas como “¿cómo se llama el empleado con el salario máximo?” sin subconsultas:MAX(nombre) KEEP (DENSE_RANK LAST ORDER BY salario)se lee “de las filas con el último salario en orden ascendente (el mayor), dame elMAX(nombre)”.
SELECT dept_id,
MAX(salario) AS salario_max,
MAX(nombre) KEEP (DENSE_RANK LAST ORDER BY salario) AS mejor_pagado,
MIN(salario) AS salario_min,
MIN(nombre) KEEP (DENSE_RANK FIRST ORDER BY salario) AS peor_pagado,
COUNT(*) KEEP (DENSE_RANK FIRST ORDER BY salario) AS cuantos_con_min
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID SALARIO_MAX MEJOR_PAGADO SALARIO_MIN PEOR_PAGADO CUANTOS_CON_MIN
---------- ----------- ------------ ----------- ------------ ---------------
10 6500 Ana 6500 Ana 1
20 4800 Luis 1700 Lucía 1
30 5000 Marta 2400 Sara 1
40 4200 Pedro 1650 Jorge 1
50 4300 Laura 2300 Nuria 1
60 4000 Javier 1600 Manuel 2
6 filas seleccionadas.
En LOGÍSTICA hay dos empleados con el salario mínimo (Manuel y Rosa): COUNT(*) KEEP (...) lo indica (2), y MIN(nombre) elige uno de ellos (el primero alfabéticamente). El agregado exterior (MIN, MAX, COUNT, SUM…) decide qué hacer cuando hay empates.
Último pedido de cada cliente y su estado, en una sola agrupación:
SELECT cliente_id,
MAX(fecha_pedido) AS ultima_fecha,
MAX(pedido_id) KEEP (DENSE_RANK LAST ORDER BY fecha_pedido) AS ultimo_pedido,
MAX(estado) KEEP (DENSE_RANK LAST ORDER BY fecha_pedido) AS estado_ultimo
FROM pedidos
GROUP BY cliente_id
ORDER BY cliente_id
FETCH FIRST 6 ROWS ONLY;
CLIENTE_ID ULTIMA_FEC ULTIMO_PEDIDO ESTADO_ULTIM
---------- ---------- ------------- ------------
1001 15/06/2026 5021 ENVIADO
1002 03/03/2026 5018 ENTREGADO
1003 27/11/2025 5015 ENTREGADO
1004 08/09/2025 5012 ENTREGADO
1005 21/04/2026 5019 ENTREGADO
1006 25/06/2025 5011 ENTREGADO
6 filas seleccionadas.
KEEP también puede usarse como función analítica (con OVER). Junto a cada empleado, el más veterano de su departamento:
SELECT dept_id, nombre, salario,
MAX(nombre) KEEP (DENSE_RANK FIRST ORDER BY fecha_alta) OVER (PARTITION BY dept_id) AS veterano
FROM empleados
WHERE dept_id IN (20, 60)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO VETERANO
---------- --------------------------- ---------- ----------
20 Luis 4800 Luis
20 Elena 2300 Luis
20 Carmen 2200 Luis
20 David 2100 Luis
20 Sergio 1900 Luis
20 Lucía 1700 Luis
60 Javier 4000 Javier
60 Cristina 1750 Javier
60 Rosa 1600 Javier
60 Manuel 1600 Javier
10 filas seleccionadas.
| Técnica para “el X del máximo Y” | Ventaja |
|---|---|
| Subconsulta correlacionada (módulo 31) | Estándar, devuelve todos los empatados |
ROW_NUMBER en vista en línea | Estándar, control total de empates, permite top-N |
KEEP (DENSE_RANK LAST ...) | Muy conciso, una sola agrupación, varios “X” a la vez |
7. Consultas jerárquicas con CONNECT BY
📘 Concepto: Oracle dispone desde sus primeras versiones de una sintaxis propia para recorrer jerarquías almacenadas con una autorrelación (como
empleados.jefe_idocategorias.categoria_padre_id): la consulta jerárquica conSTART WITHyCONNECT BY. Es más concisa que la CTE recursiva del módulo 32 y aporta pseudocolumnas y funciones específicas.
SELECT columnas, LEVEL, ...
FROM tabla
[WHERE condición] -- filtra filas DESPUÉS de construir el árbol
START WITH condición_raíz -- filas por las que empieza el recorrido
CONNECT BY [NOCYCLE] PRIOR col_padre = col_hijo [AND condición] -- cómo se enlaza un padre con sus hijos
[ORDER SIBLINGS BY columnas]; -- ordena los hermanos sin romper el árbol
| Elemento | Significado |
|---|---|
START WITH | Condición que identifica las raíces del recorrido (puede haber varias) |
CONNECT BY PRIOR emp_id = jefe_id | “El emp_id de la fila anterior (padre) es el jefe_id de la fila hija”: recorrido hacia abajo |
CONNECT BY PRIOR jefe_id = emp_id | “El jefe_id del anterior es el emp_id del siguiente”: recorrido hacia arriba |
LEVEL | Pseudocolumna: profundidad (1 para las raíces) |
SYS_CONNECT_BY_PATH(col, 'sep') | Ruta desde la raíz hasta la fila |
CONNECT_BY_ROOT col | Valor de col en la raíz de la rama |
CONNECT_BY_ISLEAF | 1 si la fila no tiene hijos, 0 si los tiene |
CONNECT_BY_ISCYCLE | Con NOCYCLE: 1 si la fila tiene un hijo que es también antepasado suyo |
ORDER SIBLINGS BY | Ordena los hijos de cada nodo manteniendo la estructura de árbol |
7.1. El organigrama
SELECT emp_id, nombre, jefe_id, LEVEL AS nivel
FROM empleados
START WITH jefe_id IS NULL
CONNECT BY PRIOR emp_id = jefe_id;
EMP_ID NOMBRE JEFE_ID NIVEL
---------- --------------------------- ---------- ----------
100 Ana (nulo) 1
101 Luis 100 2
110 Carmen 101 3
111 David 101 3
112 Elena 101 3
113 Sergio 101 3
114 Lucía 101 3
102 Marta 100 2
...
24 filas seleccionadas.
Oracle devuelve las filas en orden de recorrido en profundidad (cada jefe seguido de todo su equipo). Con sangría y hermanos en orden alfabético:
SELECT LPAD(' ', 3 * (LEVEL - 1)) || nombre || ' (' || oficio || ')' AS organigrama,
LEVEL AS nivel
FROM empleados
START WITH jefe_id IS NULL
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
ORGANIGRAMA NIVEL
---------------------------------------- ----------
Ana (DIRECTOR GENERAL) 1
Javier (DIRECTOR) 2
Cristina (ADMINISTRATIVO) 3
Manuel (OPERARIO) 3
Rosa (OPERARIO) 3
Laura (DIRECTOR) 2
Álvaro (ANALISTA) 3
Nuria (DISEÑADOR) 3
Luis (DIRECTOR) 2
Carmen (VENDEDOR) 3
David (VENDEDOR) 3
Elena (VENDEDOR) 3
Lucía (ADMINISTRATIVO) 3
Sergio (VENDEDOR) 3
Marta (DIRECTOR) 2
Hugo (TÉCNICO SISTEMAS) 3
Pablo (ANALISTA) 3
Iván (PROGRAMADOR) 4
Raquel (PROGRAMADOR) 4
Sara (PROGRAMADOR) 4
Tomás (BECARIO) 3
Pedro (DIRECTOR) 2
Isabel (CONTABLE) 3
Jorge (ADMINISTRATIVO) 3
24 filas seleccionadas.
⚠️ Importante: Un
ORDER BYnormal destruye el orden jerárquico (mezclaría todos los niveles). Para ordenar en una consulta jerárquica usaORDER SIBLINGS BY, que ordena solo entre hermanos.
7.2. Ruta, raíz y hojas
SELECT emp_id,
SYS_CONNECT_BY_PATH(nombre, '/') AS ruta,
CONNECT_BY_ROOT nombre AS raiz,
CONNECT_BY_ISLEAF AS es_hoja
FROM empleados
START WITH emp_id = 102
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
EMP_ID RUTA RAIZ ES_HOJA
---------- -------------------------------------------------- ------------ ----------
102 /Marta Marta 0
124 /Marta/Hugo Marta 1
120 /Marta/Pablo Marta 0
122 /Marta/Pablo/Iván Marta 1
121 /Marta/Pablo/Raquel Marta 1
123 /Marta/Pablo/Sara Marta 1
160 /Marta/Tomás Marta 1
7 filas seleccionadas.
CONNECT_BY_ROOT es especialmente útil cuando hay varias raíces. Cada director con su equipo (primeras 10 filas):
SELECT CONNECT_BY_ROOT nombre AS raiz, nombre, LEVEL AS nivel
FROM empleados
START WITH oficio = 'DIRECTOR'
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre
FETCH FIRST 10 ROWS ONLY;
RAIZ NOMBRE NIVEL
------------ --------------------------- ----------
Javier Javier 1
Javier Cristina 2
Javier Manuel 2
Javier Rosa 2
Laura Laura 1
Laura Álvaro 2
Laura Nuria 2
Luis Luis 1
Luis Carmen 2
Luis David 2
10 filas seleccionadas.
7.3. Recorrido hacia arriba
Invirtiendo el PRIOR se sube desde un empleado hasta la raíz. Cadena de mando de Iván (122):
SELECT LEVEL AS paso, emp_id, nombre, oficio
FROM empleados
START WITH emp_id = 122
CONNECT BY PRIOR jefe_id = emp_id;
PASO EMP_ID NOMBRE OFICIO
---------- ---------- --------------------------- -----------------
1 122 Iván PROGRAMADOR
2 120 Pablo ANALISTA
3 102 Marta DIRECTOR
4 100 Ana DIRECTOR GENERAL
4 filas seleccionadas.
7.4. Filtrar filas frente a podar ramas
El orden de evaluación de una consulta jerárquica es: primero los JOIN (si los hay), después START WITH / CONNECT BY (se construye el árbol) y por último el WHERE. Esto tiene una consecuencia importante:
- Una condición en el
WHEREelimina filas sueltas del resultado, pero sus descendientes siguen apareciendo. - Una condición en el
CONNECT BYimpide seguir bajando por ese nodo: poda la rama entera.
Quitamos a Pablo (120) con WHERE:
SELECT LPAD(' ', 2 * (LEVEL - 1)) || nombre AS arbol, LEVEL AS nivel
FROM empleados
WHERE emp_id <> 120
START WITH emp_id = 102
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
ARBOL NIVEL
------------------------------ ----------
Marta 1
Hugo 2
Iván 3
Raquel 3
Sara 3
Tomás 2
6 filas seleccionadas.
Desaparece Pablo, pero sus subordinados siguen ahí (con nivel 3, como si colgaran de Hugo visualmente: ¡cuidado al interpretar la sangría!). Ahora con la condición en el CONNECT BY:
SELECT LPAD(' ', 2 * (LEVEL - 1)) || nombre AS arbol, LEVEL AS nivel
FROM empleados
START WITH emp_id = 102
CONNECT BY PRIOR emp_id = jefe_id
AND emp_id <> 120
ORDER SIBLINGS BY nombre;
ARBOL NIVEL
------------------------------ ----------
Marta 1
Hugo 2
Tomás 2
3 filas seleccionadas.
Se ha podado toda la rama de Pablo. Del mismo modo se limita la profundidad con LEVEL en el CONNECT BY:
SELECT LPAD(' ', 2 * (LEVEL - 1)) || nombre AS arbol, oficio, LEVEL AS nivel
FROM empleados
START WITH jefe_id IS NULL
CONNECT BY PRIOR emp_id = jefe_id
AND LEVEL <= 2
ORDER SIBLINGS BY nombre;
ARBOL OFICIO NIVEL
------------------------------ ----------------- ----------
Ana DIRECTOR GENERAL 1
Javier DIRECTOR 2
Laura DIRECTOR 2
Luis DIRECTOR 2
Marta DIRECTOR 2
Pedro DIRECTOR 2
6 filas seleccionadas.
(Con WHERE LEVEL <= 2 el resultado sería el mismo, pero Oracle construiría el árbol entero antes de filtrar.)
7.5. Consultas jerárquicas con combinaciones
Como el JOIN se evalúa antes de la jerarquía, se pueden combinar sin problemas:
SELECT LPAD(' ', 2 * (LEVEL - 1)) || e.nombre AS arbol, d.nombre AS departamento, d.localidad
FROM empleados e
LEFT JOIN departamentos d ON d.dept_id = e.dept_id
START WITH e.emp_id = 102
CONNECT BY PRIOR e.emp_id = e.jefe_id
ORDER SIBLINGS BY e.nombre;
ARBOL DEPARTAMENTO LOCALIDAD
------------------------------ -------------- ----------
Marta INFORMÁTICA Valencia
Hugo INFORMÁTICA Valencia
Pablo INFORMÁTICA Valencia
Iván INFORMÁTICA Valencia
Raquel INFORMÁTICA Valencia
Sara INFORMÁTICA Valencia
Tomás (nulo) (nulo)
7 filas seleccionadas.
El LEFT JOIN conserva a Tomás, que no tiene departamento.
7.6. El árbol de categorías
SELECT LPAD(' ', 4 * (LEVEL - 1)) || nombre AS arbol,
LEVEL AS nivel,
CONNECT_BY_ISLEAF AS hoja,
SYS_CONNECT_BY_PATH(nombre, ' > ') AS ruta
FROM categorias
START WITH categoria_padre_id IS NULL
CONNECT BY PRIOR categoria_id = categoria_padre_id
ORDER SIBLINGS BY nombre;
ARBOL NIVEL HOJA RUTA
------------------------------ ---------- ---------- --------------------------------------------------
Electrónica 1 0 > Electrónica
Audio 2 1 > Electrónica > Audio
Telefonía 2 1 > Electrónica > Telefonía
Informática 1 0 > Informática
Ordenadores sobremesa 2 1 > Informática > Ordenadores sobremesa
Periféricos 2 0 > Informática > Periféricos
Monitores 3 1 > Informática > Periféricos > Monitores
Ratones y teclados 3 1 > Informática > Periféricos > Ratones y teclados
Portátiles 2 1 > Informática > Portátiles
Oficina 1 0 > Oficina
Mobiliario 2 1 > Oficina > Mobiliario
11 filas seleccionadas.
SYS_CONNECT_BY_PATH pone el separador también delante del primer elemento.
CONNECT_BY_ROOT resuelve en una línea lo que en el módulo 31 necesitó dos autocombinaciones: la categoría raíz de cada categoría, a cualquier profundidad. Número de productos y valor del stock por familia:
SELECT raiz, COUNT(p.producto_id) AS productos, SUM(p.precio * p.stock) AS valor_stock
FROM (SELECT categoria_id, CONNECT_BY_ROOT nombre AS raiz
FROM categorias
START WITH categoria_padre_id IS NULL
CONNECT BY PRIOR categoria_id = categoria_padre_id) c
JOIN productos p ON p.categoria_id = c.categoria_id
GROUP BY raiz
ORDER BY valor_stock DESC;
RAIZ PRODUCTOS VALOR_STOCK
------------ ---------- -----------
Informática 13 101687
Electrónica 5 31640
Oficina 2 7374
3 filas seleccionadas.
7.7. Ciclos: NOCYCLE y CONNECT_BY_ISCYCLE
Simulemos un error en los datos: hacemos a Sara (123) jefa de Marta (102), que a su vez es jefa de Pablo, jefe de Sara. Un ciclo:
UPDATE empleados SET jefe_id = 123 WHERE emp_id = 102;
SELECT nombre, LEVEL
FROM empleados
START WITH emp_id = 102
CONNECT BY PRIOR emp_id = jefe_id;
1 fila actualizada.
ERROR:
ORA-01436: CONNECT BY loop in user data
Help: https://docs.oracle.com/error-help/db/ora-01436/
ninguna fila seleccionada
Con NOCYCLE, Oracle deja de bajar cuando encontraría un antepasado, y CONNECT_BY_ISCYCLE marca la fila donde se cerraría el ciclo:
SELECT LPAD(' ', 2 * (LEVEL - 1)) || nombre AS arbol, LEVEL AS nivel,
CONNECT_BY_ISCYCLE AS ciclo
FROM empleados
START WITH emp_id = 102
CONNECT BY NOCYCLE PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
ROLLBACK;
ARBOL NIVEL CICLO
------------------------------ ---------- ----------
Marta 1 0
Hugo 2 0
Pablo 2 0
Iván 3 0
Raquel 3 0
Sara 3 1
Tomás 2 0
7 filas seleccionadas.
Rollback terminado.
Sara tiene ciclo = 1: su “hija” (Marta) es también su antepasada. Es una herramienta excelente para detectar errores de datos en jerarquías.
7.8. Generar filas con CONNECT BY LEVEL
Un truco muy extendido en Oracle: una consulta jerárquica sobre dual sin condición de enlace real genera tantas filas como permita la condición sobre LEVEL:
SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 5;
SELECT TO_CHAR(DATE '2026-10-01' + LEVEL - 1, 'DD/MM/YYYY') AS dia
FROM dual
CONNECT BY LEVEL <= 3;
N
----------
1
2
3
4
5
5 filas seleccionadas.
DIA
----------
01/10/2026
02/10/2026
03/10/2026
3 filas seleccionadas.
Es más corto que la CTE recursiva del módulo 32, pero no es estándar.
7.9. Subconsultas jerárquicas
Una consulta jerárquica puede ir dentro de una subconsulta escalar. Número total de subordinados de cada jefe:
SELECT e.nombre,
(SELECT COUNT(*) - 1
FROM empleados s
START WITH s.emp_id = e.emp_id
CONNECT BY PRIOR s.emp_id = s.jefe_id) AS subordinados
FROM empleados e
WHERE e.oficio IN ('DIRECTOR', 'DIRECTOR GENERAL', 'ANALISTA')
ORDER BY subordinados DESC, e.nombre;
NOMBRE SUBORDINADOS
--------------------------- ------------
Ana 23
Marta 6
Luis 5
Javier 3
Pablo 3
Laura 2
Pedro 2
Álvaro 0
8 filas seleccionadas.
7.10. CONNECT BY frente a CTE recursiva
| Necesidad | CONNECT BY | CTE recursiva (módulo 32) |
|---|---|---|
| Nivel | LEVEL | Columna calculada nivel + 1 |
| Ruta | SYS_CONNECT_BY_PATH | Concatenación |
| Raíz de cada fila | CONNECT_BY_ROOT | Arrastrar la columna desde el ancla |
| ¿Es hoja? | CONNECT_BY_ISLEAF | NOT EXISTS sobre los hijos |
| Orden de árbol | ORDER SIBLINGS BY | SEARCH DEPTH FIRST BY |
| Ciclos | NOCYCLE + CONNECT_BY_ISCYCLE | CYCLE ... SET |
| Cálculos acumulados a lo largo del camino | Difícil | Natural |
| Portabilidad | Solo Oracle | Estándar SQL |
8. PIVOT y UNPIVOT
📘 Concepto: Pivotar (pivot) es convertir valores de filas en columnas: de “una fila por categoría y año” a “una fila por categoría, con una columna por año”, como una tabla dinámica de una hoja de cálculo. Despivotar (unpivot) es la operación inversa: convertir columnas en filas. Oracle lo soporta desde 11g con las cláusulas
PIVOTyUNPIVOT.
Para los ejemplos usaremos una vista auxiliar con cada línea vendida (pedidos no cancelados), su familia de producto (categoría raíz), año, trimestre, unidades e importe. Creamos la vista y vemos los datos agrupados “en vertical”:
CREATE OR REPLACE VIEW v_ventas AS
SELECT c.raiz,
EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
'T' || TO_CHAR(p.fecha_pedido, 'Q') AS trimestre,
l.cantidad,
ROUND(l.cantidad * l.precio_unitario * (1 - l.descuento / 100), 2) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
JOIN (SELECT categoria_id, CONNECT_BY_ROOT nombre AS raiz
FROM categorias
START WITH categoria_padre_id IS NULL
CONNECT BY PRIOR categoria_id = categoria_padre_id) c
ON c.categoria_id = pr.categoria_id
WHERE p.estado <> 'CANCELADO';
SELECT raiz, anio, SUM(importe) AS ventas
FROM v_ventas
GROUP BY raiz, anio
ORDER BY raiz, anio;
Vista creada.
RAIZ ANIO VENTAS
------------ ---------- ----------
Electrónica 2025 3936,93
Electrónica 2026 2504,05
Informática 2025 35331,74
Informática 2026 16904,74
Oficina 2025 1644
Oficina 2026 1296
6 filas seleccionadas.
8.1. PIVOT
SELECT *
FROM (consulta_origen)
PIVOT (agregado(columna_valor) FOR columna_a_pivotar IN (valor1 [AS alias1], valor2 [AS alias2], ...))
Ventas por familia con una columna por año:
SELECT *
FROM (SELECT raiz, anio, importe FROM v_ventas)
PIVOT (SUM(importe) FOR anio IN (2025 AS ventas_2025, 2026 AS ventas_2026))
ORDER BY raiz;
RAIZ VENTAS_2025 VENTAS_2026
------------ ----------- -----------
Electrónica 3936,93 2504,05
Informática 35331,74 16904,74
Oficina 1644 1296
3 filas seleccionadas.
Cómo funciona PIVOT:
- Toma la consulta de origen.
- Agrupa implícitamente por todas las columnas que no aparecen en el
PIVOT(aquí,raiz). - Para cada valor de la lista
IN, crea una columna con el agregado de las filas que tienen ese valor.
Ventas de 2025 por familia y trimestre:
SELECT *
FROM (SELECT raiz, trimestre, importe FROM v_ventas WHERE anio = 2025)
PIVOT (SUM(importe) FOR trimestre IN ('T1' AS t1, 'T2' AS t2, 'T3' AS t3, 'T4' AS t4))
ORDER BY raiz;
RAIZ T1 T2 T3 T4
------------ ---------- ---------- ---------- ----------
Electrónica 59,9 3538,13 (nulo) 338,9
Informática 16565,1 16429,94 1658,8 677,9
Oficina 747 (nulo) (nulo) 897
3 filas seleccionadas.
Las celdas (nulo) indican que no hubo ventas. Si prefieres ceros, envuelve las columnas en NVL en un SELECT exterior.
8.2. La trampa de las columnas sobrantes
Como PIVOT agrupa por todas las columnas no pivotadas, una columna de más en el origen estropea el resultado. Si dejamos cantidad en la consulta de origen:
SELECT *
FROM (SELECT raiz, anio, cantidad, importe FROM v_ventas)
PIVOT (SUM(importe) FOR anio IN (2025 AS ventas_2025, 2026 AS ventas_2026))
ORDER BY raiz
FETCH FIRST 6 ROWS ONLY;
RAIZ CANTIDAD VENTAS_2025 VENTAS_2026
------------ ---------- ----------- -----------
Electrónica 1 1397,8 1248,05
Electrónica 4 (nulo) 1136,2
Electrónica 2 558 119,8
Electrónica 6 1981,13 (nulo)
Informática 1 3705,9 4141,05
Informática 5 6457,5 (nulo)
6 filas seleccionadas.
¡Ahora agrupa por familia y cantidad! Por eso el origen de un PIVOT debe ser siempre una vista en línea que seleccione solo las columnas necesarias (nunca FROM tabla PIVOT ... directamente sobre una tabla con muchas columnas).
8.3. Varios agregados
Se pueden pivotar varios agregados a la vez; cada columna resultante se llama con el alias del valor, un guion bajo y el alias del agregado (A25_UDS):
SELECT *
FROM (SELECT raiz, anio, cantidad, importe FROM v_ventas)
PIVOT (SUM(cantidad) AS uds, SUM(importe) AS eur
FOR anio IN (2025 AS a25, 2026 AS a26))
ORDER BY raiz;
RAIZ A25_UDS A25_EUR A26_UDS A26_EUR
------------ ---------- ---------- ---------- ----------
Electrónica 18 3936,93 8 2504,05
Informática 141 35331,74 45 16904,74
Oficina 6 1644 4 1296
3 filas seleccionadas.
Aquí cantidad sí forma parte del PIVOT (como valor agregado), así que no provoca agrupación extra. Con COUNT(*) las celdas sin datos valen 0 en lugar de NULL. Empleados por departamento y oficio:
SELECT *
FROM (SELECT dept_id, oficio FROM empleados WHERE dept_id IS NOT NULL)
PIVOT (COUNT(*) FOR oficio IN ('DIRECTOR' AS dir, 'VENDEDOR' AS vend,
'PROGRAMADOR' AS prog, 'ADMINISTRATIVO' AS admin))
ORDER BY dept_id;
DEPT_ID DIR VEND PROG ADMIN
---------- ---------- ---------- ---------- ----------
10 0 0 0 0
20 1 4 0 1
30 1 0 3 0
40 1 0 0 1
50 1 0 0 0
60 1 0 0 1
6 filas seleccionadas.
DIRECCIÓN sale con ceros: Ana es DIRECTOR GENERAL, un oficio que no está en la lista. Los valores que no figuran en el IN simplemente se ignoran.
⚠️ Importante: La lista
INdelPIVOTdebe contener literales fijos: el número de columnas de una consulta SQL se decide al analizarla, no al ejecutarla. Si los valores no se conocen de antemano, existe la variantePIVOT XML(que devuelve una única columna XML) o hay que generar la sentencia dinámicamente desde un programa o con PL/SQL (ver el Curso de Oracle PL/SQL).
8.4. Pivotar a mano con CASE
Antes de 11g (y en cualquier SGBD) se pivota con agregados condicionales: SUM(CASE WHEN condición THEN valor END). Es más largo, pero más flexible: permite columnas calculadas, totales y condiciones arbitrarias:
SELECT raiz,
SUM(CASE WHEN anio = 2025 THEN importe END) AS ventas_2025,
SUM(CASE WHEN anio = 2026 THEN importe END) AS ventas_2026,
SUM(importe) AS total,
ROUND(100 * SUM(CASE WHEN anio = 2026 THEN importe END)
/ SUM(CASE WHEN anio = 2025 THEN importe END) - 100, 1) AS pct_var
FROM v_ventas
GROUP BY raiz
ORDER BY raiz;
RAIZ VENTAS_2025 VENTAS_2026 TOTAL PCT_VAR
------------ ----------- ----------- ---------- ----------
Electrónica 3936,93 2504,05 6440,98 -36,4
Informática 35331,74 16904,74 52236,48 -52,2
Oficina 1644 1296 2940 -21,2
3 filas seleccionadas.
(2026 solo incluye datos hasta julio, de ahí las caídas.) CASE sin ELSE devuelve NULL, que SUM ignora. También se usa DECODE(anio, 2025, importe) en código antiguo.
8.5. UNPIVOT
SELECT ...
FROM origen
UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS] (columna_valor FOR columna_nombre IN (col1 [AS 'etiqueta1'], col2 [AS 'etiqueta2'], ...))
Volver a convertir el resultado pivotado en filas:
WITH pivotada AS (
SELECT *
FROM (SELECT raiz, anio, importe FROM v_ventas)
PIVOT (SUM(importe) FOR anio IN (2025 AS ventas_2025, 2026 AS ventas_2026))
)
SELECT raiz, anio, ventas
FROM pivotada
UNPIVOT (ventas FOR anio IN (ventas_2025 AS 2025, ventas_2026 AS 2026))
ORDER BY raiz, anio;
RAIZ ANIO VENTAS
------------ ---------- ----------
Electrónica 2025 3936,93
Electrónica 2026 2504,05
Informática 2025 35331,74
Informática 2026 16904,74
Oficina 2025 1644
Oficina 2026 1296
6 filas seleccionadas.
Un caso real: una tabla con varias columnas “del mismo tipo” (teléfonos, correos, importes por mes) que se quiere tratar como filas. Contactos de los empleados de INFORMÁTICA en formato (tipo, valor):
SELECT emp_id, nombre, tipo, valor
FROM empleados
UNPIVOT (valor FOR tipo IN (telefono AS 'TELÉFONO', email AS 'EMAIL'))
WHERE dept_id = 30
ORDER BY emp_id, tipo;
EMP_ID NOMBRE TIPO VALOR
---------- --------------------------- --------- ----------------------------
102 Marta EMAIL marta.sanchez@empresa.es
102 Marta TELÉFONO 600100102
120 Pablo EMAIL pablo.moreno@empresa.es
120 Pablo TELÉFONO 600100120
121 Raquel EMAIL raquel.molina@empresa.es
121 Raquel TELÉFONO 600100121
122 Iván EMAIL ivan.castro@empresa.es
122 Iván TELÉFONO 600100122
123 Sara EMAIL sara.ortiz@empresa.es
124 Hugo EMAIL hugo.rubio@empresa.es
124 Hugo TELÉFONO 600100124
11 filas seleccionadas.
Sara no tiene teléfono y su fila de teléfono no aparece: por defecto UNPIVOT es EXCLUDE NULLS. Con INCLUDE NULLS se conservan:
SELECT emp_id, nombre, tipo, valor
FROM empleados
UNPIVOT INCLUDE NULLS (valor FOR tipo IN (telefono AS 'TELÉFONO', email AS 'EMAIL'))
WHERE emp_id IN (123, 131)
ORDER BY emp_id, tipo;
EMP_ID NOMBRE TIPO VALOR
---------- --------------------------- --------- ----------------------------
123 Sara EMAIL sara.ortiz@empresa.es
123 Sara TELÉFONO (nulo)
131 Jorge EMAIL jorge.vazquez@empresa.es
131 Jorge TELÉFONO (nulo)
4 filas seleccionadas.
Las columnas que se despivotan deben ser de tipos compatibles (aquí, ambas VARCHAR2). Presupuesto frente a coste anual de personal (14 pagas) por departamento:
SELECT dept_id, concepto, importe
FROM (SELECT d.dept_id, d.presupuesto,
(SELECT SUM(e.salario) * 14 FROM empleados e WHERE e.dept_id = d.dept_id) AS coste_anual
FROM departamentos d)
UNPIVOT (importe FOR concepto IN (presupuesto AS 'PRESUPUESTO', coste_anual AS 'COSTE ANUAL'))
WHERE dept_id <= 30
ORDER BY dept_id, concepto;
DROP VIEW v_ventas;
DEPT_ID CONCEPTO IMPORTE
---------- ------------ ----------
10 COSTE ANUAL 91000
10 PRESUPUESTO 500000
20 COSTE ANUAL 210000
20 PRESUPUESTO 350000
30 COSTE ANUAL 263200
30 PRESUPUESTO 420000
6 filas seleccionadas.
Vista borrada.
💡 Consejo: Antes de 11g,
UNPIVOTse escribía conUNION ALL:SELECT dept_id, 'PRESUPUESTO', presupuesto FROM ... UNION ALL SELECT dept_id, 'COSTE ANUAL', coste_anual FROM ....UNPIVOTlee la tabla una sola vez.
9. MATCH_RECOGNIZE: reconocimiento de patrones (introducción)
📘 Concepto:
MATCH_RECOGNIZE(Oracle 12c, estándar SQL:2016) busca patrones en secuencias de filas ordenadas usando una sintaxis parecida a las expresiones regulares: “una subida seguida de dos bajadas”, “tres pedidos seguidos cada vez mayores”, “sesiones de navegación con menos de 30 minutos entre clics”… Es una herramienta avanzada; aquí solo verás un ejemplo para saber que existe.
Clientes con una racha de pedidos de importe creciente:
SELECT *
FROM (SELECT p.cliente_id, p.pedido_id, p.fecha_pedido,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
GROUP BY p.cliente_id, p.pedido_id, p.fecha_pedido)
MATCH_RECOGNIZE (
PARTITION BY cliente_id
ORDER BY fecha_pedido
MEASURES FIRST(pedido_id) AS desde,
LAST(pedido_id) AS hasta,
COUNT(*) AS pedidos,
ROUND(LAST(importe) - FIRST(importe), 2) AS crecimiento
ONE ROW PER MATCH
PATTERN (inicio sube+)
DEFINE sube AS importe > PREV(importe)
)
ORDER BY cliente_id;
CLIENTE_ID DESDE HASTA PEDIDOS CRECIMIENTO
---------- ---------- ---------- ---------- -----------
1001 5006 5021 2 2022,85
1005 5005 5019 2 360
1008 5008 5024 2 699
1009 5014 5023 2 339
4 filas seleccionadas.
| Cláusula | Significado en el ejemplo |
|---|---|
PARTITION BY / ORDER BY | Secuencias independientes por cliente, en orden de fecha |
DEFINE sube AS importe > PREV(importe) | Una fila es sube si su importe supera al de la fila anterior |
PATTERN (inicio sube+) | Una fila cualquiera (inicio, sin definición: siempre cumple) seguida de una o más sube |
MEASURES | Qué calcular de cada coincidencia |
ONE ROW PER MATCH | Una fila por racha encontrada |
10. JSON en SQL (introducción)
Oracle incluye funciones del estándar SQL/JSON para generar JSON a partir de tablas relacionales y para leer JSON y convertirlo en filas y columnas. Es muy útil para alimentar aplicaciones web y APIs directamente desde la base de datos.
| Función | Para qué sirve |
|---|---|
JSON_OBJECT('clave' VALUE expr, ...) | Construye un objeto {...} por fila |
JSON_ARRAYAGG(expr ORDER BY ...) | Agregado: construye un array [...] con los valores de un grupo |
JSON_OBJECTAGG(KEY k VALUE v) | Agregado: construye un objeto con pares clave/valor de un grupo |
JSON_SERIALIZE(json PRETTY) | Da formato legible a un JSON |
JSON_VALUE(json, 'ruta') | Extrae un valor escalar |
JSON_QUERY(json, 'ruta') | Extrae un fragmento (objeto o array) |
JSON_TABLE(json, 'ruta' COLUMNS (...)) | Convierte un JSON en filas y columnas |
JSON_EXISTS(json, 'ruta') | Condición: ¿existe la ruta? |
10.1. Generar JSON
SELECT JSON_OBJECT('id' VALUE emp_id,
'nombre' VALUE nombre || ' ' || apellidos,
'salario' VALUE salario,
'comision' VALUE comision) AS json
FROM empleados
WHERE dept_id = 40;
JSON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id":103,"nombre":"Pedro Gómez Díaz","salario":4200,"comision":null}
{"id":130,"nombre":"Isabel Domínguez Vega","salario":2600,"comision":null}
{"id":131,"nombre":"Jorge Vázquez Ramos","salario":1650,"comision":null}
3 filas seleccionadas.
Por defecto los NULL se incluyen como null. Con ABSENT ON NULL la clave se omite:
SELECT JSON_OBJECT('id' VALUE emp_id, 'comision' VALUE comision ABSENT ON NULL) AS json
FROM empleados
WHERE emp_id IN (110, 120);
JSON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"id":110,"comision":500}
{"id":120}
2 filas seleccionadas.
Combinando JSON_OBJECT con el agregado JSON_ARRAYAGG y GROUP BY se generan documentos anidados. Cada departamento con el array de sus empleados:
SELECT JSON_OBJECT(
'departamento' VALUE d.nombre,
'empleados' VALUE JSON_ARRAYAGG(
JSON_OBJECT('id' VALUE e.emp_id, 'nombre' VALUE e.nombre, 'oficio' VALUE e.oficio)
ORDER BY e.emp_id)
) AS json
FROM departamentos d
JOIN empleados e ON e.dept_id = d.dept_id
WHERE d.dept_id IN (40, 50)
GROUP BY d.nombre;
JSON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"departamento":"CONTABILIDAD","empleados":[{"id":103,"nombre":"Pedro","oficio":"DIRECTOR"},{"id":130,"nombre":"Isabel","oficio":"CONTABLE"},{"id":131,"nombre":"Jorge","oficio":"ADMINISTRATIVO"}]}
{"departamento":"MARKETING","empleados":[{"id":104,"nombre":"Laura","oficio":"DIRECTOR"},{"id":140,"nombre":"Nuria","oficio":"DISEÑADOR"},{"id":141,"nombre":"Álvaro","oficio":"ANALISTA"}]}
2 filas seleccionadas.
Con JSON_SERIALIZE(... PRETTY) el resultado se formatea. Un pedido con sus líneas, usando una subconsulta escalar con JSON_ARRAYAGG:
SELECT JSON_SERIALIZE(
JSON_OBJECT('pedido' VALUE p.pedido_id,
'cliente' VALUE c.nombre,
'lineas' VALUE (SELECT JSON_ARRAYAGG(JSON_OBJECT('producto' VALUE l.producto_id,
'cantidad' VALUE l.cantidad)
ORDER BY l.num_linea)
FROM lineas_pedido l
WHERE l.pedido_id = p.pedido_id))
PRETTY) AS json
FROM pedidos p
JOIN clientes c ON c.cliente_id = p.cliente_id
WHERE p.pedido_id = 5005;
JSON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"pedido" : 5005,
"cliente" : "Clínica Dental Sonrisa",
"lineas" :
[
{
"producto" : 15,
"cantidad" : 3
},
{
"producto" : 18,
"cantidad" : 1
}
]
}
1 fila seleccionada.
💡 Consejo: Por defecto estas funciones devuelven
VARCHAR2(4000). Para documentos grandes añadeRETURNING CLOB(por ejemplo,JSON_ARRAYAGG(... RETURNING CLOB)), o, desde Oracle 21c,RETURNING JSONpara el tipo nativoJSON. En SQL*Plus, para ver columnasCLOBcompletas usaSET LONG 10000.
10.2. Leer JSON
Extraer valores sueltos con JSON_VALUE (la ruta empieza en $, el documento raíz):
WITH doc AS (
SELECT '{"pedido": 5005, "cliente": "Clínica Dental Sonrisa", "lineas": [{"producto": 15, "cantidad": 3}, {"producto": 18, "cantidad": 1}]}' AS j
FROM dual
)
SELECT JSON_VALUE(j, '$.pedido' RETURNING NUMBER) AS pedido,
JSON_VALUE(j, '$.cliente') AS cliente,
JSON_VALUE(j, '$.lineas[0].producto' RETURNING NUMBER) AS primer_producto,
JSON_VALUE(j, '$.lineas.size()' RETURNING NUMBER) AS num_lineas
FROM doc;
PEDIDO CLIENTE PRIMER_PRODUCTO NUM_LINEAS
---------- --------------------------- --------------- ----------
5005 Clínica Dental Sonrisa 15 2
1 fila seleccionada.
Los arrays se indexan desde 0 y size() es un método de ruta que devuelve su tamaño.
Convertir un JSON en filas con JSON_TABLE y combinarlo con tablas relacionales. NESTED PATH recorre el array de líneas:
WITH doc AS (
SELECT '{"pedido": 5005, "lineas": [{"producto": 15, "cantidad": 3}, {"producto": 18, "cantidad": 1}, {"producto": 99, "cantidad": 2}]}' AS j
FROM dual
)
SELECT jt.pedido, jt.prod_id, pr.nombre, jt.cantidad
FROM doc,
JSON_TABLE(doc.j, '$'
COLUMNS (pedido NUMBER PATH '$.pedido',
NESTED PATH '$.lineas[*]'
COLUMNS (prod_id NUMBER PATH '$.producto',
cantidad NUMBER PATH '$.cantidad'))) jt
LEFT JOIN productos pr ON pr.producto_id = jt.prod_id
ORDER BY jt.prod_id;
PEDIDO PROD_ID NOMBRE CANTIDAD
---------- ---------- --------------------------- ----------
5005 15 Silla Ergonómica 3
5005 18 Impresora Láser 1
5005 99 (nulo) 2
3 filas seleccionadas.
El producto 99 no existe en productos: el LEFT JOIN lo conserva con el nombre a NULL. Este patrón (recibir un JSON y validarlo contra las tablas) es habitual en integraciones con APIs.
11. Consultas de tipo informe
Las técnicas de este bloque se combinan en consultas que responden preguntas de negocio completas. Dos ejemplos.
11.1. Cuadro de mando de departamentos
Para cada departamento: empleados, masa salarial, media, mejor pagado, peso sobre la masa total, ranking, proyectos y horas.
WITH emp AS (
SELECT dept_id,
COUNT(*) AS num_emp,
SUM(salario) AS masa,
ROUND(AVG(salario), 2) AS media,
MAX(nombre) KEEP (DENSE_RANK LAST ORDER BY salario) AS mejor_pagado
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
),
proy AS (
SELECT p.dept_id, COUNT(DISTINCT p.proyecto_id) AS proyectos, NVL(SUM(a.horas), 0) AS horas
FROM proyectos p
LEFT JOIN asignaciones a ON a.proyecto_id = p.proyecto_id
GROUP BY p.dept_id
)
SELECT d.nombre AS departamento,
e.num_emp, e.masa, e.media, e.mejor_pagado,
ROUND(100 * RATIO_TO_REPORT(e.masa) OVER (), 1) AS pct_masa,
RANK() OVER (ORDER BY e.masa DESC) AS rk,
NVL(pr.proyectos, 0) AS proy,
NVL(pr.horas, 0) AS horas
FROM departamentos d
JOIN emp e ON e.dept_id = d.dept_id
LEFT JOIN proy pr ON pr.dept_id = d.dept_id
ORDER BY rk;
DEPARTAMENTO NUM_EMP MASA MEDIA MEJOR_PAGADO PCT_MASA RK PROY HORAS
-------------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
INFORMÁTICA 6 18800 3133,33 Marta 28,1 1 2 2780
VENTAS 6 15000 2500 Luis 22,4 2 0 0
MARKETING 3 9300 3100 Laura 13,9 3 1 480
LOGÍSTICA 4 8950 2237,5 Javier 13,4 4 1 300
CONTABILIDAD 3 8450 2816,67 Pedro 12,6 5 0 0
DIRECCIÓN 1 6500 6500 Ana 9,7 6 1 0
6 filas seleccionadas.
Ingredientes: dos CTE agregadas (módulo 32), KEEP para el nombre del mejor pagado, RATIO_TO_REPORT y RANK sobre el resultado agregado, y LEFT JOIN para no perder los departamentos sin proyectos. Las horas se agregan en una CTE separada para no multiplicar filas al combinar empleados y asignaciones.
11.2. Análisis de Pareto de productos
El principio de Pareto (regla 80/20) dice que una minoría de elementos suele concentrar la mayoría del efecto. Diez productos más vendidos con su peso y el porcentaje acumulado:
WITH ventas_prod AS (
SELECT pr.nombre AS producto,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS ventas
FROM lineas_pedido l
JOIN pedidos p ON p.pedido_id = l.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
WHERE p.estado <> 'CANCELADO'
GROUP BY pr.nombre
)
SELECT producto, ventas,
RANK() OVER (ORDER BY ventas DESC) AS rk,
ROUND(100 * RATIO_TO_REPORT(ventas) OVER (), 1) AS pct,
ROUND(100 * SUM(ventas) OVER (ORDER BY ventas DESC ROWS UNBOUNDED PRECEDING)
/ SUM(ventas) OVER (), 1) AS pct_acum
FROM ventas_prod
ORDER BY rk
FETCH FIRST 10 ROWS ONLY;
PRODUCTO VENTAS RK PCT PCT_ACUM
---------------------- ---------- ---------- ---------- ----------
Portátil UltraBook 14 12361,25 1 20,1 20,1
Portátil Básico 15 10479 2 17 37,1
Torre Oficina i5 6490 3 10,5 47,6
Torre Workstation 5146,29 4 8,4 56
Monitor 24 FHD 4934,5 5 8 64
Portátil Gaming 16 4497 6 7,3 71,3
Monitor 27 4K 4128,95 7 6,7 78
Smartphone Gama Media 3085,68 8 5 83
Smartphone Gama Alta 1948,05 9 3,2 86,1
Silla Ergonómica 1743 10 2,8 89
10 filas seleccionadas.
Ocho de los diecinueve productos vendidos generan más del 80 % de la facturación. Fíjate en que FETCH FIRST se aplica después de las funciones analíticas, por eso los porcentajes se calculan sobre todos los productos, no solo sobre los diez mostrados.
12. Errores frecuentes y buenas prácticas
| Error | Síntoma | Solución |
|---|---|---|
Función analítica en el WHERE | ORA-00934 (u ORA-30483) | Vista en línea / CTE, o QUALIFY en 26ai |
LAST_VALUE con ORDER BY y ventana por defecto | Devuelve la fila actual | ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
Acumulado con empates en el ORDER BY | Filas empatadas con el mismo acumulado | ROWS UNBOUNDED PRECEDING y desempate por clave |
ROW_NUMBER sin desempate | Resultados distintos en cada ejecución | Añadir la clave al ORDER BY del OVER |
Olvidar que la ventana opera tras el WHERE | “Totales” que no son totales | Calcular en una consulta sin filtro y filtrar fuera |
ORDER BY normal en una consulta jerárquica | Se pierde la estructura de árbol | ORDER SIBLINGS BY |
Condición en WHERE en lugar de en CONNECT BY | Aparecen descendientes de un nodo excluido | Poner la condición en CONNECT BY para podar |
| Ciclo en los datos | ORA-01436 | NOCYCLE y CONNECT_BY_ISCYCLE para localizarlo |
LEVEL en una subconsulta escalar sin CONNECT BY propio | ORA-01788 | Generar las filas en una CTE o vista en línea |
PIVOT directamente sobre una tabla | Una fila por cada combinación de columnas sobrantes | Vista en línea con solo las columnas necesarias |
Valores dinámicos en el IN de PIVOT | No se admite una subconsulta | Lista fija, PIVOT XML o SQL dinámico |
| JSON de más de 4000 bytes | Error o truncamiento | RETURNING CLOB |
13. Ejercicios
Todos los ejercicios usan el esquema EMPRESA (empresa_crear.sql, empresa_datos.sql).
Ejercicios resueltos
Ejercicio 1: Ranking dentro de cada oficio
Para los oficios DIRECTOR, ADMINISTRATIVO y VENDEDOR, muestra cada empleado con su puesto por salario dentro de su oficio.
Ver solución
SELECT oficio, nombre, salario,
RANK() OVER (PARTITION BY oficio ORDER BY salario DESC) AS puesto
FROM empleados
WHERE oficio IN ('DIRECTOR', 'ADMINISTRATIVO', 'VENDEDOR')
ORDER BY oficio, puesto;
OFICIO NOMBRE SALARIO PUESTO
----------------- --------------------------- ---------- ----------
ADMINISTRATIVO Cristina 1750 1
ADMINISTRATIVO Lucía 1700 2
ADMINISTRATIVO Jorge 1650 3
DIRECTOR Marta 5000 1
DIRECTOR Luis 4800 2
DIRECTOR Laura 4300 3
DIRECTOR Pedro 4200 4
DIRECTOR Javier 4000 5
VENDEDOR Elena 2300 1
VENDEDOR Carmen 2200 2
VENDEDOR David 2100 3
VENDEDOR Sergio 1900 4
12 filas seleccionadas.
Ejercicio 2: El tercer salario con DENSE_RANK
Muestra el empleado o empleados con el tercer salario más alto de la empresa (compáralo con el ejercicio 24 del módulo 31).
Ver solución
SELECT nombre, apellidos, salario
FROM (SELECT nombre, apellidos, salario,
DENSE_RANK() OVER (ORDER BY salario DESC) AS dr
FROM empleados)
WHERE dr = 3;
NOMBRE APELLIDOS SALARIO
--------------------------- ------------------ ----------
Luis Martínez Ruiz 4800
1 fila seleccionada.
Para el N-ésimo salario basta con cambiar el 3: no hacen falta N subconsultas anidadas. Con RANK en lugar de DENSE_RANK, el “tercer puesto” podría no existir si hubiera un empate en el segundo.
Ejercicio 3: Distancia al mínimo y al máximo
Para los departamentos 30 y 40, muestra cada empleado con cuánto cobra por encima del mínimo de su departamento y cuánto le falta para el máximo.
Ver solución
SELECT dept_id, nombre, salario,
salario - MIN(salario) OVER (PARTITION BY dept_id) AS sobre_min,
MAX(salario) OVER (PARTITION BY dept_id) - salario AS bajo_max
FROM empleados
WHERE dept_id IN (30, 40)
ORDER BY dept_id, salario DESC;
DEPT_ID NOMBRE SALARIO SOBRE_MIN BAJO_MAX
---------- --------------------------- ---------- ---------- ----------
30 Marta 5000 2600 0
30 Pablo 3500 1100 1500
30 Raquel 2800 400 2200
30 Iván 2600 200 2400
30 Hugo 2500 100 2500
30 Sara 2400 0 2600
40 Pedro 4200 2550 0
40 Isabel 2600 950 1600
40 Jorge 1650 0 2550
9 filas seleccionadas.
Ejercicio 4: Peso de cada línea en su pedido
Para el pedido 5010, muestra el importe de cada línea y el porcentaje que representa sobre el total del pedido.
Ver solución
SELECT num_linea, producto_id,
ROUND(cantidad * precio_unitario * (1 - descuento / 100), 2) AS importe,
ROUND(100 * RATIO_TO_REPORT(cantidad * precio_unitario * (1 - descuento / 100)) OVER (), 1) AS pct_pedido
FROM lineas_pedido
WHERE pedido_id = 5010
ORDER BY num_linea;
NUM_LINEA PRODUCTO_ID IMPORTE PCT_PEDIDO
---------- ----------- ---------- ----------
1 3 8483 72,8
2 9 2363 20,3
3 8 586,5 5
4 17 225 1,9
4 filas seleccionadas.
Para hacerlo para todos los pedidos a la vez: RATIO_TO_REPORT(...) OVER (PARTITION BY pedido_id).
Ejercicio 5: Unidades acumuladas de un producto
Para el “Monitor 24 FHD” (producto 9), muestra cada venta con la fecha, la cantidad y las unidades acumuladas vendidas hasta ese momento.
Ver solución
SELECT p.fecha_pedido, l.pedido_id, l.cantidad,
SUM(l.cantidad) OVER (ORDER BY p.fecha_pedido, l.pedido_id) AS acumulado
FROM lineas_pedido l
JOIN pedidos p ON p.pedido_id = l.pedido_id
WHERE l.producto_id = 9
ORDER BY p.fecha_pedido;
FECHA_PEDI PEDIDO_ID CANTIDAD ACUMULADO
---------- ---------- ---------- ----------
15/01/2025 5001 2 2
28/01/2025 5002 10 12
02/06/2025 5010 20 32
30/05/2026 5020 8 40
4 filas seleccionadas.
Se añade pedido_id al ORDER BY para que dos ventas del mismo día no reciban el mismo acumulado.
Ejercicio 6: Media móvil de los tres últimos pedidos
Para los pedidos de 2026, muestra el importe de cada uno y la media de su importe y el de los dos pedidos anteriores.
Ver solución
WITH importes AS (
SELECT p.pedido_id, p.fecha_pedido,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.fecha_pedido >= DATE '2026-01-01'
GROUP BY p.pedido_id, p.fecha_pedido
)
SELECT pedido_id, fecha_pedido, importe,
ROUND(AVG(importe) OVER (ORDER BY fecha_pedido ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS media_3
FROM importes
ORDER BY fecha_pedido;
PEDIDO_ID FECHA_PEDI IMPORTE MEDIA_3
---------- ---------- ---------- ----------
5016 12/01/2026 8945,05 8945,05
5017 09/02/2026 838 4891,53
5018 03/03/2026 2085,25 3956,1
5019 21/04/2026 1296 1406,42
5020 30/05/2026 1663,64 1681,63
5021 15/06/2026 2462,05 1807,23
5022 01/07/2026 1499 1874,9
5023 08/07/2026 418,8 1459,95
5024 10/07/2026 1497 1138,27
9 filas seleccionadas.
Ejercicio 7: Los mayores huecos entre pedidos
Calcula los días transcurridos entre cada pedido y el anterior (de cualquier cliente) y muestra los tres huecos más largos.
Ver solución
SELECT pedido_id, fecha_pedido, hueco
FROM (SELECT pedido_id, fecha_pedido,
fecha_pedido - LAG(fecha_pedido) OVER (ORDER BY fecha_pedido) AS hueco
FROM pedidos)
ORDER BY hueco DESC NULLS LAST
FETCH FIRST 3 ROWS ONLY;
PEDIDO_ID FECHA_PEDI HUECO
---------- ---------- ----------
5012 08/09/2025 75
5019 21/04/2026 49
5016 12/01/2026 46
3 filas seleccionadas.
NULLS LAST evita que el primer pedido (sin anterior, hueco NULL) aparezca arriba: en Oracle los NULL van al principio en un orden descendente.
Ejercicio 8: Primer y último pedido de cada cliente
Muestra, en una sola fila por cliente, su primer y su último pedido, de dos formas: con FIRST_VALUE/LAST_VALUE y con KEEP. (Muestra los cinco primeros clientes.)
Ver solución
SELECT DISTINCT cliente_id,
FIRST_VALUE(pedido_id) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS primer_pedido,
LAST_VALUE(pedido_id) OVER (PARTITION BY cliente_id ORDER BY fecha_pedido
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ultimo_pedido
FROM pedidos
ORDER BY cliente_id
FETCH FIRST 5 ROWS ONLY;
SELECT cliente_id,
MIN(pedido_id) KEEP (DENSE_RANK FIRST ORDER BY fecha_pedido) AS primer_pedido,
MAX(pedido_id) KEEP (DENSE_RANK LAST ORDER BY fecha_pedido) AS ultimo_pedido
FROM pedidos
GROUP BY cliente_id
ORDER BY cliente_id
FETCH FIRST 5 ROWS ONLY;
CLIENTE_ID PRIMER_PEDIDO ULTIMO_PEDIDO
---------- ------------- -------------
1001 5001 5021
1002 5002 5018
1003 5003 5015
1004 5004 5012
1005 5005 5019
5 filas seleccionadas.
CLIENTE_ID PRIMER_PEDIDO ULTIMO_PEDIDO
---------- ------------- -------------
1001 5001 5021
1002 5002 5018
1003 5003 5015
1004 5004 5012
1005 5005 5019
5 filas seleccionadas.
La versión analítica necesita DISTINCT (todas las filas del cliente tienen los mismos valores) y la ventana completa en LAST_VALUE. La versión con KEEP es más natural para este problema.
Ejercicio 9: Veterano y novato de cada departamento
Muestra, por departamento, el nombre y la fecha de alta del empleado más antiguo y del más reciente.
Ver solución
SELECT dept_id,
MIN(nombre) KEEP (DENSE_RANK FIRST ORDER BY fecha_alta) AS veterano,
MIN(fecha_alta) AS alta_veterano,
MAX(nombre) KEEP (DENSE_RANK LAST ORDER BY fecha_alta) AS novato,
MAX(fecha_alta) AS alta_novato
FROM empleados
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;
DEPT_ID VETERANO ALTA_VETER NOVATO ALTA_NOVAT
---------- ---------- ---------- ---------- ----------
10 Ana 15/01/2010 Ana 15/01/2010
20 Luis 01/03/2012 Sergio 01/09/2021
30 Marta 20/06/2011 Sara 01/06/2022
40 Pedro 10/09/2013 Jorge 09/01/2023
50 Laura 02/02/2015 Álvaro 12/04/2021
60 Javier 11/11/2014 Manuel 17/10/2022
6 filas seleccionadas.
Ejercicio 10: Cuartiles de clientes
Calcula la facturación de cada cliente (pedidos no cancelados; 0 si no tiene) y clasifícalos en cuartiles con NTILE.
Ver solución
WITH fact AS (
SELECT c.cliente_id, c.nombre,
ROUND(NVL(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 0), 2) AS facturado
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.cliente_id AND p.estado <> 'CANCELADO'
LEFT JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
GROUP BY c.cliente_id, c.nombre
)
SELECT nombre AS cliente, facturado,
NTILE(4) OVER (ORDER BY facturado DESC) AS cuartil
FROM fact
ORDER BY facturado DESC;
CLIENTE FACTURADO CUARTIL
--------------------------- ---------- ----------
Instituto Tecnológico Sur 20602,55 1
Construcciones Levante S.A. 14260,19 1
Academia Nova 7891,3 1
Talleres Martín S.L. 4704,15 1
Hotel Mar Azul S.L. 4202,77 2
Diseños Creativos S.L. 3054 2
Clínica Dental Sonrisa 2232 2
Gestoría Delta 1497 2
Antonio Ruiz Pérez 999 3
Panadería La Espiga 838 3
María José Herrera 837,9 3
Laura Benítez Sanz 498,6 4
Carlos Ibáñez Rey 0 4
Farmacia Central 0 4
14 filas seleccionadas.
14 filas en 4 grupos: 4, 4, 3 y 3 (los primeros grupos reciben las filas sobrantes). La condición p.estado <> 'CANCELADO' va en el ON del LEFT JOIN para no perder a los clientes sin pedidos.
Ejercicio 11: El 25 % mejor pagado
Lista los empleados que están en el 25 % superior de salarios según CUME_DIST.
Ver solución
SELECT nombre, salario, ROUND(cd, 3) AS cume_dist
FROM (SELECT nombre, salario,
CUME_DIST() OVER (ORDER BY salario DESC) AS cd
FROM empleados)
WHERE cd <= 0.25
ORDER BY salario DESC;
NOMBRE SALARIO CUME_DIST
--------------------------- ---------- ----------
Ana 6500 ,042
Marta 5000 ,083
Luis 4800 ,125
Laura 4300 ,167
Pedro 4200 ,208
Javier 4000 ,25
6 filas seleccionadas.
Ejercicio 12: Renumerar las líneas por importe
Para el pedido 5007, numera las líneas de mayor a menor importe y muéstralas junto a su número de línea original.
Ver solución
SELECT ROW_NUMBER() OVER (ORDER BY cantidad * precio_unitario * (1 - descuento / 100) DESC) AS nueva_linea,
num_linea AS linea_original, producto_id,
ROUND(cantidad * precio_unitario * (1 - descuento / 100), 2) AS importe
FROM lineas_pedido
WHERE pedido_id = 5007
ORDER BY nueva_linea;
NUEVA_LINEA LINEA_ORIGINAL PRODUCTO_ID IMPORTE
----------- -------------- ----------- ----------
1 1 11 1650,48
2 3 20 558
3 2 13 330,65
3 filas seleccionadas.
Ejercicio 13: El pedido estrella de cada mes
Para el primer semestre de 2025, muestra el pedido de mayor importe de cada mes.
Ver solución
WITH importes AS (
SELECT p.pedido_id, TRUNC(p.fecha_pedido, 'MM') AS mes,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.fecha_pedido < DATE '2025-07-01'
GROUP BY p.pedido_id, TRUNC(p.fecha_pedido, 'MM')
)
SELECT TO_CHAR(mes, 'YYYY-MM') AS mes, pedido_id, importe
FROM (SELECT i.*, ROW_NUMBER() OVER (PARTITION BY mes ORDER BY importe DESC) AS rn
FROM importes i)
WHERE rn = 1
ORDER BY mes;
MES PEDIDO_ID IMPORTE
------- ---------- ----------
2025-01 5002 7402,5
2025-02 5004 6232,5
2025-03 5005 936
2025-04 5007 2539,13
2025-05 5009 4772,44
2025-06 5010 11657,5
6 filas seleccionadas.
Ejercicio 14: Comparar con la incorporación anterior
En VENTAS, muestra cada empleado por orden de alta, el nombre de quien se incorporó justo antes y la diferencia de salario entre ambos.
Ver solución
SELECT dept_id, nombre, fecha_alta, salario,
LAG(nombre) OVER (PARTITION BY dept_id ORDER BY fecha_alta) AS anterior,
salario - LAG(salario) OVER (PARTITION BY dept_id ORDER BY fecha_alta) AS dif_salario
FROM empleados
WHERE dept_id = 20
ORDER BY fecha_alta;
DEPT_ID NOMBRE FECHA_ALTA SALARIO ANTERIOR DIF_SALARIO
---------- --------------------------- ---------- ---------- ---------- -----------
20 Luis 01/03/2012 4800 (nulo) (nulo)
20 Carmen 12/04/2016 2200 Luis -2600
20 David 01/07/2017 2100 Carmen -100
20 Elena 15/01/2019 2300 David 200
20 Lucía 10/02/2020 1700 Elena -600
20 Sergio 01/09/2021 1900 Lucía 200
6 filas seleccionadas.
Ejercicio 15: Ranking de comerciales
Muestra las ventas de cada comercial (pedidos no cancelados de sus clientes), su puesto, su porcentaje sobre el total y el porcentaje acumulado.
Ver solución
WITH ventas AS (
SELECT c.comercial_id,
ROUND(SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)), 2) AS vendido
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.cliente_id
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
AND c.comercial_id IS NOT NULL
GROUP BY c.comercial_id
)
SELECT e.nombre, v.vendido,
RANK() OVER (ORDER BY v.vendido DESC) AS puesto,
ROUND(100 * RATIO_TO_REPORT(v.vendido) OVER (), 1) AS pct,
ROUND(100 * SUM(v.vendido) OVER (ORDER BY v.vendido DESC) / SUM(v.vendido) OVER (), 1) AS pct_acum
FROM ventas v
JOIN empleados e ON e.emp_id = v.comercial_id
ORDER BY puesto;
NOMBRE VENDIDO PUESTO PCT PCT_ACUM
--------------------------- ---------- ---------- ---------- ----------
Elena 23833,55 1 39 39
Carmen 22018,34 2 36 75
David 12931,97 3 21,2 96,2
Sergio 2335 4 3,8 100
4 filas seleccionadas.
Ejercicio 16: Productos del 80 %
Lista los productos que, ordenados de más a menos ventas, se necesitan para alcanzar el 80 % de la facturación (incluido el que lo sobrepasa).
Ver solución
WITH ventas_prod AS (
SELECT pr.nombre AS producto,
SUM(l.cantidad * l.precio_unitario * (1 - l.descuento / 100)) AS ventas
FROM lineas_pedido l
JOIN pedidos p ON p.pedido_id = l.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
WHERE p.estado <> 'CANCELADO'
GROUP BY pr.nombre
),
acum AS (
SELECT producto, ventas,
SUM(ventas) OVER (ORDER BY ventas DESC ROWS UNBOUNDED PRECEDING) / SUM(ventas) OVER () AS pct_acum,
(SUM(ventas) OVER (ORDER BY ventas DESC ROWS UNBOUNDED PRECEDING) - ventas) / SUM(ventas) OVER () AS pct_previo
FROM ventas_prod
)
SELECT producto, ROUND(ventas, 2) AS ventas, ROUND(100 * pct_acum, 1) AS pct_acum
FROM acum
WHERE pct_previo < 0.80
ORDER BY ventas DESC;
PRODUCTO VENTAS PCT_ACUM
---------------------- ---------- ----------
Portátil UltraBook 14 12361,25 20,1
Portátil Básico 15 10479 37,1
Torre Oficina i5 6490 47,6
Torre Workstation 5146,29 56
Monitor 24 FHD 4934,5 64
Portátil Gaming 16 4497 71,3
Monitor 27 4K 4128,95 78
Smartphone Gama Media 3085,68 83
8 filas seleccionadas.
pct_previo es el acumulado antes de sumar el producto: el filtro < 0.80 incluye el producto que cruza el umbral.
Ejercicio 17: El equipo de Luis
Muestra con CONNECT BY el equipo de Luis Martínez (101), con sangría, la ruta de códigos y si es hoja.
Ver solución
SELECT LPAD(' ', 2 * (LEVEL - 1)) || nombre AS arbol,
SYS_CONNECT_BY_PATH(emp_id, '/') AS ruta,
CONNECT_BY_ISLEAF AS hoja
FROM empleados
START WITH emp_id = 101
CONNECT BY PRIOR emp_id = jefe_id
ORDER SIBLINGS BY nombre;
ARBOL RUTA HOJA
------------------------------ -------------------------------------------------- ----------
Luis /101 0
Carmen /101/110 1
David /101/111 1
Elena /101/112 1
Lucía /101/114 1
Sergio /101/113 1
6 filas seleccionadas.
Ejercicio 18: Empleados y hojas por nivel
Cuenta, para cada nivel del organigrama, cuántos empleados hay y cuántos de ellos no tienen subordinados.
Ver solución
SELECT LEVEL AS nivel,
COUNT(*) AS empleados,
SUM(CONNECT_BY_ISLEAF) AS hojas
FROM empleados
START WITH jefe_id IS NULL
CONNECT BY PRIOR emp_id = jefe_id
GROUP BY LEVEL
ORDER BY nivel;
NIVEL EMPLEADOS HOJAS
---------- ---------- ----------
1 1 0
2 5 0
3 15 14
4 3 3
4 filas seleccionadas.
En el nivel 3 solo Pablo tiene subordinados. Se puede agrupar por LEVEL y agregar CONNECT_BY_ISLEAF.
Ejercicio 19: El director de cada empleado
Para cada empleado que no sea director (ni la directora general), muestra su director de área (el empleado con oficio DIRECTOR del que depende) y a cuántos niveles está de él.
Ver solución
SELECT nombre, oficio, CONNECT_BY_ROOT nombre AS director, LEVEL - 1 AS distancia
FROM empleados
WHERE LEVEL > 1
START WITH oficio = 'DIRECTOR'
CONNECT BY PRIOR emp_id = jefe_id
ORDER BY director, distancia, nombre;
NOMBRE OFICIO DIRECTOR DISTANCIA
--------------------------- ----------------- ---------- ----------
Cristina ADMINISTRATIVO Javier 1
Manuel OPERARIO Javier 1
Rosa OPERARIO Javier 1
Álvaro ANALISTA Laura 1
Nuria DISEÑADOR Laura 1
Carmen VENDEDOR Luis 1
David VENDEDOR Luis 1
Elena VENDEDOR Luis 1
...
18 filas seleccionadas.
WHERE LEVEL > 1 quita a los propios directores después de construir el árbol. Aquí sí se puede usar ORDER BY normal, porque no nos interesa la forma de árbol.
Ejercicio 20: Ruta de los periféricos
Muestra todos los productos de la categoría “Periféricos” y de sus subcategorías, con la ruta de categorías desde “Periféricos”.
Ver solución
SELECT pr.nombre AS producto, c.ruta
FROM productos pr
JOIN (SELECT categoria_id, SYS_CONNECT_BY_PATH(nombre, ' / ') AS ruta
FROM categorias
START WITH nombre = 'Periféricos'
CONNECT BY PRIOR categoria_id = categoria_padre_id) c
ON c.categoria_id = pr.categoria_id
ORDER BY c.ruta, pr.nombre;
PRODUCTO RUTA
---------------------- --------------------------------------------------
Impresora Láser / Periféricos
Webcam HD / Periféricos
Monitor 24 FHD / Periféricos / Monitores
Monitor 27 4K / Periféricos / Monitores
Pack teclado y ratón / Periféricos / Ratones y teclados
Ratón inalámbrico / Periféricos / Ratones y teclados
Teclado mecánico / Periféricos / Ratones y teclados
7 filas seleccionadas.
La consulta jerárquica en la vista en línea obtiene todas las categorías del subárbol; el JOIN trae sus productos.
Ejercicio 21: Profundidad de cada equipo
Para cada director, muestra cuántos niveles hay por debajo de él y cuántas personas tiene en total.
Ver solución
SELECT CONNECT_BY_ROOT nombre AS director, MAX(LEVEL) - 1 AS niveles_debajo, COUNT(*) - 1 AS personas
FROM empleados
START WITH oficio = 'DIRECTOR'
CONNECT BY PRIOR emp_id = jefe_id
GROUP BY CONNECT_BY_ROOT nombre
ORDER BY niveles_debajo DESC, director;
DIRECTOR NIVELES_DEBAJO PERSONAS
---------- -------------- ----------
Marta 2 6
Javier 1 3
Laura 1 2
Luis 1 5
Pedro 1 2
5 filas seleccionadas.
Ejercicio 22: Pedidos por mes con CONNECT BY LEVEL
Genera los doce meses de 2026 con CONNECT BY LEVEL y muestra cuántos pedidos hay en cada uno.
Ver solución
Un primer intento natural es usar LEVEL dentro de una subconsulta escalar:
SELECT TO_CHAR(ADD_MONTHS(DATE '2026-01-01', LEVEL - 1), 'YYYY-MM') AS mes,
(SELECT COUNT(*)
FROM pedidos p
WHERE TRUNC(p.fecha_pedido, 'MM') = ADD_MONTHS(DATE '2026-01-01', LEVEL - 1)) AS pedidos
FROM dual
CONNECT BY LEVEL <= 12;
(SELECT COUNT(*)
*
ERROR en línea 2:
ORA-01788: CONNECT BY clause required in this query block
Help: https://docs.oracle.com/error-help/db/ora-01788/
LEVEL solo existe en el bloque de consulta que tiene el CONNECT BY; la subconsulta es otro bloque. Se genera la serie en una CTE y se usa su columna:
WITH meses AS (
SELECT ADD_MONTHS(DATE '2026-01-01', LEVEL - 1) AS mes
FROM dual
CONNECT BY LEVEL <= 12
)
SELECT TO_CHAR(m.mes, 'YYYY-MM') AS mes,
(SELECT COUNT(*)
FROM pedidos p
WHERE TRUNC(p.fecha_pedido, 'MM') = m.mes) AS pedidos
FROM meses m
ORDER BY m.mes;
MES PEDIDOS
------- ----------
2026-01 1
2026-02 1
2026-03 1
2026-04 1
2026-05 1
2026-06 1
2026-07 3
2026-08 0
2026-09 0
2026-10 0
2026-11 0
2026-12 0
12 filas seleccionadas.
Ejercicio 23: Pedidos por año y estado
Muestra con PIVOT el número de pedidos de cada año (filas) por estado (columnas).
Ver solución
SELECT *
FROM (SELECT EXTRACT(YEAR FROM fecha_pedido) AS anio, estado FROM pedidos)
PIVOT (COUNT(*) FOR estado IN ('PENDIENTE' AS pendiente, 'ENVIADO' AS enviado,
'ENTREGADO' AS entregado, 'CANCELADO' AS cancelado))
ORDER BY anio;
ANIO PENDIENTE ENVIADO ENTREGADO CANCELADO
---------- ---------- ---------- ---------- ----------
2025 0 0 14 1
2026 3 2 4 0
2 filas seleccionadas.
Ejercicio 24: Unidades por familia y trimestre
Muestra las unidades vendidas en 2025 (pedidos no cancelados) por familia de producto (categoría raíz) y trimestre, con PIVOT.
Ver solución
SELECT *
FROM (SELECT c.raiz, 'T' || TO_CHAR(p.fecha_pedido, 'Q') AS trimestre, l.cantidad
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
JOIN productos pr ON pr.producto_id = l.producto_id
JOIN (SELECT categoria_id, CONNECT_BY_ROOT nombre AS raiz
FROM categorias
START WITH categoria_padre_id IS NULL
CONNECT BY PRIOR categoria_id = categoria_padre_id) c
ON c.categoria_id = pr.categoria_id
WHERE p.fecha_pedido >= DATE '2025-01-01'
AND p.fecha_pedido < DATE '2026-01-01'
AND p.estado <> 'CANCELADO')
PIVOT (SUM(cantidad) FOR trimestre IN ('T1' AS t1, 'T2' AS t2, 'T3' AS t3, 'T4' AS t4))
ORDER BY raiz;
RAIZ T1 T2 T3 T4
------------ ---------- ---------- ---------- ----------
Electrónica 1 15 (nulo) 2
Informática 60 75 3 3
Oficina 3 (nulo) (nulo) 3
3 filas seleccionadas.
Ejercicio 25: Masa salarial en una fila
Muestra en una sola fila la masa salarial de DIRECCIÓN, VENTAS, INFORMÁTICA, el resto de departamentos, los empleados sin departamento y el total, con CASE.
Ver solución
SELECT SUM(CASE WHEN dept_id = 10 THEN salario END) AS direccion,
SUM(CASE WHEN dept_id = 20 THEN salario END) AS ventas,
SUM(CASE WHEN dept_id = 30 THEN salario END) AS informatica,
SUM(CASE WHEN dept_id IN (40, 50, 60) THEN salario END) AS otros,
SUM(CASE WHEN dept_id IS NULL THEN salario END) AS sin_dept,
SUM(salario) AS total
FROM empleados;
DIRECCION VENTAS INFORMATICA OTROS SIN_DEPT TOTAL
---------- ---------- ----------- ---------- ---------- ----------
6500 15000 18800 26700 900 67900
1 fila seleccionada.
Con CASE se pueden agrupar varios valores en una columna (otros) y tratar los nulos (sin_dept), cosas que PIVOT no permite directamente.
Ejercicio 26: Despivotar el resumen de pedidos
Toma el resultado del ejercicio 23 y conviértelo de nuevo en filas (año, estado, pedidos), sin las combinaciones con 0 pedidos.
Ver solución
WITH resumen AS (
SELECT *
FROM (SELECT EXTRACT(YEAR FROM fecha_pedido) AS anio, estado FROM pedidos)
PIVOT (COUNT(*) FOR estado IN ('PENDIENTE' AS pendiente, 'ENVIADO' AS enviado,
'ENTREGADO' AS entregado, 'CANCELADO' AS cancelado))
)
SELECT anio, estado, pedidos
FROM resumen
UNPIVOT (pedidos FOR estado IN (pendiente AS 'PENDIENTE', enviado AS 'ENVIADO',
entregado AS 'ENTREGADO', cancelado AS 'CANCELADO'))
WHERE pedidos > 0
ORDER BY anio, pedidos DESC;
ANIO ESTADO PEDIDOS
---------- ---------- ----------
2025 ENTREGADO 14
2025 CANCELADO 1
2026 ENTREGADO 4
2026 PENDIENTE 3
2026 ENVIADO 2
5 filas seleccionadas.
Los ceros no son NULL, así que EXCLUDE NULLS no los elimina: hace falta el WHERE.
Ejercicio 27: Conceptos económicos por departamento
Para INFORMÁTICA y MARKETING, muestra en filas su presupuesto, su coste anual de personal (salarios × 14) y la suma de presupuestos de sus proyectos, con UNPIVOT.
Ver solución
SELECT d.nombre AS departamento, u.concepto, u.importe
FROM (SELECT dept_id, presupuesto,
(SELECT SUM(e.salario) * 14 FROM empleados e WHERE e.dept_id = d.dept_id) AS coste_anual,
(SELECT NVL(SUM(p.presupuesto), 0) FROM proyectos p WHERE p.dept_id = d.dept_id) AS proyectos
FROM departamentos d)
UNPIVOT (importe FOR concepto IN (presupuesto AS 'PRESUPUESTO', coste_anual AS 'PERSONAL',
proyectos AS 'PROYECTOS')) u
JOIN departamentos d ON d.dept_id = u.dept_id
WHERE u.dept_id IN (30, 50)
ORDER BY d.nombre, u.importe DESC;
DEPARTAMENTO CONCEPTO IMPORTE
-------------- ------------ ----------
INFORMÁTICA PRESUPUESTO 420000
INFORMÁTICA PERSONAL 263200
INFORMÁTICA PROYECTOS 210000
MARKETING PRESUPUESTO 200000
MARKETING PERSONAL 130200
MARKETING PROYECTOS 40000
6 filas seleccionadas.
El resultado de UNPIVOT (con alias u) se puede combinar con otras tablas como cualquier tabla derivada.
Ejercicio 28: El historial de un cliente en JSON
Genera un documento JSON formateado con el nombre y la ciudad del cliente 1002 y un array con sus pedidos (id, fecha en formato ISO y estado) ordenados por fecha.
Ver solución
SELECT JSON_SERIALIZE(
JSON_OBJECT('cliente' VALUE c.nombre,
'ciudad' VALUE c.ciudad,
'pedidos' VALUE JSON_ARRAYAGG(
JSON_OBJECT('id' VALUE p.pedido_id,
'fecha' VALUE TO_CHAR(p.fecha_pedido, 'YYYY-MM-DD'),
'estado' VALUE p.estado)
ORDER BY p.fecha_pedido))
PRETTY) AS json
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.cliente_id
WHERE c.cliente_id = 1002
GROUP BY c.nombre, c.ciudad;
JSON
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
"cliente" : "Construcciones Levante S.A.",
"ciudad" : "Alicante",
"pedidos" :
[
{
"id" : 5002,
"fecha" : "2025-01-28",
"estado" : "ENTREGADO"
},
{
"id" : 5009,
"fecha" : "2025-05-19",
"estado" : "ENTREGADO"
},
{
"id" : 5018,
"fecha" : "2026-03-03",
"estado" : "ENTREGADO"
}
]
}
1 fila seleccionada.
Convertir la fecha con TO_CHAR y un formato ISO evita que el JSON dependa del formato de fecha de la sesión.
Ejercicio 29: Comparar una tarifa en JSON con los precios actuales
Un proveedor envía esta tarifa: [{"id": 1, "precio": 949}, {"id": 2, "precio": 1499}, {"id": 9, "precio": 129}]. Conviértela en filas con JSON_TABLE y compárala con los precios actuales de productos.
Ver solución
WITH tarifa AS (
SELECT '[{"id": 1, "precio": 949}, {"id": 2, "precio": 1499}, {"id": 9, "precio": 129}]' AS j
FROM dual
)
SELECT p.producto_id, p.nombre AS producto, p.precio AS precio_actual, t.precio AS precio_tarifa,
t.precio - p.precio AS diferencia
FROM tarifa,
JSON_TABLE(tarifa.j, '$[*]'
COLUMNS (id NUMBER PATH '$.id',
precio NUMBER PATH '$.precio')) t
JOIN productos p ON p.producto_id = t.id
ORDER BY p.producto_id;
PRODUCTO_ID PRODUCTO PRECIO_ACTUAL PRECIO_TARIFA DIFERENCIA
----------- ---------------------- ------------- ------------- ----------
1 Portátil UltraBook 14 899 949 50
2 Portátil Gaming 16 1499 1499 0
9 Monitor 24 FHD 139 129 -10
3 filas seleccionadas.
La ruta '$[*]' recorre cada elemento del array raíz y genera una fila por elemento.
Ejercicio 30: Informe de clientes por año
Para cada cliente con pedidos no cancelados, muestra las ventas de 2025 y 2026 en columnas (0 si no hay), la variación, su puesto en 2026 y en qué tercio de clientes está por ventas totales.
Ver solución
WITH ventas AS (
SELECT p.cliente_id,
EXTRACT(YEAR FROM p.fecha_pedido) AS anio,
l.cantidad * l.precio_unitario * (1 - l.descuento / 100) AS importe
FROM pedidos p
JOIN lineas_pedido l ON l.pedido_id = p.pedido_id
WHERE p.estado <> 'CANCELADO'
),
pivotada AS (
SELECT *
FROM ventas
PIVOT (SUM(importe) FOR anio IN (2025 AS v2025, 2026 AS v2026))
)
SELECT c.nombre AS cliente,
ROUND(NVL(v2025, 0), 2) AS v2025,
ROUND(NVL(v2026, 0), 2) AS v2026,
ROUND(NVL(v2026, 0) - NVL(v2025, 0), 2) AS variacion,
RANK() OVER (ORDER BY NVL(v2026, 0) DESC) AS rk_2026,
NTILE(3) OVER (ORDER BY NVL(v2025, 0) + NVL(v2026, 0) DESC) AS grupo
FROM pivotada pv
JOIN clientes c ON c.cliente_id = pv.cliente_id
ORDER BY rk_2026, cliente;
CLIENTE V2025 V2026 VARIACION RK_2026 GRUPO
--------------------------- ---------- ---------- ---------- ---------- ----------
Instituto Tecnológico Sur 11657,5 8945,05 -2712,45 1 1
Talleres Martín S.L. 2242,1 2462,05 219,95 2 1
Construcciones Levante S.A. 12174,94 2085,25 -10089,69 3 1
Hotel Mar Azul S.L. 2539,13 1663,64 -875,49 4 2
Diseños Creativos S.L. 1555 1499 -56 5 2
Gestoría Delta 0 1497 1497 6 2
Clínica Dental Sonrisa 936 1296 360 7 2
Panadería La Espiga 0 838 838 8 3
Laura Benítez Sanz 79,8 418,8 339 9 3
Academia Nova 7891,3 0 -7891,3 10 1
Antonio Ruiz Pérez 999 0 -999 10 3
María José Herrera 837,9 0 -837,9 10 3
12 filas seleccionadas.
La CTE ventas solo tiene las columnas que el PIVOT necesita (cliente_id, anio, importe), así que el agrupamiento implícito es por cliente. Las funciones analíticas se aplican después sobre el resultado pivotado. Los tres clientes sin ventas en 2026 empatan en el puesto 10.
Ejercicio 31: Los dos productos más vendidos de cada familia (QUALIFY)
Muestra los dos productos con más unidades vendidas de cada familia (categoría raíz) usando QUALIFY (Oracle 26ai). Escribe también la versión para 19c.
Ver solución
SELECT raiz, producto, uds
FROM (SELECT c.raiz, pr.nombre AS producto, SUM(l.cantidad) AS uds
FROM lineas_pedido l
JOIN productos pr ON pr.producto_id = l.producto_id
JOIN (SELECT categoria_id, CONNECT_BY_ROOT nombre AS raiz
FROM categorias
START WITH categoria_padre_id IS NULL
CONNECT BY PRIOR categoria_id = categoria_padre_id) c
ON c.categoria_id = pr.categoria_id
GROUP BY c.raiz, pr.nombre)
QUALIFY ROW_NUMBER() OVER (PARTITION BY raiz ORDER BY uds DESC, producto) <= 2
ORDER BY raiz, uds DESC;
RAIZ PRODUCTO UDS
------------ ---------------------- ----------
Electrónica Smartphone Gama Media 11
Electrónica Auriculares Bluetooth 10
Informática Monitor 24 FHD 40
Informática Pack teclado y ratón 30
Oficina Silla Ergonómica 7
Oficina Mesa Elevable 5
6 filas seleccionadas.
Versión para 19c: mover ROW_NUMBER() OVER (...) AS rn a la lista del SELECT de la vista en línea (o de otra vista en línea que la envuelva) y filtrar con WHERE rn <= 2 en la consulta exterior.
Ejercicio 32: Empleados hasta un presupuesto
Se quiere ofrecer un bono a los empleados mejor pagados mientras la suma acumulada de sus salarios no supere 20 000 €. Lista quiénes entran.
Ver solución
SELECT emp_id, nombre, salario,
SUM(salario) OVER (ORDER BY salario DESC, emp_id ROWS UNBOUNDED PRECEDING) AS acumulado
FROM empleados
QUALIFY SUM(salario) OVER (ORDER BY salario DESC, emp_id ROWS UNBOUNDED PRECEDING) <= 20000
ORDER BY salario DESC;
EMP_ID NOMBRE SALARIO ACUMULADO
---------- --------------------------- ---------- ----------
100 Ana 6500 6500
102 Marta 5000 11500
101 Luis 4800 16300
3 filas seleccionadas.
Con Laura (4300) el acumulado sería 20 600 y se pasaría. En 19c, calcula el acumulado en una vista en línea y filtra fuera.
Ejercicios propuestos
Ejercicio 33: Diferencia con la media móvil
Para cada mes de 2025 con ventas, calcula la diferencia entre las ventas del mes y la media de los dos meses anteriores con ventas.
Ejercicio 34: Antigüedad relativa
Para cada empleado, muestra su puesto por antigüedad dentro de su departamento y dentro de la empresa, y el número de días que lleva de diferencia con el siguiente más antiguo de su departamento.
Ejercicio 35: Producto más caro de cada pedido
Para cada pedido, muestra el nombre del producto con mayor precio unitario y su precio, con KEEP y con ROW_NUMBER. Compara los resultados.
Ejercicio 36: Clientes que repiten producto
Usando LAG sobre las líneas de los pedidos de cada cliente ordenadas por fecha, encuentra los clientes que han comprado el mismo producto en dos pedidos consecutivos.
Ejercicio 37: Sueldo del jefe y del jefe del jefe
Con una consulta jerárquica y PRIOR en la lista del SELECT (PRIOR salario), muestra cada empleado con el salario de su jefe directo. Investiga qué devuelve PRIOR en el nivel 1.
Ejercicio 38: Organigrama sin la rama de VENTAS
Muestra el organigrama completo con sangría, excepto el director de VENTAS y todo su equipo.
Ejercicio 39: Categorías hoja con número de productos
Lista las categorías hoja (sin subcategorías) con su ruta completa y el número de productos que contienen.
Ejercicio 40: Tabla de ventas por comercial y trimestre
Con PIVOT, muestra las ventas (no canceladas) de 2025 por comercial (filas) y trimestre (columnas), con una columna final de total calculada en una consulta exterior.
Ejercicio 41: UNPIVOT de fechas
Convierte las columnas fecha_pedido y fecha_envio de los pedidos de julio de 2026 en filas (pedido, tipo de fecha, fecha), incluyendo las fechas de envío nulas.
Ejercicio 42: Catálogo en JSON
Genera un documento JSON por categoría raíz con su nombre y un array de productos (nombre y precio) de toda su rama, ordenados por precio.
Ejercicio 43: Análisis ABC de clientes
Clasifica a los clientes en A (acumulan el primer 70 % de la facturación), B (el siguiente 20 %) y C (el 10 % restante).
Ejercicio 44: Racha de pedidos decrecientes
Adapta el ejemplo de MATCH_RECOGNIZE para encontrar clientes con al menos dos pedidos consecutivos de importe decreciente, mostrando el primer y el último pedido de cada racha.
Resumen
| Concepto | Sintaxis / idea clave |
|---|---|
| Función analítica | función(...) OVER ([PARTITION BY ...] [ORDER BY ...] [ventana]); no agrupa filas |
| Momento de cálculo | Después de WHERE/GROUP BY/HAVING, antes del ORDER BY; no en WHERE |
ROW_NUMBER / RANK / DENSE_RANK | 1,2,3 / 1,2,2,4 / 1,2,2,3 |
NTILE(n), PERCENT_RANK, CUME_DIST | Grupos de tamaño igual; posición relativa entre 0 y 1 |
| Top-N por grupo | ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) en vista en línea + WHERE rn <= N; QUALIFY en 26ai |
| Acumulado | SUM(x) OVER (ORDER BY y ROWS UNBOUNDED PRECEDING) |
| Media móvil | AVG(x) OVER (ORDER BY y ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) |
| Porcentaje del total | RATIO_TO_REPORT(x) OVER ([PARTITION BY ...]) |
| Ventana por defecto | Con ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; sin él: toda la partición |
ROWS / RANGE | Por posición / por valor (empates incluidos) |
LAG / LEAD | Valor de la fila anterior / siguiente (desplazamiento, defecto) |
FIRST_VALUE / LAST_VALUE / NTH_VALUE | LAST_VALUE necesita ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
KEEP | MAX(col) KEEP (DENSE_RANK LAST ORDER BY x): el col de la fila con el mayor x |
CONNECT BY | START WITH raíz CONNECT BY PRIOR emp_id = jefe_id (abajo) / PRIOR jefe_id = emp_id (arriba) |
| Pseudocolumnas jerárquicas | LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE, CONNECT_BY_ROOT col, SYS_CONNECT_BY_PATH(col, sep) |
| Orden y poda | ORDER SIBLINGS BY; condición en CONNECT BY poda ramas, en WHERE quita filas |
| Ciclos | CONNECT BY NOCYCLE (sin él: ORA-01436) |
PIVOT | PIVOT (agg(v) FOR col IN (val1 AS a1, ...)); origen en vista en línea con solo las columnas necesarias |
| Pivot manual | SUM(CASE WHEN col = val THEN v END) |
UNPIVOT | UNPIVOT [INCLUDE NULLS] (valor FOR nombre IN (c1 AS 'x', c2 AS 'y')) |
MATCH_RECOGNIZE | Patrones sobre secuencias de filas (PATTERN, DEFINE, MEASURES) |
| JSON | Generar: JSON_OBJECT, JSON_ARRAYAGG; leer: JSON_VALUE, JSON_TABLE |