窗口函数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

create table sales ( year number, sales number );   insert into sales values (2020,1000); insert into sales values (2021,2000); insert into sales values (2022,3000); insert into sales values (2023,4000); insert into sales values (2024,5000);   --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录 --unbounded：不受控制的，无限的 --preceding：在...之前 --following：在...之后<br> rows between unbounded preceding and unbounded following 表中的所有记录 rows between unbounded preceding and current row 是指第一行至当前行的汇总(默认) rows between current row and unbounded following 指当前行到最后一行的汇总 rows between 1 preceding and current row 是指当前行的上一行(rownum-1)到当前行的汇总 rows between 1 preceding and 2 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总     select year, sales, sum(sales)over(order by year) a, sum(sales)over(order by year rows between unbounded preceding and unbounded following) b, sum(sales)over(order by year rows between unbounded preceding and current row) c, sum(sales)over(order by year rows between current row and unbounded following) d, sum(sales)over(order by year rows between 1 preceding and current row) e, sum(sales)over(order by year rows between 1 preceding and 2 following) f from sales order by year <br>