SQL 180. 连续出现的数字

题目 ： https://leetcode.cn/problems/consecutive-numbers/

数据

Create table If Not Exists Logs (
  id int, 
  num int
);

Truncate table Logs;

insert into Logs (id, num) values ('1', '1');
insert into Logs (id, num) values ('2', '1');
insert into Logs (id, num) values ('3', '1');
insert into Logs (id, num) values ('4', '2');
insert into Logs (id, num) values ('5', '1');
insert into Logs (id, num) values ('6', '2');
insert into Logs (id, num) values ('7', '2');

需求

查询所有至少连续出现三次的数字

查询结果 :

| ConsecutiveNums |
| 1               |

解决

连续的值 ，有3种情况 ，如：

• 1, 1, 1, 2, 2, 1, 1 (1 连续性);
• 1, 2, 1, 1, 2, 1, 1 (都不连续性);
• 1, 1, 1, 2, 1, 1, 1 (1 连续性, 去重);

方法1

技术点:

• lead(): 查询当前行向下偏移 n 行的值
• lag() : 查询当前行向上偏移 n 行的值

lead/lag(列名, 偏移的offset, 超出窗口的默认值) 
	over (partition by 分组列 order by 排序列 
  rows between 开始位置 preceding and 结束位置 following)

查询该行的，下列值，下下列值

select num,
  lead(num, 1, 0) over(order by id) as lead_1,
  lead(num, 2, 0) over(order by id) as lead_2
from Logs

查询结果:

num|lead_1|lead_2|
---+------+------+
  1|     1|     1|
  1|     1|     2|
  1|     2|     1|
  2|     1|     2|
  1|     2|     2|
  2|     2|     0|
  2|     0|     0|

当该行值 = 下列值 = 下下列值就连续相等了

• 注意点：num可能有相同值多次连续，所以要去重

-- 求下列，下下列
with t1 as (
  select num,
    lead(num, 1, 0) over(order by id) as lead_1,
    lead(num, 2, 0) over(order by id) as lead_2
  from Logs
)
-- 比较下列，下下列，并去重
select num as ConsecutiveNums
from t1
where num = lead_1 and lead_1 = lead_2
group by num;

缺点: 不够通用，当连续 n 次时，就不好处理了

方法2

思路 :

• 对 num 排序
• 对 num 进行分区排序
• 对俩个值进行差值
• 当差值相同数大于 3 时 , 就说明连续

| num | row_bumber | row_bumber分组 | 差 |
| 1   |       1    |       1        |  0 |
| 1   |       2    |       2        |  0 |
| 1   |       3    |       3        |  0 |
| 2   |       4    |       1        |  3 |
| 2   |       5    |       2        |  3 |
| 1   |       6    |       4        |  2 |
| 1   |       7    |       5        |  2 |

根据id 进行排序 ， 避免id不连续情况

select num,
  row_number() over(order by id) as row_bumber
from Logs

查询结果：

num|row_bumber|
---+----------+
  1|         1|
  1|         2|
  1|         3|
  2|         4|
  1|         5|
  2|         6|
  2|         7|

根据 num 分区窗口，在窗口中根据 id 排序

select num,
  row_number() over(partition by num order by id) as row_bumber_partition
from Logs

查询结果：

num|row_bumber_partition|
---+--------------------+
  1|                   1|
  1|                   2|
  1|                   3|
  1|                   4|
  2|                   1|
  2|                   2|
  2|                   3|

对比差值，查看区别

select num,
  row_number() over(order by id) as row_bumber,
  row_number() over(partition by num order by id) as row_bumber_partition,
  row_number() over(order by id) - row_number() over(partition by num order by id) as diff
from Logs

查询结果：

num|row_bumber|row_bumber_partition|diff|
---+----------+--------------------+----+
  1|         1|                   1|   0|
  1|         2|                   2|   0|
  1|         3|                   3|   0|
  2|         4|                   1|   3|
  1|         5|                   4|   1|
  2|         6|                   2|   4|
  2|         7|                   3|   4|

解决 :

-- 求差值
with t1 as(
  select num,
    row_number() over(order by id) - row_number() over(partition by num order by id) as diff
  from Logs
),
-- 筛选连续3次的值
t2 as (
  select num as ConsecutiveNums
  from t1
  group by num, diff
  having count(*) >= 3
)
-- 去重
select num
from t2
group by num;