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

Janelas e funções da janela foram introduzidos no SQL: 1999. Com uma janela, um utilizador 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 dada 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 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 divididos antes de serem processados, neste caso, pela SOMA função. Uma partição é atribuído a cada cliente. Dentro de cada partição irá ser uma lista de números de factura, e associada a 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 funo de janela de distribui uma partição de janela ordenados em algum número inteiro positivo n de baldes, os baldes de numeração 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, é distribuído para o primeiro 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:

SELECCIONAR nome, sobrenome, NTILE (5) SOBRE (ORDEM pelo salário DESC) COMO BUCKETFROM funcionário-

Se existirem, por exemplo, 11 empregados, cada balde é preenchido com dois excepto para o primeiro recipiente, o qual é 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 adicionado a capacidade de fazer tais nidificação com funções da janela.

Como exemplo, considere um caso em que um investidor de ações está tentando determinar se ele é 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 corrente, que correspondem às vendas 100 imediatamente antes do momento actual. Cada vez que uma linha é avaliada quando o valor das Preço de venda é menos do que o 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 classificação que você escolheu para colocar uma partição, a fim terá duplicatas. Você pode querer avaliar todas as linhas que têm o mesmo 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 precedente e 2 SEGUINTE) FROM clientes-

A armação de janela, neste exemplo, é constituído por até cinco grupos de linhas: dois grupos antes que o grupo contendo a linha corrente, o grupo que contém a linha corrente, e dois grupos seguintes o grupo que contém a linha corrente. As linhas em cada grupo têm o mesmo Data de venda, e a Data de venda associado a cada grupo é diferente do Data de venda Os valores para os outros grupos.