Server排名

Question

我有一个股票表，我想为每一个股票建立滚动排名。

下面是我的数据集的一个示例：

StockID QuoteID QuoteDay    QuoteClose
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.1969
251     102     2018-04-08  0.1986
251     103     2018-04-09  0.2291
251     104     2018-04-10  0.1963
251     105     2018-04-11  0.1995
251     106     2018-04-12  0.2271
251     107     2018-04-13  0.3722
251     108     2018-04-14  0.3073
251     109     2018-04-15  0.3292
251     110     2018-04-16  0.2905
251     111     2018-04-17  0.2784

每一天，我都想把QuoteClose排在前6天的前面。

例如：

StockID QuoteID QuoteDay    QuoteClose  7d Rank
47      230     04/06/18     5.1200     
47      231     04/07/18     5.2100     
47      232     04/08/18     5.3000     
47      233     04/09/18     5.2100     
47      234     04/10/18     5.1900     
47      235     04/11/18     5.5200     
47      236     04/12/18     7.1600       1
47      237     04/13/18     6.6900       2
47      238     04/14/18     6.6300       3
47      239     04/15/18     7.0200       2
47      240     04/16/18     6.6300       4
47      241     04/17/18     6.5800       6
251     100     04/06/18     0.1906     
251     101     04/07/18     0.1969     
251     102     04/08/18     0.1986     
251     103     04/09/18     0.2291     
251     104     04/10/18     0.1963     
251     105     04/11/18     0.1995     
251     106     04/12/18     0.2271       2
251     107     04/13/18     0.3722       1
251     108     04/14/18     0.3073       2
251     109     04/15/18     0.3292       2
251     110     04/16/18     0.2905       4
251     111     04/17/18     0.2784       5

注意，前6天没有排名。

我在组合排名和行号方面有问题，所以我的方法是有一个循环，每次只在7天的数据中为每个股票提供数据。

例如，对于股票#47，在4/12/18，我在比较Quoteclose从4/6/8-4/12/8到4/12/8之间的排名，以确定排名将是1。

然后，我将该排名保存到另一张表。

然后循环将窗口向前移动1天，并找到4/13/18的排序，这将是2。对该股票和表中的所有其他股票重复这个过程。

我的计划很复杂，但应该完成任务。

是否有更好、更清洁的方法来完成所有股票的滚动排名？

我试图使这是一个动态的过程，以便我可以产生不同的排名周期:7天，20天，等等。

Answer 1

把它分成几个步骤：

首先，(OrderedRankSets )为每个StockID和QuoteDay获取一组行，其中包括当前行和所有先前行，这些行由QuoteDay降号。稍后，我们可以使用这个编号来限制我们要排列的前面的行。

第二，对每个报价集进行排序，在这些引用集中，我们实际上在每个报价日的期间中至少有行数，并且只包括到周期长度的行数。

最后，我们不需要输出集合的所有行，我们只需要每个报价日的最新数据(报价日=前报价日)，所以最后的查询只是转储(参见SQL Fiddle)。

DECLARE @Period INT = 7;

WITH 
    OrderedRankSets AS (
       SELECT
            Quotes.StockID
        ,   Quotes.QuoteID
        ,   Quotes.QuoteDay
        ,   PriorQuotes.QuoteDay AS PriorQuoteDay
        ,   PriorQuotes.QuoteClose
        ,   ROW_NUMBER() OVER (PARTITION BY Quotes.StockID, Quotes.QuoteDay ORDER BY PriorQuotes.QuoteDay DESC) AS RowNumber
        ,   COUNT(*) OVER (PARTITION BY Quotes.StockID, Quotes.QuoteDay) AS [RowCount]
        FROM 
            Quotes
        JOIN Quotes AS PriorQuotes ON (PriorQuotes.StockID = Quotes.StockID
                                       AND PriorQuotes.QuoteDay <= Quotes.QuoteDay)
    )
,  RankedQuoteDays AS (
       SELECT 
          OrderedRankSets.*
       ,  CASE WHEN [RowCount] < @Period THEN NULL ELSE RANK() OVER (PARTITION BY StockID, QuoteDay ORDER BY QuoteClose DESC) END AS QuoteRank
       FROM
          OrderedRankSets
       WHERE
          RowNumber <= @Period
   )
SELECT
    RankedQuoteDays.StockID
,   RankedQuoteDays.QuoteID
,   RankedQuoteDays.QuoteDay
,   RankedQuoteDays.QuoteClose
,   RankedQuoteDays.QuoteRank
FROM
    RankedQuoteDays
WHERE
    QuoteDay = PriorQuoteDay
ORDER BY
    StockID, QuoteDay

Answer 2

我不确定我是否理解你的排名，但我假设这是一个7(变化日)滚动平均排名从最好(最大的数字)到最差(最小)。

declare  @t table (
StockID int,    QuoteID int,    QuoteDay date,  QuoteClose money)
insert into @t
values
    (47,230,'20180406',5.12)
,   (47,231,'20180407',5.21)
,   (47,232,'20180408',5.3)
,   (47,233,'20180409',5.21)
,   (47,234,'20180410',5.19)
,   (47,235,'20180411',5.52)
,   (47,236,'20180412',7.16)
,   (47,237,'20180413',6.69)
,   (47,238,'20180414',6.63)
,   (47,239,'20180415',7.02)
,   (47,240,'20180416',6.63)
,   (47,241,'20180417',6.58)
,   (251,100,'20180406',0.1906)
,   (251,101,'20180407',0.1969)
,   (251,102,'20180408',0.1986)
,   (251,103,'20180409',0.2291)
,   (251,104,'20180410',0.1963)
,   (251,105,'20180411',0.1995)
,   (251,106,'20180412',0.2271)
,   (251,107,'20180413',0.3722)
,   (251,108,'20180414',0.3073)
,   (251,109,'20180415',0.3292)
,   (251,110,'20180416',0.2905)
,   (251,111,'20180417',0.2784)

declare @dayInRank int = 7


;with cte as
(
    select rn=row_number()over(partition by StockID order by QuoteDay)
        ,*
    from @t
)

,RollingAvg as
(
    select StockID,QuoteDay,RollingAvg=s.RollingSum/@dayInRank
    from cte
        cross apply (select SUM(QuoteClose) from cte c2 where cte.StockID=c2.StockID and c2.rn between cte.rn-(@dayInRank-1) and cte.rn) s(RollingSum) 
    where rn>=@dayInRank
)

select *,Rank=Row_Number()over (partition by StockID order by ra.RollingAvg desc)
from RollingAvg ra
order by StockID,QuoteDay