SQL Server中如何获取滚动最大值对应的session_date？

我有一个SQL Server表，包含日期字段session_date和数值字段price，原始数据如下：

我需要计算最近3行（按日期排序）的滚动最大值，同时获取每个最大值对应的session_date。注：原期望结果中的price值与原始数据存在矛盾，以下是基于原始数据修正后的期望结果：

我已经写出了计算滚动最大值的查询，现在需要添加对应的session_date字段：

select session_date
, price
, max(price) over (order by session_date rows between 3 preceding and current row) as rolling_max
from my_table

要获取滚动窗口内最大值对应的日期，可以使用FIRST_VALUE窗口函数，在窗口内部先按价格降序、日期降序排序，这样就能拿到最大值对应的最新日期：

SELECT 
    session_date,
    price,
    MAX(price) OVER (ORDER BY session_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_max,
    FIRST_VALUE(session_date) OVER (
        ORDER BY session_date 
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        ORDER BY price DESC, session_date DESC
    ) AS rolling_last_date
FROM my_table
ORDER BY session_date DESC;

关键说明 #

• 窗口范围和计算滚动最大值的范围完全一致：ROWS BETWEEN 3 PRECEDING AND CURRENT ROW，确保两者基于同一个最近3行的数据集。
• FIRST_VALUE(session_date)在窗口内先按price DESC排序（优先取价格最高的记录），如果存在多个相同最大值，再按session_date DESC取最新的日期，最终返回排序后的第一个记录的日期。
• 如果需要在价格相同时取最早的日期，只需把session_date DESC改为session_date ASC即可。

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