Funciones de Ventana de MySQL

Introducción

En este laboratorio, explorará el poder de las Funciones de Ventana de MySQL. Aprenderá a realizar cálculos en conjuntos de filas de tablas que están relacionadas con la fila actual.

Comenzará creando un conjunto de datos de ejemplo y luego utilizará funciones como ROW_NUMBER(), SUM(), AVG() y LAG() combinadas con la cláusula OVER() para realizar análisis de datos avanzados. Este laboratorio proporciona ejemplos prácticos, paso a paso, para ayudarle a comprender y aplicar estas potentes funciones.

Configurar la Base de Datos y la Tabla

Antes de utilizar las funciones de ventana, necesita una base de datos y una tabla con datos de ejemplo. En este paso, creará una base de datos llamada company y una tabla llamada employees.

Primero, abra la terminal desde su escritorio.

Conéctese al servidor MySQL como usuario root. Dado que este es un entorno de laboratorio, puede usar sudo para conectarse sin contraseña.

sudo mysql -u root

Una vez conectado, verá el prompt de MySQL (mysql>).

Ahora, cree la base de datos company y cambie a ella. La cláusula IF NOT EXISTS evita un error si la base de datos ya existe.

CREATE DATABASE IF NOT EXISTS company;
USE company;

A continuación, cree la tabla employees. Esta tabla almacenará el ID del empleado, el nombre, el departamento y el salario.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Ahora, inserte algunos datos de ejemplo en la tabla employees.

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
    employee_name = VALUES(employee_name),
    department = VALUES(department),
    salary = VALUES(salary);

Para verificar que los datos se insertaron correctamente, puede ver todas las filas de la tabla employees.

SELECT * FROM employees;

La salida debería mostrar los cinco registros que insertó:

+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary   |
+-------------+---------------+------------+----------+
|           1 | Alice         | Sales      | 60000.00 |
|           2 | Bob           | Marketing  | 55000.00 |
|           3 | Charlie       | Sales      | 65000.00 |
|           4 | David         | IT         | 70000.00 |
|           5 | Eve           | Marketing  | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)

Con la base de datos y la tabla listas, puede pasar al siguiente paso para aprender sobre las funciones de ventana.

Clasificar Filas con ROW_NUMBER()

La función ROW_NUMBER() asigna un entero secuencial único a cada fila dentro de una partición de un conjunto de resultados. Se utiliza comúnmente para clasificar y paginar.

La sintaxis básica es:
ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])

• OVER(): Esta cláusula define la ventana (el conjunto de filas) para la función.
• ORDER BY: Esta cláusula, dentro de OVER(), especifica el orden en que se asignan los números de fila.

Continuando en la shell de MySQL, ahora usará ROW_NUMBER() para clasificar a los empleados por su salario en orden descendente.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

Esta consulta agrega una columna salary_rank, numerando a los empleados del salario más alto al más bajo.

+---------------+------------+----------+-------------+
| employee_name | department | salary   | salary_rank |
+---------------+------------+----------+-------------+
| David         | IT         | 70000.00 |           1 |
| Charlie       | Sales      | 65000.00 |           2 |
| Eve           | Marketing  | 62000.00 |           3 |
| Alice         | Sales      | 60000.00 |           4 |
| Bob           | Marketing  | 55000.00 |           5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)

Como puede ver, David tiene un rango de 1 porque tiene el salario más alto. Esto demuestra cómo ROW_NUMBER() se puede utilizar para crear una clasificación simple.

Calcular un Total Acumulado con SUM()

Un total acumulado, o suma acumulativa, es la suma de una secuencia de números que se actualiza a medida que se agrega cada nuevo número. En SQL, puede calcular esto usando SUM() OVER().

La sintaxis es:
SUM(column_name) OVER (ORDER BY column_name [ASC|DESC])

Esta función suma los valores de una columna en el orden especificado por la cláusula ORDER BY.

Ahora, calculemos el total acumulado de salarios, ordenados por employee_id.

SELECT
    employee_name,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
    employees;

El resultado mostrará el salario de cada empleado y la suma acumulativa hasta esa fila.

+---------------+----------+---------------+
| employee_name | salary   | running_total |
+---------------+----------+---------------+
| Alice         | 60000.00 |      60000.00 |
| Bob           | 55000.00 |     115000.00 |
| Charlie       | 65000.00 |     180000.00 |
| David         | 70000.00 |     250000.00 |
| Eve           | 62000.00 |     312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)

Por ejemplo, el running_total para Bob es la suma de su salario y el de Alice (60000.00 + 55000.00 = 115000.00). Esto es útil para rastrear métricas acumulativas como ventas o gastos a lo largo del tiempo.

Agrupar Cálculos con PARTITION BY

La cláusula PARTITION BY divide el conjunto de resultados en particiones (grupos) y aplica la función de ventana a cada partición de forma independiente. Esto es útil para realizar cálculos dentro de categorías específicas.

La sintaxis es:
function() OVER (PARTITION BY column_name ORDER BY ...)

Usemos PARTITION BY para clasificar a los empleados dentro de cada departamento según su salario.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
    employees;

Esta consulta particiona los datos por department y luego clasifica a los empleados en cada departamento por salario.

+---------------+------------+----------+--------------+
| employee_name | department | salary   | rank_in_dept |
+---------------+------------+----------+--------------+
| David         | IT         | 70000.00 |            1 |
| Eve           | Marketing  | 62000.00 |            1 |
| Bob           | Marketing  | 55000.00 |            2 |
| Charlie       | Sales      | 65000.00 |            1 |
| Alice         | Sales      | 60000.00 |            2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Observe que la clasificación se reinicia para cada departamento. Por ejemplo, tanto Eve como Charlie tienen el rango 1, pero dentro de sus respectivos departamentos 'Marketing' y 'Sales'. Esto permite un análisis más granular en comparación con una clasificación global.

Comparar Filas con LAG()

La función LAG() proporciona acceso a una fila en un desplazamiento físico especificado que precede a la fila actual. Es útil para comparar un valor en la fila actual con un valor en una fila anterior.

La sintaxis es:
LAG(expression, offset, default_value) OVER (ORDER BY ...)

• expression: La columna o expresión a recuperar.
• offset: El número de filas a retroceder (el valor predeterminado es 1).
• default_value: El valor a devolver si el desplazamiento está fuera de los límites (por ejemplo, para la primera fila).

Encontremos el salario del empleado anterior en la lista, ordenado por employee_id.

SELECT
    employee_name,
    salary,
    LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
    employees;

Esta consulta recupera el salario de la fila anterior. Para la primera fila, donde no hay fila anterior, devuelve NULL.

+---------------+----------+-----------------+
| employee_name | salary   | previous_salary |
+---------------+----------+-----------------+
| Alice         | 60000.00 |            NULL |
| Bob           | 55000.00 |        60000.00 |
| Charlie       | 65000.00 |        55000.00 |
| David         | 70000.00 |        65000.00 |
| Eve           | 62000.00 |        70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)

Puede usar esto para calcular la diferencia entre salarios consecutivos. Cuando el salario anterior es NULL (para la primera fila), el resultado también será NULL.

SELECT
    employee_name,
    salary,
    salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
    employees;

Esta consulta calcula la diferencia entre el salario del empleado actual y el anterior.

+---------------+----------+-------------+
| employee_name | salary   | salary_diff |
+---------------+----------+-------------+
| Alice         | 60000.00 |        NULL |
| Bob           | 55000.00 |    -5000.00 |
| Charlie       | 65000.00 |    10000.00 |
| David         | 70000.00 |     5000.00 |
| Eve           | 62000.00 |    -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)

Ahora ha practicado varias funciones de ventana clave. Puede salir del shell de MySQL.

exit;

Resumen

En este laboratorio, ha explorado el uso de las funciones de ventana de MySQL. Aprendió a asignar números de fila usando ROW_NUMBER(), a calcular totales acumulados con SUM() OVER(), a realizar cálculos en grupos específicos usando PARTITION BY y a acceder a datos de filas anteriores con LAG().

Al aplicar estas funciones a un conjunto de datos de ejemplo, adquirió experiencia práctica en la realización de análisis de datos avanzados directamente dentro de sus consultas SQL. Estas son habilidades valiosas para generar informes y obtener información compleja de sus datos.