1 概述
- lag 与 lead 函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取同一个字段的 前 N 行数据(lag) 和 后 N 行数据(lead) 作为独立的列,从而更方便地进行数据过滤。
- 这种操作也可以替代表的 自连接,并且有更高的效率。
2 语法
-- lag 和 lead 一样
lag(value_expr, [offset], [default]) over([partition by ..] order by ..)
(1) value_expr: 字段名
(2) offset: 偏移量 -> N 默认 1
(3) default: 字段值超出偏移量时的默认值,默认为 null
3 实例
实例1:获取当前记录的 id 、上一条记录的 id 以及 下一条记录的 id
WITH t_test AS (
SELECT 1 ID, 'a' NAME, '男' sex FROM dual UNION ALL
SELECT 2 ID, 'b' NAME, '女' sex FROM dual UNION ALL
SELECT 3 ID, 'c' NAME, '女' sex FROM dual UNION ALL
SELECT 4 ID, 'd' NAME, '女' sex FROM dual UNION ALL
SELECT 5 ID, 'e' NAME, '男' sex FROM dual
)
SELECT t.id,
LAG(t.id, 1, NULL) OVER(ORDER BY t.id) previous_id,
LEAD(t.id, 1, NULL) OVER(ORDER BY t.id) next_id,
t.name,
t.sex
FROM t_test t;
测试结果:
实例2:获取 “性别” 相同的,当前记录的 id 、上一条记录的 id 以及 下一条记录的 id
WITH t_test AS (
SELECT 1 ID, 'a' NAME, '男' sex FROM dual UNION ALL
SELECT 2 ID, 'b' NAME, '女' sex FROM dual UNION ALL
SELECT 3 ID, 'c' NAME, '女' sex FROM dual UNION ALL
SELECT 4 ID, 'd' NAME, '女' sex FROM dual UNION ALL
SELECT 5 ID, 'e' NAME, '男' sex FROM dual
)
SELECT t.id,
LAG(t.id, 1, NULL) OVER(PARTITION BY t.sex ORDER BY t.id) previous_id,
LEAD(t.id, 1, NULL) OVER(PARTITION BY t.sex ORDER BY t.id) next_id,
t.name,
t.sex
FROM t_test t;
测试结果:
实例3:获取 “性别” 相同的,当前记录的 id 和 下一条记录的 id 相差 2 及以上的记录
WITH t_test AS (
SELECT 1 ID, 'a' NAME, '男' sex FROM dual UNION ALL
SELECT 2 ID, 'b' NAME, '女' sex FROM dual UNION ALL
SELECT 3 ID, 'c' NAME, '女' sex FROM dual UNION ALL
SELECT 4 ID, 'd' NAME, '女' sex FROM dual UNION ALL
SELECT 5 ID, 'e' NAME, '男' sex FROM dual
)
SELECT tt.*
FROM (SELECT t.id,
lead(t.id, 1, NULL) over(PARTITION BY t.sex ORDER BY t.id) next_id,
t.name,
t.sex
FROM t_test t) tt
WHERE tt.next_id >= tt.id + 2;
测试结果: