偏移类开窗用法
(以LAG为例):
LAG(COL_NAME,OFFSET,DEFVAL)OVER([partition ] order )
:向前偏移N行取数
COL_NAME:要分析的字段
OFFSET:偏移量 --默认偏移一行
DEFVAL:默认返回值 --默认返回空null
前N行数据: lag
后N行的数据: lead
用例数据:
create table BUSINESS
(
data_dt VARCHAR2(50),
day VARCHAR2(20),
week VARCHAR2(30),
amt NUMBER
)
--数据初始化
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-04', '星期一', '第一周', 3000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-05', '星期二', '第一周', 2000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-06', '星期三', '第一周', 1000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-07', '星期四', '第一周', 4000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-08', '星期五', '第一周', 6000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-09', '星期六', '第一周', 6000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-10', '星期日', '第一周', 7000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-11', '星期一', '第二周', 2000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-12', '星期二', '第二周', 3000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-13', '星期三', '第二周', 4000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-14', '星期四', '第二周', 2000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-15', '星期五', '第二周', 7000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-16', '星期六', '第二周', 5000);
insert into BUSINESS (data_dt, day, week, amt)
values ('2020-05-17', '星期日', '第二周', 7000);
commit;
----查询每天的营业额以及前一天的营业额
select t.*,
lag(amt,1,0)over(order by data_dt) lg,
lead(amt)over(partition by week order by data_dt) ld
from business t
查询结果:
偏移类开窗函数注意点:
1)分析函数名内必须包含要分析的内容,其他两项参数可以默认
2)分析子句内必须添加ORDER BY,且不能指定窗口
3)若不再有可供偏移的行,则返回默认值
4)偏移量不允许写负数
5)默认返回值与分析的字段数据类型要保持一致