LabEx
EntrarÚnete

Escritura Avanzada de Consultas en PostgreSQL

PostgreSQLBeginner
Practicar Ahora

Introducción

En este laboratorio, mejorarás tus habilidades de escritura de consultas PostgreSQL explorando técnicas avanzadas. Aprenderás a usar subconsultas dentro de la cláusula WHERE para filtrar datos basándote en los resultados de otra consulta.

El laboratorio te guía a través de la definición y utilización de Expresiones de Tabla Comunes (CTEs, Common Table Expressions) para mejorar la legibilidad y modularidad de las consultas. Además, aplicarás funciones de ventana como ROW_NUMBER para realizar cálculos en conjuntos de filas. Finalmente, dominarás la agrupación y el filtrado de datos utilizando las cláusulas GROUP BY y HAVING para extraer información significativa de tus conjuntos de datos.

Escribir una Subconsulta en una Cláusula WHERE

En este paso, aprenderás a usar una subconsulta dentro de la cláusula WHERE de una consulta SQL. Una subconsulta, también conocida como consulta interna o consulta anidada, es una consulta incrustada dentro de otra consulta. Las subconsultas se utilizan para devolver datos que se utilizarán en la consulta principal como una condición para restringir aún más los datos que se recuperarán.

Entendiendo las Subconsultas en la Cláusula WHERE

Una subconsulta en la cláusula WHERE se utiliza normalmente para comparar el valor de una columna con el resultado de la subconsulta. La subconsulta se ejecuta primero, y su resultado es entonces utilizado por la consulta externa.

Sintaxis Básica:

SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

Aquí, la subconsulta (SELECT column_name FROM another_table WHERE condition) devuelve un conjunto de valores. La consulta externa entonces selecciona filas de table_name donde column_name está en ese conjunto.

Escenario:

Supongamos que tienes dos tablas: employees (empleados) y departments (departamentos). La tabla employees contiene información sobre los empleados, incluyendo su employee_id (id_empleado), employee_name (nombre_empleado) y department_id (id_departamento). La tabla departments contiene información sobre los departamentos, incluyendo su department_id (id_departamento) y department_name (nombre_departamento).

Queremos encontrar a todos los empleados que trabajan en el departamento de 'Sales' (Ventas).

Paso 1: Crear las Tablas e Insertar Datos

Primero, conéctate a la base de datos PostgreSQL usando el usuario postgres:

sudo -u postgres psql

A continuación, crea la tabla departments:

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50)
);

Inserta algunos datos de ejemplo en la tabla departments:

INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');

Ahora, crea la tabla employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INTEGER REFERENCES departments(department_id)
);

Inserta algunos datos de ejemplo en la tabla employees:

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);

Paso 2: Escribir la Subconsulta

Ahora, escribamos la consulta para encontrar a todos los empleados que trabajan en el departamento de 'Sales' (Ventas) usando una subconsulta en la cláusula WHERE.

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

Explicación:

  • La subconsulta (SELECT department_id FROM departments WHERE department_name = 'Sales') selecciona el department_id (id_departamento) de la tabla departments donde el department_name (nombre_departamento) es 'Sales' (Ventas). En este caso, devolverá 1.
  • La consulta externa entonces selecciona el employee_name (nombre_empleado) de la tabla employees donde el department_id (id_departamento) está en el conjunto devuelto por la subconsulta (que es solo 1).

Paso 3: Ejecutar la Consulta y Ver los Resultados

Ejecuta la consulta en tu terminal psql. Deberías ver la siguiente salida:

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Esto muestra que Alice y Charlie son los empleados que trabajan en el departamento de 'Sales' (Ventas).

Paso 4: Usando EXISTS con Subconsultas

Otra forma de usar subconsultas en la cláusula WHERE es con el operador EXISTS. El operador EXISTS prueba la existencia de filas en una subconsulta. Devuelve verdadero si la subconsulta devuelve alguna fila, y falso en caso contrario.

Aquí tienes un ejemplo de cómo usar EXISTS para lograr el mismo resultado:

SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.department_name = 'Sales'
);

Esta consulta logra el mismo resultado que la anterior, pero utiliza el operador EXISTS en lugar de IN.

Explicación:

  • La subconsulta SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales' comprueba si hay un departamento con el nombre 'Sales' (Ventas) que tenga el mismo department_id (id_departamento) que el empleado actual.
  • Si la subconsulta devuelve alguna fila (lo que significa que hay un departamento de 'Sales' (Ventas) con el mismo department_id (id_departamento)), el operador EXISTS devuelve verdadero, y se selecciona el nombre del empleado.

Ejecuta la consulta en tu terminal psql. Deberías ver la misma salida que antes:

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Ahora has utilizado con éxito una subconsulta en la cláusula WHERE para filtrar datos basándote en una condición en otra tabla. También has aprendido a usar el operador EXISTS con una subconsulta.

Definir y Utilizar un CTE

En este paso, aprenderás a definir y usar una Expresión de Tabla Común (CTE, Common Table Expression) en PostgreSQL. Un CTE es un conjunto de resultados nombrado temporal que puedes referenciar dentro de una única sentencia SELECT, INSERT, UPDATE o DELETE. Los CTEs son útiles para dividir consultas complejas en partes más simples y legibles.

Entendiendo los CTEs

Los CTEs se definen utilizando la cláusula WITH. Existen solo durante la duración de la ejecución de la consulta.

Sintaxis Básica:

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE condition;

Aquí, cte_name es el nombre que le das al CTE. La sentencia SELECT dentro de los paréntesis define el conjunto de resultados del CTE. La sentencia SELECT externa entonces consulta el CTE como si fuera una tabla regular.

Escenario:

Continuando con las tablas employees (empleados) y departments (departamentos) del paso anterior, usemos un CTE para encontrar los nombres de los empleados y sus correspondientes nombres de departamento.

Paso 1: Verificar las Tablas y los Datos

Asegúrate de que las tablas employees y departments existan y contengan los datos del paso anterior. Puedes verificar esto ejecutando las siguientes consultas en tu terminal psql:

SELECT * FROM departments;
SELECT * FROM employees;

Si las tablas o los datos faltan, consulta el paso anterior para crearlos e insertar los datos.

Paso 2: Definir un CTE

Ahora, definamos un CTE llamado EmployeeDepartments que une las tablas employees y departments para recuperar los nombres de los empleados y sus nombres de departamento.

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;

Explicación:

  • La cláusula WITH EmployeeDepartments AS (...) define el CTE llamado EmployeeDepartments.
  • La sentencia SELECT dentro de los paréntesis une la tabla employees (con el alias e) con la tabla departments (con el alias d) en la columna department_id (id_departamento).
  • La sentencia SELECT externa entonces recupera el employee_name (nombre_empleado) y el department_name (nombre_departamento) del CTE EmployeeDepartments.

Paso 3: Ejecutar la Consulta y Ver los Resultados

Ejecuta la consulta en tu terminal psql. Deberías ver la siguiente salida:

 employee_name | department_name
---------------+-----------------
 Alice         | Sales
 Bob           | Marketing
 Charlie       | Sales
 David         | Engineering
 Eve           | Marketing
(5 rows)

Esto muestra los nombres de todos los empleados y sus correspondientes nombres de departamento.

Paso 4: Usando CTEs para Filtrar

También puedes usar CTEs para filtrar datos. Por ejemplo, encontremos a todos los empleados que trabajan en el departamento de 'Sales' (Ventas) usando el CTE EmployeeDepartments.

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name
FROM EmployeeDepartments
WHERE department_name = 'Sales';

Explicación:

  • Esta consulta es similar a la anterior, pero añade una cláusula WHERE a la sentencia SELECT externa para filtrar los resultados para que solo incluyan a los empleados que trabajan en el departamento de 'Sales' (Ventas).

Ejecuta la consulta en tu terminal psql. Deberías ver la siguiente salida:

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Esto muestra que Alice y Charlie son los empleados que trabajan en el departamento de 'Sales' (Ventas).

Ahora has definido y utilizado con éxito un CTE para unir tablas y filtrar datos. Los CTEs pueden mejorar enormemente la legibilidad y el mantenimiento de consultas SQL complejas.

Aplicar Funciones de Ventana (p. ej., ROW_NUMBER)

En este paso, aprenderás a aplicar funciones de ventana (window functions) en PostgreSQL. Las funciones de ventana realizan cálculos en un conjunto de filas de una tabla que están relacionadas con la fila actual. Son similares a las funciones de agregación, pero a diferencia de estas, las funciones de ventana no agrupan las filas en una única fila de salida. En cambio, proporcionan un valor para cada fila en el conjunto de resultados.

Entendiendo las Funciones de Ventana

Las funciones de ventana utilizan la cláusula OVER() para definir la ventana de filas para el cálculo. La cláusula OVER() puede incluir las cláusulas PARTITION BY y ORDER BY para definir aún más la ventana.

Sintaxis Básica:

SELECT
    column1,
    column2,
    WINDOW_FUNCTION(column3) OVER (
        [PARTITION BY column4]
        [ORDER BY column5]
    ) AS alias_name
FROM
    table_name;
  • WINDOW_FUNCTION: El nombre de la función de ventana (p. ej., ROW_NUMBER, RANK, SUM, AVG).
  • OVER(): Especifica la ventana sobre la cual opera la función.
  • PARTITION BY: Divide las filas en particiones, y la función de ventana se aplica a cada partición de forma independiente.
  • ORDER BY: Define el orden de las filas dentro de cada partición.
  • alias_name: El alias para el resultado calculado de la función de ventana.

Escenario:

Continuando con las tablas employees (empleados) y departments (departamentos) de los pasos anteriores, usemos la función de ventana ROW_NUMBER() para asignar un rango único a cada empleado dentro de sus respectivos departamentos basándonos en su employee_name (nombre_empleado).

Paso 1: Aplicar la Función de Ventana ROW_NUMBER()

Ahora, escribamos una consulta que utilice la función de ventana ROW_NUMBER() para asignar un rango a cada empleado dentro de su departamento.

SELECT
    employee_name,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
    employees;

Explicación:

  • ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name): Esta es la función de ventana.
    • ROW_NUMBER(): Asigna un entero secuencial único a cada fila dentro de la ventana.
    • PARTITION BY department_id: Divide las filas en particiones basándose en el department_id (id_departamento). Esto significa que la clasificación se realizará por separado para cada departamento.
    • ORDER BY employee_name: Especifica el orden en el que se clasifican las filas dentro de cada partición. En este caso, los empleados se clasifican alfabéticamente por su employee_name (nombre_empleado).
  • employee_rank: Este es el alias dado al resultado de la función de ventana.

Paso 2: Ejecutar la Consulta y Ver los Resultados

Ejecuta la consulta en tu terminal psql. Deberías ver la siguiente salida:

 employee_name | department_id | employee_rank
---------------+---------------+---------------
 Alice         |             1 |             1
 Charlie       |             1 |             2
 Bob           |             2 |             1
 Eve           |             2 |             2
 David         |             3 |             1
(5 rows)

Esto muestra el nombre de cada empleado, su ID de departamento y su rango dentro de su departamento. Por ejemplo, Alice tiene el rango 1 en el departamento 1, y Charlie tiene el rango 2 en el departamento 1.

Paso 3: Usando Funciones de Ventana con CTEs

También puedes usar funciones de ventana dentro de CTEs para hacer que tus consultas estén más organizadas. Reescribamos la consulta anterior usando un CTE.

WITH RankedEmployees AS (
    SELECT
        employee_name,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
    FROM
        employees
)
SELECT
    employee_name,
    department_id,
    employee_rank
FROM
    RankedEmployees;

Esta consulta produce el mismo resultado que la anterior, pero utiliza un CTE para encapsular el cálculo de la función de ventana.

Ejecuta la consulta en tu terminal psql. Deberías ver la misma salida que antes.

Agrupar y Filtrar con GROUP BY y HAVING

En este paso, aprenderás a usar las cláusulas GROUP BY y HAVING en PostgreSQL para agrupar filas y filtrar los resultados agrupados. La cláusula GROUP BY agrupa las filas que tienen los mismos valores en las columnas especificadas en filas de resumen, como encontrar el número de empleados en cada departamento. La cláusula HAVING se utiliza para filtrar estas filas agrupadas basándose en una condición especificada.

Entendiendo GROUP BY y HAVING

  • GROUP BY: Esta cláusula agrupa las filas con los mismos valores en una o más columnas en una fila de resumen. Normalmente se utilizan funciones de agregación (p. ej., COUNT, SUM, AVG, MIN, MAX) para calcular los valores de cada grupo.
  • HAVING: Esta cláusula filtra los grupos creados por la cláusula GROUP BY. Es similar a la cláusula WHERE, pero opera sobre grupos en lugar de filas individuales.

Sintaxis Básica:

SELECT
    column1,
    aggregate_function(column2)
FROM
    table_name
WHERE
    condition
GROUP BY
    column1
HAVING
    aggregate_function(column2) condition;
  • column1: La columna por la que se agrupa.
  • aggregate_function(column2): Una función de agregación aplicada a column2 para cada grupo.
  • WHERE: Filtra las filas antes de agrupar.
  • GROUP BY: Agrupa las filas basándose en los valores de column1.
  • HAVING: Filtra los grupos después de agrupar, basándose en el resultado de la función de agregación.

Escenario:

Continuando con las tablas employees (empleados) y departments (departamentos), usemos GROUP BY y HAVING para encontrar los departamentos que tienen más de un empleado.

Paso 1: Agrupar por Departamento y Contar Empleados

Primero, escribamos una consulta para agrupar los empleados por departamento y contar el número de empleados en cada departamento.

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name;

Explicación:

  • SELECT d.department_name, COUNT(e.employee_id) AS employee_count: Esto selecciona el nombre del departamento y el recuento de los IDs de los empleados para cada departamento.
  • FROM employees e JOIN departments d ON e.department_id = d.department_id: Esto une las tablas employees y departments en la columna department_id (id_departamento).
  • GROUP BY d.department_name: Esto agrupa las filas por nombre de departamento, por lo que la función COUNT() contará el número de empleados en cada departamento.

Paso 2: Ejecutar la Consulta y Ver los Resultados

Ejecuta la consulta en tu terminal psql. Deberías ver la siguiente salida:

 department_name | employee_count
-----------------+----------------
 Engineering     |              1
 Marketing       |              2
 Sales           |              2
(3 rows)

Esto muestra el número de empleados en cada departamento.

Paso 3: Filtrar con HAVING

Ahora, añadamos una cláusula HAVING para filtrar los resultados para que solo incluyan los departamentos que tienen más de un empleado.

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name
HAVING
    COUNT(e.employee_id) > 1;

Explicación:

  • Esta consulta es la misma que la anterior, pero añade una cláusula HAVING:
    • HAVING COUNT(e.employee_id) > 1: Esto filtra los grupos para que solo incluyan los departamentos donde el recuento de los IDs de los empleados es mayor que 1.

Paso 4: Ejecutar la Consulta y Ver los Resultados

Ejecuta la consulta en tu terminal psql. Deberías ver la siguiente salida:

 department_name | employee_count
-----------------+----------------
 Marketing       |              2
 Sales           |              2
(2 rows)

Esto muestra solo los departamentos que tienen más de un empleado (Marketing y Sales).

Ahora has utilizado con éxito las cláusulas GROUP BY y HAVING para agrupar filas y filtrar los resultados agrupados basándose en una condición.

Resumen

En este laboratorio, has aprendido a escribir consultas avanzadas de PostgreSQL. Comenzaste utilizando subconsultas (subqueries) dentro de la cláusula WHERE para filtrar datos basándote en los resultados de otra consulta. Esto implicó comprender la sintaxis y la aplicación de las subconsultas para comparar los valores de las columnas con un conjunto de valores devueltos por la consulta interna.

Luego, pasaste a definir y utilizar Expresiones de Tabla Comunes (CTEs, Common Table Expressions) para mejorar la legibilidad y la modularidad de las consultas. Los CTEs te permiten dividir consultas complejas en partes más simples y manejables.

A continuación, aplicaste funciones de ventana (window functions) como ROW_NUMBER para realizar cálculos en conjuntos de filas. Las funciones de ventana son similares a las funciones de agregación, pero proporcionan un valor para cada fila en el conjunto de resultados, en lugar de agrupar las filas en una única fila de salida.

Finalmente, dominaste la agrupación y el filtrado de datos utilizando las cláusulas GROUP BY y HAVING para extraer información significativa de tus conjuntos de datos. La cláusula GROUP BY agrupa las filas que tienen los mismos valores en las columnas especificadas en filas de resumen, mientras que la cláusula HAVING filtra estas filas agrupadas basándose en una condición especificada.

Relacionado PostgreSQL Cursos
PostgreSQL para Principiantes

PostgreSQL para Principiantes

databasepostgresql
Laboratorios Prácticos Avanzados de PostgreSQL

Laboratorios Prácticos Avanzados de PostgreSQL

databasepostgresql