Este artigo é a quarta parte de uma série sobre bugs, armadilhas e melhores práticas do T-SQL. Anteriormente, eu cobri determinismo, subconsultas e joinins. O foco do artigo deste mês são bugs, pitfalls e melhores práticas relacionadas a funções de janela. Obrigado Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, Umachandar Jayachandran (UC), Fabiano Neves Amorim, Milos Radivojevic, Simon Sabin, Adam Machanic, Thomas Grohser, Chan Ming Man e Paul White por oferecerem as vossas ideias!
Nos meus exemplos vou usar uma base de dados de amostras chamada TSQLV5. Você pode encontrar o script que cria e popula esse banco de dados aqui, e seu diagrama ER aqui.
Existem duas armadilhas comuns envolvendo funções de janela, ambas resultantes de padrões implícitos contraintuitivos que são impostos pelo padrão SQL. Uma armadilha tem a ver com cálculos de totais em execução onde se obtém um quadro de janela com a opção RANGE implícita. Outra armadilha está um pouco relacionada, mas tem consequências mais graves, envolvendo uma definição de quadro implícita para as funções FIRST_VALUE e LAST_VALUE.
Quadro de janela com a opção RANGE implícita
A nossa primeira armadilha envolve o cálculo de totais em execução usando uma função de janela agregada, onde você especifica explicitamente a cláusula de ordem da janela, mas não especifica explicitamente a unidade do quadro de janela (ROWS ou RANGE) e sua extensão de quadro de janela relacionada, por exemplo LINHAS SEM LIMITES ANTERIORES. O padrão implícito é contraintuitivo e suas consequências podem ser surpreendentes e dolorosas.
Para demonstrar esta armadilha, vou usar uma tabela chamada Transações com dois milhões de transações de conta bancária com créditos (valores positivos) e débitos (valores negativos). Execute o seguinte código para criar a tabela Transações e preencha-a com dados de amostra:
SET NOCOUNT ON; USE TSQLV5; -- http://tsql.solidq.com/SampleDatabases/TSQLV5.zip DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) -- creates POC index ); DECLARE @num_partitions AS INT = 100, @rows_per_partition AS INT = 20000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
A nossa armadilha tem tanto um lado lógico com um potencial bug lógico como um lado de performance com uma penalidade de performance. A penalidade de performance só é relevante quando a função de janela é otimizada com operadores de processamento de modo de linha. O SQL Server 2016 introduz o operador batch-mode Window Aggregate, que remove a parte da penalidade de performance da armadilha, mas antes do SQL Server 2019 este operador só é usado se você tiver um índice de coluna-store presente nos dados. O SQL Server 2019 introduz o modo batch no suporte a rowstore, para que você possa obter processamento em modo batch mesmo que não haja índices de columnstore presentes nos dados. Para demonstrar a penalidade de desempenho com o processamento em modo de linha, se você estiver executando as amostras de código neste artigo no SQL Server 2019 ou posterior, ou no Azure SQL Database, use o seguinte código para definir o nível de compatibilidade do banco de dados para 140, de modo a não ativar o modo de lote na loja de linha ainda:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 140;
Utilizar o seguinte código para ligar o tempo e as estatísticas de E/S na sessão:
SET STATISTICS TIME, IO ON;
Para evitar esperar que dois milhões de linhas sejam impressas no SSMS, sugiro executar as amostras de código nesta seção com a opção Descartar após a execução ativada (vá para Opções de Consulta, Resultados, Grade, e verifique Resultados de Descartar após a execução).
Antes de chegarmos à armadilha, considere a seguinte consulta (chame-lhe Consulta 1) que calcula o saldo da conta bancária após cada transação aplicando um total em execução usando uma função agregada de janela com uma especificação de quadro explícita:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
O plano para esta consulta, usando processamento em modo de linha, é mostrado na Figura 1.
Figure 1: Plano para a consulta 1, processamento em modo de linha
O plano puxa os dados pré-ordenados do índice agrupado da tabela. Em seguida, ele usa os operadores do Projeto Segmento e Sequência para computar os números das linhas para descobrir quais linhas pertencem ao quadro da linha atual. Em seguida, ele usa os operadores Segmento, Spool de janela e Agregado de fluxo para computar a função agregada de janela. O operador Spool de janela é usado para fazer spool das linhas do frame que precisam ser agregadas. Sem qualquer otimização especial, o plano teria que escrever por linha todas as suas linhas de frame aplicáveis para o spool, e então agregá-las. Isto teria resultado em complexidade quadrática, ou N2. A boa notícia é que, quando o frame começa com UNBOUNDED PRECEDING, o SQL Server identifica o caso como um caso de linha rápida, no qual ele simplesmente pega o total da linha anterior e adiciona o valor da linha atual para computar o total da linha atual, resultando em escalonamento linear. Neste modo de faixa rápida, o plano escreve apenas duas linhas no spool por linha de entrada – uma com o agregado, e outra com o detalhe.
O Spool Window pode ser fisicamente implementado de uma de duas maneiras. Seja como um rápido spool na memória que foi especialmente projetado para funções de janela, ou como um spool lento no disco, que é essencialmente uma tabela temporária em tempdb. Se o número de linhas que precisam ser escritas no spool por linha subjacente puder exceder 10.000, ou se o SQL Server não puder prever o número, ele usará o spool mais lento no disco. Em nosso plano de consulta, temos exatamente duas linhas escritas no spool por linha subjacente, então o SQL Server usa o spool in-memory. Infelizmente, não há como dizer a partir do plano que tipo de spool você está recebendo. Há duas maneiras de descobrir isso. Uma é usar um evento estendido chamado window_spool_ondisk_warning. Outra opção é ativar STATISTICS IO, e verificar o número de leituras lógicas relatadas para uma tabela chamada Worktable. Um número maior que zero significa que você tem o spool no disco. Zero significa que você tem o spool in-memory. Aqui estão as estatísticas de E/S para a nossa consulta:
Como pode ver, temos o spool in-memory usado. Este é geralmente o caso quando você usa a unidade de frame da janela ROWS com UNBOUNDED PRECEDING como primeiro delimitador.
Aqui estão as estatísticas de tempo para a nossa consulta:
Esta consulta demorou cerca de 4,5 segundos a completar na minha máquina com os resultados descartados.
Agora para a captura. Se você usar a opção RANGE ao invés de ROWS, com os mesmos delimitadores, pode haver uma diferença sutil no significado, mas uma grande diferença no desempenho em modo de linha. A diferença de significado só é relevante se você não tiver uma ordem total, ou seja, se você estiver ordenando por algo que não é único. A opção ROWS UNBOUNDED PRECEDING pára com a linha atual, portanto, em caso de empates, o cálculo é não-determinístico. Por outro lado, a opção PRECEDIÇÃO DE LINHA DESBASTECIDA olha para a linha atual, e inclui empates, se presentes. Ela usa lógica similar à opção TOP WITH TIES. Quando você tem a ordenação total, ou seja, você está ordenando por algo único, não há laços para incluir, e portanto ROWS e RANGE tornam-se logicamente equivalentes em tal caso. O problema é que quando você usa RANGE, o SQL Server sempre usa o spool no disco sob processamento em modo de linha, já que ao processar uma determinada linha ele não pode prever quantas mais linhas serão incluídas. Isto pode ter uma severa penalidade de desempenho.
Consulte a seguinte consulta (chame-lhe Consulta 2), que é a mesma da Consulta 1, usando apenas a opção RANGE em vez de ROWS:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
O plano para esta consulta é mostrado na Figura 2.
Figure 2: Plano para a Consulta 2, processamento em modo de linha
A Consulta 2 é logicamente equivalente à Consulta 1 porque temos ordem total; no entanto, como usa RANGE, é otimizada com o spool em disco. Observe que no plano da Query 2 o Spool da Janela parece o mesmo que no plano da Query 1, e os custos estimados são os mesmos.
Aqui estão o tempo e as estatísticas de E/S para a execução da Query 2:
Tabela ‘Worktable’ leituras lógicas: 12044701. Tabela ‘Transações’ leituras lógicas: 6208.
Nota o grande número de leituras lógicas contra a Mesa de Trabalho, indicando que você tem o spool no disco. O tempo de execução é mais de quatro vezes maior do que para a Query 1.
Se você estiver pensando que se esse for o caso, você simplesmente evitará usar a opção RANGE, a menos que você realmente precise incluir gravatas, isso é bom de se pensar. O problema é que se você usar uma função de janela que suporta um frame (agregados, FIRST_VALUE, LAST_VALUE) com uma cláusula explícita de ordem de janela, mas sem menção da unidade de frame de janela e sua extensão associada, você está recebendo RANGE UNBOUNDED PRECEDING por padrão. Este padrão é ditado pelo padrão SQL, e o padrão o escolheu porque geralmente prefere opções mais determinísticas como padrão. A seguinte consulta (chame-lhe Query 3) é um exemplo que cai nessa armadilha:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
Muitas pessoas escrevem assim assumindo que estão recebendo a PRECEDIÇÃO DE RANGE UNBOUNDED por padrão, não percebendo que estão realmente recebendo a PRECEDIÇÃO DE RANGE UNBOUNDED. O problema é que como a função usa ordem total, você obtém o mesmo resultado como com ROWS, então você não pode dizer que há um problema com o resultado. Mas os números de desempenho que você vai obter são como na Query 2. Eu vejo pessoas caindo nesta armadilha o tempo todo.
A melhor prática para evitar este problema é nos casos em que você usa uma função de janela com um frame, seja explícito sobre a unidade do frame da janela e sua extensão, e geralmente prefere ROWS. Reserve o uso de RANGE apenas para os casos em que a encomenda não é única e você precisa incluir laços.
Considere a seguinte consulta ilustrando um caso quando existe uma diferença conceptual entre ROWS e RANGE:
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS sumrows, SUM(val) OVER( ORDER BY orderdate RANGE UNBOUNDED PRECEDING ) AS sumrange FROM Sales.OrderValues ORDER BY orderdate;
Esta consulta gera a seguinte saída:
orderdate orderid val sumrows sumrange ---------- -------- -------- -------- --------- 2017-07-04 10248 440.00 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2303.40 2017-07-08 10250 1552.60 3856.00 4510.06 2017-07-08 10251 654.06 4510.06 4510.06 2017-07-09 10252 3597.90 8107.96 8107.96 ...
Observa a diferença nos resultados para as linhas onde a mesma data de ordem aparece mais de uma vez, como é o caso de 8 de julho de 2017. Observe como a opção ROWS não inclui laços e, portanto, não é determinística, e como a opção RANGE inclui laços, e portanto é sempre determinística.
É questionável se na prática você tem casos em que você ordena por algo que não é único, e você realmente precisa da inclusão de laços para fazer o cálculo determinístico. O que é provavelmente muito mais comum na prática é fazer uma de duas coisas. Uma é quebrar laços adicionando algo à ordem da janela para torná-la única e desta forma resultar em um cálculo determinístico, assim:
>
SELECT orderdate, orderid, val, SUM(val) OVER( ORDER BY orderdate, orderid ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues ORDER BY orderdate;
>
Esta consulta gera a seguinte saída:
orderdate orderid val runningsum ---------- -------- --------- ----------- 2017-07-04 10248 440.00 440.00 2017-07-05 10249 1863.40 2303.40 2017-07-08 10250 1552.60 3856.00 2017-07-08 10251 654.06 4510.06 2017-07-09 10252 3597.90 8107.96 ...
Outra opção é aplicar agrupamento preliminar, no nosso caso, por data de encomenda, assim:
SELECT orderdate, SUM(val) AS daytotal, SUM(SUM(val)) OVER( ORDER BY orderdate ROWS UNBOUNDED PRECEDING ) AS runningsum FROM Sales.OrderValues GROUP BY orderdate ORDER BY orderdate;
Esta consulta gera a seguinte saída onde cada data de pedido aparece apenas uma vez:
orderdate daytotal runningsum ---------- --------- ----------- 2017-07-04 440.00 440.00 2017-07-05 1863.40 2303.40 2017-07-08 2206.66 4510.06 2017-07-09 3597.90 8107.96 ...
De qualquer forma, lembre-se da melhor prática aqui!
A boa notícia é que se você estiver rodando no SQL Server 2016 ou posterior e tiver um índice de columnstore presente nos dados (mesmo que seja um falso índice de columnstore filtrado), ou se você estiver rodando no SQL Server 2019 ou posterior, ou no Azure SQL Database, independentemente da presença de índices de columnstore, todas as três consultas acima mencionadas são otimizadas com o operador batch-mode Window Aggregate. Com este operador, muitas das ineficiências de processamento do modo de linha são eliminadas. Este operador não utiliza nenhum spool, portanto não há problema de in-memory versus on-disk spool. Ele usa processamento mais sofisticado onde pode aplicar múltiplas passagens paralelas sobre a janela de linhas na memória tanto para ROWS como para RANGE.
Para demonstrar usando a otimização em modo batch, certifique-se de que o nível de compatibilidade da sua base de dados está definido para 150 ou superior:
ALTER DATABASE TSQLV5 SET COMPATIBILITY_LEVEL = 150;
>
Executar novamente a Query 1:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
O plano para esta consulta é mostrado na Figura 3.
Figure 3: Plano para a consulta 1, processamento em lote
Aqui estão as estatísticas de desempenho que obtive para esta consulta:
Tabela ‘Transações’ leituras lógicas: 6208.
O tempo de execução caiu para 1 segundo!
Executar a consulta 2 com a opção explícita RANGE novamente:
>
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING ) AS balance FROM dbo.Transactions;
>
O plano para esta consulta é mostrado na Figura 4.
>
Figure 2: Plano para a consulta 2, processamento em lote
Aqui estão as estatísticas de desempenho que obtive para esta consulta:
Tabela ‘Transações’ leituras lógicas: 6208.
A performance é a mesma da Query 1.
Executar a Query 3 novamente, com a opção implícita RANGE:
SELECT actid, tranid, val, SUM(val) OVER( PARTITION BY actid ORDER BY tranid ) AS balance FROM dbo.Transactions;
>
O plano e os números de performance são naturalmente os mesmos da Query 2.
Quando terminar, execute o seguinte código para desligar as estatísticas de performance:
SET STATISTICS TIME, IO OFF;
>
>>Sempre, não se esqueça de desligar a opção Descartar resultados após a execução no SSMS.
Implicitar frame com FIRST_VALUE e LAST_VALUE
As funções FIRST_VALUE e LAST_VALUE são funções de janela offset que retornam uma expressão da primeira ou última linha do frame da janela, respectivamente. A parte complicada sobre elas é que muitas vezes quando as pessoas as usam pela primeira vez, elas não percebem que suportam um frame, ao invés de pensar que elas se aplicam a toda a partição.
Consulte a seguinte tentativa de retornar informações de pedidos, mais os valores do primeiro e último pedidos do cliente:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Se acreditar incorrectamente que estas funções funcionam em toda a partição da janela, o que é a crença de muitas pessoas que utilizam estas funções pela primeira vez, espera naturalmente que o FIRST_VALUE devolva o valor da encomenda da primeira encomenda do cliente, e o LAST_VALUE devolva o valor da encomenda da última encomenda do cliente. Na prática, porém, estas funções suportam um quadro. Como um lembrete, com funções que suportam um quadro, quando você especifica a cláusula de ordem de janela mas não a unidade de quadro de janela e sua extensão associada, você obtém PRECEDIÇÃO DE LINHA DESBASTRADA por padrão. Com a função FIRST_VALUE, você obterá o resultado esperado, mas se sua consulta for otimizada com operadores de modo de linha, você pagará a penalidade de usar o spool no disco. Com a função LAST_VALUE, é ainda pior. Não apenas que você pagará a penalidade do spool no disco, mas ao invés de obter o valor da última linha na partição, você obterá o valor da linha atual!
Aqui está a saída da consulta acima:
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 814.50 1 2018-10-03 10692 878.00 814.50 878.00 1 2018-10-13 10702 330.00 814.50 330.00 1 2019-01-15 10835 845.80 814.50 845.80 1 2019-03-16 10952 471.20 814.50 471.20 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 88.80 2 2018-08-08 10625 479.75 88.80 479.75 2 2018-11-28 10759 320.00 88.80 320.00 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 403.20 3 2018-04-15 10507 749.06 403.20 749.06 3 2018-05-13 10535 1940.85 403.20 1940.85 3 2018-06-19 10573 2082.00 403.20 2082.00 3 2018-09-22 10677 813.37 403.20 813.37 3 2018-09-25 10682 375.50 403.20 375.50 3 2019-01-28 10856 660.00 403.20 660.00 ...
A maioria das vezes quando as pessoas vêem tal saída pela primeira vez, elas pensam que o SQL Server tem um bug. Mas é claro que não tem; é simplesmente o padrão SQL padrão. Há um bug na consulta. Percebendo que há um frame envolvido, você quer ser explícito sobre a especificação do frame, e usar o frame mínimo que captura a linha que você está procurando. Além disso, certifique-se de que você usa a unidade ROWS. Então, para obter a primeira linha na partição, use a função FIRST_VALUE com o frame ROWS ENTRE A PRECEDÊNCIA DESBLOQUEADA E A CORRENTE ROW. Para obter a última linha na partição, utilize a função LAST_VALUE com o quadro ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
Aqui está a nossa consulta revista com o bug corrigido:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS firstval, LAST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS lastval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
>
Desta vez você obtém o resultado correto:
custid orderdate orderid val firstval lastval ------- ---------- -------- ---------- ---------- ---------- 1 2018-08-25 10643 814.50 814.50 933.50 1 2018-10-03 10692 878.00 814.50 933.50 1 2018-10-13 10702 330.00 814.50 933.50 1 2019-01-15 10835 845.80 814.50 933.50 1 2019-03-16 10952 471.20 814.50 933.50 1 2019-04-09 11011 933.50 814.50 933.50 2 2017-09-18 10308 88.80 88.80 514.40 2 2018-08-08 10625 479.75 88.80 514.40 2 2018-11-28 10759 320.00 88.80 514.40 2 2019-03-04 10926 514.40 88.80 514.40 3 2017-11-27 10365 403.20 403.20 660.00 3 2018-04-15 10507 749.06 403.20 660.00 3 2018-05-13 10535 1940.85 403.20 660.00 3 2018-06-19 10573 2082.00 403.20 660.00 3 2018-09-22 10677 813.37 403.20 660.00 3 2018-09-25 10682 375.50 403.20 660.00 3 2019-01-28 10856 660.00 403.20 660.00 ...
Uma pessoa se pergunta qual foi a motivação para o padrão suportar até mesmo um frame com estas funções. Se você pensar sobre isso, você irá usá-las principalmente para obter algo da primeira ou última linha na partição. Se você precisa do valor de, digamos, duas linhas antes da corrente, ao invés de usar FIRST_VALUE com um frame que começa com 2 PRECEDING, não é muito mais fácil usar LAG com um offset explícito de 2, assim:
SELECT custid, orderdate, orderid, val, LAG(val, 2) OVER( PARTITION BY custid ORDER BY orderdate, orderid ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Esta consulta gera a seguinte saída:
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 NULL 1 2018-10-03 10692 878.00 NULL 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 NULL 2 2018-08-08 10625 479.75 NULL 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 NULL 3 2018-04-15 10507 749.06 NULL 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
Aparentemente, existe uma diferença semântica entre o uso da função LAG acima e o FIRST_VALUE com um frame que começa com 2 PRECEDING. Com o primeiro, se não existir uma linha no offset desejado, você recebe um NULL por padrão. Com o último, você ainda obtém o valor da primeira linha que está presente, ou seja, o valor da primeira linha na partição. Considere a seguinte consulta:
SELECT custid, orderdate, orderid, val, FIRST_VALUE(val) OVER( PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS prevtwoval FROM Sales.OrderValues ORDER BY custid, orderdate, orderid;
Esta consulta gera a seguinte saída:
custid orderdate orderid val prevtwoval ------- ---------- -------- ---------- ----------- 1 2018-08-25 10643 814.50 814.50 1 2018-10-03 10692 878.00 814.50 1 2018-10-13 10702 330.00 814.50 1 2019-01-15 10835 845.80 878.00 1 2019-03-16 10952 471.20 330.00 1 2019-04-09 11011 933.50 845.80 2 2017-09-18 10308 88.80 88.80 2 2018-08-08 10625 479.75 88.80 2 2018-11-28 10759 320.00 88.80 2 2019-03-04 10926 514.40 479.75 3 2017-11-27 10365 403.20 403.20 3 2018-04-15 10507 749.06 403.20 3 2018-05-13 10535 1940.85 403.20 3 2018-06-19 10573 2082.00 749.06 3 2018-09-22 10677 813.37 1940.85 3 2018-09-25 10682 375.50 2082.00 3 2019-01-28 10856 660.00 813.37 ...
Observar que desta vez não existem NULLs na saída. Então há algum valor em suportar um frame com FIRST_VALUE e LAST_VALUE. Apenas certifique-se de que você se lembra da melhor prática para ser sempre explícito sobre a especificação do frame com estas funções, e para usar a opção ROWS com o frame mínimo que contém a linha que você está depois.
Conclusion
Este artigo focou em bugs, armadilhas e melhores práticas relacionadas às funções de janela. Lembre-se que ambas as funções de agregação de janelas e as funções de compensação de janelas FIRST_VALUE e LAST_VALUE suportam um frame, e que se você especificar a cláusula de ordem de janela mas não especificar a unidade de frame de janela e sua extensão associada, você está recebendo RANGE UNBOUNDED PRECEDING por padrão. Isto incorre em uma penalidade de desempenho quando a consulta é otimizada com operadores de modo de linha. Com a função LAST_VALUE isto resulta em obter os valores da linha atual em vez da última linha na partição. Lembre-se de ser explícito sobre o frame e geralmente preferir a opção ROWS ao RANGE. É ótimo ver as melhorias de desempenho com o operador batch-mode Window Aggregate. Quando aplicável, pelo menos a armadilha de desempenho é eliminada.