`
winie
  • 浏览: 222416 次
  • 性别: Icon_minigender_1
  • 来自: 重庆
社区版块
存档分类
最新评论

分析函数(OVER)

阅读更多
Oracle分析函数简介:

在日常的生产环境中,我们接触得比较多的是OLTP系统(即Online Transaction Process),这些系统的特点是具备实时要求,或者至少说对响应的时间多长有一定的要求;其次这些系统的业务逻辑一般比较复杂,可能需要经过多次的运算。比如我们经常接触到的电子商城。

在这些系统之外,还有一种称之为OLAP的系统(即Online Aanalyse Process),这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大,对实时响应的要求不高或者根本不关注这方面的要求,以查询、统计操作为主。

我们来看看下面的几个典型例子:
①查找上一年度各个销售区域排名前10的员工
②按区域查找上一年度订单总额占区域订单总额20%以上的客户
③查找上一年度销售最差的部门所在的区域
④查找上一年度销售最好和最差的产品

我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同,具体有:

①需要对同样的数据进行不同级别的聚合操作
②需要在表内将多条数据和同一条数据进行多次的比较
③需要在排序完的结果集上进行额外的过滤操作

二、Oracle分析函数简单实例:

下面我们通过一个实际的例子:按区域查找上一年度订单总额占区域订单总额20%以上的客户,来看看分析函数的应用。

【1】测试环境:

SQL> desc orders_tmp;
名称                                      是否为空? 类型
----------------------------------------- -------- -------------

CUST_NBR                                           NUMBER(5)
REGION_ID                                          NUMBER(5)
SALESPERSON_ID                                     NUMBER(5)
YEAR                                               NUMBER(4)
MONTH                                              NUMBER(2)
TOT_ORDERS                                         NUMBER(7)
TOT_SALES                                          NUMBER(11,2)


【2】测试数据:

SQL> select * from orders_tmp;
CUST_NBR  REGION_ID SALESPERSON_ID    YEAR   MONTH TOT_ORDERS  TOT_SALES
-------- ---------- -------------- ------- ------- ---------- ----------
      11          7             11    2001       7          2      12204
       4          5              4    2001      10          2      37802
       7          6              7    2001       2          3       3750
      10          6              8    2001       1          2      21691
      10          6              7    2001       2          3      42624
      15          7             12    2000       5          6         24
      12          7              9    2000       6          2      50658
       1          5              2    2000       3          2      44494
       1          5              1    2000       9          2      74864
       2          5              4    2000       3          2      35060
       2          5              4    2000       4          4       6454
       2          5              1    2000      10          4      35580
       4          5              4    2000      12          2      39190


【3】测试语句:

SQL> select o.cust_nbr customer,
  2         o.region_id region,
  3         sum(o.tot_sales) cust_sales,
  4         sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  5  from orders_tmp o
  6  where o.year = 2001
  7  group by o.region_id, o.cust_nbr;

  CUSTOMER     REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
         4          5      37802        37802
         7          6       3750        68065
        10          6      64315        68065
        11          7      12204        12204


三、分析函数OVER解析:

请注意上面的绿色高亮部分,group by的意图很明显:将数据按区域ID,客户进行分组,那么Over 这一部分有什么用呢?假如我们只需要统计每个区域每个客户的订单总额,那么我们只需要group by o.region_id, o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额,这一点和前面的不同:需要在前面分组的基础上按区域累加。很显然 group by和sum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)。

这就是over函数的作用了!它的作用是告诉SQL引擎:按区域对数据进行分区,然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。

现在我们已经知道2001年度每个客户及其对应区域的订单总额,那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了

SQL> select *
  2     from (select o.cust_nbr customer,
  3             o.region_id region,
  4             sum(o.tot_sales) cust_sales,
  5             sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  6           from orders_tmp o
  7           where o.year = 2001
  8           group by o.region_id, o.cust_nbr) all_sales
  9  where all_sales.cust_sales > all_sales.region_sales * 0.2;

  CUSTOMER     REGION CUST_SALES REGION_SALES
---------- ---------- ---------- ------------
         4          5      37802        37802
        10          6      64315        68065
        11          7      12204        12204


现在我们已经知道这些大客户是谁了!哦,不过这还不够,如果我们想要知道每个大客户所占的订单比例呢?看看下面的SQL语句,只需要一个简单的 Round函数就搞定了。

SQL> select all_sales.*,
  2         100 * round(cust_sales / region_sales, 2) || '%' Percent
  3    from (select o.cust_nbr customer,
  4            o.region_id region,
  5            sum(o.tot_sales) cust_sales,
  6            sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales
  7          from orders_tmp o
  8          where o.year = 2001
  9          group by o.region_id, o.cust_nbr) all_sales
10  where all_sales.cust_sales > all_sales.region_sales * 0.2;

  CUSTOMER     REGION CUST_SALES REGION_SALES PERC
---------- ---------- ---------- ------------ ----
         4          5      37802        37802 100%
        10          6      64315        68065 94%
        11          7      12204        12204 100%


总结:

①Over 函数指明在那些字段上做分析,其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。

②Over 函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,还有诸如Rank,Dense_rank等。
参考资料:《Mastering Oracle SQL》 (By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)
分享到:
评论

相关推荐

    oracle分析函数over_及开窗函数.txt

    ### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中,分析函数(Analytic Functions)是一种非常强大的工具,用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算,并且每行返回一个...

    oracle的分析函数over 及开窗函数

    ### Oracle的分析函数OVER及开窗函数 #### 一、分析函数OVER ##### 1. 概念介绍 从Oracle 8.1.6版本开始,Oracle引入了分析函数,这些函数可以对分组的数据执行复杂的操作,如计算累积总和、排名等。与聚合函数...

    oracle 分析函数详解(有例子)

    1 Oracle开发专题之:分析函数 OVER 2 Oracle开发专题之:分析函数 Rank Dense rank row number 3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题...

    oracle分析函数全面解析

    分析函数OVER解析** OVER子句是使用分析函数的关键部分,它允许用户定义一个计算窗口,这个窗口可以包含当前行以及前面或后面的行。例如: - **Rank, Dense_rank, row_number**: 这三个函数都用于对行进行排序并...

    Oracle开发的over函数

    #### 三、分析函数OVER解析 1. **分析函数语法**: 分析函数的基本语法结构如下: ```sql FUNCTION_NAME(,) OVER ( ) ``` - `FUNCTION_NAME` 是分析函数的名称,例如SUM、COUNT等。 - `<argument>...

    oracle分析函数.doc

    3. **分析函数OVER解析** `OVER`子句是分析函数的关键部分,它可以接受两个主要部分:`PARTITION BY`和`ORDER BY`。 - `PARTITION BY`:将数据集划分为多个分区,每个分区内的分析函数独立计算。 - `ORDER BY`:...

    oracle分析函数指南

    2. **分析函数OVER解析** `OVER()` 子句是分析函数的核心部分,它定义了函数的操作范围,即所谓的“窗口”或“分区”。窗口可以是整个结果集,也可以是根据某些列划分的子集。窗口可以进一步通过 `ORDER BY` 子句...

    oracle分析函数

    2. **分析函数OVER解析** OVER子句是分析函数的核心部分,它可以定义一个“窗口”(Window),在这个窗口内的数据行上执行计算。窗口可以按照行的顺序、分组或其他自定义规则来定义。例如,你可以计算每组的累计和...

    深入浅出oracle分析函数(全)

    1. **分析函数OVER()**: 分析函数的核心在于OVER子句,它定义了一个计算的“窗口”或“分区”,在这个窗口内执行特定的函数。窗口可以是整个结果集,也可以是基于某些列的分组。例如,`ROW_NUMBER()`、`RANK()`和`...

    Oracle 分析函数

    #### 三、分析函数OVER解析 - **1. 分析函数的基本结构** ```sql FUNCTION_NAME(, ) OVER ( [PARTITION BY ] [ORDER BY ] [ROWS BETWEEN <start> AND <end> | RANGE BETWEEN <start> AND ] ) ``` - **2. ...

    ORACLE 常用分析函数

    主要包括分析函数(OVER);分析函数2(Rank, Dense_rank, row_number);分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述  ROW_NUMBER () ...

    Oracle中的分析函数详解

    Oracle数据库系统是世界上最...在实际工作中,结合PARTITION BY和OVER子句的使用,分析函数可以解决许多复杂的业务问题,提升数据处理能力。因此,对分析函数的深入理解是成为高级Oracle数据库开发人员的关键技能之一。

    oracle 分析函数

    1. 分析函数OVER子句: OVER子句是分析函数的核心部分,它定义了函数如何对数据进行分组和排序。例如,在上述的第一个示例中,我们想要统计商店每天的累计销售额。通过使用`SUM(SALE) OVER (ORDER BY DATE)`,我们...

    分析函数详细解析

    ### 分析函数详细解析 #### 一、概述 ##### 1.1 什么是分析函数 随着信息技术的发展,企业对数据的依赖程度日益增加。在信息化初期,数据收集成为了首要任务,因为“无数据,不真实”的理念深入人心。然而,随着...

    分析函数使用.pdf

    ### Oracle分析函数简介与应用 #### 一、引言 在Oracle 8i版本中引入了一系列新的分析函数,这些函数极大地增强了SQL查询的能力,并且在处理复杂的数据分析任务时提供了更为简便、高效的解决方案。本文旨在介绍...

Global site tag (gtag.js) - Google Analytics