Oracle analytic function-ntile

NTILE的语法如下
NTILE(expr) OVER ([ query_partition_clause ] order_by_clause)

NTILE函数对一个数据分区中的有序结果集进行划分,将其分组为各个桶,并为每个小组分配一个唯一的组编号。这个函数在统计分析中是很有用的。例如,如果想移除异常值,你可以将它们分组到顶部或底部的桶中,然后在统计分析的时候将这些值排除。ORACLE数据库统计信息收集也使用NTILE函数来计算直方图信息边界。在统计学术语中,NTILE函数创建等宽直方图信息。
原始数据

SQL> select first_name ||' '|| last_name as Name, salary
  2    from employees
  3   where department_id = 30;

NAME                                                                                             SALARY
-------------------------------------------------------------------------------------------- ----------
Den Raphaely                                                                                      11000
Alexander Khoo                                                                                     2600
Shelli Baida                                                                                       2900
Sigal Tobias                                                                                       2600
Guy Himuro                                                                                         2600
Karen Colmenares                                                                                   2500
已选择6行。

接下来对上述6个员工的薪水分配4,5,6三种桶数,观察一下当分配不同的桶数的情况下,员工的薪水值落在的桶号。

SQL> select first_name || ' ' || last_name as Name,
  2         salary,
  3         ntile(4) over(order by salary desc) as n4,
  4         ntile(5) over(order by salary desc) as n5,
  5         ntile(6) over(order by salary desc) as n6
  6    from employees
  7   where department_id = 30;
NAME                                                             SALARY         N4         N5         N6
--------------------------------------------------------------- ---------- ---------- ---------- ----------
Den Raphaely                                                      11000          1          1          1
Shelli Baida                                                       2900          1          1          2
Sigal Tobias                                                       2600          2          2          3
Guy Himuro                                                         2600          2          3          4
Alexander Khoo                                                     2600          3          4          5
Karen Colmenares                                                   2500          4          5          6
已选择6行。

当分配的桶数为6个的时候,因为部门ID=30的员工有6个人,每个员工的薪水值都被分配到不同的桶中。当桶数小于6的时候,会有多个值分配到相同的桶中的情况发生。

此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s