分析函数之窗口子句(r4笔记第3天)

关于分析函数,可能大家基本都是从row_number()开始了解到的。分析函数的使用在某种程度上可以避免自连接,使得原本较为繁琐复杂的查询一下子变得精简起来。 分析函数分为分区子句,排序子句,和窗口子句,对于窗口子句来说,可能开始比较难懂,这部分的使用也尤为重要。 还是先举个例子,然后基于例子再来简单分析一下分析函数。 我们创建一个测试表sales_fact create table sales_fact( product varchar2(200) not null, country varchar2(100), region varchar2(100), year number, week number, sale number(10,2) );

然后使用以下的pl/sql插入一部分数据,我们来针对美国的牛肉贸易来做一个简单的分析:) declare tmp_sql varchar2(1000); begin for tmp_year in 2012..2014 loop for i in 1..50 loop insert into sales_fact values('BEEF','USA','NOUTH',tmp_year,i,abs(mod(dbms_random.random,12)*100)); end loop; end loop; end; /

使用分析函数中的sum,这个sum和平时使用的sum还是有很大的不同。这个sum会按照年份来统计自1月份到当前月份的销售额。比如2012年2月的累计销售额就是100+400=500

#######  sum #############
select year,week,sale,
sum(sale) over(
                partition by product,country,region,year 
               order by  week
 rows between  unbounded preceding and current row
               )  running_sum_ytd
from sales_fact
where country='USA' and product='BEEF' and  year in (2012,2013)
order by product,country,year,week;
      YEAR        WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ----------  ---------------
      2012          1        400             400
      2012          2         100             500
       2012          3        600            1100
      2012          4         100            1200
      2012          5        200            1400
       2012          6          0            1400
      2012          7         100            1500
      2012          8        600            2100
       2012          9        300            2400
      2012         10         700            3100
      2012         11        400             3500
......
      YEAR       WEEK       SALE RUNNING_SUM_YTD
----------  ---------- ---------- ---------------
      2012         45         300           22900
      2012         46        900           23800
       2012         47        300           24100
      2012         48         800           24900
      2012         49        400           25300
       2012         50        100           25400
      2013          1         100             100
      2013          2        600             700
       2013          3        800            1500
      2013          4        1000            2500
      2013          5       1000            3500
对于上面的查询我们只修改一处。把rows between  unbounded preceding and current row修改为rows between unbounded preceding and unbounded  following 
输出结果会大大不同。原因在于rows  between unbounded preceding and current  row是一种窗口函数,是相关分析函数的默认值,如果知道那个为unbounded  following就会统计自1月份到12月份的销售额。

select year,week,sale,
sum(sale)  over(
               partition by product,country,region,year  
               order by week
 rows between unbounded preceding and unbounded  following 
               ) running_sum_ytd
from  sales_fact
where country='USA' and product='BEEF'  and year in  (2012,2013)
order by product,country,year,week;
     YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ----------  ---------- ---------------
      2012          1        400           25400
      2012          2         100           25400
      2012          3        600           25400
       2012          4        100           25400
      2012          5         200           25400
      2012          6          0           25400
       2012          7        100           25400
      2012          8         600           25400
      2012          9        300           25400
       2012         10        700           25400
      2012         11         400           25400
...
      YEAR       WEEK       SALE  RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
       2012         45        300           25400
      2012         46         900           25400
      2012         47        300           25400
       2012         48        800           25400
      2012         49         400           25400
      2012         50        100           25400
       2013          1        100           28700
      2013          2         600           28700
      2013          3        800           28700
       2013          4       1000           28700
      2013          5        1000           28700

对于max的使用,情况也是类似。我们可以根据需要来选择数据的范围来得到最大值。
####### max  ############
select year,week,sale,
max(sale) over(
                partition by product,country,region,year
               order by  week
      rows between  unbounded preceding and unbounded following
               ) max_sale  
from sales_fact
where country='USA' and product='BEEF' and year in  (2012,2013)
order by product,country,year,week;
      YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
      2012          1        400       1100
       2012          2        100       1100
      2012          3        600        1100
      2012          4        100       1100
      2012           5        200       1100
      2012          6          0       1100
       2012          7        100       1100
      2012          8        600        1100
      2012          9        300       1100
      2012          10        700       1100
      2012         11        400        1100
...
      YEAR       WEEK       SALE   MAX_SALE
----------  ---------- ---------- ----------
      2012         45        300        1100
      2012         46        900       1100
      2012          47        300       1100
      2012         48        800       1100
       2012         49        400       1100
      2012         50        100        1100
      2013          1        100       1100
      2013           2        600       1100
      2013          3        800       1100
       2013          4       1000       1100
      2013          5       1000        1100

比如2012年第1周的销售额是400,最高销售额是的当年的1100. YEAR WEEK SALE MAX_SALE ---------- ---------- ---------- ---------- 2012 1 400 1100 再来看看另外一个Max的使用。不同之处在于窗口函数的部分。

select  year,week,sale,
max(sale) over(
               partition by  product,country,region,year
               order by  week
  rows between  unbounded preceding and current row 
               ) max_sale  
from sales_fact
where country='USA' and product='BEEF'  and year in  (2012,2013)
order by product,country,year,week;
     YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
      2012          1        400        400
       2012          2        100        400
      2012          3        600         600
      2012          4        100        600
      2012           5        200        600
      2012          6          0        600
       2012          7        100        600
      2012          8        600         600
      2012          9        300        600
      2012          10        700        700
      2012         11        400         700
...
      YEAR       WEEK       SALE   MAX_SALE
----------  ---------- ---------- ----------
      2012         45        300        1100
      2012         46        900       1100
      2012          47        300       1100
      2012         48        800       1100
       2012         49        400       1100
      2012         50        100        1100
      2013          1        100        100
      2013           2        600        600
      2013          3        800        800
       2013          4       1000       1000
      2013          5       1000        1000

比如2012年第2周,相比于第2周来说,最高销售额是第1周的400。第3周的时候相比第1周,第2周,最高销售额是第3周的600.

     YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
      2012          1        400        400
       2012          2        100        400
      2012          3        600         600

从实际的使用角度来说,使用rows between unbounded preceding and current row 得到的数据是截止到指定时间的最大值,而rows between unbounded preceding and unbounded following得到的是历史数据最大值。 对于窗口函数的使用不限于此,我们还可以指定更细粒度的数据区间。 像rows between 2 preceding and 2 following 比较的数据就是当前行的前2行和后2行对应的区间的数据。

select  year,week,sale,
max(sale) over(
               partition by  product,country,region,year
               order by  week
   rows between 2  preceding and 2 following 
               ) max_sale
from  sales_fact
where country='USA' and product='BEEF'  and year in  (2012,2013)
order by product,country,year,week;
     YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
      2012          1        400        600
       2012          2        100        600
      2012          3        600         600
    2012          4         100        600
       2012          5        200        600
      2012          6          0         600
      2012           7        100        600
       2012          8        600        700
      2012          9         300        700
      2012         10        700        700
       2012         11        400        700
...
      YEAR       WEEK        SALE   MAX_SALE
---------- ---------- ---------- ----------
       2012         45        300       1000
      2012         46        900        1000
      2012         47        300        900
      2012          48        800        900
      2012         49        400        800
       2012         50        100        800
      2013          1        100         800
      2013          2        600       1000
      2013           3        800       1000
      2013          4       1000       1000
       2013          5       1000       1000

比如说对于2012年第6中,销售额为0,但是在前2周和后2周的区间范围内,销售额最大值为600.

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-01-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

细致入微:Oracle RAC DRM引起性能问题案例一则

熊军(老熊) 云和恩墨西区总经理 Oracle ACED,ACOUG核心会员 客户一套运行在Oracle 10.2.0.5 RAC上的系统,间歇性地出现性能问...

42670
来自专栏数据和云

Oracle初学者入门指南-什么是 Metalink 或 MOS ?

身为一个Oracle DBA,你可能经常看到老DBA们讲Metalink或者MOS,你必须知道这是什么。 Metalink是Oracle的官方技术支持站点,其...

37290
来自专栏数据和云

极限优化:从75到2000,由技能到性能提升岂止20倍

崔华,网名 dbsnake Oracle ACE Director,ACOUG 核心专家 编辑手记:感谢崔华授权我们独家转载其精品文章,也欢迎大家向“Oracl...

27440
来自专栏数据和云

数据恢复:如何恢复Linux中意外删除的Oracle和MySQL数据库

张乐奕 云和恩墨副总经理,Oracle ACE总监,ACOUG 联合创始人 没有删除过数据库的DBA职业生涯是不完整的,删除过数据库还能幸存的DBA一定是订阅...

35360
来自专栏数据和云

数据时代:大数据终将改变人类的商业行为从而改变世界

编者按:2016数据库技术大会下周即将开幕,这篇文章是大会记者的采访稿,在此整理再次分享给大家。 “人类正从IT时代走向DT(Data technology)...

29980
来自专栏数据和云

无所不能:Oracle的数据库文件系统DBFS是什么鬼?

张乐奕 云和恩墨副总经理,Oracle ACE总监,ACOUG 联合创始人 在Oracle11g新增了一个特别的功能—DBFS,从字面翻译,DBFS=DATA...

622100
来自专栏ytkah

Excel表格的35招必学秘技[配图]

一、让数据按需排序   如果你要将员工按其所在的部门进行排序,这些部门名称既的有关信息不是按拼音顺序,也不是按笔画顺序,怎么办?可采用自定义序列来排序。  ...

86880
来自专栏数据和云

返璞归真:如何判断一个初始化参数是否来自默认设置

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 Oracle初...

29970
来自专栏数据和云

故障恢复:从未知错误ORA-600 [3712]以猜测推理达成恢复

李真旭 Oracle ACE,云和恩墨技术专家 个人博客:www.killdb.com 在Oracle数据库的日常维护中,我们可能经常会遇到一些从未见过的错误,...

32860
来自专栏数据和云

与时俱进:ASM内存管理与创建表空间之ORA-569错误解决

杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE总监,ITPUB Oracle数据库管理版版主 在一个测试数据库上创建表空...

427100

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励