O Hub Redgate

passei os últimos seis anos viajando pelos EUA, contando aos profissionais da base de dados sobre as funções da janela T-SQL aos sábados e outros eventos. Espanta-me como poucas pessoas ouviram falar destas funções e ainda menos quem as está a usar. No final de cada apresentação, uma ou mais pessoas vêm até dizer que desejavam ter aprendido sobre essas funções anos antes, porque poderiam ter sido benéficos para tantas consultas.,estas funções foram promovidas para melhorar o desempenho em relação a outros métodos mais tradicionais. Concordo parcialmente. Eles tornam muitas consultas mais fáceis de escrever, e, às vezes, eles melhoram o desempenho.

nada a ver com o Windows OS

estas funções fazem parte dos padrões ANSI SQL 2003 e, no caso do servidor SQL, são funções T-SQL usadas para escrever consultas. Eles não têm nada a ver com o Sistema Operacional Windows ou qualquer chamada da API. Outros sistemas de banco de dados, como Oracle, também incluíram estes como parte de sua própria linguagem SQL.,as funções

janela (também, janelas ou janelas) realizam um cálculo sobre um conjunto de linhas. Eu gosto de pensar em “olhar através da janela” nas linhas que estão sendo devolvidas e ter uma última chance de realizar um cálculo. A janela é definida pela cláusula de OVER que determina se as linhas são particionadas em conjuntos menores e se elas são ordenadas. Na verdade, se você usar uma função de janela você sempre vai usar uma cláusula OVER. A cláusula OVER também faz parte do próximo valor para sintaxe necessária para o objeto sequencial, mas, caso contrário, é usado com funções de janela.,

a cláusula OVER pode conter uma partição por opção. Isto quebra as linhas em conjuntos menores. Podes pensar que isto é o mesmo que o Grupo A, mas não é. Ao agrupar, uma linha por grupo único é retornada. Ao usar a partição BY, todas as linhas de detalhe são retornadas juntamente com os cálculos. Se você tem uma janela em sua casa que é dividida em painéis, cada painel é uma janela. Ao pensar sobre as funções da janela, todo o conjunto de resultados é uma partição, mas ao usar a partição por, cada partição também pode ser considerada uma janela., A partição Por é suportada-e opcional-para todas as funções de windowing.

a cláusula OVER também pode conter uma ordem por opção. Isto é independente da ordem por cláusula da consulta. Algumas das funções requerem ordem por, e não é suportado pelos outros. Quando a ordem das linhas é importante ao aplicar o cálculo, a ordem Por é necessária.

As Funções Da Janela só podem ser usadas na seleção e ordem por cláusulas de uma consulta. Eles são aplicados após qualquer junção, filtragem ou agrupamento.,

funções de Ranking

as funções de janela mais usadas, funções de ranking, estão disponíveis desde 2005. Foi quando a Microsoft introduziu o número de linha, o RANK, o DENSE_RANK e o NTILE. ROW_NUMBER é usado com muita frequência, para adicionar números de linha únicos a uma partição ou a todo o conjunto de resultados. Adicionar um número de linha, ou uma das outras funções de ranking, não é geralmente o objetivo, mas é um passo ao longo do caminho para a solução.

ordem Por é exigida na cláusula OVER ao usar o número de linha e as outras funções deste grupo., Isto indica ao motor da base de dados a ordem pela qual os números devem ser aplicados. Se os valores das colunas ou expressões usadas na ordem por não são únicos, então RANK e DENSE_RANK irá lidar com os laços, enquanto ROW_NUMBER não se importa com os laços. NTILE é usado para dividir as linhas em baldes com base na ordem por.

um dos benefícios do ROW_NUMBER é a capacidade de transformar linhas não-únicas em linhas únicas. Isto pode ser usado para eliminar linhas duplicadas, por exemplo.

para mostrar como isto funciona, comece com uma tabela temporária contendo linhas duplicadas., O primeiro passo é criar a tabela e povoá-la.

Se adicionar o número de linha e o particionamento por cada coluna irá reiniciar os números de linha para cada conjunto único de linhas. Você pode identificar as linhas únicas, encontrando aqueles com um número de linha igual a um.,

Agora, tudo que você tem a fazer é excluir as linhas que têm um número de linha maior do que um., O problema é que você não pode adicionar funções de janela para a cláusula onde.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Você pode então apagar as linhas da direita do CTE.

sucesso! As linhas extras foram apagadas, e um conjunto único de linhas permanece.

Para ver a diferença entre ROW_NUMBER, RANK, DENSE_RANK, executar esta consulta:

A ORDEM, para cada cláusula é Datadopedido que não é o único. Este cliente fez duas encomendas em 2013-10-24. ROW_NUMBER apenas continuou a atribuir números e não fez nada diferente, mesmo que haja uma data duplicada., RANK atribuído 6 a ambas as linhas e, em seguida, apanhado até ROW_NUMBER com um 8 na próxima linha. O DENSE_RANK também atribuiu 6 às duas linhas, mas atribuiu 7 à linha seguinte.

dois explicam a diferença, pense em número de linha como posicional. O RANK é tanto posicional quanto lógico. Essas duas linhas são classificadas logicamente da mesma forma, mas a próxima linha é classificada pela posição no conjunto. O DENSE_RANK classifica-os logicamente. Ordem 2013-11-04 é a sétima data única.

A função final neste grupo é chamada de NTILE. Ele atribui números de balde para as linhas em vez de números de linha ou fileiras., Aqui está um exemplo:

NTILE tem um parâmetro, neste caso 4, que é o número de segmentos que você deseja ver nos resultados. A ordem BY é aplicada à soma das vendas. As linhas com os 25% mais baixos são atribuídas 1; as linhas com os 25% mais elevados são atribuídas 4. Finalmente, os resultados de NTILE são multiplicados por 1000 para chegar ao montante de bônus. Uma vez que 14 Não pode ser dividido uniformemente por 4, uma linha extra vai para cada um dos dois primeiros baldes.

agregados de janelas

agregados de janelas também foram introduzidos com o SQL Server 2005., Estes tornam a escrita algumas consultas difíceis fácil, mas muitas vezes realizar pior do que as técnicas mais antigas. Eles permitem que você adicione sua função agregada favorita a uma consulta não agregada. Por exemplo, você gostaria de exibir todas as encomendas de clientes, juntamente com o subtotal para cada cliente., Adicionando uma SOMA usando a cláusula OVER, você pode fazer isso muito facilmente:

adicionando a PARTIÇÃO, o subtotal é calculado para cada cliente. Qualquer função agregada pode ser usada, e a ordem na cláusula OVER não é suportada.

melhorias agregadas da janela em 2012

a partir de 2012, você pode adicionar uma ordem à cláusula OVER para agregados da janela para produzir totais em execução e médias móveis, por exemplo. Ao mesmo tempo, a Microsoft introduziu o conceito de framing. Adicionar uma partição Por é como dividir uma janela em painéis., Adicionar enquadramento é como criar uma janela de vitral. Cada linha tem uma janela individual onde a expressão será aplicada.

com esta melhoria, você pode criar totais em execução mesmo sem adicionar a sintaxe de enquadramento., Here is an example that returns a running total by customer:

O quadro padrão, que é usado se um quadro não é especificado, é o INTERVALO ENTRE UNBOUNDED ANTERIOR E a LINHA ATUAL. Infelizmente, isto não irá funcionar tão bem como se você especificar esta moldura em vez disso: linhas entre a linha anterior sem restrições e a linha atual. A diferença é a palavra linhas. O intervalo é apenas parcialmente implementado neste momento, e é destinado para trabalhar com períodos de tempo, enquanto as linhas são posicionais., A moldura, linhas entre a linha anterior sem limites e a linha atual, significa que a janela consiste na primeira linha da partição e todas as linhas até a linha atual. Cada cálculo é feito sobre um conjunto diferente de linhas. Por exemplo, ao realizar o cálculo para a Linha 4, as linhas 1 a 4 são usadas.

Ao efectuar o cálculo para a linha 5, as linhas são de 1 a 5. A janela cresce à medida que você se move de uma linha para a seguinte.,

Pode também usar as linhas de sintaxe entre a linha anterior e a linha ou linhas actuais entre a linha actual e a linha seguinte. Isto poderia ser útil para calcular uma média móvel de três meses, por exemplo. A figura seguinte representa linhas entre 2 linhas anteriores e a linha actual.

Quando 5 é a linha actual, a janela move-se; não muda o tamanho.,

Aqui está a lista de termos que você precisa saber ao escrever o enquadramento opção:

eu admito que essa sintaxe é um pouco confuso, mas usando o SQL Prompt ajuda a escrever o enquadramento opção mais fácil!

funções de Offset

também incluídas com o lançamento do SQL Server 2012 são quatro funções que lhe permitem incluir valores de outras linhas – sem fazer uma auto-junção. A Microsoft chama estas “funções analíticas”, mas eu sempre me refiro a elas como “funções offset” ao apresentar sobre este tópico., Duas das funções permitem-lhe extrair colunas ou expressões de uma linha antes (LAG) ou depois (LEAD) da linha actual. As outras duas funções permitem-lhe devolver os valores da primeira linha da partição (FIRST_VALUE) ou da última linha da partição (LAST_VALUE). FIRST_VALUE e LAST_VALUE também requerem framing, por isso certifique-se de incluir a moldura ao usar estas funções. Todas as quatro funções requerem a ordem por opção da cláusula OVER. Isso faz sentido, porque o motor de banco de dados deve saber a ordem das linhas para descobrir qual linha contém o valor a retornar.,algumas pessoas têm uma banda favorita; outras têm um filme favorito. Tenho um desfasamento favorito. É fácil de usar (sem moldura!) e tem um ótimo desempenho., Here is an example:

LAG e LEAD requerem um argumento – o de coluna ou expressão que você deseja retornar. Por padrão, LAG retorna o valor da linha anterior, e LEAD retorna o valor da seguinte linha. Você pode modificar isso fornecendo um valor para o parâmetro OFFSET, que é 1 por padrão. Repare que a primeira linha da partição devolve nulo. Se você deseja anular os NULLs, você pode fornecer um valor padrão., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE e LAST_VALUE pode ser usado para localizar um valor a partir da primeira linha ou último da linha de partição. Certifique-se de especificar a moldura, não só por razões de desempenho, mas porque a moldura padrão não funciona como você esperaria com LAST_VALUE. A moldura por omissão, intervalo entre a linha anterior sem limites e a linha actual, só sobe para a linha actual. A última linha da partição não está incluída., Para obter os resultados esperados, certifique-se de especificar as linhas entre a linha actual e a seguinte sem restrições ao usar o LAST_VALUE., Aqui está um exemplo usando FIRST_VALUE:

Funções Estatísticas

a Microsoft grupos de essas quatro funções – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – juntamente com o desvio de funções de chamar todos os oito as funções analíticas. Uma vez que eu gosto de distinguir estas das funções offset, eu chamo estas estatísticas.

PERCENT_ rank e CUME_DIST fornecem um ranking para cada linha sobre uma partição. Eles diferem um pouco. O PERCENT_ rank devolve a percentagem de linhas que estão abaixo da linha actual., “A minha pontuação é superior a 90% das Pontuações.”CUME_DIST, or cumulative distribution, returns the exact rank. “A minha pontuação é de 90% das Pontuações.”Aqui está um exemplo usando a temperatura média alta em St.Louis para cada mês. Note que as fileiras foram determinadas pela temperatura Fahrenheit.

as fileiras não são determinadas pelos valores relativos, mas pelas posições das linhas. Notem que Março e novembro têm a mesma temperatura média alta, então eles foram classificados da mesma forma.

pode estar a perguntar-se como calcular o PERCENT_ rank e o CUME_DIST., Aqui estão as fórmulas:

PERCENTILE_DISC e PERCENTILE_CONT funcionam de maneira oposta. Dada uma posição percentual, encontre o valor nessa posição., Eles diferem nesse PERCENTIL_ Disc irá retornar um valor que existe no conjunto enquanto PERCENTIL_CONT irá calcular um valor exato se nenhum dos valores no conjunto cai precisamente nessa posição. Você pode usar o PERCENTIL_CONT para calcular uma mediana fornecendo 0, 5 como a classificação por cento. Por exemplo, que temperatura é de 50% em St. Louis?

a função PERCENTIL_CONT mede a média dos dois valores mais próximos do meio, 67 e 69, e mede-os. O PERCENTIL_ Disc devolve um valor exacto, 67., Note também que estas duas funções têm uma cláusula extra Não vista nas outras funções, dentro do grupo, que contém a ordem em vez de dentro da cláusula OVER.

resumo

este artigo é uma visão geral muito rápida das funções da janela T-SQL. Dois tipos de funções foram liberados com o SQL Server 2005, as funções de ranking e agregados de janelas. Com 2012, você tem aprimorado o agregado de janelas com o framing e as funções analíticas. Eu gosto de separar as funções analíticas em dois grupos, as funções offset e Estatística., As funções da janela tornam muitas consultas mais fáceis de escrever, e eu acredito que esse é o principal benefício. Em alguns casos, as consultas funcionarão melhor, também, mas isso é uma discussão para outro dia.espero que este artigo o tenha inspirado a aprender mais sobre estas funções fantásticas!