智慧团建登录网站入口,知名网络推广,网站的优化外包,东莞哪里建设网站好目录
题目
准备数据 分析数据 题目
找出所有至少连续出现三次的数字。
准备数据
## 创建库
create database db;
use db;## 创建表
Create table If Not Exists Logs (id int, num int)## 向表中插入数据
Truncate table Logs
insert into Logs (id, num) values (1, 1)
i…目录
题目
准备数据 分析数据 题目
找出所有至少连续出现三次的数字。
准备数据
## 创建库
create database db;
use db;## 创建表
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) 分析数据 1 是唯一连续出现至少三次的数字。 遇见连续性问题需要两列差值相同的最后进行相减相同的即为连续。 第一步根据num分组id排序进行排名 select *,row_number() over (partition by num order by id) rn
from logs; 第二步算差值将id列减去rn列的值 with t1 as (select *,row_number() over (partition by num order by id) rnfrom logs
) select *,(id - cast(rn as signed)) as diff
from t1; 注意rn列需要强制性转化 with t2 as (with t1 as (select *,row_number() over (partition by num order by id) rnfrom logs) select *,(id - cast(rn as signed)) as difffrom t1
) selectdistinct t2.num as ConsecutiveNums
from t2
group by t2.num,t2.diff
having count(t2.diff) 3;
