Agrupación de Datos en SQLite

Introducción

En este laboratorio, aprenderá cómo resumir y analizar datos en SQLite utilizando funciones de agregación y cláusulas de agrupación. Explorará COUNT y SUM para realizar cálculos, agrupará datos por columnas individuales, filtrará grupos con HAVING y ordenará la salida agrupada. Esta experiencia práctica le proporcionará habilidades esenciales de manipulación de datos en SQLite.

Skills Graph

Crear la tabla Orders e insertar datos

En este paso, creará una base de datos llamada sales.db y una tabla orders dentro de ella. Luego, insertará datos de muestra en la tabla. Esta tabla se utilizará a lo largo del laboratorio para practicar técnicas de agrupación de datos.

Primero, abra su terminal en la VM de LabEx. Su ruta predeterminada es /home/labex/project.

Para comenzar, cree la base de datos sales.db y conéctese a ella utilizando el siguiente comando:

sqlite3 sales.db

Este comando abrirá el shell de SQLite, y verá un indicador como sqlite>.

A continuación, cree la tabla orders con columnas para order_id, customer_id, product_name, quantity y price. Ejecute el siguiente comando SQL:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

Este comando crea la tabla orders con las columnas y los tipos de datos especificados. La columna order_id se establece como la clave primaria (primary key).

Ahora, inserte datos de muestra en la tabla orders. Ejecute las siguientes sentencias INSERT una por una:

INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);

Estos comandos insertan siete filas de datos en la tabla orders, representando diferentes pedidos de clientes.

Para verificar que los datos se han insertado correctamente, puede ejecutar una consulta SELECT simple:

SELECT * FROM orders;

Este comando mostrará todas las filas y columnas en la tabla orders.

Agregación con COUNT y SUM

En este paso, utilizará las funciones de agregación COUNT y SUM para realizar cálculos sobre los datos en la tabla orders. Las funciones de agregación le permiten resumir datos de múltiples filas en un único resultado.

Debería seguir conectado a la base de datos sales.db del paso anterior. Si no es así, vuelva a conectarse utilizando:

sqlite3 sales.db

Primero, usemos la función COUNT para determinar el número total de pedidos en la tabla. Ejecute el siguiente comando SQL:

SELECT COUNT(*) FROM orders;

Esta consulta devolverá el número total de filas en la tabla orders, lo que representa el número total de pedidos.

La función COUNT(*) cuenta todas las filas de la tabla, independientemente de si alguna columna contiene valores NULL.

A continuación, usemos la función SUM para calcular la cantidad total de todos los productos pedidos. Ejecute el siguiente comando SQL:

SELECT SUM(quantity) FROM orders;

Esta consulta devolverá la suma de la columna quantity para todas las filas en la tabla orders.

La función SUM suma los valores en la columna especificada.

Finalmente, calculemos los ingresos totales generados por todos los pedidos. Ejecute el siguiente comando SQL:

SELECT SUM(quantity * price) FROM orders;

Esta consulta multiplica las columnas quantity y price para cada fila y luego suma los resultados, lo que le da los ingresos totales.

Agrupar por columnas individuales

En este paso, aprenderá a usar la cláusula GROUP BY para agrupar filas según los valores en una o más columnas. Esto se utiliza a menudo en conjunto con funciones de agregación para calcular estadísticas resumidas para cada grupo.

Debería seguir conectado a la base de datos sales.db del paso anterior. Si no es así, vuelva a conectarse utilizando:

sqlite3 sales.db

Agrupemos la tabla orders por customer_id y contemos el número de pedidos para cada cliente. Ejecute el siguiente comando SQL:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

Esta consulta devolverá el customer_id y el número de pedidos (order_count) para cada cliente único. La cláusula GROUP BY customer_id le dice a SQLite que agrupe las filas según los valores en la columna customer_id. La función COUNT(*) luego cuenta el número de filas en cada grupo.

A continuación, agrupemos la tabla orders por product_name y calculemos la cantidad total pedida para cada producto. Ejecute el siguiente comando SQL:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

Esta consulta devolverá el product_name y la cantidad total pedida (total_quantity) para cada producto único. La cláusula GROUP BY product_name le dice a SQLite que agrupe las filas según los valores en la columna product_name. La función SUM(quantity) luego calcula la suma de la columna quantity para cada grupo.

Finalmente, agrupemos la tabla orders por customer_id y calculemos los ingresos totales generados por cada cliente. Ejecute el siguiente comando SQL:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;

Esta consulta devolverá el customer_id y los ingresos totales (total_revenue) generados por cada cliente.

Aplicar HAVING a los grupos

En este paso, aprenderá a usar la cláusula HAVING para filtrar grupos después de que hayan sido creados por la cláusula GROUP BY. La cláusula HAVING es similar a la cláusula WHERE, pero opera sobre grupos en lugar de filas individuales.

Debería seguir conectado a la base de datos sales.db del paso anterior. Si no es así, vuelva a conectarse utilizando:

sqlite3 sales.db

Agrupemos la tabla orders por customer_id y contemos el número de pedidos para cada cliente. Luego, usaremos la cláusula HAVING para filtrar los resultados e incluir solo a los clientes que hayan realizado más de un pedido. Ejecute el siguiente comando SQL:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

Esta consulta devolverá el customer_id y el número de pedidos (order_count) para cada cliente que haya realizado más de un pedido. La cláusula GROUP BY customer_id agrupa las filas por customer_id, y la cláusula HAVING COUNT(*) > 1 filtra los grupos para incluir solo aquellos donde el recuento de pedidos es mayor que 1.

A continuación, agrupemos la tabla orders por product_name y calculemos la cantidad total pedida para cada producto. Luego, usaremos la cláusula HAVING para filtrar los resultados e incluir solo los productos donde la cantidad total pedida sea mayor que 1. Ejecute el siguiente comando SQL:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;

Esta consulta devolverá el product_name y la cantidad total pedida (total_quantity) para cada producto donde la cantidad total pedida sea mayor que 1.

Finalmente, agrupemos la tabla orders por customer_id y calculemos los ingresos totales generados por cada cliente. Luego, usaremos la cláusula HAVING para filtrar los resultados e incluir solo a los clientes que hayan generado más de $1000 en ingresos. Ejecute el siguiente comando SQL:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;

Esta consulta devolverá el customer_id y los ingresos totales (total_revenue) generados por cada cliente que haya generado más de $1000 en ingresos.

Ordenar la salida agrupada

En este paso, aprenderá a usar la cláusula ORDER BY para ordenar la salida de una consulta que incluye la cláusula GROUP BY. Ordenar la salida agrupada puede facilitar el análisis y la comprensión de los datos.

Debería seguir conectado a la base de datos sales.db del paso anterior. Si no es así, vuelva a conectarse utilizando:

sqlite3 sales.db

Agrupemos la tabla orders por customer_id y contemos el número de pedidos para cada cliente. Luego, usaremos la cláusula ORDER BY para ordenar los resultados en orden descendente según el número de pedidos. Ejecute el siguiente comando SQL:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;

Esta consulta devolverá el customer_id y el número de pedidos (order_count) para cada cliente, ordenados en orden descendente según el order_count. La cláusula GROUP BY customer_id agrupa las filas por customer_id, y la cláusula ORDER BY order_count DESC ordena los resultados en orden descendente según el alias order_count.

A continuación, agrupemos la tabla orders por product_name y calculemos la cantidad total pedida para cada producto. Luego, usaremos la cláusula ORDER BY para ordenar los resultados en orden ascendente según el nombre del producto (product_name). Ejecute el siguiente comando SQL:

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;

Esta consulta devolverá el product_name y la cantidad total pedida (total_quantity) para cada producto, ordenados en orden ascendente según el product_name.

Finalmente, agrupemos la tabla orders por customer_id y calculemos los ingresos totales generados por cada cliente. Luego, usaremos la cláusula ORDER BY para ordenar los resultados en orden descendente según los ingresos totales. Ejecute el siguiente comando SQL:

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;

Esta consulta devolverá el customer_id y los ingresos totales (total_revenue) generados por cada cliente, ordenados en orden descendente según el total_revenue.

Para salir de la shell de SQLite, ejecute:

.exit

Resumen

En este laboratorio, ha aprendido a usar funciones de agregación como COUNT y SUM para resumir datos en SQLite. Creó una base de datos sales.db con una tabla orders e insertó datos de muestra. Luego, usó COUNT(*) para determinar el número total de pedidos y SUM(quantity * price) para calcular los ingresos totales. También aprendió a agrupar datos usando la cláusula GROUP BY, a filtrar grupos usando la cláusula HAVING y a ordenar la salida usando la cláusula ORDER BY. Estas habilidades proporcionan una base sólida para el análisis de datos en SQLite.