SQLite ROW_NUMBER

Resumo : neste tutorial, você aprenderá como usar o SQLite ROW_NUMBER()para atribuir um número inteiro sequencial a cada linha no conjunto de resultados de uma consulta.

Introdução à ROW_NUMBER()função SQLite

É ROW_NUMBER()uma função de janela que atribui um número inteiro sequencial a cada linha do conjunto de resultados de uma consulta. As linhas são ordenadas a partir de uma com base na ordem especificada pela ORDER BYcláusula na definição da janela.

O seguinte mostra a sintaxe da ROW_NUMBER()função:

ROW_NUMBER() OVER (
    [PARTITION BY expression1, expression2,...]
    ORDER BY expression1 [ASC | DESC], expression2,...
)
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe,

• Primeiro, a PARTITION BYcláusula divide as linhas derivadas da FROMcláusula em partições. A PARTITION BYcláusula é opcional. Se você ignorá-lo, ROW_NUMBER()tratará todo o conjunto de resultados como uma única partição.
• Em seguida, a ORDER BYcláusula especifica a ordem das linhas em cada partição. A ORDER BYcláusula é obrigatória porque a ROW_NUMBER()função é sensível à ordem.
• Finalmente, cada linha em cada partição recebe um número inteiro sequencial denominado número da linha. O número da linha é redefinido para cada partição.

ROW_NUMBER()Exemplos de SQLite

Usaremos as tabelas customers e invoicesdo banco de dados de exemplo para a demonstração.

Usando SQLite ROW_NUMBER() com ORDER BYexemplo de cláusula

A instrução a seguir retorna o nome, o sobrenome e o país de todos os clientes. Além disso, utiliza a ROW_NUMBER()função para adicionar um número inteiro sequencial a cada registro de cliente.

SELECT
    ROW_NUMBER () OVER ( 
        ORDER BY Country 
    ) RowNum,
    FirstName,
    LastName,
    country 
FROM
    customers;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída parcial:

Usando SQLite ROW_NUMBER()com PARTITION BYexemplo

A instrução a seguir atribui um número inteiro sequencial a cada cliente e redefine o número quando o país do cliente muda:

SELECT
    ROW_NUMBER () OVER ( 
        PARTITION BY Country
        ORDER BY FirstName
    ) RowNum,
    FirstName,
    LastName,
    country 
FROM
    customers;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A imagem a seguir mostra a saída parcial:

Neste exemplo:

• Primeiro, a PARTITION BYcláusula divide os clientes em partições por país.
• Segundo, a ORDER BYcláusula classifica os clientes em cada partição pelo primeiro nome.
• Terceiro, a ROW_NUMBER()função atribui a cada linha em cada partição um número inteiro sequencial e redefine o número quando o país muda.

Usando SQLite ROW_NUMBER()para paginação

A ROW_NUMBER()função pode ser útil para paginação. Por exemplo, se você deseja exibir informações de clientes em uma tabela por páginas com 10 linhas por página.

A instrução a seguir retorna informações dos clientes das linhas 21 a 30, que é a terceira página com 10 linhas por página:

SELECT * FROM (
    SELECT
        ROW_NUMBER () OVER ( 
            ORDER BY FirstName
        ) RowNum,
        FirstName,
        LastName,
        Country 
    FROM
        customers
) t
WHERE 
    RowNum > 20 AND RowNum <= 30
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Neste exemplo:

• Primeiro, a ROW_NUMBER()função atribui a cada linha um número inteiro sequencial.
• Segundo, a consulta externa seleciona a linha de 20 a 30.

Usando SQL ROW_NUMBER()para encontrar o enésimo valor mais alto por grupo

A instrução a seguir cria uma nova visualização nomeada Salesque consiste em ID do cliente, nome, sobrenome, país e valor. O valor é recuperado da invoicestabela:

CREATE VIEW Sales 
AS
SELECT
    CustomerId,
    FirstName,
    LastName,
    Country,
    SUM( total ) Amount 
FROM
    invoices 
    INNER JOIN customers USING (CustomerId)
GROUP BY
    CustomerId;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A consulta a seguir retorna os dados da Salesvisualização:

SELECT
	* 
FROM
	sales;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A seguinte declaração encontra os clientes que têm os valores mais elevados em cada país:

SELECT 
    Country,
    FirstName,
    LastName,
    Amount
FROM (
    SELECT 
        Country, 
        FirstName,
        LastName,
        Amount,
        ROW_NUMBER() OVER (
            PARTITION BY country 
            ORDER BY Amount DESC
        ) RowNum
    FROM 
        Sales )
WHERE
    RowNum = 1;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A saída é a seguinte:

Na subconsulta :

• Primeiro, a PARTITION BYcláusula divide os clientes por país.
• Em segundo lugar, a ORDER BYcláusula classifica os clientes em cada país pelo valor, do maior para o menor.
• Terceiro, ROW_NUMBER()atribui a cada linha um número inteiro sequencial. Ele redefine o número quando o país muda.

A consulta externa seleciona os clientes que possuem RowNumo valor 1.

Se você alterar o número da linha na WHEREcláusula para 2, 3 e assim por diante, obterá os clientes que têm o segundo valor mais alto, o terceiro valor mais alto e etc.

Neste tutorial, você aprendeu como usar a ROW_NUMBER()função SQLite para atribuir um número inteiro sequencial a cada linha no conjunto de resultados da consulta.