WIDTH_BUCKET和NTILE函式.txt

to_be_Dba發表於2013-01-23
函式

WIDTH_BUCKET函式

oracle提供的WIDTH_BUCKET函式可以根據輸入引數建立等長的段。

函式語法：width_bucket(expr,min_value,max_value,num_buckets)
expr是表示式，必須是數字型別、日期型別或者能夠轉換成數字型別的資料型別
min_value和max_value是expr的最終可接受範圍，也必須是數字或日期型別，且不為空
num_buckets是bucket數量，必須是正整數

範圍MIN到MAX被分為num_buckets節，每節有相同的大小。返回expr所在的那一節。
如果expr小於MIN，將返回0，如果expr大於或等於MAX，將返回num_buckets+1。
MIN和MAX都不能為NULL，num_buckets必須是一個正整數。如果expr是NULL，則返回NULL。


如：
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      8000 TL         MANAGER                                                      10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30


--下面我們將薪水平均分為多個級別，1000到2000分為10份，每份是100，看下面的結果
SQL> select empno,ename,job,mgr,hiredate,sal,width_bucket(sal,1000,2000,10) from emp;

SQL> select empno,ename,job,mgr,hiredate,sal,width_bucket(sal,1000,2000,10) from empp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL WIDTH_BUCKET(SAL,1000,2000,10)
---------- ---------- --------- ---------- --------- ---------- ------------------------------
      7566 JONES      MANAGER         7839 02-APR-81       2975                             11
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                             11
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                             11
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                             11
      7839 KING       PRESIDENT            17-NOV-81       5000                             11
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                              2
      7900 JAMES      CLERK           7698 03-DEC-81        950                              0
      7902 FORD       ANALYST         7566 03-DEC-81       3000                             11
      7934 MILLER     CLERK           7782 23-JAN-82       1300                              4
      8000 TL         MANAGER
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600                              7
      7521 WARD       SALESMAN        7698 22-FEB-81       1250                              3
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250                              3
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                              6

2975>2000,所以結果是10+1
2000>1100>1000,在範圍內，且正好是1000+100，即2份
1250大於1000+100*2，小於1000+100*3，所以是3份


--再看看日期，我們只看81年的
SQL>   select empno,ename,job,mgr,to_char(hiredate,'yyyy-mm-dd') from empp where hiredate

     EMPNO ENAME      JOB              MGR TO_CHAR(HI
---------- ---------- --------- ---------- ----------
      7566 JONES      MANAGER         7839 1981-04-02
      7698 BLAKE      MANAGER         7839 1981-05-01
      7782 CLARK      MANAGER         7839 1981-06-09
      7839 KING       PRESIDENT            1981-11-17
      7900 JAMES      CLERK           7698 1981-12-03
      7902 FORD       ANALYST         7566 1981-12-03
      7934 MILLER     CLERK           7782 1982-01-23
      7499 ALLEN      SALESMAN        7698 1981-02-20
      7521 WARD       SALESMAN        7698 1981-02-22
      7654 MARTIN     SALESMAN        7698 1981-09-28
      7844 TURNER     SALESMAN        7698 1981-09-08


--日期劃分規則定義為：從1月1日開始，12月31日結束，7天作為一個週期
SQL> select empno,ename,to_char(hiredate,'yyyy-mm-dd'),
width_bucket(hiredate,to_date('1981-1-1','yyyy-mm-dd'),to_date('1982-1-6','yyyy-mm-dd'),53) period
from empp where hiredate

     EMPNO ENAME      TO_CHAR(HI     PERIOD
---------- ---------- ---------- ----------
      7566 JONES      1981-04-02          2
      7698 BLAKE      1981-05-01          3
      7782 CLARK      1981-06-09          4
      7839 KING       1981-11-17          7
      7900 JAMES      1981-12-03          7
      7902 FORD       1981-12-03          7
      7934 MILLER     1982-01-23          8
      7499 ALLEN      1981-02-20          1
      7521 WARD       1981-02-22          2
      7654 MARTIN     1981-09-28          6
      7844 TURNER     1981-09-08          5


用下面的方法可以檢視hiredate是當年的第幾周：
(hiredate-trunc(hiredate,'yyyy'))/7

SQL> select empno,ename,to_char(hiredate,'yyyy-mm-dd'),
floor((hiredate-trunc(hiredate,'yyyy'))/7) week_count,
width_bucket(hiredate,to_date('1981-1-1','yyyy-mm-dd'),to_date('1981-12-31','yyyy-mm-dd'),7) period
from empp where hiredate  2    3    4 
     EMPNO ENAME      TO_CHAR(HI WEEK_COUNT     PERIOD
---------- ---------- ---------- ---------- ----------
      7566 JONES      1981-04-02         13          2
      7698 BLAKE      1981-05-01         17          3
      7782 CLARK      1981-06-09         22          4
      7839 KING       1981-11-17         45          7
      7900 JAMES      1981-12-03         48          7
      7902 FORD       1981-12-03         48          7
      7934 MILLER     1982-01-23          3          8
      7499 ALLEN      1981-02-20          7          1
      7521 WARD       1981-02-22          7          2
      7654 MARTIN     1981-09-28         38          6
      7844 TURNER     1981-09-08         35          5

對比可以看出，period是在53 (即365/7+1)基礎上運算的。看來用法還不是這樣。不過此函式可用於日期。

與其相對應的是ntile。官方文件中將兩者分別解釋為高度柱狀圖和寬度柱狀圖
（WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram
range is divided into intervals that have identical size. (Compare this function with
NTILE, which creates equiheight histograms.)）


NTILE
語法：NTILE (expr) over ([query_paration_caluse] order_by_clause)
這是一個分析函式，將expr的有序資料分成一系列的塊，並算出每一塊的數量。
expr必須可以解釋為一個位置，如果不是整性，oracle將其截斷為整數，返回值是number型別。

該分析函式不能巢狀使用，但expr可以是其他函式的嵌入。
可以理解為expr是桶的個數，依次向編號為1至expr的桶中放入資料，放到expr後下一個數放入1，依次迴圈直到完成。

通過下面三個查詢可以理解：

SQL>  select ename,sal,ntile(5) over(order by sal desc) from empp where sal is not null;

ENAME             SAL NTILE(5)OVER(ORDERBYSALDESC)
---------- ---------- ----------------------------
KING             5000                            1
SCOTT            3000                            1
FORD             3000                            1
JONES            2975                            2
BLAKE            2850                            2
CLARK            2450                            2
ALLEN            1600                            3
TURNER           1500                            3
MILLER           1300                            3
WARD             1250                            4
MARTIN           1250                            4
ADAMS            1100                            5
JAMES             950                            5

13 rows selected.

SQL> select ename,sal,ntile(4) over(order by sal desc) from empp where sal is not null;

ENAME             SAL NTILE(4)OVER(ORDERBYSALDESC)
---------- ---------- ----------------------------
KING             5000                            1
SCOTT            3000                            1
FORD             3000                            1
JONES            2975                            1
BLAKE            2850                            2
CLARK            2450                            2
ALLEN            1600                            2
TURNER           1500                            3
MILLER           1300                            3
WARD             1250                            3
MARTIN           1250                            4
ADAMS            1100                            4
JAMES             950                            4

13 rows selected.

SQL>  select ename,sal,ntile(3) over(order by sal desc) from empp where sal is not null;

ENAME             SAL NTILE(3)OVER(ORDERBYSALDESC)
---------- ---------- ----------------------------
KING             5000                            1
SCOTT            3000                            1
FORD             3000                            1
JONES            2975                            1
BLAKE            2850                            1
CLARK            2450                            2
ALLEN            1600                            2
TURNER           1500                            2
MILLER           1300                            2
WARD             1250                            3
MARTIN           1250                            3
ADAMS            1100                            3
JAMES             950                            3

13 rows selected.

這種分配的方式還是相對比較平均的，即各個組中的數相差很少。

參考：
pl/sql challenge
百度
官方文件

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-753169/，如需轉載，請註明出處，否則將追究法律責任。

相關文章