Como usar sql: funções da janela 2011

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

Conteúdo

Olhar para trás com a função lag
Olhe para frente com a função lead
Olhe para uma linha especificada com a função nth_value
Olhe para um valor muito específico com first_value e last_value

, CONDUZIR, NTH_VALUE, FIRST_VALUE, e LAST_VALUE.

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 o total de vendas para cada dia do ano corrente. Uma coisa que você pode querer saber é como as vendas de hoje comparar a de 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:

SELECCIONAR TotalSale AS TodaySale, GAL (TotalSale, 7) SOBRE O (POR ORDEM SaleDate) COMO PrevDaySaleFROM DailyTotals-

Os primeiros sete linhas em uma moldura da janela não terá um predecessor que é uma semana mais velho. 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:

SELECCIONAR TotalSale AS TodaySale, GAL (TotalSale, 7, 0) SOBRE O (POR ORDEM SaleDate) COMO PrevDaySaleFROM DailyTotals-

O comportamento padrão é para contar linhas que têm uma extensão 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 lag, você pode fazê-lo adicionando as palavras-chave IGNORE NULLS como mostrado na seguinte variante do exemplo:

SELECCIONAR TotalSale AS TodaySale, GAL (TotalSale, 7, 0) ignoram NULLSOVER (POR ORDEM SaleDate) COMO PrevDaySaleFROM DailyTotals-

Olhe para frente com a função LEAD

o CONDUZIR função de janela opera 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 a uma linha após a linha atual na moldura da janela. Um exemplo pode ser:

SELECCIONAR TotalSale AS TodaySale, LEAD (TotalSale, 7, 0) ignoram NULLSOVER (POR ORDEM SaleDate) COMO NextDaySaleFROM DailyTotals-

Olhe para uma linha especificada com a função NTH_VALUE

o NTH_VALUE função é semelhante à LAG e CONDUZIR funções, excepto que, em vez da avaliação de uma expressão numa linha anterior ou seguinte da linha corrente, que avalia uma expressão numa linha que está em um deslocamento especificado a partir da primeira ou na última linha na moldura da janela.

Aqui está um exemplo:

SELECIONE TotalSale AS ChosenSale, NTH_VALUE (TotalSale, 2) FROM FIRSTIGNORE NULLSOVER (ORDER BY SaleDate) linhas entre 10 anterior e 10 seguintes) AS EarlierSaleFROM DailyTotals-

Neste exemplo, EarlierSale é avaliada como se segue:

A armação de janela associada com a linha corrente é formada. Ele inclui os dez anteriores e os dez seguintes linhas.
TotalSale é avaliada em cada linha da moldura da janela.
IGNORE NULLS é especificado, de modo que todas as linhas contendo 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, mover para a frente por duas filas (para a frente, porque DE PRIMEIRA foi especificada).

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 offset é 0. Com esses dois, você pode optar por ignorar ou respeitar nulos.