SQL连续问题(窗口函数构造等差数列）

带有“连续”的问题，都可以考虑通过

step1：row_number() rank()构造连续值-题目中的id/时间等备选连续值，通过用连续值-连续值 = 定值的等差数列原理，

step2:对定值进行groupby，再计算连续值的个数，范围等问题。

603. 连续空余座位

180. 连续出现的数字

2173. 最多连胜的次数

用SQL求每个用户的最大连续听歌天数__牛客网

603. 连续空余座位

 

step1：seat_id本身是连续值，再为free=1的列标记行号作为连续值。

step2:两个连续值相减为定值的部分即为连续座位，对定值进行groupby，再对count进行筛选即可。

with t1 as (
select *,
        if(free is true, row_number() over(partition by free order by seat_id),null) as fn
from Cinema),
t2 as 
(select (seat_id -fn) as diff 
from t1
group by (seat_id -fn)
having count(*) >= 2)
select seat_id 
from t1 
where (seat_id-fn) in (select * from t2)

180. 连续出现的数字

 

step1：Id本身是连续值，再对相同num做窗内排序构造连续值。

step2:两个连续值相减为定值的部分即为连续数字，对定值进行groupby，再对count进行筛选即可。

但是这道题直接用Id可能会溢出（？）目前尚不清楚为啥，可以再构造一列row_number作为连续值。

with t as (
select 
    *,
    row_number() over(order by id)rk,
    row_number() over(partition by num order by id) nk
from Logs)
select distinct num as ConsecutiveNums
from t
group by num, (rk - nk)
having count(*) >= 3;

 2173. 最多连胜的次数

示例 1:

输入: 
Matches 表:
+-----------+------------+--------+
| player_id | match_day  | result |
+-----------+------------+--------+
| 1         | 2022-01-17 | Win    |
| 1         | 2022-01-18 | Win    |
| 1         | 2022-01-25 | Win    |
| 1         | 2022-01-31 | Draw   |
| 1         | 2022-02-08 | Win    |
| 2         | 2022-02-06 | Lose   |
| 2         | 2022-02-08 | Lose   |
| 3         | 2022-03-30 | Win    |
+-----------+------------+--------+
输出: 
+-----------+----------------+
| player_id | longest_streak |
+-----------+----------------+
| 1         | 3              |
| 2         | 0              |
| 3         | 1              |
+-----------+----------------+
解释: 
Player 1:
从 2022-01-17 到 2022-01-25, player 1连续赢了三场比赛。
2022-01-31, player 1 平局.
2022-02-08, player 1 赢了一场比赛。
最多连胜了三场比赛。

Player 2:
从 2022-02-06 到 2022-02-08, player 2 输了两场比赛。
最多连赢了0场比赛。

Player 3:
2022-03-30, player 3 赢了一场比赛。
最多连赢了一场比赛。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/longest-winning-streak
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

step1：构造连续值：一列row_number，一列只对赢的局做row_number

step2: 对差值做group by，特殊地，这里用了sum(if())做count(*)，方便用win筛选，最后对sum求最大值就可以了。

with t1 as (select *,
                   row_number() over (partition by player_id order by match_day)                                   rn,
                   if(result = 'Win', row_number() over (partition by player_id, result order by match_day), null) winr
            from Matches),
     t2 as (select player_id, (rn - winr) as grp, sum(if(result = 'Win', 1, 0)) as max_win
            from t1
            group by player_id, grp)
select t2.player_id, max(max_win) as longest_streak
from t2
group by t2.player_id;

用SQL求每个用户的最大连续听歌天数__牛客网

step1：构造连续值：一列rank（），一列ftime。

这题特殊在同一天同一人可能有多次听课记录，例如下图1和2实际是一条记录。

所以这题用rank或者denserank，如果用row_number会导致相同数据有不同的标记。

另外，count 的时候会重复计数。所以需要对uin和ftime做预聚合。

step1：构造连续值：一列rank（），一列ftime。

这题特殊在同一天同一人可能有多次听课记录，例如下图1和2实际是一条记录。

所以这题用rank或者denserank，如果用row_number会导致相同数据有不同的标记。

另外，count 的时候会重复计数。所以需要对uin和ftime做预聚合。

step2: 对差值做group by，求最大值即可。

WITH t1 AS (SELECT uin,
                   ftime,
                   RANK() OVER (PARTITION BY uin ORDER BY ftime) AS rn
            FROM user_listen_record
            GROUP BY uin, ftime),
     t2 AS (SELECT uin, (ftime - rn) AS diff, COUNT(*) AS cnt
            FROM t1
            GROUP BY uin, diff)
SELECT uin, MAX(cnt) AS max_continuation_date_cnt
FROM t2
GROUP BY uin;