Alternativa a subconsulta para el filtraje de registros

Oracle, pionero en BD relacionales.

Alternativa a subconsulta para el filtraje de registros

Notapor Pere » Vie Jun 18, 2010 9:55 am

En ocasiones es menester filtrar una consulta de modo que los registros seleccionados atiendan a una condición más sofisticada tal como que para un determinado subconjunto de registros el valor de un campo sea máximo o mínimo.

Por ejemplo supongamos que tenemos estas dos tablas: DEPARTAMENTOS y EMPLEADOS. La tabla EMPLEADOS contiene una clave foránea de la tabla DEPARTAMENTOS de modo que todo empleado pertenece o trabaja para un departamento.

Supongamos que necesitamos conocer quien es el empleado más veterano por departamento.

Bien, para ello tenemos el campo FECHA_ALTA en la tabla EMPLEADOS que nos indica en que fecha se ha incorporado cada empleado a la empresa, de modo que la siguiente consulta nos devolvería la fecha de alta de los empleados más veteranos por departamento que siguen trabajando en la empresa:

Código: Seleccionar todo

select ID_DEPARTAMENTO
, min(FECHA_ALTA) FECHA_ALTA_MINIMA
  from EMPLEADOS
 where ESTADO 
= 'A' 
 group by ID_DEPARTAMENTO 


Pero esta consulta no nos dice quien es la persona. Tan solo la fecha en que se incorporó. Para conocer esto último podríamos realizar la siguiente consulta:

Código: Seleccionar todo

select D
.NOMBRE_DEPARTAMENTO, E.NOMBRE, E.APELLIDOS, E.FECHA_ALTA
  from EMPLEADOS E
, DEPARTAMENTOS D
 where E
.ID_DEPARTAMENTO = D.ID_DEPARTAMENTO
   and E
.ESTADO = 'A'
   and E.FECHA_ALTA = (select min(FECHA_ALTA)
                         from EMPLEADOS EMP
                        where EMP
.ESTADO = 'A'
                          and EMP.ID_DEPARTAMENTO = E.ID_DEPARTAMENTO)
  


Ahora bien, cuando estamos manejando cientos de miles de registros, estas subconsultas resultan costosas y la eficiencia baja considerablemente. Existe un modo de realizar esto en oracle de un modo mucho más eficiente. La idea sería realizar una consulta que obtenga todos los empleados por departamentos y posteriormente solo se quede con aquellos que la fecha de alta sea mínima:


Código: Seleccionar todo

select D
.NOMBRE_DEPARTAMENTO, 
       min
(E.NOMBRE)     keep (dense_rank first order by E.FECHA_ALTA nulls last) NOMBRE, 
       min
(E.APELLIDOS)  keep (dense_rank first order by E.FECHA_ALTA nulls last) APELLIDOS, 
       min
(E.FECHA_ALTA) keep (dense_rank first order by E.FECHA_ALTA nulls last) FECHA_ALTA
  from EMPLEADOS E
, DEPARTAMENTOS D
 where E
.ID_DEPARTAMENTO = D.ID_DEPARTAMENTO
   and E
.ESTADO = 'A'
 group by D.NOMBRE_DEPARTAMENTO


Si por ejemplo se desea obtener en lugar de los empleados más veteranos por departamento, los más novatos, tan solo debería añadir en la cláusula KEEP que la ordenación la realice de forma descendiente:

Código: Seleccionar todo

select D
.NOMBRE_DEPARTAMENTO, 
       min
(E.NOMBRE)     keep (dense_rank first order by E.FECHA_ALTA desc nulls last) NOMBRE, 
       min
(E.APELLIDOS)  keep (dense_rank first order by E.FECHA_ALTA desc nulls last) APELLIDOS, 
       min
(E.FECHA_ALTA) keep (dense_rank first order by E.FECHA_ALTA Desc nulls last) FECHA_ALTA
  from EMPLEADOS E
, DEPARTAMENTOS D
 where E
.ID_DEPARTAMENTO = D.ID_DEPARTAMENTO
   and E
.ESTADO = 'A'
 group by D.NOMBRE_DEPARTAMENTO


Obsérvese como no es necesario cambiar a max el agregado de la cláusula SELECT, puesto que el máximo o el mínimo o la media de los registros que cumplen la cláusula KEEP es el mismo valor, puesto que solo existe un registro por departamento. Aquel que la cláusula KEEP posiciona según la ordenación indicada. Por tanto el que encapsulemos el nombre y los apellidos de una función de agragado(max o min) es solo por sintaxis, por que se está agrupando por departamento y en consecuencia es necesario establecerlo así. Es si queréis, una trampa para obtener lo que se precisa de forma más eficiente.

Si por ejemplo hubiese dos empleados igual de veteranos para un departamento, entonces esto último que se ha expuesto no sería del todo cierto y quizás no podría usarse este método. Solo se mostraría un empleado de los dos, a diferencia del método “tradicional” antes mostrado, que devolvería los dos. En cualquier caso esta alternativa resulta muy interesante y en esta en la mano del profesional valorar si aplicarla o no.

Espero os resulte útil.
Pere
 
Mensajes: 74
Registrado: Mar Feb 02, 2010 9:44 pm

Volver a SGBD Oracle

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 20 invitados