ITPub博客

首页 > 数据库 > Oracle > 分析函数在数据分析中的应用

分析函数在数据分析中的应用

Oracle 作者:armromono 时间:2013-07-11 09:16:16 0 删除 编辑

我们来看看下面的几个典型例子:

查找上一年度各个销售区域排名前10的员工

按区域查找上一年度订单总额占区域订单总额20%以上的客户

查找上一年度销售最差的部门所在的区域

查找上一年度销售最好和最差的产品

 

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

 

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

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

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

 

Oracle分析函数简单实例:

 

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

 

1】测试环境:

分析函数在数据分析中的应用

SQL> desc orders_tmp;

分析函数在数据分析中的应用

 Name                           Null   Type

分析函数在数据分析中的应用

 ----------------------- -------- ----------------

分析函数在数据分析中的应用

 CUST_NBR                    NOT NULL NUMBER(5)

分析函数在数据分析中的应用

 REGION_ID                   NOT NULL NUMBER(5)

分析函数在数据分析中的应用

 SALESPERSON_ID      NOT NULL NUMBER(5)

分析函数在数据分析中的应用

 YEAR                              NOT NULL NUMBER(4)

分析函数在数据分析中的应用

 MONTH                         NOT NULL NUMBER(2)

分析函数在数据分析中的应用

 TOT_ORDERS              NOT NULL NUMBER(7)

分析函数在数据分析中的应用

 TOT_SALES                 NOT NULL 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

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

13 rows selected.

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 bysum是无法做到这一点的(因为聚集操作的级别不一样,前者是对一个客户,后者是对一批客户)

 

这就是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> 

现在我们已经知道这些大客户是谁了!哦,不过这还不够,如果我们想要知道每个大客户所占的订单比例呢?看看下面的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 PERCENT

分析函数在数据分析中的应用

---------- ---------- ---------- ------------ ----------------------------------------

分析函数在数据分析中的应用

         4            5                  37802        37802    100%

分析函数在数据分析中的应用

        10           6                  64315        68065      94%

分析函数在数据分析中的应用

        11           7                  12204        12204    100%

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

SQL> 

总结:

 

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

 

Over函数可以和其它聚集函数、分析函数搭配,起到不同的作用。例如这里的SUM,还有诸如RankDense_rank等。

 

 分析函数2(Rank, Dense_rank, row_number)

目录

===============================================

1.使用rownum为记录排名

2.使用分析函数来为记录排名

3.使用分析函数为记录进行分组排名

 

一、使用rownum为记录排名:

 

在前面一篇《Oracle开发专题之:分析函数》,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题:

 

对所有客户按订单总额进行排名

按区域和客户订单总额进行排名

找出订单总额排名前13位的客户

找出订单总额最高、最低的客户

找出订单总额排名前25%的客户

 

按照前面第一篇文章的思路,我们只能做到对各个分组的数据进行统计,如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下。

 

1】测试环境:

分析函数在数据分析中的应用

SQL> desc user_order;

分析函数在数据分析中的应用

 Name                                      Null   Type

分析函数在数据分析中的应用

 ----------------------------------------- -------- ----------------------------

分析函数在数据分析中的应用

 REGION_ID                                          NUMBER(2)

分析函数在数据分析中的应用

 CUSTOMER_ID                                  NUMBER(2)

分析函数在数据分析中的应用

 CUSTOMER_SALES                          NUMBER

2】测试数据:

分析函数在数据分析中的应用

SQL> select * from user_order order by customer_sales;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID CUSTOMER_SALES

分析函数在数据分析中的应用

---------- ----------- --------------

分析函数在数据分析中的应用

         5           1              151162

分析函数在数据分析中的应用

        10          29             903383

分析函数在数据分析中的应用

         6           7              971585

分析函数在数据分析中的应用

        10          28            986964

分析函数在数据分析中的应用

         9          21           1020541

分析函数在数据分析中的应用

         9          22           1036146

分析函数在数据分析中的应用

         8          16           1068467

分析函数在数据分析中的应用

         6           8            1141638

分析函数在数据分析中的应用

         5           3            1161286

分析函数在数据分析中的应用

         5           5            1169926

分析函数在数据分析中的应用

         8          19           1174421

分析函数在数据分析中的应用

         7          12           1182275

分析函数在数据分析中的应用

         7          11           1190421

分析函数在数据分析中的应用

         6          10           1196748

分析函数在数据分析中的应用

         6           9            1208959

分析函数在数据分析中的应用

        10          30          1216858

分析函数在数据分析中的应用

                                    1224992

分析函数在数据分析中的应用

                       24              1224992

分析函数在数据分析中的应用

                       23              1224992

分析函数在数据分析中的应用

           8          18           1253840

分析函数在数据分析中的应用

         7          15           1255591

分析函数在数据分析中的应用

         7          13           1310434

分析函数在数据分析中的应用

        10          27          1322747

分析函数在数据分析中的应用

         8          20           1413722

分析函数在数据分析中的应用

         6           6            1788836

分析函数在数据分析中的应用

        10          26          1808949

分析函数在数据分析中的应用

         5           4            1878275

分析函数在数据分析中的应用

         7          14           1929774

分析函数在数据分析中的应用

         8          17           1944281

分析函数在数据分析中的应用

         9          25           2232703

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

30 rows selected.

注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户,如果使用rownum会有什么样的后果呢?

分析函数在数据分析中的应用

SQL> select rownum, t.*

分析函数在数据分析中的应用

  2    from (select * 

分析函数在数据分析中的应用

  3            from user_order

分析函数在数据分析中的应用

  4           order by customer_sales desct

分析函数在数据分析中的应用

  5   where rownum <= 12

分析函数在数据分析中的应用

  6   order by customer_sales desc;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

    ROWNUM  REGION_ID CUSTOMER_ID CUSTOMER_SALES

分析函数在数据分析中的应用

---------- ---------- ----------- --------------

分析函数在数据分析中的应用

         1          9                 25        2232703

分析函数在数据分析中的应用

         2          8                 17        1944281

分析函数在数据分析中的应用

         3          7                 14        1929774

分析函数在数据分析中的应用

         4          5                   4        1878275

分析函数在数据分析中的应用

         5         10                26        1808949

分析函数在数据分析中的应用

         6          6                   6        1788836

分析函数在数据分析中的应用

         7          8                 20        1413722

分析函数在数据分析中的应用

         8         10                27        1322747

分析函数在数据分析中的应用

         9          7                13        1310434

分析函数在数据分析中的应用

        10          7               15        1255591

分析函数在数据分析中的应用

        11          8               18        1253840

          12                                          1224992

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

12 rows selected.

很明显假如只是简单地按rownum进行排序的话,我们漏掉了另外两条记录(参考上面的结果)。

 

二、使用分析函数来为记录排名:

 

针对上面的情况,Oracle从8i开始就提供了3个分析函数:rand,dense_rank,row_number来解决诸如此类的问题,下面我们来看看这3个分析函数的作用以及彼此之间的区别:

 

RankDense_rankRow_number函数为每条记录产生一个从1开始至N的自然数,N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

 

ROW_NUMBER

Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 

DENSE_RANK

Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。 

RANK

Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

 

这样的介绍有点难懂,我们还是通过实例来说明吧,下面的例子演示了3个不同函数在遇到相同数据时不同排名策略:

分析函数在数据分析中的应用

SQL> select region_id, customer_id, sum(customer_sales) total,

分析函数在数据分析中的应用

  2         rank() over(order by sum(customer_sales) descrank,

分析函数在数据分析中的应用

  3         dense_rank() over(order by sum(customer_sales) descdense_rank,

分析函数在数据分析中的应用

  4         row_number() over(order by sum(customer_sales) descrow_number

分析函数在数据分析中的应用

  5    from user_order

分析函数在数据分析中的应用

  6   group by region_id, customer_id;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER

分析函数在数据分析中的应用

---------- ----------- ---------- ---------- ---------- ----------

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

        

分析函数在数据分析中的应用

         8          18                1253840         11         11         11

分析函数在数据分析中的应用

         5           2                 1224992         12         12         12

分析函数在数据分析中的应用

         9          23                1224992         12         12         13

分析函数在数据分析中的应用

         9          24                1224992         12         12         14

分析函数在数据分析中的应用

        10          30               1216858         15           13            15

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

30 rows selected.

请注意上面的绿色高亮部分,这里生动的演示了3种不同的排名策略:

 

对于第一条相同的记录,3种函数的排名都是一样的:12

 

当出现第二条相同的记录时,Rank和Dense_rank依然给出同样的排名12;而row_number则顺延递增为13,依次类推至第三条相同的记录

 

当排名进行到下一条不同的记录时,可以看到Rank函数在12和15之间空出了13,14的排名,因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增

 

比较上面3种不同的策略,我们在选择的时候就要根据客户的需求来定夺了:

 

假如客户就只需要指定数目的记录,那么采用row_number是最简单的,但有漏掉的记录的危险

 

假如客户需要所有达到排名水平的记录,那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要,选择dense_rank或得到最大的记录

 

三、使用分析函数为记录进行分组排名:

 

上面的排名是按订单总额来进行排列的,现在跟进一步:假如是为各个地区的订单总额进行排名呢?这意味着又多了一次分组操作:对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持,我们所要做的仅仅是在over函数中order by的前面增加一个分组子句:partition by region_id。

分析函数在数据分析中的应用

SQL> select region_id, customer_id, 

               sum(customer_sales) total,

分析函数在数据分析中的应用

  2         rank() over(partition by region_id

                        order by sum(customer_sales) descrank,

分析函数在数据分析中的应用

  3         dense_rank() over(partition by region_id

                        order by sum(customer_sales) descdense_rank,

分析函数在数据分析中的应用

  4         row_number() over(partition by region_id

                        order by sum(customer_sales) descrow_number

 

分析函数在数据分析中的应用

  5    from user_order

分析函数在数据分析中的应用

  6   group by region_id, customer_id;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER

分析函数在数据分析中的应用

---------- ----------- ---------- ---------- ---------- ----------

分析函数在数据分析中的应用

         5           4                1878275          1          1          1

分析函数在数据分析中的应用

         5           2                1224992          2          2          2

分析函数在数据分析中的应用

         5           5                1169926          3          3          3

分析函数在数据分析中的应用

         6           6                1788836          1          1          1

分析函数在数据分析中的应用

         6           9                1208959          2          2          2

分析函数在数据分析中的应用

         6          10               1196748          3          3          3       

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

30 rows selected.

现在我们看到的排名将是基于各个地区的,而非所有区域的了!Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分,这样排列函数就能够应用于这各个子集。

 

前面我们提到的5个问题已经解决了2个了(第1,2),剩下的3个问题(Top/Bottom N,First/Last, NTile)会在下一篇讲解。

 

分析函数3(Top/Bottom N、First/Last、NTile)

 1.带空值的排列

2.Top/Bottom N查询

3.First/Last排名查询

4.按层次查询

 

一、带空值的排列:

 假如被排列的数据中含有空值呢?

分析函数在数据分析中的应用

SQL> select region_id, customer_id,

分析函数在数据分析中的应用

  2         sum(customer_sales) cust_sales,

分析函数在数据分析中的应用

  3         sum(sum(customer_sales)) over(partition by region_id) ran_total,

分析函数在数据分析中的应用

  4         rank() over(partition by region_id

分析函数在数据分析中的应用

  5                  order by sum(customer_sales) descrank

分析函数在数据分析中的应用

  6    from user_order

分析函数在数据分析中的应用

  7   group by region_id, customer_id;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID CUST_SALES  RAN_TOTAL       RANK

分析函数在数据分析中的应用

---------- ----------- ---------- ---------- ---------- 

分析函数在数据分析中的应用

        10          31                    6238901          1

分析函数在数据分析中的应用

        10          26    1808949    6238901          2

分析函数在数据分析中的应用

        10          27    1322747    6238901          3

分析函数在数据分析中的应用

        10          30    1216858    6238901          4

分析函数在数据分析中的应用

        10          28     986964    6238901          5

分析函数在数据分析中的应用

        10          29     903383    6238901          6

我们看到这里有一条记录的CUST_TOTAL字段值为NULL,但居然排在第一名了!显然这不符合情理。所以我们重新调整完善一下我们的排名策略,看看下面的语句:

分析函数在数据分析中的应用

SQL> select region_id, customer_id,

分析函数在数据分析中的应用

  2         sum(customer_sales) cust_total,

分析函数在数据分析中的应用

  3         sum(sum(customer_sales)) over(partition by region_id) reg_total,

分析函数在数据分析中的应用

  4         rank() over(partition by region_id 

                        order by sum(customer_sales) desc NULLS LASTrank

分析函数在数据分析中的应用

  5        from user_order

分析函数在数据分析中的应用

  6       group by region_id, customer_id;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK

分析函数在数据分析中的应用

---------- ----------- ---------- ---------- ----------

分析函数在数据分析中的应用

        10          26    1808949     6238901           1

分析函数在数据分析中的应用

        10          27    1322747    6238901           2

分析函数在数据分析中的应用

        10          30    1216858    6238901           3

分析函数在数据分析中的应用

        10          28     986964     6238901           4

分析函数在数据分析中的应用

        10          29     903383     6238901           5

分析函数在数据分析中的应用

        10          31     6238901                           6

绿色高亮处,NULLS LAST/FIRST告诉Oracle让空值排名最后后第一。

 

注意是NULLS,不是NULL

 

二、Top/Bottom N查询:

 

在日常的工作生产中,我们经常碰到这样的查询:找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示:

 

1】找出所有订单总额排名前3的大客户:

分析函数在数据分析中的应用

SQL> select *

分析函数在数据分析中的应用

SQL>   from (select region_id,

分析函数在数据分析中的应用

SQL>                customer_id,

分析函数在数据分析中的应用

SQL>                sum(customer_sales) cust_total,

分析函数在数据分析中的应用

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

分析函数在数据分析中的应用

SQL>           from user_order

分析函数在数据分析中的应用

SQL>          group by region_id, customer_id)

分析函数在数据分析中的应用

SQL>  where rank <= 3;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID CUST_TOTAL       RANK

分析函数在数据分析中的应用

---------- ----------- ---------- ----------

分析函数在数据分析中的应用

         9          25    2232703          1

分析函数在数据分析中的应用

         8          17    1944281          2

分析函数在数据分析中的应用

         7          14    1929774          3

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

SQL> 

【2】找出每个区域订单总额排名前3的大客户:

分析函数在数据分析中的应用

SQL> select *

分析函数在数据分析中的应用

  2    from (select region_id,

分析函数在数据分析中的应用

  3                 customer_id,

分析函数在数据分析中的应用

  4                 sum(customer_sales) cust_total,

分析函数在数据分析中的应用

  5                 sum(sum(customer_sales)) over(partition by region_id) reg_total,

分析函数在数据分析中的应用

  6                 rank() over(partition by region_id

                                order by sum(customer_sales) desc NULLS LAST) rank

分析函数在数据分析中的应用

  7            from user_order

分析函数在数据分析中的应用

  8           group by region_id, customer_id)

分析函数在数据分析中的应用

  9   where rank <= 3;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID CUST_TOTAL  REG_TOTAL       RANK

分析函数在数据分析中的应用

---------- ----------- ---------- ---------- ----------

分析函数在数据分析中的应用

         5           4    1878275    5585641          1

分析函数在数据分析中的应用

         5           2    1224992    5585641          2

分析函数在数据分析中的应用

         5           5    1169926    5585641          3

分析函数在数据分析中的应用

         6           6    1788836    6307766          1

分析函数在数据分析中的应用

         6           9    1208959    6307766          2

分析函数在数据分析中的应用

         6          10    1196748    6307766          3

分析函数在数据分析中的应用

         7          14    1929774    6868495          1

分析函数在数据分析中的应用

         7          13    1310434    6868495          2

分析函数在数据分析中的应用

         7          15    1255591    6868495          3

分析函数在数据分析中的应用

         8          17    1944281    6854731          1

分析函数在数据分析中的应用

         8          20    1413722    6854731          2

分析函数在数据分析中的应用

         8          18    1253840    6854731          3

分析函数在数据分析中的应用

         9          25    2232703    6739374          1

分析函数在数据分析中的应用

         9          23    1224992    6739374          2

分析函数在数据分析中的应用

         9          24    1224992    6739374          2

分析函数在数据分析中的应用

        10          26    1808949    6238901          1

分析函数在数据分析中的应用

        10          27    1322747    6238901          2

分析函数在数据分析中的应用

        10          30    1216858    6238901          3

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

18 rows selected.

三、First/Last排名查询:

 

想象一下下面的情形:找出订单总额最多、最少的客户。按照前面我们学到的知识,这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名,第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦?因为Rank函数只告诉我们排名的结果,却无法自动替我们从中筛选结果。

 

幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话:

分析函数在数据分析中的应用

SQL> select min(customer_id)

分析函数在数据分析中的应用

  2         keep (dense_rank first order by sum(customer_sales) descfirst,

分析函数在数据分析中的应用

  3         min(customer_id)

分析函数在数据分析中的应用

  4         keep (dense_rank last order by sum(customer_sales) desclast

分析函数在数据分析中的应用

  5    from user_order

分析函数在数据分析中的应用

  6   group by customer_id;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

     FIRST       LAST

分析函数在数据分析中的应用

---------- ----------

分析函数在数据分析中的应用

        31          1

这里有几个看起来比较疑惑的地方:

 

为什么这里要用min函数

Keep这个东西是干什么的

fist/last是干什么的

dense_rank和dense_rank()有什么不同,能换成rank吗?

 

首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢?

分析函数在数据分析中的应用

SQL> select keep (dense_rank first order by sum(customer_sales) descfirst, 

分析函数在数据分析中的应用

  2             keep (dense_rank last order by sum(customer_sales) desclast

分析函数在数据分析中的应用

  3    from user_order

分析函数在数据分析中的应用

  4   group by customer_id;

分析函数在数据分析中的应用

select keep (dense_rank first order by sum(customer_sales) descfirst,

分析函数在数据分析中的应用

                        *

分析函数在数据分析中的应用

ERROR at line 1:

分析函数在数据分析中的应用

ORA-00907missing right parenthesis

接下来看看第2个问题:keep是干什么用的?从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据,这就是keep的作用。告诉Oracle只保留符合keep条件的记录。

 

那么什么才是符合条件的记录呢?这就是第3个问题了。dense_rank是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。

 

第4个问题:如果我们把dense_rank换成rank呢?

分析函数在数据分析中的应用

SQL> select min(region_id)

分析函数在数据分析中的应用

  2          keep(rank first order by sum(customer_sales) descfirst,

分析函数在数据分析中的应用

  3         min(region_id)

分析函数在数据分析中的应用

  4          keep(rank last order by sum(customer_sales) desclast

分析函数在数据分析中的应用

  5    from user_order

分析函数在数据分析中的应用

  6   group by region_id;

分析函数在数据分析中的应用

select min(region_id)

分析函数在数据分析中的应用

*

分析函数在数据分析中的应用

ERROR at line 1:

分析函数在数据分析中的应用

ORA-02000missing DENSE_RANK

四、按层次查询:

现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom N,第一个,最后一个记录。有时我们会收到类似下面这样的需求:找出订单总额排名前1/5的客户。

 

很熟悉是不?我们马上会想到第二点中提到的方法,可是rank函数只为我们做好了排名,并不知道每个排名在总排名中的相对位置,这时候就引入了另外一个分析函数NTile,下面我们就以上面的需求为例来讲解一下:

分析函数在数据分析中的应用

SQL> select region_id,

分析函数在数据分析中的应用

  2         customer_id,

分析函数在数据分析中的应用

  3         ntile(5over(order by sum(customer_sales) desctil

分析函数在数据分析中的应用

  4    from user_order

分析函数在数据分析中的应用

  5   group by region_id, customer_id;

分析函数在数据分析中的应用

 

分析函数在数据分析中的应用

 REGION_ID CUSTOMER_ID       TILE

分析函数在数据分析中的应用

---------- ----------- ----------

分析函数在数据分析中的应用

        10          31          1

分析函数在数据分析中的应用

         9          25           1

分析函数在数据分析中的应用

        10          26          1

分析函数在数据分析中的应用

         6           6            1         

分析函数在数据分析中的应用

         8          18           2

分析函数在数据分析中的应用

         5           2            2

分析函数在数据分析中的应用

         9          23           3

分析函数在数据分析中的应用

         6           9            3

分析函数在数据分析中的应用

         7          11           3

分析函数在数据分析中的应用

         5           3            4

分析函数在数据分析中的应用

         6           8            4

分析函数在数据分析中的应用

         8          16           4

分析函数在数据分析中的应用

         6           7            5

分析函数在数据分析中的应用

        10          29          5

分析函数在数据分析中的应用

         5           1            5

Ntil函数为各个记录在记录集中的排名计算比例,我们看到所有的记录被分成5个等级,那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。

来源:

http://www.blogjava.net/pengpenglin/archive/2008/06/27/211019.html

 

 

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22033663/viewspace-1115690/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-07-04