Curso de MariaDB / Comparar ROW_NUMBER, RANK e DENSE_RANK
As funções de janela do MariaDB acrescentam cálculos a um conjunto sem reduzir suas linhas a uma única linha de saída. ROW_NUMBER atribui um número diferente a cada linha; RANK e DENSE_RANK permitem compartilhar posições quando os valores de ordenação empatam, mas diferem nos saltos deixados depois.
Separar número de linha de posição compartilhada
Dois clientes com o mesmo valor de compras podem precisar de números de linha distintos para apresentação, mas da mesma posição pelo valor. Primeiro calcule um total por cliente e depois classifique essas linhas. Trocar somente a função não corrige uma entrada formada por itens de pedido nem uma definição incorreta de empate. Separe as etapas com WITH e expressões de tabela comuns (CTEs).
| Função | Empates | Sequência do exemplo |
|---|---|---|
| ROW_NUMBER | Um número diferente por linha | 1, 2, 3, 4 |
| RANK | Compartilha posições e deixa saltos | 1, 1, 3, 3 |
| DENSE_RANK | Compartilha posições sem saltos | 1, 1, 2, 2 |
Preparar clientes com valores iguais
Conecte ao servidor de prática como course_user e selecione shopcourse. O guia do cliente e de arquivos SQL UTF-8 explica como salvar o código e executá-lo dentro do cliente com SOURCE C:/mariadb-lab/lesson.sql;, substituindo o caminho pelo real. Execute o SQL no cliente MariaDB.
Confira SELECT VERSION(), DATABASE(); e SHOW TABLES; antes de criar c32_customers, c32_products, c32_orders e c32_items. São nomes exclusivos deste exercício. Se já existirem, examine definições e dados antes de decidir se pode removê-los; preserve tabelas de aplicações. Confira se sua versão aceita a sintaxe de funções de janela utilizada.
USE shopcourse;
CREATE TABLE c32_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 c32_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 c32_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_c32_order_customer FOREIGN KEY (customer_id)
REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_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_c32_item_order FOREIGN KEY (order_id)
REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_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 c32_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 compartilham Seul (서울); os clientes 2 e 4 vivem em Busan e Daejeon. Conserve esses literais UTF-8 para reproduzir os resultados.
O cliente 1 soma 11500.00 e 8000.00 em dois pedidos pagos; o cliente 2 soma 19500.00 em um. Ambos têm, portanto, 19500.00. O cliente 3 tem somente um pedido cancelado, e o 4 não tem pedidos. O relatório inclui ambos com total pago zero: incluir quem não comprou e excluir valores cancelados são decisões desta consulta.
Agregar por cliente e comparar as três funções
WITH customer_totals AS (
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 goods_total
FROM c32_customers c LEFT JOIN c32_orders o
ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;
Os LEFT JOINs conservam clientes mesmo sem pedidos ou itens. CASE soma valores somente quando o pedido está pago e COALESCE converte um total ausente em zero. GROUP BY produz uma linha por cliente antes das funções de janela. O valor usa os preços dos itens e não inclui frete.
| Cliente | Total | row_no | amount_rank | dense_amount_rank |
|---|---|---|---|---|
| 1 | 19500.00 | 1 | 1 | 1 |
| 2 | 19500.00 | 2 | 1 | 1 |
| 3 | 0.00 | 3 | 3 | 2 |
| 4 | 0.00 | 4 | 3 | 2 |
ROW_NUMBER ordena pelo valor decrescente e usa customer_id como desempate único. RANK e DENSE_RANK ordenam somente pelo valor para manter a mesma posição nos totais iguais. Acrescentar o ID único às definições dessas funções tornaria as combinações diferentes e eliminaria os empates desejados. Depois dos dois primeiros empatados, RANK continua em 3 e DENSE_RANK em 2.
Distinguir ordem da janela, apresentação e partições
ORDER BY dentro de OVER define a ordem do cálculo. O ORDER BY do SELECT final define a ordem de apresentação. Escreva ambos quando precisar definir as duas: a ordenação de uma janela não garante sozinha a ordem de exibição.
PARTITION BY divide linhas em grupos nos quais o cálculo recomeça. Para numerar clientes por cidade, a etapa agregada precisa fornecer city. Diferentemente de GROUP BY, a partição da janela não reduz os clientes a uma única linha por cidade.
A consulta completa seguinte usa as mesmas tabelas e conserva uma linha por cliente. Ela numera dentro de cada cidade e mostra o resultado pelo ID do cliente.
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
ROW_NUMBER() OVER (PARTITION BY city
ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;
| customer_id | goods_total | regional_row_no |
|---|---|---|
| 1 | 19500.00 | 1 |
| 2 | 19500.00 | 1 |
| 3 | 0.00 | 2 |
| 4 | 0.00 | 1 |
Os clientes 1 e 3 compartilham a partição de Seul e recebem 1 e 2. Os clientes 2 e 4 começam cada um outra partição em 1. O SELECT também mostra a cidade coreana armazenada, omitida na tabela para destacar os números. Remova somente PARTITION BY city para comparar uma numeração global e depois restaure a partição por cidade.
Filtrar a classificação em uma consulta externa
O WHERE do mesmo SELECT não pode usar diretamente o row_no que está sendo calculado. Obtenha a classificação em outra CTE ou tabela derivada e filtre por fora. “Duas linhas” e “todos os empatados em primeiro” são pedidos distintos; escolha a coluna calculada que expressa a condição.
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;
O resultado contém os clientes 1 e 2, com números de linha 1 e 2 e posição 1 para ambos. Mude somente a condição externa para row_no <= 1: permanece o cliente 1. Com amount_rank = 1, permanecem os dois empatados. Restaure row_no <= 2 após comparar.
Classificar os itens originais numeraria itens, em vez de clientes. Mantenha visível a separação entre a agregação por cliente e a classificação desses totais.
Verificar empates e população do relatório
Use empates deliberados e observe também a posição seguinte. No primeiro exemplo, mude somente o desempate de ROW_NUMBER de customer_id ASC para customer_id DESC: os empatados trocam seus números, enquanto as posições baseadas somente no valor permanecem iguais. Restaure ASC e confira o resultado inicial. Um número gerado para um relatório não substitui o identificador estável do cliente.
Confira também se pedidos cancelados não contribuem para o valor e se clientes sem compras continuam incluídos quando o relatório exigir. As referências oficiais de ROW_NUMBER, RANK e DENSE_RANK explicam a numeração e a política de empates.
Antes de repetir a preparação, confira banco selecionado, definições e registros; copie o que precisar conservar. Se as tabelas seguintes forem somente objetos prescindíveis deste exercício, remova nesta ordem, primeiro as filhas e depois as referenciadas. DROP TABLE remove estrutura e dados e não é desfeito por um ROLLBACK comum; separe essa limpeza dos testes de transações.
DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;




