基于生效日期从对应列获取价格并计算金额的技术实现需求

问题场景 #

需要根据交易记录（Table 2）中的日期，从价格表（Table 1）中获取对应部件的生效价格（即不晚于交易日期的最新价格），再通过「价格 × 数量」计算出交易金额。

数据示例 #

Table 1（价格表）

Table 2（交易表）

预期输出 #

解决方案 #

1. Excel/Google Sheets 实现 #

获取生效价格

在交易表的Price列（比如D2单元格）输入以下公式，下拉填充即可匹配对应部件的生效价格：

=LOOKUP(2,1/(Table1[Part]=A2)/(Table1[Date]<=B2),Table1[Price])

公式逻辑：通过(Table1[Part]=A2)/(Table1[Date]<=B2)筛选出符合部件且日期不晚于交易日期的记录，LOOKUP(2,1/...)会取最后一条符合条件的价格（即最新生效价格）。

计算金额

在Amount列（比如E2单元格）输入公式：

=C2*D2

2. SQL 实现 #

使用窗口函数ROW_NUMBER()筛选每个交易记录对应的最新生效价格，再计算金额：

WITH ranked_prices AS (
    SELECT 
        t.Part,
        t.Date,
        t.Quantity,
        p.Price,
        -- 按部件和交易日期分组，价格日期倒序排序，取最新的一条
        ROW_NUMBER() OVER (PARTITION BY t.Part, t.Date ORDER BY p.Date DESC) AS rn
    FROM Table2 t
    -- 关联价格表，筛选出部件相同且价格日期不晚于交易日期的记录
    JOIN Table1 p ON t.Part = p.Part AND p.Date <= t.Date
)
SELECT 
    Part,
    Date,
    Quantity,
    Price,
    Quantity * Price AS Amount
FROM ranked_prices
WHERE rn = 1;

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