如何选择RANK而不是条件(取决于前一行)

Question

我们所拥有的

我有这样的桌子

id          PlayerId    Amount
----------- ----------- -----------
1           1           10
2           1           20
3           1           30
4           1           40
5           1           40
11          1           20
13          1           20
15          1           40
14          2           19
12          2           10
6           2           1
7           2           5
8           2           10
9           2           20
10          2           30

我必须只选择数量大于前一行数量的行(每个玩家(。

这里有一个查询

SELECT a.id,
a.PlayerId,
a.Amount,
a.PreVval,
a.NextVal
FROM (SELECT id,
PlayerId,
Amount,
LAG(Amount) OVER (PARTITION BY PlayerId ORDER BY id) PreVval,
lead(Amount) OVER (PARTITION BY PlayerId ORDER BY id) NextVal
FROM dbo.Bets ) a 
WHERE a.Amount > a.PreVval OR a.Amount < a.NextVal OR (a.PreVval IS NULL AND a.Amount < a.NextVal)
ORDER BY a.PlayerId, a.id

id          PlayerId    Amount      PreVval     NextVal
----------- ----------- ----------- ----------- -----------
1           1           10          NULL        20
2           1           20          10          30
3           1           30          20          40
4           1           40          30          40
13          1           20          20          40
15          1           40          20          NULL
6           2           1           NULL        5
7           2           5           1           10
8           2           10          5           20
9           2           20          10          30
10          2           30          20          10
12          2           10          30          19
14          2           19          10          NULL

问题

所以现在我需要选择增加步数>4，我的意思是玩家1的1,2,3,4和玩家2的6,7,8,9,10

查询应在15m行上运行

Answer 1

下面的查询是一个可以设置"step_count"的示例。

WITH Bets(id,PlayerId,Amount)
AS
(
SELECT 1,1,10 UNION ALL
SELECT 2,1,20 UNION ALL
SELECT 3,1,30 UNION ALL
SELECT 4,1,40 UNION ALL
SELECT 5,1,40 UNION ALL
SELECT 11,1,20 UNION ALL
SELECT 13,1,20 UNION ALL
SELECT 15,1,40 UNION ALL
SELECT 14,2,19 UNION ALL
SELECT 12,2,10 UNION ALL
SELECT 6,2,1 UNION ALL
SELECT 7,2,5 UNION ALL
SELECT 8,2,10 UNION ALL
SELECT 9,2,20 UNION ALL
SELECT 10,2,30
)
,split_ranges
as(
select *,case when lag(amount) over(partition by playerid order by id) > amount
or lag(amount) over(partition by playerid order by id) is null 
then row_number() over(partition by playerid order by id)
end as rnk_val
from bets
)
,groups_data
as(
select *
,max(rnk_val) over(partition by playerid order by id) as fill_ranges
from split_ranges
)
select * from (   
select *,count(*) over(partition by playerid,fill_ranges) as cnt
from groups_data
)x
where x.cnt>=4   

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6bd815da2cbfa8f65bc999e573f2041

Answer 2

下面的逻辑有点棘手，但您可以检查一下-

演示在这里

WITH CTE
AS
(
SELECT * FROM
(
SELECT id,
PlayerId,
Amount,
LAG(Amount) OVER (PARTITION BY PlayerId ORDER BY id) PreVval,
lead(Amount) OVER (PARTITION BY PlayerId ORDER BY id) NextVal,
ISNULL(LAG(ID,3) OVER (PARTITION BY PlayerId ORDER BY id),0) LAG3,
ISNULL(LAG(ID,2) OVER (PARTITION BY PlayerId ORDER BY id),0) LAG2,
ISNULL(LAG(ID,1) OVER (PARTITION BY PlayerId ORDER BY id),0) LAG1,
ISNULL(LEAD(ID,1) OVER (PARTITION BY PlayerId ORDER BY id),0) LEAD1,
ISNULL(LEAD(ID,2) OVER (PARTITION BY PlayerId ORDER BY id),0) LEAD2,
ISNULL(LEAD(ID,3) OVER (PARTITION BY PlayerId ORDER BY id),0) LEAD3
FROM Bets 
) a 
WHERE a.Amount > a.PreVval 
OR a.Amount < a.NextVal 
OR (a.PreVval IS NULL AND a.Amount < a.NextVal)
)
SELECT id,PlayerId,Amount,PreVval,NextVal
FROM CTE A
WHERE ID-LAG3 = 3
OR LEAD1 - LAG2 = 3 
OR LEAD2 - LAG1 = 3
OR LEAD3 - ID = 3