Sql Server Rank on Value Rank

Question

我有一个包含三列的表: ID、Date、Value。我希望对行进行排序，以便在ID内，值至少为X的每个日期的排名都会上升，否则排名保持不变。

给定的ID、日期和值如下

1, 6/1, 8
1, 6/2, 12
1, 6/3, 14
1, 6/4, 9
1, 6/5, 11

我想返回一个至少基于10的值的排名，这样我就有了ID、日期、值和排名，如下所示：

1, 6/1, 8, 0
1, 6/2, 12, 1
1, 6/3, 14, 2
1, 6/4, 9, 2
1, 6/5, 11, 3

换句话说，每当该值超过阈值时，排名就会增加，否则排名就会保持不变。

我尝试过的是

SELECT T1.*, X.Ranking FROM TABLE T1
LEFT JOIN ( SELECT *, DENSE_RANK( ) OVER ( PARTITION BY T2.ID ORDER BY T2.DATE ) Ranking
    FROM TABLE T2 WHERE T2.VALUE >= 10 ) X
ON T1.ID = T2.ID AND T1.Date = T2.Date

这几乎是可行的。它给我的输出如下

1, 6/1, 8, NULL
1, 6/2, 12, 1
1, 6/3, 14, 2
1, 6/4, 9, NULL
1, 6/5, 11, 3

然后，我想把第一个NULL变成0，把第二个变成2。

我将上面的查询转换为cte，并尝试

    SELECT T1.*, CASE WHEN T1.Ranking IS NULL THEN ISNULL( (
        SELECT MAX( T2.Ranking ) 
        FROM cte T2 WHERE T1.ID = T2.ID AND T1.Date > T2.Date, 0 ) 
            ELSE T1.Ranking END NewRanking
    FROM cte T1

这看起来是可行的，但是我的表有200,000行，查询运行了25分钟……所以，我正在寻找比SELECT MAX更开箱即用的东西。

Answer 1

您使用的是SQL Server 2012，因此可以进行累积求和：

select t.*,
       sum(case when value >= 10 then 1 else 0 end) over
              (partition by id order by date) as ranking
from table t;