窗口函数进阶(实用操作)
创建测试表并插入测试数据
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;