Получить изменение цены для всех самых последних цен в T-SQL

Question

Мне было поручено написать приложение, которое позволит пользователю выполнять поиск в таблице цен, где цены уникальны по трем различным ключам, скажем, состояние, издатель и тип (может быть любое количество строк с одинаковым значением ключа для любого из 3 полей, но есть только одна строка с указанием state= 'Ohio', publisher= 'Bob' и type= 'silic'). Когда пользователь выбирает состояние и издателя, ему представляется список всех типов с этим состоянием и издателем. Я запускаю хранимую процедуру, чтобы вытащить эти предметы, и я вытягиваю самую последнюю цену, но мне также нужно вытащить вторую самую последнюю цену и выполнить математику, чтобы отобразить изменение цены для пользователя. В настоящее время я создал следующую функцию, но она замедляет мою хранимую процедуру от 1 до 40 секунд, в зависимости от настроения сервера при выполнении.

BEGIN
    -- Declare the return variable here
    DECLARE @priceChange float
    DECLARE @currentPriceDate date
    DECLARE @currentPrice float
    DECLARE @previousPrice float

    -- Add the T-SQL statements to compute the return value here
    SELECT TOP 1 @currentPriceDate=PriceDate ,@CurrentPrice=MarketPrice
        FROM MarketPrice_Table
            LEFT JOIN PriceEffectiveDate_Table ON MarketPrice_Table.PriceDate = PriceEffectiveDate_Table.EffectiveDate
                AND MarketPrice_Table.PublisherID = PriceEffectiveDate_Table.PublisherID
        WHERE TypeID = @TypeID
        AND MarketPrice_Table.PublisherID = @PublisherID
        AND MarketPrice_Table.StateID = @StateID
        ORDER BY PriceDate DESC;

    SET @previousPrice = (SELECT TOP 1 MarketPrice 
        FROM MarketPrice_Table
            LEFT JOIN PriceEffectiveDate_Table ON MarketPrice_Table.PriceDate = PriceEffectiveDate_Table.EffectiveDate
                AND MarketPrice_Table.PublisherID = PriceEffectiveDate_Table.PublisherID
        WHERE TypeID = @TypeID
        AND MarketPrice_Table.PublisherID = @PublisherID
        AND MarketPrice_Table.StateID = @StateID
        AND MarketPrice_Table.PriceDate <> @currentPriceDate
        ORDER BY PriceDate DESC);

    SET @priceChange = @currentPrice - @previousPrice;

    -- Return the result of the function
    RETURN @priceChange

END

Есть ли более эффективный способ сделать это, поэтому я не делаю два запроса на строку в хранимой процедуре?

Заранее благодарю за любую помощь, и дайте мне знать, если я могу уточнить что-нибудь еще!

Answer 1

Попробуйте использовать аналитическую функцию LEAD и использовать ее для возврата данных в таблицу. Я прошу прощения, если это не точно, но с некоторыми изменениями я уверен, что это даст вам то, что вы ищете.

Пытаться:

DECLARE @priceChange float
DECLARE @currentPriceDate date
DECLARE @currentPrice float
DECLARE @previousPrice FLOAT

SELECT
    *
FROM
    (   
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY MarketPrice_Table.StateID, MarketPrice_Table.PublisherID, TypeID ORDER BY PriceDate DESC) AS RowNum,
            MarketPrice_Table.StateID,
            MarketPrice_Table.PublisherID,
            TypeID,
            PriceDate, 
            MarketPrice AS CurrentPrice,
            LEAD(MarketPrice) OVER (PARTITION BY MarketPrice_Table.StateID, MarketPrice_Table.PublisherID, TypeID ORDER BY PriceDate DESC) AS PreviousPrice,
            MarketPrice - ISNULL(LEAD(MarketPrice) OVER (PARTITION BY MarketPrice_Table.StateID, MarketPrice_Table.PublisherID, TypeID ORDER BY PriceDate DESC), 0) AS PriceChange
        FROM 
            MarketPrice_Table
            LEFT JOIN PriceEffectiveDate_Table 
                ON MarketPrice_Table.PriceDate = PriceEffectiveDate_Table.EffectiveDate
                AND MarketPrice_Table.PublisherID = PriceEffectiveDate_Table.PublisherID
        WHERE 
            TypeID = @TypeID AND 
            MarketPrice_Table.PublisherID = @PublisherID AND 
            MarketPrice_Table.StateID = @StateID
    ) r
WHERE
    r.RowNum = 1

Answer 2

Попробуйте это пожалуйста:

BEGIN
-- Declare the return variable here
DECLARE @priceChange float
DECLARE @currentPriceDate varchar(8)
DECLARE @currentPrice float
DECLARE @previousPrice float

-- Add the T-SQL statements to compute the return value here
SELECT TOP 1 @currentPriceDate=Convert(varchar,PriceDate,112) ,@CurrentPrice=MarketPrice
    FROM MarketPrice_Table
        LEFT JOIN PriceEffectiveDate_Table ON Convert(varchar,MarketPrice_Table.PriceDate,112) = Convert(varchar,PriceEffectiveDate_Table.EffectiveDate,112)
            AND MarketPrice_Table.PublisherID = PriceEffectiveDate_Table.PublisherID
    WHERE TypeID = @TypeID
    AND MarketPrice_Table.PublisherID = @PublisherID
    AND MarketPrice_Table.StateID = @StateID
    ORDER BY PriceDate DESC;

SET @previousPrice = (SELECT TOP 1 MarketPrice 
    FROM MarketPrice_Table
        LEFT JOIN PriceEffectiveDate_Table ON Convert(varchar,MarketPrice_Table.PriceDate,112) = Convert(varchar,PriceEffectiveDate_Table.EffectiveDate)
            AND MarketPrice_Table.PublisherID = PriceEffectiveDate_Table.PublisherID
    WHERE TypeID = @TypeID
    AND MarketPrice_Table.PublisherID = @PublisherID
    AND MarketPrice_Table.StateID = @StateID
    AND Convert(varchar,MarketPrice_Table.PriceDate,112) <> @currentPriceDate
    ORDER BY PriceDate DESC);

SET @priceChange = @currentPrice - @previousPrice;

-- Return the result of the function
RETURN @priceChange

END