Base de datos

Estrategía 1: UNION

Intuición

La estrategía de UNION combina dos conjuntos distintos de lógica usando el operador UNION. Aquí está la intuición detrás de cada parte:

Paso 1 - Filtrar los empleados con primary_flag = ‘Y’:

SELECT
  employee_id,
  department_id
FROM
  Employee
WHERE
  primary_flag = 'Y'

• Esta parte filtra a aquellos empleados que han sido marcados explicitamente como que tienen un departamento concreto como principal.
• En el caso de los empleados que pertenecen a varios departamentos, uno de ellos tendrá el indicador primary_flag con el valor ‘Y’, lo que indica qu es el departamento principal.
• El código SQL busca employee_id y department_id donde primary_flag es ‘Y’.

Paso 2 - Filtrar los empleados que aparecen exactamente una vez en la tabla Employee:

SELECT
  employee_id,
  department_id
FROM
  Employee
GROUP BY
  employee_id
HAVING
  COUNT(employee_id) = 1

• El objetivo aquí es capturar a los empleados que están asociados a un solo departamento. En estos casos, ese único departamento es automáticamente su departamento principal.
• El código agrupa los registros de la tabla Employee por employee_id utilizando GROUP BY. Para cada ID de empleado, comprueba el recuento de filas asociadas (o departamentos).
• La cláusula HAVING filtra los grupos en los que el recuento de filas (es decir, departamentos) de ese empleado no es igual a 1.
• De esta forma, sólo se seleccionan aquellos empleados que están asociados a un único departamento.

Paso 3 - Combinar ambos resultados con UNION:

SELECT
  employee_id,
  department_id
FROM
  Employee
WHERE
  primary_flag = 'Y'
UNION
SELECT
  employee_id,
  department_id
FROM
  Employee
GROUP BY
  employee_id
HAVING
  COUNT(employee_id) = 1;

• UNION es un operador SQL que combina los resultados de dos sentencias SELECT en un único conjunto de filas. Elimina automáticamente los duplicados.
• Aquí se utiliza para combinar los resultados de las dos lógicas mencionadas: las que tienen primary_flag = 'Y' y las que sólo aparecen una vez en la tabla.
• El resultado final es una lista unificada que contiene el departamento principal de cada empleado

En esencia, el código SQL garantiza que, para cada empleado, o bien se selecciona su departamento principal marcado explícitamente, o bien, si sólo pertenece a un departamento, se elige ese departamento como principal.

Implementación

En base a lo anterior, la solución puede ser implementada como sigue:

-- Filtra empleados con primary_flag = 'Y'
SELECT
  employee_id,
  department_id
FROM
  Employee
WHERE
  primary_flag = 'Y'
UNION
-- Filtra empleados que aparecen exactamente una vez en la tabla Employee
SELECT
  employee_id,
  department_id
FROM
  Employee
GROUP BY
  employee_id
HAVING
  COUNT(employee_id) = 1;

Estrategía 2: Window Function (COUNT)

Intuición

Este enfoque utiliza una función avanzada de SQL denominada “funciones ventana”, concretamente COUNT() OVER(). He aquí la intuición de cada paso:

Paso 1 - Consulta interna con función ventana:

SELECT
  *,
  COUNT(employee_id) OVER(PARTITION BY employee_id) AS EmployeeCount
FROM
  Employee

• Esta consulta obtiene todas las columnas de la tabla Employee y añade una nueva columna computada, EmployeeCount.
• COUNT(employee_id) OVER(PARTITION BY employee_id) es una función de ventana. Veamos lo que hace:
  • PARTITION BY employee_id: Esto descompone los datos en “ventanas” o “particiones” de filas que tienen el mismo employee_id. Cada ventana es básicamente una subsección de los datos de un empleado concreto.
  • COUNT(employee_id) OVER(...): Esto cuenta el número de filas (es decir, el número de departamentos) para cada empleado dentro de su respectiva partición/ventana. El resultado es una nueva columna, EmployeeCount, que nos indica con cuántos departamentos está asociado cada empleado. Este recuento se repite para cada fila del mismo empleado.

Paso 2 - Alias y consulta externa:

SELECT
  employee_id,
  department_id
FROM
  EmployeePartition

1. El resultado de la consulta interna se trata como una tabla temporal llamada EmployeePartition.
2. De esta tabla, seleccionamos las columnas deseadas: employee_id y department_id.

Paso 3 - Filtrado con la cláusula WHERE:

WHERE
  EmployeeCount = 1
  OR primary_flag = 'Y'

• Tenemos dos condiciones para filtrar el departamento principal para cada empleado:
  • EmployeeCount = 1: Esto captura aquellos empleados que pertenecen a un solo departamento. Para ellos, ese único departamento es automáticamente su departamento principal.
  • primary_flag = 'Y': Esto captura a los empleados que pertenecen a múltiples departamentos pero tienen un departamento explícitamente marcado como primario con una bandera ‘Y’.
• Se utiliza el operador OR, por lo que cualquier fila que cumpla cualquiera de las condiciones anteriores se incluye en el resultado.

Resumen:

El código asigna primero un recuento de departamento de empleado a cada fila utilizando una función de ventana. A continuación, filtra las filas deseadas en función de si un empleado está asociado a un solo departamento o tiene un departamento marcado explícitamente como principal. El resultado final es una lista de los departamentos principales de cada empleado.

Implementación

En base a lo anterior, la solución puede ser implementada como sigue:

SELECT
  employee_id,
  department_id
FROM
  (
    SELECT
      *,
      COUNT(employee_id) OVER(PARTITION BY employee_id) AS EmployeeCount
    FROM
      Employee
  ) EmployeePartition
WHERE
  EmployeeCount = 1
  OR primary_flag = 'Y';