时间戳

首页 > 解决方案 > 如何在条件上选择 RANK（取决于上一行）

sql - 如何在条件上选择 RANK（取决于上一行）

问题描述

我们有什么

我有这样的桌子


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 行

标签： sqlsql-servertsql

解决方案

以下查询是您可以设置“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=6bd815da2cbfa8f65bc999e5736f2041

推荐阅读