基于price/trans表自动获取最新价格计算库存估值的SQL优化需求

我们有两张业务表：price表存储每日产品价格，trans表存储库存数据。核心需求是计算各可用库存的最新价值，即把库存可用数量乘以对应产品的最新有效价格。

当前使用的SQL语句需要手动调整DATEADD的天数（比如周末要把-1改成-3来获取周五的价格），无法自动适配无价格数据的日期，现需要优化为能自动获取最新可用价格的方案。

price表结构与示例数据 #

trans表为库存数据，期望结果是各库存记录匹配对应产品最新价格后计算出的估值。

现有SQL代码 #

select 
  a.party,
  a.product,
  a.accountNo,
  round(sum(a.net_invest),0) as TAmount,
  DATEADD(day, -1, convert(date, GETDATE(),103)) as PriceDate, 
  round(sum(a.Net_Units),3) as TUnits, 
  round(sum(a.Net_Units * b.nav),0) as Valuation
from Trans a, price b
where a.product = b.product 
  and b.TradingDate = DATEADD(day, -1, convert(date, GETDATE(),103))
group by a.party, a.product, a.accountNo
order by a.party, a.product

优化方案 #

方案1：窗口函数获取每个产品的最新价格 #

通过ROW_NUMBER()窗口函数按产品分组、日期倒序排序，直接提取每个产品的最新价格记录，再与库存表关联：

WITH LatestPrice AS (
    SELECT 
        product,
        price,
        TradingDate,
        ROW_NUMBER() OVER (PARTITION BY product ORDER BY TradingDate DESC) AS rn
    FROM price
    WHERE TradingDate <= CONVERT(date, GETDATE(), 103) -- 仅筛选当前及之前的价格
)
SELECT 
    a.party,
    a.product,
    a.accountNo,
    ROUND(SUM(a.net_invest), 0) AS TAmount,
    lp.TradingDate AS PriceDate,
    ROUND(SUM(a.Net_Units), 3) AS TUnits,
    ROUND(SUM(a.Net_Units * lp.price), 0) AS Valuation
FROM Trans a
JOIN LatestPrice lp ON a.product = lp.product AND lp.rn = 1
GROUP BY a.party, a.product, a.accountNo, lp.TradingDate
ORDER BY a.party, a.product

方案2：关联子查询获取最新价格 #

如果数据库不支持CTE，可以用关联子查询直接定位每个产品的最新有效价格日期，再匹配对应价格：

SELECT 
    a.party,
    a.product,
    a.accountNo,
    ROUND(SUM(a.net_invest), 0) AS TAmount,
    lp.TradingDate AS PriceDate,
    ROUND(SUM(a.Net_Units), 3) AS TUnits,
    ROUND(SUM(a.Net_Units * lp.price), 0) AS Valuation
FROM Trans a
JOIN (
    SELECT p1.product, p1.price, p1.TradingDate
    FROM price p1
    WHERE p1.TradingDate = (
        SELECT MAX(p2.TradingDate)
        FROM price p2
        WHERE p2.product = p1.product AND p2.TradingDate <= CONVERT(date, GETDATE(), 103)
    )
) lp ON a.product = lp.product
GROUP BY a.party, a.product, a.accountNo, lp.TradingDate
ORDER BY a.party, a.product

方案说明 #

• 两种方案均无需手动调整日期偏移量，会自动筛选每个产品小于等于当前日期的最新价格记录，完美适配周末、节假日无价格数据的场景。
• 窗口函数方案（方案1）性能更优，数据量较大时建议优先使用。
• 建议在price表的product和TradingDate字段上建立联合索引，可进一步提升查询效率。

内容的提问来源于stack exchange，提问作者Vinay