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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-753169/