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

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

阅读更多
一、带空值的排列:

假如被排列的数据中含有空值呢?
SQL> select region_id,
  2         customer_id,
  3         ntile(5) over(order by sum(customer_sales) desc) til
  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)就可以了。
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) desc) rank
  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 LAST) rank
  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) desc) first,
  3         min(customer_id)
  4         keep (dense_rank last order by sum(customer_sales) desc) last
  5    from user_order
  6   group by customer_id;

     FIRST       LAST
---------- ----------
        25          1


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

①为什么这里要用min函数
②Keep 这个东西是干什么的
③fist/last是干什么的
④dense_rank和dense_rank()有什么不同,能换成rank吗?

首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢?
SQL> select keep (dense_rank first order by sum(customer_sales) desc) first,
  2             keep (dense_rank last order by sum(customer_sales) desc) last
  3    from user_order
  4   group by customer_id;
select keep (dense_rank first order by sum(customer_sales) desc) first,
                        *
ERROR at line 1:
ORA-00907: missing 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) desc) first,
  3         min(region_id)
  4          keep(rank last order by sum(customer_sales) desc) last
  5    from user_order
  6   group by region_id;
select min(region_id)
*
ERROR at line 1:
ORA-02000: missing DENSE_RANK


四、按层次查询:

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

很熟悉是不?我们马上会想到第二点中提到的方法,可是rank函数只为我们做好了排名,并不知道每个排名在总排名中的相对位置,这时候就引入了另外一个分析函数NTile,下面我们就以上面的需求为例来讲解一下:
SQL> select region_id,
  2         customer_id,
  3         ntile(5) over(order by sum(customer_sales) desc) til
  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)就可以了。
分享到:
评论

相关推荐

    Oracle开发之分析函数(Top/Bottom N、First/Last、NTile)

    本文将详细介绍如何使用分析函数来实现Top/Bottom N、First/Last以及NTile等查询。 1. **Top/Bottom N查询** Top N查询是指从结果集中选择排名前N的记录,而Bottom N则是选择最后N个记录。在Oracle中,我们可以...

    ORACLE 常用分析函数

    分析函数3(Top/Bottom N、First/Last、NTile);窗口函数;报表函数;分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述  ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时...

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

    3 Oracle开发专题之:分析函数3 Top Bottom N First Last NTile 4 Oracle开发专题之:窗口函数 5 Oracle开发专题之:报表函数 6 Oracle开发专题之:分析函数总结 7 Oracle开发专题之:26个分析函数 8 分析函数...

    oracle高级函数说明

    包含Oracle常用的高级函数,比如取前N名,每个分组的前N名等。 详细介绍Oracle分析函数(OVER、Rank、Dense_rank、row_number、Top/Bottom N、First/Last、NTile) ,窗口函数,报表函数

    oracle分析函数全面解析

    - **Top/Bottom N, First/Last, NTile**: 这些函数用于选取数据集中的前N个或后N个记录,`FIRST/LAST`则用于获取每个分组的第一行或最后一行,`NTILE`将数据分割成N个等大小或近似等大小的部分。 **3. 窗口函数** ...

    oracle分析函数,窗口函数,报表函数

    Top/Bottom N、First/Last、NTile - **Top/Bottom N**:返回每个分组内排名前N或后N的行,常用于获取每个类别的最佳或最差结果。 - **First/Last**:在每个分组内返回第一行或最后一行,这在跟踪每个组的初始状态...

    oracle分析函数指南

    3. **Top/Bottom N, First/Last, NTile** - `TOP/N BOTTOM N`: 可以用来选取每个分区的前N行或后N行。 - `FIRST/LAST`: 返回每个分区中第一个或最后一个非NULL值。 - `NTILE(N)`: 将结果集分成N个桶(tiles),每...

    oracle分析函数.doc

    - **Top/Bottom N, First/Last, NTile**:这些函数用于选择数据集的顶部或底部N行,或者获取每个组的第一行或最后一行,NTILE()则将数据分成N个桶或组。 - **窗口函数**:允许在特定窗口内应用函数,例如,计算...

    oracle分析函数学习

    4. **Top/Bottom N、First/Last、NTile** - Top N和Bottom N用于选取数据集中的前N条或后N条记录,可以结合RANK或ROW_NUMBER实现。 - First/Last函数可以找到每个分组的第一个或最后一个值,这在处理时间序列数据...

    oracle分析函数

    4. **Top/Bottom N、First/Last、NTile函数** - `TOP N`和`BOTTOM N`函数用来选取每个分组的前N行或后N行。 - `FIRST`和`LAST`函数则用于返回每个分组的第一行和最后一行,通常结合`PARTITION BY`使用。 - `NTILE...

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

    3. **Top/Bottom N、First/Last、NTile**: - `TOP N`或`BOTTOM N`:选取分区中的前N行或后N行。 - `FIRST_VALUE()`和`LAST_VALUE()`:返回指定列在当前行的窗口中的第一个或最后一个值。 - `NTILE(N)`:将结果集...

    oracle connect by 和 分析函数总结.doc

    3. **Top/Bottom N, First/Last, NTILE** - `TOP/NTH_VALUE`:选取数据集中的前N个或第N个值。 - `BOTTOM_VALUE`:选取数据集中的最小N个值。 - `FIRST/LAST_VALUE`:获取窗口内的第一个或最后一个值。 - `...

Global site tag (gtag.js) - Google Analytics