08 分析函数

分析函数剖析

分析函数具有3个基本组成部分, 分区子句, 排序子句以及开窗子句, 基本语法是:

function1 (argument1, argument2, …argumentN)

    over ([partition-by clause] [order-by-clause][windowing-clause])

function1 是所调用的接收0个或多个参数的分析函数. 分区子句按照分区列的值对数据行进行分组, 所有分区列的值相同的数据行被组合为一个数据分区.

从操作上来说, 数据行按照分区列进行排序并被分为数据分区. 例如 partition by product, country 使用 product 和 country 列的值进行分区.

排序通过一列或一个表达式的值来对数据分区中的行进行排序, 在一个分析型的SQL语句中, 一个数据行在数据分区中的位置是很重要的, 并且由排序子句来控制的. 因为在分区子句中按照分区列的值来进行排序, 你实际上最终得到的是按照分区子句和排序子句中指定的列来进行排序后的结果.

排序可以按照升序或降序, 使用 NULLS FILRST 或 NULLS LAST 可以将空值放到数据分区的最上面或最下面.

select region_id, customer_id, sum(customer_sales) cust_total,

        sum(sum(customer_sales)) over(partion by region_id) reg_total,

        rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank

   from user_order

  group by region_id, customer_id;

开窗子句指定了分析函数进行运算的数据子集, 这个窗口可以是动态的并且被很恰当的成为滑动窗口. 你可以使用窗口说明子句还指定滑动窗口的上下边界条件.

[ROWS | RANGE] BETWEEN <start expr> AND <end expr>

start expr: [UNBOUNDED PRECEDING | CURRENT ROW | n PERCEDING | n FOLLOWING]

end expr: [UNBOUNDED FOLLOWING | CURRENT ROW | n PRECEDING | n FOLLOWING]

PRECEDING 窗口的上边界.

FOLLOWING 或 CURRENT ROW 指定了窗口的下边界.

分析函数不能进行嵌套, 但可以通过将包含的SQL语句放在内嵌视图中, 然后在视图之外使用分析函数来实现嵌套效果.

函数列表

image

分析函数在写需要在不同层级上对数据进行聚合的复杂查询报表时, 是非常有用的.

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 in (‘Australia’) and product = ‘Xtend Memory’

order by product, country, year, week

需要对同样的数据进行不同级别的聚合

需要再表内将多条数据和同一条数据进行多次比较

需要再排序完的结果集上进行额外的过滤操作

举例:

create table earnings -- 打工赚钱表
(
  earnmonth varchar2(6), -- 打工月份
  area varchar2(20), -- 打工地区
  sno varchar2(10), -- 打工者编号
  sname varchar2(20), -- 打工者姓名
  times int, -- 本月打工次数
  singleincome number(10,2), -- 每次赚多少钱
  personincome number(10,2) -- 当月总收入
)
insert into earnings values('200912','北平','511601','大魁',11,30,11*30);
insert into earnings values('200912','北平','511602','大凯',8,25,8*25);
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);

insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);
insert into earnings values('200912','金陵','511304','小俐',0,18,0);
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);

insert into earnings values('201001','北平','511601','大魁',0,30,0);
insert into earnings values('201001','北平','511602','大凯',14,25,14*25);
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);

insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);

按照月份, 统计每个地区的总收入

 select earnmonth,
        area,
        sum(personincome) total_salary
 from earnings
group by earnmonth,
         area

按照月份, 地区, 求打工收入排序 rank() over

 select earnmonth,
        area,
        sname,
        personincome,
        rank() over(partition by earnmonth, area 
                    order by personincome desc)
   from earnings;

image

注意排序: 1, 1, 3 跳跃式排序, 即如果排序的列的值相同, 那么会产生跳跃排序

以上, 如果换成 dense_rank(), 其他都相同, 那么排序结果是 1,1,2 没有跳跃排序, 并列的值, 还是显示同样的1.

最后, 如果上边换成是 row_number(), 其他的都相同, 那么排序结果是 1,2,3 即便是排序列的值相同, 但是显示的rank也是不同的.

求打工者收入总和, 按收入多少排序sum()

 select earnmonth,
        area,
        sname,
        sum(personincome) over(partition by earnmonth, area 
                    order by personincome) 总收入
   from earnings;

显示的数据是:

image

第一行: 大亮的收入是: 132

第二行: 得到的是前面所有行 与 本行的收入和, 即 大亮 + 上一行(小东) = 319.5

第三行: 得到额是前面所有行 与 本行的收入和. 即 大凯 + 上一行(大亮+小东) = 519.5

max, min, avg 和 sum 综合运用

select distinct earnmonth 月份, area 地区,
    max(personincome) over (partition by earnmonth, area) High,
    min(personincome) over (partition by earnmonth, area) Low,
    avg(personincome) over (partition by earnmonth, area) average,
    sum(personincome) over (partition by earnmonth, area) total
 from earnings;

lag 和 lead

求出每个打工者上个月和下个月没有赚钱(personincome大于0即为赚钱)

select earnmonth, sname,
        lag(decode(nvl(personincome, 0),
                    0, 'no incoming',
                    'incoming'),
            1, 0) over (partition by sname order by earnmonth) 上月,
        lead(decode(nvl(personincome, 0),
                    0, 'no incoming',
                    'incoming'),
            1, 0) over (partition by sname order by earnmonth) 下月
  from earnings;

lag 和 lead 函数可以在一次查询中取出某个字段的前N行和后N行数据(可以是其他字段的数据, 比如根据字段甲查询上一行或下两行的字段乙)

lag(value_expression [,offset] [,default]) over (partion clause, order clause)

其中, vale_expression: 可以是一个字段或一个内建函数. offset 是正整数, 默认为1, 指往前或往后几条记录, 因组内第一条记录没有之前行, 最后一行没有之后行, default 就是处理这样的信息, 默认为空.

举例2:

分析函数定义: 分析函数是oracle专门用于解决复杂报表统计需求的功能强大的函数, 它可以在数据中进行分组然后计算基于组的某种统计值, 并且每一组的每一行都可以返回一个统计值.

分析函数和聚合函数的不同之处? 普通的聚合函数用 group by 分组, 每个分组返回一个统计值, 而这个统计值必须是基于这整个分组的, 而分析函数采用 partition by 分组, 并且每组每行都可以返回一个统计值.

分析函数形式, 分析函数带有一个开窗函数 over(), 包含三个分析子句: partition by, order by , rows 等,

分析函数举例: scott 模式

1. 显示各部门员工的工资, 并附带显示该部分的最高工资

 select empno,
        ename,
        deptno,
        sal,
        max(sal)over(partition by deptno 
                     order by sal 
                     rows between unbounded preceding and unbounded following ) max_sal
   from emp;
select empno,
        ename,
        deptno,
        sal,
        last_value(sal)over(partition by deptno 
                            order by sal
                            rows between unbounded preceding and unbounded following ) max_sal
   from emp;

以上, 两个查询的运行结果是:

image

count() over ()  统计个数

在日常工作生活中, 我们经常碰到这样的查询, 找出排名前 5 位的订单客户 等

select *

  from (select region_id, customer_id, sum(customer_sales) cust_total,

          rank() over(order by sum(customer_sales) desc NULLS LAST) rank

     from user_order

    group by region_id, customer_id)

where rank <= 3;

按层次查询: ntile(5)

找出订单总额排名前 1/5 的客户

select region_id, customer_id,

        ntile(5) over (order by sum(customer_sales) desc) til

  from user_order

group by region_id, customer_id;

Ntil 函数为各个记录在记录集中的排名计算比例, 我们看到所有的记录被分成5个等级, 那么假如我们需要前 1/5 的记录则只需要截取 til 的值为1的记录就可以了.

Oracle 分析函数 整理笔记

开窗函数补充

over(order by salary range between 50 preceding and 150 following) 每行对应的数据窗口是之前行幅度值不超过50, 之后行幅度值不超过150;

over(order by salary rows between 50 preceding and 150 following) 每行对应的数据窗口是之前行幅度值不超过50行, 之后150行

over(order by salary rows between unbounded preceding and unbounded following) 每行对应数据窗口从第一行到最后一行.

一个 range 的举例:

计算每个员工在按薪水排序中当前行附近薪水在[n-50, n+150]之间的行数, n表示当前行的薪水.

select ename, sal, count(*) over()as cnt1,
        count(*) over (order by sal) as cnt2,
        count(*) over (order by sal range between 50 preceding and 150 following)as cnt3 from emp;

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/moveofgod/p/4221399.html