获取时间序列中每个条目对应的当日后续最大Bid及其时间戳

我有一个名为optiontracking的表，包含ConID、TimeLog、Ask、Bid字段，数据如下：

需求：针对表中每一条ConID/TimeLog/Ask记录，获取同一ConID、同一日期下，该TimeLog时间点之后出现的最大Bid值（命名为SellPrice），以及该最大Bid对应的TimeLog（命名为SellPriceTime）。后续还需要针对Max(Ask)执行类似操作，但认为需要为每组数据单独编写子查询。

目前已写出能提取每个Ask记录对应TimeLog之后最大Bid值的SQL，但无法同时获取对应时间戳，且因数据量大，希望避免使用第二个子查询。当前SQL如下：

SELECT DISTINCT OT.[ConID], OT.[TimeLog], OT.[Ask], MAX(MaxBid.[Bid]) as SellPrice from 
(
SELECT * from algotrading.[optiontracking] WHERE YEAR([TimeLog]) <= 2020 and Month([TimeLog]) <= 6
) as OT

LEFT JOIN 
(
SELECT [ConID], [Bid], [TimeLog], CONVERT(date, [TimeLog]) as CombDate from algotrading.[optiontracking] WHERE YEAR([TimeLog]) <= 2020 and Month([TimeLog]) <= 6
) as MaxBid 
on OT.[ConID] = MaxBid.[ConID] and MaxBid.[CombDate] = CONVERT(date, OT.[TimeLog]) and DATEDIFF(second, OT.[TimeLog], MaxBid.[TimeLog]) > 0

GROUP BY OT.[ConID], OT.[TimeLog], OT.[Ask], CONVERT(date, OT.[TimeLog])

查询中的年月过滤是为了测试，因为数据集过大。预期输出结果如下：

可以使用窗口函数ROW_NUMBER()来实现，避免多次子查询，同时高效获取最大Bid及对应时间戳。以下是优化后的SQL：

WITH filtered_data AS (
    SELECT 
        ConID,
        TimeLog,
        Ask,
        Bid,
        CONVERT(date, TimeLog) AS LogDate
    FROM algotrading.optiontracking
    WHERE YEAR(TimeLog) <= 2020 AND MONTH(TimeLog) <= 6
),
ranked_bids AS (
    SELECT 
        fd1.ConID,
        fd1.TimeLog AS OriginalTimeLog,
        fd1.Ask,
        fd2.Bid,
        fd2.TimeLog AS BidTimeLog,
        ROW_NUMBER() OVER (
            PARTITION BY fd1.ConID, fd1.LogDate, fd1.TimeLog 
            ORDER BY fd2.Bid DESC, fd2.TimeLog ASC
        ) AS rn
    FROM filtered_data fd1
    LEFT JOIN filtered_data fd2
        ON fd1.ConID = fd2.ConID
        AND fd1.LogDate = fd2.LogDate
        AND fd2.TimeLog > fd1.TimeLog
)
SELECT 
    ConID,
    OriginalTimeLog AS TimeLog,
    Ask,
    CASE WHEN rn = 1 THEN Bid ELSE NULL END AS SellPrice,
    CASE WHEN rn = 1 THEN BidTimeLog ELSE NULL END AS SellPriceTime
FROM ranked_bids
WHERE rn = 1 OR rn IS NULL
ORDER BY ConID, OriginalTimeLog;

说明 #

1. filtered_data CTE：先过滤出需要的时间范围数据，统一计算日期，避免重复转换。
2. ranked_bids CTE：将每条记录与同ConID、同日期且时间更晚的记录关联，用ROW_NUMBER()按Bid降序、时间升序排序，这样每个原始记录对应的最大Bid会排在第一位（rn=1）。
3. 最终查询：只取rn=1的记录，得到对应最大Bid和时间戳，没有后续数据则显示NULL。

这种方法只需扫描两次数据集（CTE的复用会优化执行计划），比多次子查询更高效，适合大数据量场景。后续针对Max(Ask)的类似操作，只需修改ORDER BY的字段为Ask即可，逻辑完全复用。

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