如何基于股票价格表在SQL Server中实现滚动排名？

嘿，我来帮你搞定这个滚动排名的需求！首先咱们先把测试数据补全，确保能正常运行验证：

DECLARE @table TABLE (MyDate DATE, Ticker VarChar(6), Price Decimal (6,2))
INSERT INTO @Table VALUES 
('2013-01-01' , 'ABC' , 100.00),
('2013-01-02' , 'ABC' , 101.50),
('2013-01-03' , 'ABC' , 99.80),
('2013-01-04' , 'ABC' , 95.50),
('2013-01-05' , 'ABC' , 78.00),
('2013-01-01' , 'JKL' , 34.57),
('2013-01-02' , 'JKL' , 33.99),
('2013-01-03' , 'JKL' , 31.85),
('2013-01-04' , 'JKL' , 30.11),
('2013-01-05' , 'JKL' , 45.00),
('2013-01-01' , 'XYZ' , 11.50),
('2013-01-02' , 'XYZ' , 12.20),
('2013-01-03' , 'XYZ' , 10.80),
('2013-01-04' , 'XYZ' , 13.10),
('2013-01-05' , 'XYZ' , 14.50)

下面分两种常见的滚动排名场景来实现，你可以根据实际需求选择：

场景1：单只股票自身的滚动窗口排名（最近N天内价格排名） #

如果你需要针对每只股票，计算当天及前N天内自身价格的排名（比如最近3天的价格高低排名），可以用SQL Server的窗口函数结合滑动窗口定义：

DECLARE @window_days INT = 2; -- 前2天，加上当天共3天的滚动窗口

SELECT 
    MyDate,
    Ticker,
    Price,
    -- 滚动窗口内价格从高到低的排名（相同价格会有相同排名，后续排名跳过）
    RANK() OVER (
        PARTITION BY Ticker 
        ORDER BY Price DESC 
        ROWS BETWEEN @window_days PRECEDING AND CURRENT ROW
    ) AS Rolling_3d_Rank,
    -- 滚动窗口内的密集排名（相同价格排名相同，后续排名不跳过）
    DENSE_RANK() OVER (
        PARTITION BY Ticker 
        ORDER BY Price DESC 
        ROWS BETWEEN @window_days PRECEDING AND CURRENT ROW
    ) AS Rolling_3d_Dense_Rank
FROM @table
ORDER BY Ticker, MyDate;

代码说明： #

• PARTITION BY Ticker：按股票代码分组，确保只计算单只股票的内部排名
• ROWS BETWEEN @window_days PRECEDING AND CURRENT ROW：定义滚动窗口范围，即当前行（当天）加上前面的N行（前N天）
• RANK() 和 DENSE_RANK()：根据需求选择排名类型，前者会跳过重复排名的位置，后者不会

场景2：全市场股票的滚动窗口排名（最近N天内所有股票价格排名） #

如果需要计算当天及前N天内所有股票价格的整体排名，分两种情况处理：

情况A：SQL Server 2022及以上版本 #

2022版本支持直接用日期区间定义滚动窗口，写法更简洁准确：

DECLARE @window_days INT = 2;

SELECT 
    MyDate,
    Ticker,
    Price,
    RANK() OVER (
        ORDER BY Price DESC 
        RANGE BETWEEN INTERVAL @window_days DAY PRECEDING AND CURRENT ROW
    ) AS Global_Rolling_Rank
FROM @table
ORDER BY MyDate, Ticker;

情况B：SQL Server 2022以下版本 #

旧版本不支持RANGE INTERVAL语法，用子查询的方式实现：

DECLARE @window_days INT = 2;

WITH StockData AS (
    SELECT * FROM @table
)
SELECT 
    s1.MyDate,
    s1.Ticker,
    s1.Price,
    -- 计算最近3天内价格高于当前记录的不同价格数量+1，得到排名
    (SELECT COUNT(DISTINCT s2.Price) 
     FROM StockData s2 
     WHERE s2.MyDate BETWEEN DATEADD(DAY, -@window_days, s1.MyDate) AND s1.MyDate
       AND s2.Price > s1.Price) + 1 AS Global_Rolling_Rank
FROM StockData s1
ORDER BY s1.MyDate, s1.Ticker;

代码说明： #

• 新版本的RANGE BETWEEN INTERVAL直接按日期范围筛选滚动窗口，无需担心行号不连续的问题
• 旧版本的子查询通过日期条件筛选出滚动窗口内的所有记录，统计比当前价格高的不同价格数，再加1得到排名

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