求和函数，滑动向上/向下取值函数

1.sum(a) over ()对a列所有值进行求和
sum(a) over (order by b)根据b列排序后，求a列第一行到当前行的所有值的和

2.sum(a) over (partition by b,c)在b,c分组的结果上，进行a列所有值的求和
sum(a) over (partition by b,c order by d)同1类似，在b,c分组中根据d排序后，求a列第一行到当前行的所有值的和

3.lag(a,1,null) over ()向上滑动去a列当前行的上一行的数值，取不到的值为null
lag(a,n,10) over (order by b)根据b列排序后，取a列当前行的上n行的数据，取不到的值为10
lag(a,n,null) over (partition by b,c)在b,c分组的结果上，进行滑动取a列当前行的上n行数据，取不到的值为null
lag(a,n,null) over (partition by b,c order by d)在b,c分组中根据d排序后，求a列当前行的上n行数据，取不到的值为null

4.lead(a,n,null) over () 向下滑动取a列当前行的下n行的数值，取不到的值为null
lead(a,n,null) over (order by b)根据b列排序后，取a列当前行的下n行的数据，取不到的值为null
lead(a,n,null) over (partition by b,c)在b,c分组的结果上，进行滑动取a列当前行的下n行数据，取不到的值为null
lead(a,n,null) over (partition by b,c order by d)在b,c分组中根据d排序后，求a列当前行的下n行数据，取不到的值为null

示例：
准备数据
创建表

create table TEST
(
  id      VARCHAR2(20) default to_char(sysdate,'yyyyMMddhh24miss') not null,
  name    VARCHAR2(20),
  class   VARCHAR2(20),
  subject VARCHAR2(10),
  grade   NUMBER(5,1)
);
comment on table TEST
  is '测试';
-- Add comments to the columns 
comment on column TEST.name
  is '姓名';
comment on column TEST.class
  is '班级';
comment on column TEST.subject
  is '学科';
comment on column TEST.grade
  is '分数';

插入数据：

insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095400', '张三', '高一(1)班', '语文', 120.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095401', '张三', '高一(1)班', '数学', 140.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095402', '张三', '高一(1)班', '英语', 90.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095403', '张三', '高一(1)班', '理综', 200.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095404', '李四', '高一(1)班', '语文', 130.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095405', '李四', '高一(1)班', '数学', 125.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095406', '李四', '高一(1)班', '英语', 100.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095407', '李四', '高一(1)班', '理综', 150.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095408', '王五', '高一(2)班', '语文', 110.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095409', '王五', '高一(2)班', '数学', 60.5);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095410', '王五', '高一(2)班', '英语', 130.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095411', '王五', '高一(2)班', '文综', 254.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095412', '赵六', '高一(2)班', '语文', 70.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095413', '赵六', '高一(2)班', '数学', 56.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095414', '赵六', '高一(2)班', '英语', 30.0);
insert into TEST (ID, NAME, CLASS, SUBJECT, GRADE) values ('20200710095415', '赵六', '高一(2)班', '文综', 120.0);

查询表：

使用sql