Como usar o SQL: 2011 Funções de janela
Adicionado em SQL: 2011 são cinco funções da janela que avaliam uma expressão em uma linha R2 que está em algum lugar na moldura da janela da linha atual R1. As funções são LAG
Menu
Estas funções permitem que você para recuperar informações de linhas especificadas que estão dentro da moldura da janela da linha atual.
Olhar para trás com a função LAG
o LAG função permite obter informações a partir da linha atual na janela que você está examinando, bem como informações de outra linha que você especificar que precede a linha atual.
Suponha, por exemplo, que você tem uma tabela que registra as vendas totais para cada dia do ano em curso. Uma coisa que você pode querer saber é como as vendas de hoje comparar com ontem. Você poderia fazer isso com o LAG função, como segue:
SELECIONE TotalSale AS TodaySale, LAG (TotalSale) OVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-
Para cada linha DailyTotals, esta consulta iria retornar uma linha listando valor das vendas totais desse linha e figura total de vendas do dia anterior. O deslocamento padrão é 1, que é por isso resultado do dia anterior é retornado ao invés de qualquer outro.
Para comparar as vendas do dia atual aos de uma semana antes, você poderia usar o seguinte:
SELECIONE TotalSale AS TodaySale, LAG (TotalSale, 7) OVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-
As primeiras sete linhas de uma moldura de janela não terá um antecessor que é uma semana mais velhos. A resposta padrão para essa situação é para retornar um resultado nulo para PrevDaySale. Se você preferir algum outro resultado para um resultado nulo, por exemplo zero, você pode especificar o que você deseja retornado nesta situação, em vez do valor nulo padrão, por exemplo, 0 (zero), como mostrado aqui:
SELECIONE TotalSale AS TodaySale, LAG (TotalSale, 7, 0) OVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-
O comportamento padrão é para contar linhas que têm uma medida lag, que neste caso é TotalSale, que contém um valor nulo. Se você quiser pular essas linhas e contar apenas linhas que têm um valor real na medida em atraso, você pode fazê-lo adicionando as palavras-chave IGNORE NULLS como mostrado na seguinte variante do exemplo:
SELECIONE TotalSale AS TodaySale, LAG (TotalSale, 7, 0) IGNORE NULLSOVER (ORDER BY SaleDate) AS PrevDaySaleFROM DailyTotals-
Olhe para frente com a função LEAD
o CONDUZIR função de janela funciona exatamente da mesma forma como o LAG função funciona, exceto que, em vez de olhar para trás para uma linha anterior, ele olha para frente para uma linha a seguir a linha atual na moldura da janela. Um exemplo pode ser:
SELECIONE TotalSale AS TodaySale, LEAD (TotalSale, 7, 0) IGNORE NULLSOVER (ORDER BY SaleDate) AS NextDaySaleFROM DailyTotals-
Olhe para uma linha especificada com a função NTH_VALUE
o NTH_VALUE função é semelhante à LAG e CONDUZIR funções, exceto que em vez da avaliação de uma expressão em uma fileira anteriores ou posteriores a linha atual, avalia uma expressão em uma linha que está em um deslocamento especificado a partir da primeira ou a última linha na moldura da janela.
Aqui está um exemplo:
SELECIONE TotalSale AS ChosenSale, NTH_VALUE (TotalSale, 2) a partir FIRSTIGNORE NULLSOVER (ORDER BY SaleDate) linhas entre 10 anterior e 10 seguintes) AS EarlierSaleFROM DailyTotals-
Neste exemplo, EarlierSale é avaliada como se segue:
A moldura da janela associado com a linha atual é formada. Ele inclui os dez anterior e os dez seguintes linhas.
TotalSale é avaliada em cada linha da moldura da janela.
IGNORE NULLS é especificado, então todas as linhas que contém um valor nulo para TotalSale são ignorados.
A partir do primeiro valor remanescente após a exclusão de linhas que contêm um valor nulo para TotalSale, avançar por duas fileiras (para a frente porque DE PRIMEIRA foi especificado).
O valor de EarlierSale é o valor de TotalSale a partir da linha especificada.
Se você não quiser pular linhas que têm um valor nulo para TotalSale, especificamos NULLS RESPEITO ao invés de IGNORE NULLS. o NTH_VALUE função funciona de forma semelhante, se você especificar DE ÚLTIMA ao invés de DE PRIMEIRA, exceto em vez de contar para a frente a partir do primeiro registro na moldura da janela, você contar para trás a partir do último registro na moldura da janela.
O número que especifica o número de linhas para contar ainda é positivo, mesmo que você está contando para trás em vez de para a frente.
Olhe para um valor muito específico com FIRST_VALUE e LAST_VALUE
o FIRST_VALUE e LAST_VALUE funções são casos especiais da NTH_VALUE função. FIRST_VALUE é equivalente a NTH_VALUE Onde DE PRIMEIRA é especificado e o deslocamento é 0 (zero). LAST_VALUE é equivalente a NTH_VALUE Onde DE ÚLTIMA é especificado eo deslocamento é 0. Com ambos, você pode optar por ignorar ou respeitar nulos.