Curso de MariaDB / Consultar ventas mensuales, compras por cliente y productos sin ventas

Las ventas mensuales, las compras por cliente y los productos sin ventas tienen unidades de resultado diferentes aunque consulten los mismos datos. Defina qué estados e importes incluye y qué significa «sin ventas», y concilie los totales mensuales y por cliente solo cuando sus períodos y condiciones sean comparables.

Definir los indicadores de los informes

En estas prácticas, el importe de ventas es cantidad por precio unitario de compra de las líneas de pedidos pagados; se excluyen envío y pedidos cancelados. No representa una política contable completa de ingresos, impuestos o reembolsos. Un producto sin ventas es un producto registrado que nunca ha aparecido en un pedido pagado.

Revise los criterios de GROUP BY y HAVING y los riesgos de multiplicar importes al unir varias tablas. Antes de escribir un informe, indique qué representa una fila y qué población se examina.

Informe Qué representa una fila Población del ejemplo
Ventas mensuales Un año y mes Líneas pagadas de 2026
Compras por cliente Un cliente Toda la historia disponible; incluye ceros
Productos sin ventas Un producto Ninguna línea en un pedido pagado en toda la historia

Todos los pedidos iniciales están en 2026, por lo que los importes mensuales y por cliente se pueden conciliar en este juego de datos. Si añade pedidos de otros años, la consulta por cliente siguiente sigue sumándolos y la mensual no: deberá alinear explícitamente sus períodos antes de esperar el mismo total.

Preparar datos pequeños que se puedan comprobar a mano

Conéctese como course_user al servidor de prácticas y seleccione shopcourse. La guía del cliente y los archivos SQL UTF-8 explica cómo guardar el código y ejecutarlo con SOURCE C:/mariadb-lab/lesson.sql;, sustituyendo la ruta por la real. Ejecute el SQL dentro del cliente MariaDB, no directamente en la consola del sistema operativo.

Compruebe SELECT VERSION(), DATABASE(); y SHOW TABLES; antes de crear c42_customers, c42_products, c42_orders y c42_items. Son nombres exclusivos del ejercicio. Si ya existen, inspeccione sus definiciones y registros antes de decidir si son prescindibles; no sustituya tablas de una aplicación.

USE shopcourse;
CREATE TABLE c42_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
  REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
  REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
  REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Se conservan los nombres y las ciudades originales en coreano. Los productos 1–4 son un cuaderno, un bolígrafo, una taza y una lámpara. Los clientes 1 y 3 viven en Seúl; el 2 en Busan y el 4 en Daejeon. Mantenga los literales UTF-8 al reproducir los resultados.

El pedido 101 suma 11500 en productos, el 102 suma 8000 y el 103 suma 19500: el total pagado es 39000. Los 1500 del pedido cancelado 104 se excluyen. Los nombres de tabla independientes evitan que pruebas de otros artículos modifiquen esta base de comparación.

Agrupar por año y mes y contar pedidos correctamente

SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
 COUNT(DISTINCT o.order_id) AS paid_orders,
 SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;
Mes Pedidos pagados Importe de productos
2026-01 2 19500.00
2026-02 1 19500.00

La unión contiene cinco líneas pagadas, pero solo tres pedidos. Por eso se usa COUNT(DISTINCT order_id) para contar pedidos, mientras SUM agrega los importes de sus líneas. Un pedido pagado sin líneas no aparece en esta unión interna; si debe contar esos pedidos, calcule ese indicador desde la población de pedidos correspondiente.

Agrupar únicamente por el número de mes podría mezclar enero de años diferentes. El año y mes del ejemplo evitan esa mezcla, y el filtro utiliza inicio incluido y fin excluido. La consulta no genera filas de cero para meses ausentes; hacerlo requiere una población explícita de calendario. La referencia oficial de GROUP BY explica la agrupación y su relación con los filtros.

Conservar a los clientes sin compras en sus totales

SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

Los ID 1–4 tienen importes pagados de 19500.00, 19500.00, 0.00 y 0.00. LEFT JOIN mantiene los clientes y CASE suma solo las líneas pagadas; COALESCE transforma el total ausente en cero. El cliente 3 solo tiene un pedido cancelado y el 4 no tiene pedidos: ambos se conservan.

Mover la condición de pago a WHERE puede eliminar clientes sin pedidos pagados. Mantenga la ubicación del filtro que exige el informe. Si adapta el informe a un período, añada también esa condición en la etapa adecuada para conservar la población de clientes, en lugar de convertir por accidente la unión externa en un filtro que los excluya.

No sume directamente el envío de cada pedido a través de estas líneas: se repetiría una vez por línea. El informe solo suma productos. Las cantidades definidas por pedido necesitan un cálculo separado a esa unidad o una agregación previa.

Buscar productos sin ninguna venta pagada

SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
 WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;

Solo aparece el producto 4, la lámpara (스탠드). NOT EXISTS comprueba que no haya ninguna línea relacionada con un pedido pagado. Un producto que figurara únicamente en pedidos cancelados también se consideraría sin ventas según esta definición.

«Nunca vendido» no equivale a «sin ventas en 2026» ni a «sin ninguna aparición en pedidos de cualquier estado». Las otras preguntas necesitan condiciones diferentes. La práctica de EXISTS y NOT EXISTS permite comparar preguntas de existencia; la referencia oficial de EXISTS detalla la prueba de presencia de filas.

Conciliar totales, omisiones y límites temporales

En los datos iniciales, los dos importes mensuales suman 39000 y los cuatro importes de clientes también. Si difieren, compare claves primarias, períodos, estado y precio unitario; después revise la multiplicación de filas y el filtrado tras LEFT JOIN. Un total general verosímil no demuestra que cada fila ni cada informe sean correctos.

En informes reales, defina también un momento de observación coherente, la política de zona horaria y el tratamiento de cancelaciones y reembolsos. Los valores DATETIME fijos de estas prácticas mantienen estables sus límites entre fechas de ejecución. En una aplicación, pase los períodos solicitados como parámetros del controlador en lugar de concatenar texto en el SQL.

Antes de repetir la preparación, revise la base seleccionada, las definiciones y los datos y respalde lo que deba conservar. Si las cuatro tablas siguientes pertenecen solo al ejercicio y son prescindibles, elimínelas en este orden, primero las hijas y después las referenciadas. DROP TABLE elimina estructuras y registros y no se revierte con un ROLLBACK ordinario; mantenga esta limpieza fuera de las pruebas transaccionales.

DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;