基于SQL Server为股票数据表创建单只股票滚动排名的技术咨询

嘿，我来帮你搞定每只股票的滚动排名问题！先把你提供的样本数据整理成更清晰的表格，方便咱们理解：

下面分两种常见的排名需求，给出具体实现方案：

1. 滑动窗口滚动排名（比如最近3天/3条记录） #

这种排名是针对每只股票，计算当前日期及最近N天（或N条记录）内的收盘价排名，适合观察短期价格相对表现。

Python Pandas 实现 #

import pandas as pd

# 构造样本数据（你可以替换成读取自己的数据源）
data = [
    [47, 230, '2018-04-06', 5.1200],
    [47, 231, '2018-04-07', 5.2100],
    [47, 232, '2018-04-08', 5.3000],
    [47, 233, '2018-04-09', 5.2100],
    [47, 234, '2018-04-10', 5.1900],
    [47, 235, '2018-04-11', 5.5200],
    [47, 236, '2018-04-12', 7.1600],
    [47, 237, '2018-04-13', 6.6900],
    [47, 238, '2018-04-14', 6.6300],
    [47, 239, '2018-04-15', 7.0200],
    [47, 240, '2018-04-16', 6.6300],
    [47, 241, '2018-04-17', 6.5800],
    [251, 100, '2018-04-06', 0.1906],
    [251, 101, '2018-04-07', 0.1960]
]

df = pd.DataFrame(data, columns=['StockID', 'QuoteID', 'QuoteDay', 'QuoteClose'])
df['QuoteDay'] = pd.to_datetime(df['QuoteDay'])

# 按股票分组，计算最近3天（含当天）的滚动排名，收盘价越高排名越靠前
df['Rolling_Rank_3d'] = df.groupby('StockID')['QuoteClose'].rolling(window=3, min_periods=1).rank(ascending=False).reset_index(level=0, drop=True)

# 如果是按最近3条记录（不管日期间隔），直接用window=3即可，上面的代码已经支持
print(df[['StockID', 'QuoteDay', 'QuoteClose', 'Rolling_Rank_3d']])

• rolling(window=3)：定义滑动窗口大小为3
• min_periods=1：保证即使窗口不足3条记录（比如股票前2天的数据）也能计算排名
• rank(ascending=False)：按收盘价降序排名，数值越高排名越前

SQL（MySQL）实现 #

SELECT 
    StockID,
    QuoteID,
    QuoteDay,
    QuoteClose,
    -- 最近3天（含当天）的滚动排名，收盘价越高排名越前
    RANK() OVER (
        PARTITION BY StockID 
        ORDER BY QuoteClose DESC 
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
    ) AS Rolling_Rank_3d
FROM 
    your_stock_table
ORDER BY 
    StockID, QuoteDay;

• PARTITION BY StockID：按股票分组计算排名
• RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW：定义窗口为当前日期及前2天（共3天）
• 如果要按最近3条记录（不管日期），把RANGE换成ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
• 若需要连续排名（相同价格不跳过名次），用DENSE_RANK()代替RANK()；若需要唯一排名，用ROW_NUMBER()

2. 累积排名（从股票第一条记录到当前日期） #

这种排名是计算每只股票从上市以来到当前日期的收盘价排名，适合观察长期价格相对位置。

Python Pandas 实现 #

# 按股票分组，计算累积排名，相同价格按出现顺序排唯一名次
df['Cumulative_Rank'] = df.groupby('StockID')['QuoteClose'].rank(method='first', ascending=False)

print(df[['StockID', 'QuoteDay', 'QuoteClose', 'Cumulative_Rank']])

• method='first'：相同收盘价的记录，按出现顺序分配唯一排名，避免并列
• ascending=False：收盘价越高排名越前

SQL（MySQL）实现 #

SELECT 
    StockID,
    QuoteID,
    QuoteDay,
    QuoteClose,
    -- 累积排名，从该股票最早记录到当前
    ROW_NUMBER() OVER (
        PARTITION BY StockID 
        ORDER BY QuoteClose DESC, QuoteDay ASC
    ) AS Cumulative_Rank
FROM 
    your_stock_table
ORDER BY 
    StockID, QuoteDay;

• ROW_NUMBER()：保证每个记录有唯一排名，相同收盘价按日期先后排序
• 若需要并列排名，用RANK()或DENSE_RANK()代替ROW_NUMBER()

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