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;