06_窗口函数进阶(高级用法)

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

创建测试表并插入测试数据
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;     
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,923评论 6 498
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,154评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 161,775评论 0 351
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,960评论 1 290
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,976评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,972评论 1 295
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,893评论 3 416
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,709评论 0 271
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,159评论 1 308
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,400评论 2 331
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,552评论 1 346
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,265评论 5 341
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,876评论 3 325
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,528评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,701评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,552评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,451评论 2 352

推荐阅读更多精彩内容