Oracle笔记 之 偏移量分析函数lag/lead-over函数

偏移量分析函数

解析

lag() over() 与lead() over() 函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。
这种操作可以代替自联接查询，并且LAG和LEAD的执行效率更高。
lag/lead(field, num, defaultvalue)指定要去的字段field，偏移量num和默认值defaultvalue。
lag()与lead()通过over()指定相应分组及排序方法。
over()可以使用partition by 语句（用于分组） order by 语句（用于排序）。
partition by column1 order by column2表示以column1字段进行分组，再 以column2字段进行排序，对数据进行查询。
注意：这里的partition by表示取偏移量的数据范围，而不是SQL语句里的分组函数。

示例

• 创建数据表

-- Create table
create table TEST_LAGOVER
(
  ddate  DATE,
  ymdate NUMBER,
  dvalue NUMBER
);
-- Add comments to the table 
comment on table TEST_LAGOVER
  is '测试--偏移量分析函数';

• 添加测试数据
  使用测试窗口添加数据
  生成连续数值

-- Created on 2022-07-07 by ADMINISTRATOR 
Declare
  -- Local variables here
  i Integer;
Begin
  -- Test statements here
  For ydate In (Select 2019 + Level ydate From dual Connect By Level <= 10) Loop
    For ddate In (Select Level ddate From dual Connect By Level <= 20) Loop
      Insert Into test_lagover
        Select to_date(y.ynumber * 10000 + m.mnumber * 100 + ddate.ddate,
                       'yyyymmdd'),
               y.ynumber * 100 + m.mnumber,
               ddate.ddate
          From (Select ydate.ydate + Level ynumber
                  From dual
                Connect By Level <= 1) y
          Full Join (Select Level mnumber From dual Connect By Level <= 12) m
            On 1 = 1;
    End Loop;
  End Loop;
  Commit;
End;

# 数据
1036	2021-01-01	202101	1
1048	2021-01-02	202101	2
1060	2021-01-03	202101	3
1072	2021-01-04	202101	4
1084	2021-01-05	202101	5
1096	2021-01-06	202101	6
1108	2021-01-07	202101	7
1120	2021-01-08	202101	8
1132	2021-01-09	202101	9
1144	2021-01-10	202101	10
1156	2021-01-11	202101	11
1168	2021-01-12	202101	12
1180	2021-01-13	202101	13
1192	2021-01-14	202101	14
1204	2021-01-15	202101	15
1216	2021-01-16	202101	16
1228	2021-01-17	202101	17
1240	2021-01-18	202101	18
1252	2021-01-19	202101	19
1264	2021-01-20	202101	20
1037	2021-02-01	202102	1
1049	2021-02-02	202102	2
1061	2021-02-03	202102	3
1073	2021-02-04	202102	4
1085	2021-02-05	202102	5
1097	2021-02-06	202102	6
1109	2021-02-07	202102	7
1121	2021-02-08	202102	8
1133	2021-02-09	202102	9
1145	2021-02-10	202102	10
1157	2021-02-11	202102	11
1169	2021-02-12	202102	12
1181	2021-02-13	202102	13
1193	2021-02-14	202102	14
1205	2021-02-15	202102	15
1217	2021-02-16	202102	16
1229	2021-02-17	202102	17
1241	2021-02-18	202102	18
1253	2021-02-19	202102	19
1265	2021-02-20	202102	20

• lag() over()函数
  取前一天的日期为比较对象，前一天的销量为对比值，默认值为null。

Select ddate,
       lag(ddate, 1, Null) over(Partition By ymdate Order By ddate) pymdate,
       dvalue,
       lag(dvalue, 1, Null) over(Partition By ymdate Order By ddate) pymdate
  From test_lagover

# 结果
1	2021-01-01		1	
2	2021-01-02	2021-01-01	2	1
3	2021-01-03	2021-01-02	3	2
4	2021-01-04	2021-01-03	4	3
5	2021-01-05	2021-01-04	5	4
6	2021-01-06	2021-01-05	6	5
7	2021-01-07	2021-01-06	7	6
8	2021-01-08	2021-01-07	8	7
9	2021-01-09	2021-01-08	9	8
10	2021-01-10	2021-01-09	10	9
11	2021-01-11	2021-01-10	11	10
12	2021-01-12	2021-01-11	12	11
13	2021-01-13	2021-01-12	13	12
14	2021-01-14	2021-01-13	14	13
15	2021-01-15	2021-01-14	15	14
16	2021-01-16	2021-01-15	16	15
17	2021-01-17	2021-01-16	17	16
18	2021-01-18	2021-01-17	18	17
19	2021-01-19	2021-01-18	19	18
20	2021-01-20	2021-01-19	20	19

• lead() over()函数
  取后一天的日期为比较对象，后一天的销量为对比值，默认值为null。

Select ddate,
       lead(ddate, 1, Null) over(Partition By ymdate Order By ddate) nymdate,
       dvalue,
       lead(dvalue, 1, Null) over(Partition By ymdate Order By ddate) ndvalue
  From test_lagover;

# 结果
1	2021-01-01	2021-01-02	1	2
2	2021-01-02	2021-01-03	2	3
3	2021-01-03	2021-01-04	3	4
4	2021-01-04	2021-01-05	4	5
5	2021-01-05	2021-01-06	5	6
6	2021-01-06	2021-01-07	6	7
7	2021-01-07	2021-01-08	7	8
8	2021-01-08	2021-01-09	8	9
9	2021-01-09	2021-01-10	9	10
10	2021-01-10	2021-01-11	10	11
11	2021-01-11	2021-01-12	11	12
12	2021-01-12	2021-01-13	12	13
13	2021-01-13	2021-01-14	13	14
14	2021-01-14	2021-01-15	14	15
15	2021-01-15	2021-01-16	15	16
16	2021-01-16	2021-01-17	16	17
17	2021-01-17	2021-01-18	17	18
18	2021-01-18	2021-01-19	18	19
19	2021-01-19	2021-01-20	19	20
20	2021-01-20		20	

应用

因为每月相同天数的数值都是一样的所有比例都是0，可以在测试窗口添加变量修改数值。

• 取环比
  当前月份相比上一月份销售额的增长值与上一月份销售额的百分比（month-to-month）。

Select s.*, round((s.dvalue - s.pdvalue) * 100 / s.pdvalue, 2) ratio2
  From (Select ymdate,
               lag(ymdate, 1, Null) over(Order By ymdate) pymdate,
               dvalue,
               lag(dvalue, 1, Null) over(Order By ymdate) pdvalue
          From (Select ymdate, Sum(dvalue) dvalue
                  From test_lagover
                 Group By ymdate)) s
 Where pymdate Is Not Null;

# 结果
1	202102	202101	210	210	0
2	202103	202102	210	210	0
3	202104	202103	210	210	0
4	202105	202104	210	210	0
5	202106	202105	210	210	0
6	202107	202106	210	210	0
7	202108	202107	210	210	0
8	202109	202108	210	210	0
9	202110	202109	210	210	0
10	202111	202110	210	210	0
11	202112	202111	210	210	0
12	202201	202112	210	210	0
13	202202	202201	210	210	0
14	202203	202202	210	210	0
15	202204	202203	210	210	0

• 取同比
  当前月份相比上一年度同一月份销售额的增长值与上一年度同一月份销售额的百分比（year-to-year）。

Select s.*, round((s.dvalue - s.pdvalue) * 100 / s.pdvalue, 2) ratio2
  From (Select ymdate,
               lag(ymdate, 12, Null) over(Order By ymdate) pymdate,
               dvalue,
               lag(dvalue, 12, Null) over(Order By ymdate) pdvalue
          From (Select ymdate, Sum(dvalue) dvalue
                  From test_lagover
                 Group By ymdate)) s
 Where pymdate Is Not Null;

# 结果
1	202201	202101	210	210	0
2	202202	202102	210	210	0
3	202203	202103	210	210	0
4	202204	202104	210	210	0
5	202205	202105	210	210	0
6	202206	202106	210	210	0
7	202207	202107	210	210	0
8	202208	202108	210	210	0
9	202209	202109	210	210	0
10	202210	202110	210	210	0
11	202211	202111	210	210	0
12	202212	202112	210	210	0
13	202301	202201	210	210	0
14	202302	202202	210	210	0
15	202303	202203	210	210	0