1.题目:
2.解题思路:
3.解题:
# 1. 按照num进行分组
select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs;
WITH t1 AS (
select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
)
select *,id - dcsl as chazhi from t1;
# 2.计数
WITH
t1 AS (
select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
),
t2 AS (
select *,id - dcsl as chazhi from t1
)
select id,num,dcsl,count(chazhi) over(PARTITION BY num,chazhi) jishu from t2;
# 3. 筛选,大于等于三的
WITH
t1 AS (
select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
),
t2 AS (
select *,cast(id as unsigned) - cast(dcsl as unsigned) as chazhi from t1
),
t3 as(
select id,num,dcsl,count(chazhi) over(PARTITION BY num,chazhi) jishu from t2
)
select num as ConsecutiveNums from t3 where jishu >=3 GROUP BY num;
注意:
这里id - dcsl 要改为cast(id as unsigned) - cast(dcsl as unsigned) (我也不知道为什么,别人帮我解决的),不然会报错
答案:
WITH
t1 AS (
select *,row_number() OVER (PARTITION BY num order by id) as dcsl FROM Logs
),
t2 AS (
select *,cast(id as signed) - cast(dcsl as signed) as chazhi from t1
),
t3 as(
select id,num,dcsl,count(chazhi) over(PARTITION BY num,chazhi) jishu from t2
)
select num as ConsecutiveNums from t3 where jishu >=3 GROUP BY num;