语法
- Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。
LAG语法:LAG(<expression>[,offset[, default_value]]) OVER ( PARTITION BY expr,... ORDER BY expr [ASC|DESC],... )
LEAD语法:LEAD(<expression>[,offset[, default_value]]) OVER ( PARTITION BY (expr) ORDER BY (expr))
- offset:偏移量
- default_value:用于指定最后一行的返回值
e.g
编写一个SQL查询,用于查找所有至少连续出现三次的数字
输入表格:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
- 使用lead()函数
select distinct(num) from (
select id,num,
lead(num,1) over() as num_1, #
lead(num,2) over() as num_2
from logs
) as c
where c.num = c.num_1 and c.num_1 = c.num_2;