Orace分析函数ntile与width_bucket

ntile：

伪SQL: ntile(<number of bucket>:exp) over(<partition and order to determine bucket>)

 

NTILE is an analytic function. It divides an ordered data set into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expr. The expr value must resolve to a positive constant for each partition. Oracle Database expects an integer, and if expr is a noninteger constant, then Oracle truncates the value to an integer. The return value is NUMBER.

The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed one for each bucket, starting with bucket 1.

If expr is greater than the number of rows, then a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.

 

大概意思是可以看作是把有序的数据集合平均分配到指定的exp数量的桶中,将桶号分配给每一行。

如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的行数最多相差1

如果expr大于的行数,然后桶的数量等于将的行数,和剩下的桶是空的

示例：

SQL> select empno,sal, ntile(4) over(order by sal desc) as order_quartitle from
check_test;

EMPNO SAL ORDER_QUARTITLE
---------- ---------- ---------------
7839 5000 1
7902 3000 1
7788 3000 1
7566 2975 1
7698 2850 2
7782 2450 2
7499 1600 2
7844 1500 2
7934 1300 3
7521 1250 3
7654 1250 3

EMPNO SAL ORDER_QUARTITLE
---------- ---------- ---------------
7876 1100 4
7900 950 4
7369 800 4

 

width_bucket：

伪sql：width_bucket(<expression or column>,<lower bound>,<lower bound>,<number of buckets>)

 

SQL> select empno,
2 sum(sal) as t_sal,
3 width_bucket(sum(sal), 0, 5001, 6) as t_bucket
4 from check_test t
5 group by t.empno
6 ;

EMPNO T_SAL T_BUCKET
----- ---------- ----------
7782 2450 3
7839 5000 6
7844 1500 2
7698 2850 4
7521 1250 2
7902 3000 4
7566 2975 4
7654 1250 2
7788 3000 4
7934 1300 2
7499 1600 2
7876 1100 2
7369 800 1
7900 950 2