leetcode解题小记180. 连续出现的数字

题目

180. 连续出现的数字
     SQL架构
     编写一个 SQL 查询，查找所有至少连续出现三次的数字。

±—±----+
| Id | Num |
±—±----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
±—±----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

±----------------+
| ConsecutiveNums |
±----------------+
| 1 |
±----------------+

思路

行与行之间的关系，又是“连续”，考虑用lag/lead函数，这里用的是 lag

编程

lag函数的用法：lag(要lag的列, 2) OVER(ORDER BY 分组排序) as xxx

代码

/* Write your PL/SQL query statement below */
select distinct(ConsecutiveNums) as "ConsecutiveNums"
from ( 
select  
lag(Num, 1) over (order by Id) as lag1,
lag(Num, 2) over (order by Id) as lag2,
Num as ConsecutiveNums 
from Logs)t
where t.ConsecutiveNums = t.lag1 and t.ConsecutiveNums = t.lag2

扩展

如果不用lag/lead的函数呢？