#Oracle LAG和LEAD分析函数
--作用
--Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。

--Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。


--创建测试表并插入测试数据
Create table Customer (customer_id number, name varchar2(20), visited_on date, amount number);
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', DATE'2019-01-01', '100');
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', DATE'2019-01-02', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', DATE'2019-01-03', '120');
insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Khaled', DATE'2019-01-04', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Khaled', DATE'2019-01-05', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Khaled', DATE'2019-01-06', '140');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Anna', DATE'2019-01-07', '150');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Anna', DATE'2019-01-08', '80');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Anna', DATE'2019-01-09', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Jhon', DATE'2019-01-10', '130');
COMMIT;

--假设下面的的visited_on都是连续的

--lag函数
--lag(exp_str,offset,defval) over()
--exp_str:对比的字段
--offset:是exp_str字段的偏移量,即 offset 为N ,指的是在表中从当前行位置向前数N行就是我们所要找的那一行了,默认为1
--defval:当在表中从当前行位置向前数N行已经超出了表的范围时,lag()函数将defval这个参数值作为函数的返回值,如果不给值则会返回null
--注意:defval 是不能使用窗口函数的

--01:取每个用户当前日期的上一天的AMOUNT金额,如果没有上一天则给0
select a.*,
       lag(a.amount,1,0) over(partition by a.customer_id order by a.visited_on) amount2
  from Customer a;
  
--02:取每个用户当前日期的上一天的AMOUNT金额,如果没有上一天则给null
--defval直接不用给值即可
select a.*,
       lag(a.amount,1) over(partition by a.customer_id order by a.visited_on) amount2
  from Customer a;

--03:取每个用户当前日期的上两天的AMOUNT金额,如果没有上两天则给0
select a.*,
       lag(a.amount,2,0) over(partition by a.customer_id order by a.visited_on) amount2
  from Customer a;



--Lead函数
  --exp_str:对比的字段
--offset:是exp_str字段的偏移量,即 offset 为N ,指的是在表中从当前行位置向前数N行就是我们所要找的那一行了,默认为1
--defval:当在表中从当前行位置向前数N行已经超出了表的范围时,lag()函数将defval这个参数值作为函数的返回值,如果不给值则会返回null
--注意:defval 是不能使用窗口函数的

--01:取每个用户当前日期的下一天的AMOUNT金额,如果没有下一天则给0
select a.*,
       lead(a.amount,1,0) over(partition by a.customer_id order by a.visited_on) amount2
  from Customer a;
  
--02:取每个用户当前日期的下一天的AMOUNT金额,如果没有下一天则给null
--defval直接不用给值即可
select a.*,
       lead(a.amount,1) over(partition by a.customer_id order by a.visited_on) amount2
  from Customer a;

--03:取每个用户当前日期的下两天的AMOUNT金额,如果没有下两天则给0
select a.*,
       lead(a.amount,2,0) over(partition by a.customer_id order by a.visited_on) amount2
  from Customer a