Como usar uma janela de SQL para criar um conjunto de resultados

Janelas e funções da janela foram introduzidos pela primeira vez no SQL: 1999. Com uma janela, um usuário pode opcionalmente particionar um conjunto de dados, opcionalmente ordenar as linhas em cada partição, e especificar um conjunto de linhas (a moldura da janela) que está associado com uma determinada linha.

A armação de janela de uma fileira R é um subconjunto da partição contendo R. Por exemplo, a moldura da janela pode consistir em que todas as linhas a partir do início da partição até e incluindo R, com base na forma linhas são ordenadas na partição .

Uma funo de janela calcula um valor para uma linha de R, com base nas linhas no quadro de janela de R.

Por exemplo, suponha que você tenha uma tabela de vendas que tem colunas de CustID, InvoiceNo e TotalSale. Seu gerente de vendas pode querer saber o que as vendas totais foram a cada cliente ao longo de um determinado intervalo de números de faturas. Você pode obter o que ela quer com o seguinte código SQL:

SELECIONE CustID, InvoiceNo, SUM (TotalSale) OVER (PARTITION BY CustIDORDER POR InvoiceNoROWS BETWEENUNBOUNDED PRECEDINGAND CURRENT ROW) FROM vendas-

o SOBRE cláusula determina como as linhas da consulta são divididas antes de serem processados, neste caso, pela SOMA função. Uma partição é atribuído a cada cliente. Dentro de cada partição será uma lista de números de factura, e associado com cada um deles será a soma de toda a TotalSale os valores superiores do intervalo especificado de linhas, para cada cliente.

SQL: 2011 adicionou vários grandes melhorias para a funcionalidade janela original, incorporando novas palavras-chave.

Como particionar uma janela em baldes com NTILE

o NTILE função de janela distribui uma partição janela ordenou em algum número inteiro positivo n de baldes, numerando os baldes de 1 a n. Se o número de linhas em uma partição m não é divisível por n, em seguida, após a função NTILE enche os baldes de maneira uniforme, o restante m / n, chamado r, é imputado inteiramente à primeira r baldes, tornando-as maiores que os outros baldes.

Suponha que você queira classificar seus funcionários por salário, dividindo-os em cinco baldes, do maior para o menor. Você pode fazê-lo com o seguinte código:

SELECIONE nome, sobrenome, NTILE (5) OVER (ORDER BY Salário DESC) AS BUCKETFROM funcionário-

Se existirem, por exemplo, 11 empregados, cada balde é preenchido com dois excepto para o primeiro segmento, que é preenchido com três. O primeiro balde conterá os três funcionários mais bem pagos, eo quinto balde conterá os dois funcionários mais mal pagos.

Como funções da janela ninho

Às vezes, para obter o resultado que você precisa, a maneira mais fácil é para aninhar uma função dentro de outra. SQL: 2011 adicionou a capacidade de fazer tais assentamento com funções da janela.

Como exemplo, considere um caso em que um investidor de ações está tentando determinar se é um bom momento para comprar um estoque particular. Para obter uma alça sobre isso, ela decide comparar o preço atual da ação para o preço que foi vendido por nos imediatamente anteriores 100 comércios. Ela pergunta, quantas vezes nos últimos 100 comércios que vendeu por menos do que o preço atual.

Para chegar a uma resposta, ela faz a seguinte consulta:

SaleTime SELECT, SUM (CASE WHEN SalePrice 
A janela engloba as 100 linhas anteriores da linha atual, que correspondem às 100 vendas imediatamente anteriores ao momento atual. Cada vez que uma fila é avaliado quando o valor de Preço de venda é inferior ao preço mais recente, 1 é adicionado à soma.
O resultado final é um número que indica o número de vendas fora do anterior cem que foram efectuadas a um preço inferior ao preço atual.
Como avaliar grupos de linhas
Às vezes, a chave de ordenação que você escolheu para colocar uma partição, a fim terá duplicatas. Você pode querer avaliar todas as linhas que têm a mesma chave de classificação como um grupo. Nesses casos, você pode usar o GRUPOS opção. Com ele você pode contar grupos de linhas onde as chaves de classificação são idênticos.
Aqui está um exemplo:

SELECIONE Cliente, SaleDate, SUM (InvoiceTotal) OVER (PARTITION BY CustomerIDORDER POR SaleDateGROUPS entre 2 anterior e 2 SEGUINTE) FROM clientes-

A moldura da janela, neste exemplo, é composto por até cinco grupos de linhas: dois grupos antes de o grupo que contém a linha atual, o grupo que contém a linha atual, e dois grupos seguintes o grupo que contém a linha atual. As linhas em cada grupo têm a mesma Data de venda, e a Data de venda associado a cada grupo é diferente do Data de venda valores para os outros grupos.