--删除表
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;
--打印员工信息，并ag漂移函数打印员工历史差注意：若用值去减去空值，则还是空所以在对比结果中增加vl函数
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;
--使用临时表，增加分析函数排序
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.*,first_value(t1.l_diff)over(partition by t1.name order by t1.l_diff desc) ll_diff from t1
)
select * from t2 where l_diff=ll_diff;
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.*,first_value(t1.l_diff)over(partition by t1.name order by t1.l_diff desc) ll_diff from t1
)
select * from t2 where l_diff=ll_diff;