SQL Server 2008中的Lead()和LAG()功能 [英] Lead() and LAG() functionality in SQL Server 2008

问题描述

希望所有的SQL GURUS都表现出色:)

Hope all the SQL GURUS out there are doing great :)

我正在尝试在SQL Server 2008中模拟LEAD()和LAG()功能.

I am trying to simulate LEAD() and LAG() functionality in SQL Server 2008.

这是我的情况:我有一个临时表，该表使用基本查询以及里程的业务逻辑进行填充.我想计算每天每个用户的累积里程.

This is my scenario: I have a temp table which is populated using the base query with the business logic for mileage. I want to calculate accumulated mileage for each user per day.

温度表是使用ROW_NUMBER()设置的，所以除了累积的里程以外，我已经拥有了温度表中所有需要的数据.

The temp table is setup using ROW_NUMBER(), so I have all the data needed in the temp table except the accumulated mileage.

我尝试将CTE与基本查询一起使用，并与自身进行自我连接，但无法正常工作.我要附上相同的屏幕截图.

I have tried using a CTE with the base query and self joining with itself and couldn't get it working. I am attaching the screen shot for the same.

任何帮助/建议将不胜感激.

Any help/suggestion would be appreciated.

推荐答案

通过将表自身连接到正确的轨道上.我在下面包括了两种执行此操作的方法，在这里应该可以正常工作.第一个窍门在您的ROW_NUMBER中，请确保按用户ID进行分区并按日期进行排序.然后，您可以使用具有聚合功能的INNER JOIN或CROSS APPLY来构建运行总计.

You are on the right track by joining the table to itself. I included 2 methods of doing this below that should work fine here. The first trick is in your ROW_NUMBER, be sure to partition by the user id and sort by the date. Then you can use either an INNER JOIN with aggregation or CROSS APPLY to build your running totals.

使用分区的ROW_NUMBER()设置数据:

Setting up the data with the partitioned ROW_NUMBER():

DECLARE @Data TABLE (
    RowNum INT,
    UserId INT,
    Date DATE,
    Miles INT
)
INSERT @Data 
    SELECT
        ROW_NUMBER() OVER (PARTITION BY UserId
            ORDER BY Date) AS RowNum,
        *
    FROM (
        SELECT 1, '2015-01-01', 5
        UNION ALL SELECT 1, '2015-01-02', 6
        UNION ALL SELECT 2, '2015-01-01', 7
        UNION ALL SELECT 2, '2015-01-02', 3
        UNION ALL SELECT 2, '2015-01-03', 2
        ) T (UserId, Date, Miles)

使用INNER JOIN进行聚合

Use INNER JOIN with Aggregation

SELECT
    D1.UserId,
    D1.Date,
    D1.Miles,
    SUM(D2.Miles) AS [Total]
FROM @Data D1
    INNER JOIN @Data D2
        ON D1.UserId = D2.UserId
            AND D2.RowNum <= D1.RowNum
GROUP BY
    D1.UserId,
    D1.Date,
    D1.Miles

使用CROSS APPLY作为运行总计

Use CROSS APPLY for the running total

SELECT
    UserId,
    Date,
    Miles,
    Total
FROM @Data D1
    CROSS APPLY (
        SELECT SUM(Miles) AS Total
        FROM @Data
        WHERE UserId = D1.UserId
            AND RowNum <= D1.RowNum
    ) RunningTotal

每种方法的输出均相同:

UserId      Date       Miles       Total
----------- ---------- ----------- -----------
1           2015-01-01 5           5
1           2015-01-02 6           11
2           2015-01-01 7           7
2           2015-01-02 3           10
2           2015-01-03 2           12

这篇关于SQL Server 2008中的Lead()和LAG()功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！