Moldura de janela SQLite

Resumo : neste tutorial, você aprenderá sobre o quadro da janela SQLite e como usá-lo para especificar um subconjunto de partição para cálculo.

Introdução à moldura da janela SQLite

Algumas funções de janela usam a moldura da janela em seus cálculos, como FIRST_VALUE(), LAST_VALUE()e SUM(). Uma moldura de janela é usada para especificar quantas linhas ao redor da linha atual a janela deve incluir.

Para definir uma moldura de janela, você usa uma das seguintes sintaxes:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end  
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O frame_startpode escolher uma das seguintes opções:

N PRECEDING
UNBOUNDED PRECEDING
CURRENT ROW
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

E frame_endpode escolher uma das seguintes opções:

CURRENT ROW
UNBOUNDED FOLLOWING
N FOLLOWING
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A imagem a seguir mostra a estrutura de um quadro em uma partição:

Aqui está o significado de cada opção:

• UNBOUNDED PRECEDING: o quadro começa na primeira linha da partição.
• N PRECEDING: o quadro começa nas Nthlinhas anteriores à linha atual.
• CURRENT ROW: é a linha atual que está sendo processada.
• UNBOUNDED FOLLOWING: o quadro termina na linha final da partição.
• M FOLLOWING: o quadro termina na Mthlinha após a linha atual.

Por padrão, as funções de janela usam a seguinte opção:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

INTERVALO vs. LINHAS

ROWSespecifica o deslocamento da linha atual e as linhas do quadro são números de linha. Por outro lado, RANGESindica que o deslocamento da linha atual e das linhas do quadro são valores de linha.

Observe que RANGEdeve ser usado apenas com a opção UNBOUNDEDou CURRENT ROWS.

Exemplos de molduras de janela SQLite

Vejamos alguns exemplos práticos de utilização de caixilhos de janelas.

Configurando uma tabela de exemplo

Primeiramente, crie uma nova tabela chamada SaleInfoque armazene os valores de vendas por ano e mês para demonstração:

CREATE TABLE SalesInfo (
    year INT NOT NULL,
    month INT NOT NULL,
    amount NUMERIC(10,2),
    PRIMARY KEY(year,month)
);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, insira alguns dados na SalesInfotabela:

INSERT INTO SalesInfo(year,month,amount)
VALUES(2018,1,100),
    (2018,2,120),
    (2018,3,120),
    (2018,4,110),
    (2018,5,130),
    (2018,6,140),
    (2018,7,150),
    (2018,8,120),
    (2018,9,110),
    (2018,10,150),
    (2018,11,170),
    (2018,12,200);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, consulte os dados da SalesInfotabela:

SELECT 
    year,
    month,
    amount
FROM
    SalesInfo;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

1) Usando o quadro da janela SQLite para calcular o exemplo total em execução

A instrução a seguir usa a SUM()função de janela para calcular o total acumulado de vendas por mês:

SELECT 
    month,
    amount,
    SUM(amount) OVER (
        ORDER BY month
    ) RunningTotal
FROM
    SalesInfo;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Conforme mencionado anteriormente, a SUM()função de janela usa as seguintes opções de moldura de janela:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Isso significa que para a linha atual, a SUM()função adicionará valores da primeira linha à linha atual para calcular a soma.

2) Usando o quadro da janela SQLite para calcular o exemplo da média móvel

A instrução a seguir usa a AVG()função de janela para calcular a média móvel de vendas:

SELECT 
    month,
    amount,
    AVG(amount) OVER (
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) SalesMovingAverage
FROM
    SalesInfo;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo, usamos a moldura da janela com três linhas: linha atual, uma linha antes e uma linha depois da linha atual. Eles AVG()usaram os valores dessas três linhas para calcular a média móvel.

Você pode usar o conjunto de resultados de saída para fazer uma visualização conforme mostrado no gráfico a seguir:

Observe que uma média móvel é frequentemente usada com dados de séries temporais, como vendas, para suavizar flutuações de curto prazo e destacar tendências de vendas de longo prazo.

Neste tutorial, você aprendeu sobre o quadro da janela SQLite para especificar um subconjunto de partição para cálculo.