Análisis de Ventanas en SQLite

Introducción

En este laboratorio, explorarás el análisis de ventanas (window analytics) de SQLite, centrándote en la clasificación de filas (ranking rows) y el cálculo de totales acumulados (running totals). Aprenderás a usar las funciones de ventana (window functions) para realizar cálculos en conjuntos de filas que están relacionadas con la fila actual.

Específicamente, usarás la función ROW_NUMBER() para asignar un rango único a cada fila basándose en el importe de las ventas. También aprenderás a calcular totales acumulados y a particionar datos para un análisis más avanzado. Este laboratorio proporciona una introducción práctica a las funciones de ventana en SQLite.

Skills Graph

Crear una Base de Datos y Tabla de Ventas

En este primer paso, crearás una base de datos SQLite llamada sales.db y una tabla llamada sales para almacenar datos de ventas. Esta tabla incluirá columnas para el ID del producto, el nombre del producto y el importe de las ventas.

Abre tu terminal en la VM de LabEx. Tu ruta predeterminada es /home/labex/project.

Para comenzar, crea la base de datos sales.db y abre la herramienta de línea de comandos de SQLite ejecutando el siguiente comando:

sqlite3 sales.db

Este comando crea el archivo de la base de datos y abre el shell de SQLite, donde puedes ejecutar comandos SQL. Verás un indicador como este:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

A continuación, crea la tabla sales con las siguientes columnas: product_id, product_name y sales_amount. Introduce el siguiente comando SQL en el indicador sqlite> y pulsa Enter:

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

Este comando configura la tabla sales donde:

• product_id es un entero que representa el identificador único de cada producto.
• product_name es un campo de texto que almacena el nombre del producto.
• sales_amount es un entero que representa el importe de las ventas del producto.

No verás ninguna salida si el comando se ejecuta correctamente.

Insertar Datos de Ejemplo en la Tabla de Ventas

Ahora que has creado la tabla sales, vamos a añadir algunos datos de ejemplo. Insertaremos seis registros que representan diferentes productos y sus importes de ventas.

Inserta los siguientes registros en la tabla sales ejecutando estos comandos uno por uno en el indicador sqlite>:

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

Estos comandos añaden seis filas a la tabla sales. Cada fila representa un producto con su ID, nombre e importe de ventas.

• INSERT INTO sales (product_id, product_name, sales_amount) especifica que estás insertando datos en las columnas product_id, product_name y sales_amount de la tabla sales.
• VALUES (1, 'Laptop', 1200) proporciona los valores que se insertarán para cada registro.

Para confirmar que los datos se han añadido correctamente, ejecuta este comando para ver todos los registros de la tabla:

SELECT * FROM sales;

Resultado Esperado:

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

Esta salida muestra el product_id, product_name y sales_amount de cada registro. El comando SELECT * recupera todas las columnas de la tabla especificada.

Clasificar Filas con ROW_NUMBER()

En este paso, aprenderás a usar la función de ventana ROW_NUMBER() para asignar una clasificación ( rank ) única a cada fila basándose en el sales_amount (importe de ventas). Esto es útil para identificar los productos más vendidos.

La función ROW_NUMBER() asigna un entero único a cada fila dentro de una partición de un conjunto de resultados. La clasificación se determina por el orden especificado en la cláusula ORDER BY.

Ejecuta la siguiente consulta en el indicador sqlite>:

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

Esta consulta calcula la clasificación de cada producto basándose en su sales_amount en orden descendente.

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) asigna una clasificación a cada fila basándose en el sales_amount, de modo que el importe de ventas más alto recibe una clasificación de 1.
• ORDER BY sales_amount DESC especifica que la clasificación debe basarse en el sales_amount en orden descendente.

Resultado Esperado:

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

Como puedes ver, la columna sales_rank ahora contiene la clasificación de cada producto basándose en su sales_amount, de modo que el importe de ventas más alto (Laptop) recibe una clasificación de 1.

Calcular Totales Acumulados (Running Totals)

En este paso, aprenderás a calcular totales acumulados (sumas acumulativas o running totals) utilizando funciones de ventana. Los totales acumulados son útiles para rastrear la suma de valores durante un período de tiempo o en un conjunto de filas.

Para calcular los totales acumulados, se utiliza la función SUM() con la cláusula OVER() y una cláusula ORDER BY para especificar el orden en que se calcula la suma.

Primero, añadamos una columna sale_date (fecha de venta) a la tabla sales y la rellenemos con algunas fechas de ejemplo. Ejecuta los siguientes comandos en el indicador sqlite>:

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

Estos comandos añaden una columna sale_date a la tabla sales y actualizan la tabla con fechas de ejemplo para cada producto.

Ahora, calculemos el total acumulado de sales_amount a lo largo del tiempo, ordenado por sale_date. Ejecuta la siguiente consulta:

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

Esta consulta calcula el total acumulado de sales_amount a lo largo del tiempo, ordenado por sale_date.

• SUM(sales_amount) OVER (ORDER BY sale_date) calcula la suma acumulativa de sales_amount hasta cada sale_date.
• ORDER BY sale_date especifica que el total acumulado debe calcularse basándose en la sale_date en orden ascendente.

Resultado Esperado:

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

La columna running_total muestra la suma acumulativa de sales_amount hasta cada sale_date. Por ejemplo, el total acumulado en '2023-01-15' es 1600, que es la suma de las ventas de '2023-01-01', '2023-01-05', '2023-01-10' y '2023-01-15'.

Particionar Datos para Análisis

En este paso, aprenderás a particionar datos utilizando la cláusula PARTITION BY dentro de las funciones de ventana. La partición te permite dividir tus datos en grupos lógicos y luego realizar cálculos dentro de cada grupo de forma independiente.

Añadamos una columna product_category (categoría de producto) a nuestra tabla sales. Ejecuta los siguientes comandos en el indicador sqlite>:

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

Estos comandos añaden una columna product_category a la tabla sales y actualizan la tabla con categorías para cada producto.

Ahora, usemos PARTITION BY para calcular el total acumulado ( running total ) de sales_amount dentro de cada product_category. Ejecuta la siguiente consulta:

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

Esta consulta calcula el total acumulado de sales_amount dentro de cada product_category, ordenado por sale_date.

• PARTITION BY product_category divide los datos en particiones basadas en la product_category.
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) calcula la suma acumulativa de sales_amount dentro de cada product_category, ordenado por sale_date.

Resultado Esperado:

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

La columna running_total_by_category muestra la suma acumulativa de sales_amount dentro de cada product_category, ordenado por sale_date. Observa que el total acumulado se reinicia para cada categoría.

Resumen

En este laboratorio, has aprendido a utilizar funciones de ventana en SQLite para realizar análisis de datos avanzados. Comenzaste creando una tabla sales e insertando datos de ejemplo. Luego, utilizaste la función ROW_NUMBER() para clasificar los productos según su importe de ventas. También aprendiste a calcular totales acumulados ( running totals ) utilizando la función SUM() con la cláusula OVER() y cómo particionar datos utilizando la cláusula PARTITION BY para realizar cálculos dentro de grupos lógicos. Estas habilidades proporcionan una base para tareas de análisis de datos más complejas en SQLite.