获取T-SQL中所有最新价格的价格变化

Question

我的任务是编写一个允许用户搜索价格表的应用程序，其中价格在3个不同的键上唯一，例如状态，出版商，和型（可能有任何数量的与任何3个字段的相同的密钥值的行，但仅存在一个行与状态 =“俄亥俄”，出版商 =” Bob'，并且类型 ='硅'）。当用户选择状态和出版商，他们提出的所有类型与状态和出版商的列表。我运行一个存储过程来提取这些项目，并且我拉取最近的价格，但是我也需要拉第二个最近的价格，并进行数学计算，以便将价格变化显示给用户。目前，我创建了以下函数，但是它会使我的存储过程减慢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