窗口函数进阶(实用操作)

创建测试表并插入测试数据
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 ('2', 'Daniel', DATE'2019-01-02', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', DATE'2019-01-03', '120');
insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', DATE'2019-01-04', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', DATE'2019-01-05', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', DATE'2019-01-06', '140');
insert into Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', DATE'2019-01-07', '150');
insert into Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', DATE'2019-01-08', '80');
insert into Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', DATE'2019-01-09', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', DATE'2019-01-10', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', DATE'2019-01-10', '150');
COMMIT;

select * from Customer;


--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
--unbounded：不受控制的，无限的
--preceding：在...之前
--following：在...之后
--CURRENT ROW：当前行
--n PRECEDING：往前 n 行数据
--n FOLLOWING：往后 n 行数据
--UNBOUNDED：起点
--UNBOUNDED PRECEDING  表示从前面的起点
--UNBOUNDED FOLLOWING 表示到后面的终点

--需求1:根据visited_on分组排序,取累积的营业额
select A.*,
       SUM(a.amount) over(order by a.visited_on) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A
ORDER BY A.Visited_On;

--注意:
--rows窗口:适用于任何类型而且可以order by多列。

--range窗口: "range 100 preceding"
--这个子句只适用于number和date，而且只能order by一列。
--如果over()里asc排列，意思是[number-100,number]这样一个闭区间是它的窗口。
--如果over()里desc排列，意思是[number,number+100]这样一个闭区间是它的窗口。

--需求2:根据visited_on分组排序,取当前日期+前一天的营业额,第一天只有当天的营业额
--rows 1 preceding:将当前行和它前面的一行划为一个窗口，因此sum函数就作用在这两行上面
--如果去当前日期+前N天的营业额,那么改为rows N preceding 即可


--!!!!如果加where条件,是先走条件的,千万注意
select A.*,
       SUM(a.amount) over(order by a.visited_on rows 1 preceding) amount,
       SUM(a.amount) over(order by a.visited_on range 1 preceding) amount2
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A
ORDER BY A.Visited_On;

--需求3:根据visited_on分组排序,取当前日期+后一天的营业额,最后一天只有当天的营业额  order by 跟个desc即可
--取N和需求2一样的操作
select A.*,
       SUM(a.amount) over(order by a.visited_on desc rows 1 preceding) amount,
       SUM(a.amount) over(order by a.visited_on desc range 1 preceding) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A
ORDER BY A.Visited_On;

--需求4 取当天金额占总金额的比例

select A.*,
       SUM(a.amount) over(),
       A.Amount/SUM(a.amount) over() amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A
ORDER BY A.Visited_On;

--需求5: 求当前行的上一行(N)(rownum-1)到当前行的下一行(N)(rownum+1)的汇总    
--BETWEEN 1 PRECEDING AND 1 FOLLOWING  PRECEDING是前   FOLLOWING是后
--需要前后多少行的数据直接改即可
select A.*,
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A
ORDER BY A.Visited_On;


--需求6,如下
select A.*,
       --当前行到最后一行的汇总     
       SUM(a.amount) over(order by a.visited_on desc) amount,
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) amount2,
       --当前行到第一行的汇总
       SUM(a.amount) over(order by a.visited_on) amount3,
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) amount4
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A
ORDER BY A.Visited_On;

--需求7:如下
--(current row)当前行的意思
--rows between 2 PRECEDING and  前N条到current row当前行
--rows between current row and 2 FOLLOWING 当前行到后N条
select A.*,
       --取当前日期+前两天日期之和,没有前两天则是当前值,只有前一天则是0+N+N
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN 2 PRECEDING AND 0 FOLLOWING) amount,
       --取当前日期+前两天日期之和,没有前两天则是当前值,只有前一天则是0+N+N
       SUM(a.amount) over(order by a.visited_on  rows between 2 PRECEDING and current row) amount2,
       --取当前日期+后两天日期之和,没有后两天则是当前值,只有后一天则是N+N+0
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN 0 PRECEDING AND 2 FOLLOWING) amount3,
       --取当前日期+后两天日期之和,没有后两天则是当前值,只有后一天则是N+N+0
       SUM(a.amount) over(order by a.visited_on  rows between current row and 2 FOLLOWING) amount4
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A;

--需求8:取当前行前N行数据到最后一行的数据
select A.*,
       --取当前行前1行数据到最后一行的数据
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A;
         
--需求9:取当前行后N行数据到最后一行的数据,没有后N行的数据则为空
select A.*,
       --取当前行后N行数据到最后一行的数据,没有后N行的数据则为空
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A;
         
--需求10:取第一行数据到当前行前N行数据,当前行没有前N行的数据则为空
select A.*,
       --取第一行数据到当前行前N行数据,当前行没有前N行的数据则为空
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A;

--需求11:取第一行数据到当前行后N行数据,当前行没有后N时,改N可以不用计算
select A.*,
       --取第一行数据到当前行后N行数据,当前行没有后N时,改N可以不用计算
       SUM(a.amount) over(order by a.visited_on ROWS BETWEEN UNBOUNDED PRECEDING AND 1 following) amount
  from (select a.visited_on,
               SUM(a.amount) amount,
               row_number() over(order by a.visited_on) rn
          from Customer a
         group by a.visited_on
         order by a.visited_on) A;