干饭FFing

漂移函数lag上一行、lead下一行

获取上一行：lag（字段，跳跃行-默认为1）over（partition by 组字段 order by 排序字段）

获取下一行：lead（字段，跳跃行-默认为1）over（partition by 组字段 order by 排序字段）

范例：计算员工涨薪中涨薪幅度是第几次，并打印信息 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
--删除表
drop table test;

--创建表
create table test (
id number,
name varchar2(20),
sal number,
hdate date
);

--插入模拟数据
insert into test values (1,'张三',3000,sysdate-3);
insert into test values (1,'李四',3110,sysdate-3);
insert into test values (1,'王五',3220,sysdate-3);
insert into test values (1,'赵六',3330,sysdate-3);


insert into test values (2,'张三',3000,sysdate-2);
insert into test values (2,'李四',4230,sysdate-2);
insert into test values (2,'王五',5410,sysdate-2);
insert into test values (2,'赵六',6537,sysdate-2);


insert into test values (3,'张三',4000,sysdate-1);
insert into test values (3,'李四',5208,sysdate-1);
insert into test values (3,'王五',6414,sysdate-1);
insert into test values (3,'赵六',9523,sysdate-1);

--提交
commit;

--打印员工信息，并lag漂移函数打印员工历史差,注意：若用值去减去空值，则还是空,所以在对比结果中增加nvl函数
select t.*,lag(sal)over(partition by name order by sal ) l_lag,nvl(sal-lag(sal)over(partition by name order by sal ),0) l_diff from test t;
--打印结果
    增薪次数
1   1   张三  3000    2023/9/24 9:31:35       0
2   2   张三  3000    2023/9/25 9:31:35   3000    0
3   3   张三  4000    2023/9/26 9:31:35   3000    1000
4   1   李四  3110    2023/9/24 9:31:35       0
5   2   李四  4230    2023/9/25 9:31:35   3110    1120
6   3   李四  5208    2023/9/26 9:31:35   4230    978
7   1   王五  3220    2023/9/24 9:31:35       0
8   2   王五  5410    2023/9/25 9:31:35   3220    2190
9   3   王五  6414    2023/9/26 9:31:35   5410    1004
10  1   赵六  3330    2023/9/24 9:31:35       0
11  2   赵六  6537    2023/9/25 9:31:35   3330    3207
12  3   赵六  9523    2023/9/26 9:31:35   6537    2986



--使用临时表，增加分析函数排序
with t1 as(
select t.*,
       lag(sal) over(partition by name order by sal) l_lag,
       nvl (sal - lag(sal) over(partition by name order by sal),0) l_diff
  from test t
) , t2 as(
select t1.* ,(rank()over (partition by t1.name order by t1.l_diff desc))  rn from t1 
)
select * from t2 where rn=1;

--打印结果
    增薪次数
1   3   张三  4000    2023/9/26 9:31:35   3000    1000    1
2   2   李四  4230    2023/9/25 9:31:35   3110    1120    1
3   2   王五  5410    2023/9/25 9:31:35   3220    2190    1
4   2   赵六  6537    2023/9/25 9:31:35   3330    3207    1
图片预览
100%