SQL Server 滚动排名

Question

我有一张不同日期的股票价格表。

DECLARE @table TABLE (MyDate DATE, Ticker VarChar(6), Price Decimal (6,2))

INSERT INTO @Table
VALUES ('1/1/13' , 'ABC' , '100.00')
          ,('1/2/13' , 'ABC' , '101.50')  
          ,('1/3/13' , 'ABC' , '99.80')
          ,('1/4/13' , 'ABC' , '95.50')
          ,('1/5/13' , 'ABC' , '78.00')
          ,('1/1/13' , 'JKL' , '34.57')
          ,('1/2/13' , 'JKL' , '33.99')  
          ,('1/3/13' , 'JKL' , '31.85')
          ,('1/4/13' , 'JKL' , '30.11')
          ,('1/5/13' , 'JKL' , '45.00')
          ,('1/1/13' , 'XYZ' , '11.50')
          ,('1/2/13' , 'XYZ' , '12.10')  
          ,('1/3/13' , 'XYZ' , '17.15')
          ,('1/4/13' , 'XYZ' , '14.10')
          ,('1/5/13' , 'XYZ' , '15.55')

我想计算每个 Ticker 和每个日期的连续 3 天排名(基于今天和前两天)。

我可以创建静态排名，但无法生成动态滚动排名。

SELECT 
    *,
    RANK() OVER (PARTITION BY Ticker ORDER BY Price DESC) AS Rank_3d
FROM 
    @table
ORDER BY 
    Ticker ASC, MyDate DESC

输出:

MyDate  Ticker  PRICE   Rank_3d
-------------------------------
2013-01-05  ABC 78.00   5
2013-01-04  ABC 95.50   4
2013-01-03  ABC 99.80   3
2013-01-02  ABC 101.50  1
2013-01-01  ABC 100.00  2
2013-01-05  JKL 45.00   1
2013-01-04  JKL 30.11   5
2013-01-03  JKL 31.85   4
2013-01-02  JKL 33.99   3
2013-01-01  JKL 34.57   2
2013-01-05  XYZ 15.55   2
2013-01-04  XYZ 14.10   3
2013-01-03  XYZ 17.15   1
2013-01-02  XYZ 12.10   4
2013-01-01  XYZ 11.50   5

我的输出应该是这样的:

MyDate     Ticker    Price    Rank_3d
-------------------------------------
1/3/13     ABC       99.80    3
1/4/13     ABC       95.50    3
1/5/13     ABC       78.00    3
1/3/13     JKL       31.85    3
1/4/13     JKL       30.11    3
1/5/13     JKL       45.00    1
1/3/13     XYZ       17.15    1
1/4/13     XYZ       14.10    2
1/5/13     XYZ       15.55    2

我错过了什么？

Answer 1

使用排名函数没有“简单”的方法来做到这一点。

对于您的特定问题，使用 lag() 并不难。前两天(缺失值)和有关系时，逻辑有点棘手。但这是一种方法:

select t.*,
       (case when price = max(price) over (partition by ticker order by date rows between 2 preceding and current row)
             then 1
             when price = min(price) over (partition by ticker order by date rows between 2 preceding and current row)
             then 3
             else 2
        end) as rank_3day
from @table t;

即使使用 apply 逻辑也很棘手:

select t.*, t2.rank
from @table t cross apply
     (select count(*) as rank
      from (select top (3) t2.*
            from @table t2
            where t2.date <= t.date
            order by t2
           ) t2
      where t2.price >= t.price
     ) t2