Curso de MariaDB / Conferir vendas mensais, compras por cliente e produtos sem vendas
Vendas mensais, compras por cliente e produtos sem vendas têm unidades de resultado diferentes, mesmo consultando os mesmos dados. Defina estados e valores incluídos e o significado de “sem vendas” e confira os totais mensais e por cliente somente quando períodos e condições forem comparáveis.
Definir os indicadores dos relatórios
Nesta prática, vendas significam quantidade vezes preço unitário de compra dos itens de pedidos pagos; frete e pedidos cancelados ficam de fora. Isso não representa uma política contábil completa de receitas, impostos ou reembolsos. Um produto sem vendas é um produto cadastrado que nunca apareceu em um pedido pago.
Examine os critérios de GROUP BY e HAVING e os riscos de multiplicar valores ao unir várias tabelas. Antes de escrever um relatório, declare o significado de uma linha e a população examinada.
| Relatório | O que uma linha representa | População do exemplo |
|---|---|---|
| Vendas mensais | Um ano e mês | Itens pagos de 2026 |
| Compras por cliente | Um cliente | Todo o histórico disponível; inclui zeros |
| Produtos sem vendas | Um produto | Nenhum item em pedido pago em todo o histórico |
Todos os pedidos iniciais estão em 2026, portanto os valores mensais e por cliente podem ser comparados neste conjunto. Ao acrescentar pedidos de outros anos, a consulta por cliente continuará somando-os, mas a mensal não: alinhe explicitamente os períodos antes de esperar o mesmo total.
Preparar dados pequenos que possam ser conferidos à mão
Conecte como course_user ao servidor de prática e selecione shopcourse. O guia do cliente e de arquivos SQL UTF-8 explica como salvar o código e executar SOURCE C:/mariadb-lab/lesson.sql;, substituindo o caminho pelo real. Execute SQL dentro do cliente MariaDB.
Confira SELECT VERSION(), DATABASE(); e SHOW TABLES; antes de criar c42_customers, c42_products, c42_orders e c42_items. São nomes exclusivos deste exercício. Se já existirem, examine definições e registros antes de decidir se são prescindíveis; preserve tabelas de aplicações.
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);
Os nomes e cidades originais em coreano são mantidos. Os produtos 1–4 são caderno, caneta, caneca e luminária. Os clientes 1 e 3 vivem em Seul; o 2, em Busan; e o 4, em Daejeon. Conserve os literais UTF-8 ao reproduzir os resultados.
O pedido 101 soma 11500 em mercadorias; o 102, 8000; e o 103, 19500: o total pago é 39000. Os 1500 do cancelado 104 ficam de fora. Nomes de tabela independentes evitam que testes de outros artigos alterem essa base de comparação.
Agrupar por ano e mês e contar pedidos corretamente
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;
| Mês | Pedidos pagos | Valor das mercadorias |
|---|---|---|
| 2026-01 | 2 | 19500.00 |
| 2026-02 | 1 | 19500.00 |
A união contém cinco itens pagos, mas somente três pedidos. Por isso, COUNT(DISTINCT order_id) conta pedidos enquanto SUM agrega valores dos itens. Um pedido pago sem itens fica fora dessa união interna; para contar esses pedidos, calcule o indicador a partir da população de pedidos correspondente.
Agrupar somente pelo número do mês poderia misturar janeiro de anos diferentes. Ano e mês evitam essa mistura, e o filtro usa início incluído e fim excluído. A consulta não cria linhas com zero para meses ausentes; isso exige uma população explícita de calendário. A referência oficial de GROUP BY explica agrupamento e relação com filtros.
Conservar clientes sem compras nos totais
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;
Os IDs 1–4 têm valores pagos de 19500.00, 19500.00, 0.00 e 0.00. LEFT JOIN mantém clientes e CASE soma somente itens pagos; COALESCE transforma o total ausente em zero. O cliente 3 tem somente um pedido cancelado, e o 4 não tem pedidos: ambos permanecem.
Mover a condição de pagamento para WHERE pode remover clientes sem pedidos pagos. Mantenha a posição do filtro exigida pelo relatório. Ao adaptar o relatório a um período, acrescente também essa condição na etapa adequada para conservar a população de clientes e evitar um filtro que os exclua acidentalmente.
Evite somar diretamente o frete de cada pedido através desses itens: ele se repetiria uma vez por item. O relatório soma somente mercadorias. Valores definidos por pedido exigem um cálculo separado nesse nível ou uma agregação prévia.
Buscar produtos sem nenhuma venda paga
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;
Aparece somente o produto 4, a luminária (스탠드). NOT EXISTS verifica se não existe nenhum item relacionado a um pedido pago. Um produto que aparecesse somente em pedidos cancelados também seria considerado sem vendas nesta definição.
“Nunca vendido” difere de “sem vendas em 2026” e de “sem nenhuma ocorrência em pedidos de qualquer estado”. As outras perguntas exigem condições distintas. A prática de EXISTS e NOT EXISTS ajuda a comparar perguntas de existência; a referência oficial de EXISTS detalha o teste de presença de linhas.
Conferir totais, omissões e limites temporais
Nos dados iniciais, os dois valores mensais somam 39000 e os quatro valores por cliente também. Se diferirem, compare chaves primárias, períodos, estado e preço unitário; depois examine multiplicação de linhas e filtragem após LEFT JOIN. Um total geral plausível não comprova que cada linha ou relatório esteja correto.
Em relatórios reais, defina também um momento consistente de observação, política de fuso horário e tratamento de cancelamentos e reembolsos. Os valores DATETIME fixos desta prática mantêm os limites estáveis entre datas de execução. Na aplicação, passe os períodos solicitados como parâmetros do driver, em vez de concatenar texto no SQL.
Antes de repetir a preparação, examine banco selecionado, definições e dados e copie o que precisar conservar. Se as quatro tabelas seguintes pertencerem somente ao exercício e forem prescindíveis, remova nesta ordem, primeiro as filhas e depois as referenciadas. DROP TABLE remove estrutura e registros e não é desfeito por um ROLLBACK comum; mantenha essa limpeza fora dos testes transacionais.
DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;




