sql-server - 获取给定日期之前的最大日期的有效方法

Question

假设我有一个名为 Transaction 的表和另一个名为 Price 的表。 Price 保存给定基金在不同日期的价格。每只基金都会在不同的日期添加价格，但不会在所有可能的日期都有价格。因此，对于基金 XYZ，我可能有 5 月 1 日、5 月 7 日和 5 月 13 日的价格，而基金 ABC 可能有 5 月 3 日、5 月 9 日和 5 月 11 日的价格。

现在我正在查看基金在交易之日的现行价格。该交易是针对 XYZ 基金于 5 月 10 日进行的。我想要的是当天最新的已知价格，即 5 月 7 日的价格。

代码如下:

select d.TransactionID, d.FundCode, d.TransactionDate, v.OfferPrice
from Transaction d
    inner join Price v
        on v.FundCode = d.FundCode
        and v.PriceDate = (
            select max(PriceDate)
            from Price
            where FundCode = v.FundCode
            /* */ and PriceDate < d.TransactionDate 
        )

它可以工作，但速度非常慢(在实际使用中需要几分钟)。如果我删除带有前导注释的行，查询会非常快(2 秒左右)，但随后它会使用每个基金的最新价格，这是错误的。

不好的是，与我们使用的其他一些表相比，价格表很小，而且我不清楚为什么它这么慢。我怀疑有问题的行强制 SQL Server 处理笛卡尔积，但我不知道如何避免它。

我一直希望找到一种更有效的方法来做到这一点，但到目前为止我还没有想到。有什么想法吗？

Answer 1

您没有指定您正在使用的 SQL Server 版本，但如果您使用的是支持排名函数和 CTE 查询的版本，我想您会发现这比使用相关子查询的性能要高得多在您的 join 声明中。

它的性能应该与 Andriy 的查询非常相似。根据表的确切索引拓扑，一种方法可能比另一种方法稍快。

我倾向于喜欢基于 CTE 的方法，因为生成的代码更具可读性(在我看来)。希望这有帮助!

;WITH set_gen (TransactionID, OfferPrice, Match_val)
AS
(
    SELECT d.TransactionID, v.OfferPrice, ROW_NUMBER() OVER(PARTITION BY d.TransactionID ORDER BY v.PriceDate ASC) AS Match_val
    FROM Transaction d
        INNER JOIN Price v
            ON v.FundCode = d.FundCode
    WHERE v.PriceDate <= d.TransactionDate
)
SELECT sg.TransactionID, d.FundCode, d.TransactionDate, sg.OfferPrice
FROM Transaction d
    INNER JOIN set_gen sg ON d.TransactionID = sg.TransactionID
WHERE sg.Match_val = 1