当前位置:Gxlcms > 数据库问题 > Oracle分析函数

Oracle分析函数

时间:2021-07-01 10:21:17 帮助过:19人阅读

t as (select rownum*10 cnt from dual connect by rownum<5 union all select rownum*4010 from dual connect by rownum<3) select cnt, row_number()over(order by cnt) rn, rank()over(order by cnt) rk, dense_rank()over(order by cnt) drk from t

排名的几个分析函数

– row_number      – rank     – dense_rank

分组排名 

  1. <span style="color: #0000ff">select</span> dept_id, sale_date, goods_type, sale_cnt, row_number()<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> <br> dept_id <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sale_cnt <span style="color: #0000ff">desc</span>) rn, rank()<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> dept_id <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sale_cnt <span style="color: #0000ff">desc</span>) rk, <br> dense_rank()<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> dept_id <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sale_cnt <span style="color: #0000ff">desc</span><span style="color: #000000">) drk
  2. </span><span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">where</span> trunc(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">MM</span><span style="color: #ff0000">‘</span>)<span style="color: #808080">=</span>date<span style="color: #ff0000">‘</span><span style="color: #ff0000">2013-04-01</span><span style="color: #ff0000">‘</span>;

技术分享图片

排名分析函数要点 

– 排名分析函数不需要参数

– 排名分析函数里的order by子句是必须的

– partition by 和 order by后均可跟多列

– 只在没有partition by 的情况下,rownum才能做到和row_number同样的事情 

分析函数功能--相邻

业务上需要用到相邻行的场景 

  – 用于获取相邻行的数据,以便于进行相关计算,例如同比环比

  – 实现重复数据只输出第一个的需求

  – 实现重复数据只输出第一个和最后一个的需求

相邻类分析函数 

  – LAG和LEAD用于获取相邻行的数据,以便于进行相关计算

  – LAG 是取到排序后当前记录之前的记录

  – LEAD 是取到排序后当前记录之后的记录 

 查出同部门按字母正序姓名比自己大和小的雇员姓名各是啥?没有比自己姓名小的设为AAA ,没有比自己姓名大的设为ZZZ。

  1. <span style="color: #0000ff">select</span><span style="color: #000000"> deptno, ename ,
  2. lag(ename,</span><span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">AAA</span><span style="color: #ff0000">‘</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> ename) lower_name ,
  3. lead(ename,</span><span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">ZZZ</span><span style="color: #ff0000">‘</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> ename) higher_name
  4. </span><span style="color: #0000ff">from</span> emp;

技术分享图片

在前例基础上,部门编号只输出一次 

  1. <span style="color: #0000ff">select</span> (<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> deptno<span style="color: #808080">=</span> lag(deptno,<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> ename) <span style="color: #0000ff">then</span> <span style="color: #0000ff">null</span> <span style="color: #0000ff">else</span> deptno <span style="color: #0000ff">end</span><span style="color: #000000">) deptno
  2. , ename
  3. , lag(ename,</span><span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">AAA</span><span style="color: #ff0000">‘</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> ename) lower_name
  4. , lead(ename,</span><span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">ZZZ</span><span style="color: #ff0000">‘</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> ename) higher_name
  5. </span><span style="color: #0000ff">from</span><span style="color: #000000"> emp; </span><span style="color: #800000; font-weight: bold"><br></span>

相邻类分析函数的要点 

– LAG/LEAD(v, n, dv)里的n表示位移,必须是0或正整数,dv是在没有取到对应值时的默认值 。n默认是1,dv默认是null。

– 相邻类分析函数后面order by子句是必须的

– partition by 和 order by后均可跟多列

分析函数功能--统计

统计的业务需求

  – 当年各月的累计销售额

  – 每名销售人员当月的销售额与平均每名销售人员销售额的差值

  – XX货物每月的最高和最低销售额对应的部门

  – 获取相邻行内最近的一个非空值

  – ……

相关统计分析函数 

  – SUM

  – AVG

  – MAX/MIN

  – FIRST_VALUE/LAST_VALUE

  – …… 

相关统计分析函数--SUM 

 求出每个部门按月的累计销售额 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id, to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>) sale_month, <span style="color: #ff00ff">sum</span>(sale_cnt) month_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> dept_id, to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span><span style="color: #000000">))
  2. </span><span style="color: #0000ff">select</span> dept_id, sale_month, month_sale_cnt, <span style="color: #ff00ff">sum</span>(month_sale_cnt)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> dept_id <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sale_month) cum_month_sale_cnt <span style="color: #0000ff">from</span> t;

技术分享图片

 求出每个部门按售出货物类别的累计销售额(按货物类别代码正序排列)以及每个货物按部门 的累计销售额(按部门编号正序排列) 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id, goods_type, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> dept_id, goods_type)
  2. </span><span style="color: #0000ff">select</span> dept_id, goods_type, goods_sale_cnt, <span style="color: #ff00ff">sum</span>(goods_sale_cnt)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> dept_id <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> goods_type) cum_gsc_goods, <span style="color: #ff00ff">sum</span>(goods_sale_cnt)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> goods_type <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> dept_id) cum_gsc_dept <span style="color: #0000ff">from</span><span style="color: #000000"> t; <br></span>

技术分享图片

排序规则=select中排在最后的规则

相关统计分析函数--AVG

 求出每个部门每种货物的销售额与该货物在公司各部门平均销售额之间的差值 

 分析函数写法: 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id, goods_type, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> dept_id, goods_type)
  2. </span><span style="color: #0000ff">select</span> dept_id, goods_type, goods_sale_cnt, <span style="color: #ff00ff">round</span>(<span style="color: #ff00ff">AVG</span>(goods_sale_cnt)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> goods_type),<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">) avg_goods_sale_cnt
  3. ,goods_sale_cnt</span><span style="color: #808080">-</span><span style="color: #ff00ff">round</span>(<span style="color: #ff00ff">AVG</span>(goods_sale_cnt) <span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> goods_type),<span style="color: #800000; font-weight: bold">2</span>) dv_goods_sale_cnt <span style="color: #0000ff">from</span> t;

传统方式写法: 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id, goods_type, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> dept_id, goods_type)
  2. ,t1 </span><span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> goods_type, <span style="color: #ff00ff">round</span>(<span style="color: #ff00ff">avg</span>(goods_sale_cnt),<span style="color: #800000; font-weight: bold">2</span>) avg_goods_sale_cnt <span style="color: #0000ff">from</span> t <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> goods_type)
  3. </span><span style="color: #0000ff">select</span> a.dept_id, a.goods_type, goods_sale_cnt, avg_goods_sale_cnt, goods_sale_cnt<span style="color: #808080">-</span><span style="color: #000000">avg_goods_sale_cnt dv_goods_sale_cnt
  4. </span><span style="color: #0000ff">from</span> t a, t1 b <span style="color: #0000ff">where</span> a.goods_type<span style="color: #808080">=</span><span style="color: #000000">b.goods_type
  5. </span><span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> <span style="color: #800000; font-weight: bold">2</span>,<span style="color: #800000; font-weight: bold">1</span>;

不同写法的比较

技术分享图片

一般情况下,分析函数性能优于传统写法

 相关统计分析函数--MAX/MIN 

 货物G01每月的最高和最低销售额对应的部门(如有多个部门按部门ID列出最小的一个,如某部门某月无销售额则不做统计) 

传统方式写法: 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id , to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>) sale_month, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">where</span> goods_type<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">G01</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> dept_id, to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span><span style="color: #000000">))
  2. , t1 </span><span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> sale_month, <span style="color: #ff00ff">max</span>(goods_sale_cnt) max_gsc, <span style="color: #ff00ff">min</span>(goods_sale_cnt) min_gsc <span style="color: #0000ff">from</span> t <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span><span style="color: #000000"> sale_month)<br>
  3. </span><span style="color: #0000ff">select</span> a.sale_month, <span style="color: #ff00ff">min</span>(<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> goods_sale_cnt<span style="color: #808080">=</span>max_gsc <span style="color: #0000ff">then</span> dept_id <span style="color: #0000ff">end</span>) max_dept_id, <span style="color: #ff00ff">min</span>(<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> goods_sale_cnt<span style="color: #808080">=</span>min_gsc <span style="color: #0000ff">then</span> dept_id <span style="color: #0000ff">end</span>) min_dept_id <br><span style="color: #0000ff">from</span> t a, t1 b <span style="color: #0000ff">where</span> a.sale_month<span style="color: #808080">=</span>b.sale_month <span style="color: #808080">and</span> (goods_sale_cnt<span style="color: #808080">=</span>min_gsc <span style="color: #808080">or</span> goods_sale_cnt<span style="color: #808080">=</span>max_gsc ) <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> a.sale_month;

分析函数写法: 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id , to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>) sale_month, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt, <span style="color: #ff00ff">max</span>(<span style="color: #ff00ff">sum</span>(sale_cnt))<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>)) max_gsc, <br><span style="color: #ff00ff">min</span>(<span style="color: #ff00ff">sum</span>(sale_cnt))<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>)) min_gsc <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">where</span> goods_type<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">G01</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> dept_id, to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYYMM</span><span style="color: #ff0000">‘</span>))<br><br><span style="color: #0000ff">select</span> a.sale_month, <span style="color: #ff00ff">min</span>(<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> goods_sale_cnt<span style="color: #808080">=</span>max_gsc <span style="color: #0000ff">then</span> dept_id <span style="color: #0000ff">end</span>) max_dept_id, <span style="color: #ff00ff">min</span>(<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> goods_sale_cnt<span style="color: #808080">=</span>min_gsc <span style="color: #0000ff">then</span> dept_id <span style="color: #0000ff">end</span>) min_dept_id <br><span style="color: #0000ff">from</span> t a <span style="color: #0000ff">where</span> (goods_sale_cnt<span style="color: #808080">=</span>min_gsc <span style="color: #808080">or</span> goods_sale_cnt<span style="color: #808080">=</span>max_gsc ) <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> a.sale_month;

最直接的写法: 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id , to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>) sale_month, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">where</span> goods_type<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">G01</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> dept_id, to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span><span style="color: #000000">))
  2. </span><span style="color: #0000ff">select</span> a.sale_month, <span style="color: #ff00ff">min</span>(dept_id)keep(dense_rank first <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> goods_sale_cnt <span style="color: #0000ff">desc</span>) max_dept_id, <span style="color: #ff00ff">min</span>(dept_id)keep(dense_rank first <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> goods_sale_cnt) min_dept_id <span style="color: #0000ff">from</span> t a <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> a.sale_month;

在这种写法里,MAX/MIN是作为聚合函数而不是分析函数去用的,后面的FIRST函数也是聚合函数

 

查出货物G03在销售当天及前十二天的最大销售额(按部门和整个公司分别求出) 

  1. <span style="color: #0000ff">select</span> dept_id, sale_date,sale_cnt, <span style="color: #ff00ff">max</span>(sale_cnt)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> dept_id <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sale_date range interval <span style="color: #ff0000">‘</span><span style="color: #ff0000">12</span><span style="color: #ff0000">‘</span> <span style="color: #ff00ff">day</span><span style="color: #000000"> preceding) max_dept_sale_cnt,
  2. </span><span style="color: #ff00ff">max</span>(sale_cnt)<span style="color: #0000ff">over</span>(<span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sale_date range numtodsinterval(<span style="color: #800000; font-weight: bold">12</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">DAY</span><span style="color: #ff0000">‘</span>) preceding) max_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">where</span> goods_type<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">G03</span><span style="color: #ff0000">‘</span><span style="color: #000000">; </span>

技术分享图片

 

相关统计分析函数--FIRST/LAST 

货物G01每月的最高和最低销售额对应的部门(如有多个部门按部门ID列出最小的一个,如某部门某月无销售额则不做统计) 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> dept_id , to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span>) sale_month, <span style="color: #ff00ff">sum</span>(sale_cnt) goods_sale_cnt <span style="color: #0000ff">from</span> lw_sales <span style="color: #0000ff">where</span> goods_type<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">G01</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> dept_id, to_char(sale_date,<span style="color: #ff0000">‘</span><span style="color: #ff0000">YYYY-MM</span><span style="color: #ff0000">‘</span><span style="color: #000000">)), <br>t1 </span><span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> a.sale_month, <span style="color: #ff00ff">min</span>(dept_id)keep(dense_rank first <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> goods_sale_cnt <span style="color: #0000ff">desc</span>) <span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> a.sale_month) max_dept_id, <span style="color: #ff00ff">min</span>(dept_id)keep(dense_rank first <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> goods_sale_cnt) <span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> a.sale_month) min_dept_id <span style="color: #0000ff">from</span><span style="color: #000000"> t a) <br><br></span><span style="color: #0000ff">select</span> sale_month, <span style="color: #ff00ff">min</span>(max_dept_id), <span style="color: #ff00ff">min</span>(min_dept_id) <span style="color: #0000ff">from</span> t1 <span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> sale_month;

 

 在这种写法里,MAX/MIN依然是作为聚合函数而不是分析函数去用的,但后面的FIRST函数是作为分析函数去用的 

相关统计分析函数--FIRST_VALUE和LAST_VALUE 

 查出所有雇员的雇员职位、编号、姓名、薪水以及同职位上薪水最高和最低的雇员的雇员编号 (薪水相同的,按雇员编号倒序排列) 

  1. <span style="color: #0000ff">select</span><span style="color: #000000"> job, empno, ename, sal,
  2. last_value(empno)</span><span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> job <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal, empno <span style="color: #0000ff">desc</span> rows <span style="color: #808080">between</span> unbounded preceding <span style="color: #808080">and</span><span style="color: #000000"> unbounded following) lv,
  3. first_value(empno)</span><span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> job <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal, empno <span style="color: #0000ff">desc</span> rows <span style="color: #808080">between</span> unbounded preceding <span style="color: #808080">and</span> unbounded following) fv <span style="color: #0000ff">from</span> emp;

 查出所有雇员的雇员职位、编号、姓名、薪水以及同职位上薪水最高和最低的雇员的雇员编号 (薪水相同的,按雇员编号倒序排列) 

  1. <span style="color: #0000ff">select</span><span style="color: #000000"> job, empno, ename, sal,
  2. </span><span style="color: #ff00ff">max</span>(empno)keep(dense_rank last <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal, empno <span style="color: #0000ff">desc</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span><span style="color: #000000"> job) lv,
  3. </span><span style="color: #ff00ff">max</span>(empno)keep(dense_rank first <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> sal, empno <span style="color: #0000ff">desc</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> job) fv <span style="color: #0000ff">from</span><span style="color: #000000"> emp; <br></span>

 在"相邻"最后一例基础上,补全缺失的部门编号 

  1. <span style="color: #0000ff">with</span> t <span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> (<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> deptno<span style="color: #808080">=</span> lag(deptno,<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> ename) <span style="color: #0000ff">then</span> <span style="color: #0000ff">null</span> <span style="color: #0000ff">else</span> deptno <span style="color: #0000ff">end</span><span style="color: #000000">) deptno
  2. , ename
  3. , lag(ename,</span><span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">AAA</span><span style="color: #ff0000">‘</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> ename) lower_name
  4. , lead(ename,</span><span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">ZZZ</span><span style="color: #ff0000">‘</span>)<span style="color: #0000ff">over</span>(partition <span style="color: #0000ff">by</span> deptno <span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span><span style="color: #000000"> ename) higher_name
  5. </span><span style="color: #0000ff">from</span><span style="color: #000000"> emp)
  6. , t1 </span><span style="color: #0000ff">as</span> (<span style="color: #0000ff">select</span> t.<span style="color: #808080">*</span>, rownum rn <span style="color: #0000ff">from</span><span style="color: #000000"> t)
  7. </span><span style="color: #0000ff">select</span> (<span style="color: #ff00ff">case</span> <span style="color: #0000ff">when</span> deptno <span style="color: #0000ff">is</span> <span style="color: #808080">not</span> <span style="color: #0000ff">null</span> <span style="color: #0000ff">then</span> deptno <span style="color: #0000ff">else</span> last_value(deptno ignore nulls)<span style="color: #0000ff">over</span>(<span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> rn) <span style="color: #0000ff">end</span><span style="color: #000000">) deptno
  8. , ename, lower_name, higher_name </span><span style="color: #0000ff">from</span> t1;

统计分析函数要点 

– 可以有partition by、order by和range/rows子句(此即windowing子句)

– 以上子句都不是必须出现的,但若出现windowing子句则其前必须出现order by子句

– 没有order by都就是分组统计,有order by就是分组累计

– FIRST/LAST这两个分析函数后面只能跟partition by子句

– FIRST_VALUE和LAST_VALUE用于获取一组有序的数据中的第一个和最后一个值,和 MAX/MIN以及FIRST/LAST函数很像,但要注意区别

– COUNT也可以作为分析函数,这和SUM/MAX等差不多,另外一个函数叫 RATIO_TO_REPORT,在做报表或饼状图的时候很有用,俩都自学吧! 

 

Oracle分析函数

标签:注意   某月   unbound   获取   png   饼状图   职位   lse   公司   

人气教程排行