TSQL Query to reference previous rows

问题:

I need help with a query. I have a query that is returning a simple set of rows.

SELECT      TOP 5 
            ReceivedYTD, 
            InvoicedYTD, 
            OrderedYTD, 
            YearReported, 
            WeekReported 
FROM        Products 
WHERE       ProductId = @ProductId
ORDER BY    YearReported DESC, 
            WeekReported DESC

The week and year reported columns are ordered by could have gaps (e.g. 1,2,4,5,6,9...)

What I would like to do is also select for the change in the values from the previous record in the order sequence. So on Week 5, I could see how it changed from week 4, and on week 4 I would see how it changed form week 2, etc. Because of the possible break in the sequence, I can't simply do a recursive left join (or at least, I don't think I can.)

回答1:

For SQL Server 2012+:

You can use the LAG function.

Here's an example to find the previous received value, then of course the others would be pretty well the same.

SELECT      TOP 5 
            ReceivedYTD, 
            InvoicedYTD, 
            OrderedYTD, 
            YearReported, 
            WeekReported,

-- Relevant part
            LAG(ReceivedYTD)
                OVER (ORDER BY YearReported DESC, WeekReported DESC) AS PreviousReceivedYTD
-- End relevant part

FROM        Products 
WHERE       ProductId = @ProductId
ORDER BY    YearReported DESC, 
            WeekReported DESC

Otherwise

I was already well into this answer before you commented back, so I just went with it.

You have to use a CTE, and join it against itself by the row number offset that you're looking for.

WITH CTE AS (
    SELECT      TOP 5 
                RowNumber = ROW_NUMBER() OVER (ORDER BY YearReported DESC, WeekReported DESC)

                ReceivedYTD, 
                InvoicedYTD, 
                OrderedYTD, 
                YearReported, 
                WeekReported,
    FROM        Products 
    WHERE       ProductId = @ProductId
    ORDER BY    YearReported DESC, 
                WeekReported DESC
)

SELECT   CTE.ReceivedYTD, 
         CTE.InvoicedYTD, 
         CTE.OrderedYTD, 
         CTE.YearReported,
         CTE.WeekReported,
         PreviousRow.ReceivedYTD AS PreviousReceivedYTD
FROM CTE
    LEFT OUTER JOIN CTE PreviousRow ON CTE.RowNumber - 1 = PreviousRow.RowNumber

回答2:

You can use lag since you are on 2012:

SELECT      TOP 5 
            ReceivedYTD, 
            LAG(ReceivedYTD, 1, 'NA') OVER (
                PARTITION BY ProductId ORDER BY YearReported DESC, WeekReported DESC) AS RecievedYTD_Last,
            InvoicedYTD, 
            LAG(InvoicedYTD, 1, 'NA') OVER (
                PARTITION BY ProductId ORDER BY YearReported DESC, WeekReported DESC) AS InvoicedYTD_Last,
            OrderedYTD, 
            LAG(OrderedYTD, 1, 'NA') OVER (
                PARTITION BY ProductId ORDER BY YearReported DESC, WeekReported DESC) AS OrderedYTD_Last,
            YearReported, 
            WeekReported,
FROM        Products P
WHERE       ProductId = @ProductId
ORDER BY    YearReported DESC, 
            WeekReported DESC