Você já precisou escrever uma consulta que acessa dados de linhas subsequentes juntamente com os dados da linha atual? Neste artigo, discutiremos diferentes maneiras de escrever esse tipo de consulta e examinaremos especificamente as funções analíticas LEAD e LAG no SQL Server.

Antes do SQL Server 2012, era possível usar a Expressão de Tabela Comum (CTE) juntamente com a função de classificação ROW_NUMBER para acessar linhas subsequentes no mesmo conjunto de resultados. No entanto, o SQL Server 2012 introduziu as funções LEAD e LAG, que simplificam esse processo.

Vamos começar criando uma tabela e carregando alguns dados de exemplo:

DECLARE @CustomerPlan TABLE (
    CustomerCode VARCHAR(10),
    PlanCode VARCHAR(10),
    StartDate DATE
)

INSERT INTO @CustomerPlan VALUES ('C00001', 'P00001', '1-Sep-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00002', '1-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00003', '10-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00001', 'P00004', '25-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00002', 'P00001', '1-Oct-2014')
INSERT INTO @CustomerPlan VALUES ('C00002', 'P00002', '1-Nov-2014')

SELECT * FROM @CustomerPlan;

Anteriormente, era possível usar uma CTE e a função ROW_NUMBER para acessar linhas subsequentes. Por exemplo, para encontrar a data de expiração do plano atual com base na data de ativação do próximo plano, você poderia usar a seguinte consulta:

WITH CTE AS (
    SELECT 
        RN = ROW_NUMBER() OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC),
        *
    FROM @CustomerPlan
)
SELECT
    [Current Row].*,
    ISNULL(DATEADD(DAY, -1, [Next Row].StartDate), '31-Dec-2099') AS EndDate
FROM CTE [Current Row]
LEFT JOIN CTE [Next Row] ON [Current Row].CustomerCode = [Next Row].CustomerCode AND [Next Row].RN = [Current Row].RN + 1
ORDER BY [Current Row].CustomerCode, [Current Row].RN;

No entanto, com a introdução da função LEAD no SQL Server 2012, você pode obter o mesmo resultado de maneira mais simplificada:

SELECT 
    *,
    DATEADD(DAY, -1, LEAD(StartDate, 1, '01-Jan-2100') OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC)) AS EndDate
FROM @CustomerPlan;

A função LEAD permite acessar a linha subsequente (ou colunas da linha subsequente) sem usar autojunção, CTE ou função de classificação. Ela possui três parâmetros principais:

1. O primeiro parâmetro especifica uma expressão escalar ou nome de coluna cujo valor da linha subsequente deve ser retornado.
2. O segundo parâmetro especifica um deslocamento para acessar qualquer linha após a linha atual. O valor padrão de 1 acessa a próxima linha imediata.
3. O terceiro parâmetro especifica o valor padrão a ser retornado caso o valor retornado seja NULL.

Da mesma forma, a função LAG permite acessar linhas anteriores no mesmo conjunto de resultados:

SELECT 
    CustomerCode,
    PlanCode AS CurrentPlanCode,
    LAG(PlanCode, 1, 'NA') OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC) AS LastPlan
FROM @CustomerPlan;

A função LAG possui os mesmos três parâmetros da função LEAD e funciona da mesma maneira. Ela permite especificar o deslocamento físico que vem antes da linha atual.

Tanto as funções LEAD quanto LAG são flexíveis e permitem especificar o número de linhas para mover para trás ou para frente a partir da linha atual com o parâmetro de deslocamento.

Em conclusão, as funções LEAD e LAG no SQL Server fornecem uma maneira simplificada de acessar dados de linhas anteriores ou subsequentes sem a necessidade de autojunção, CTE ou função de classificação. Essas funções podem melhorar significativamente suas capacidades de consulta e a eficiência do seu código.