-
1.
ASCII
返回与指定的字符对应的十进制数
;
SQL> select ascii(A) A,ascii(a)
a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2. CHR
< br>给出整数
,
返回对应的字符
;
SQL> select chr(54740)
zhao,chr(65) chr65 from dual;
ZH C
-- -
赵
A
3. CONCAT
连接两个字符串
;
SQL> select
concat(010-,88888888)||
转
23
高乾竞电话
from dual;
高乾竞电话
----------------
转
23
4. INITCAP
返回字符串并将字符串的第一个字母变为大写
;
SQL> select initcap(smith)
upp from dual;
UPP
-----
Smith
(C1,C2,I,J)
在一个字
符串中搜索指定的字符
,
返回发现指定的字符的位置
;
C1
被搜索的字符串
C2
希望搜索的字符串
I
搜索的开始位置
,
默认为
1
J
出现的位置
,
默认为
1
SQL>
select instr(oracle traning,ra,1,2) instring from
dual;
INSTRING
---------
9
返回字符串的长度
;
SQL>
select
name,
length(name),addr
,length(addr),sal,leng
th(to_char(sal))
_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL
LENGTH(TO_CHAR(SAL))
------
------------ ---------------- ------------
--------- --------------------
高乾竞
3
北京市海锭区
6 9999.99
7
from
返回字符串
,
并将所有的字符小写
SQL> select lower(AaBbCcDd)AaBbCcDd
from dual;
AABBCCDD
--------
aabbccdd
返回字符串
,
并将所有的字符大写
SQL> select upper(AaBbCcDd) upper from
dual;
UPPER
--------
AABBCCDD
和
LPAD(
粘贴字符
p>
)
RPAD
在列的右边粘贴字符
LPAD
在列的左边粘贴字符
SQL> select
lpad(rpad(gao,10,*),17,*)from dual;
LPAD(RPAD(GAO,1
-----------------
*******gao*******
不够字符则用
*
来填满
和
RTRIM
LTRIM
删除左边出现的字符串
RTRIM
删除右边出现的字符串
SQL> select ltrim(rtrim(
gao qian jing , ), ) from dual;
LTRIM(RTRIM(
-------------
gao qian jing
(string,start,count)
取子字符串
,
从
start
开始
,
取
co
unt
个
SQL> select substr(,3,8) from dual;
SUBSTR(
--------
08888888
E(string,s1,s2)
string
希望被替换的字符或变量
s1
被替换的字符串
s2
要替换的字符串
SQL> select replace(he love you,he,i)
from dual;
REPLACE(H
----------
i love you
X
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm
varchar(8));
SQL> insert
into table1 values(weather);
SQL> insert into table1 values(wether);
SQL> insert into table1
values(gao);
SQL> select xm
from table1 where soundex(xm)=soundex(weather);
XM
--------
weather
wether
? (s from string)
LEADING
剪掉前面的字符
TRAILING
剪掉后面的字符
< br>如果不指定
,
默认为空格符
返回指定值的绝对值
SQL> select abs(100),abs(-100) from
dual;
ABS(100) ABS(-100)
--------- ---------
100 100
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
返回一个给定数字的余弦
SQL> select cos(-3.1415927)
from dual;
COS(-3.1415927)
---------------
-1
返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
返回一个数字
e
的
n
次方根
SQL> select
exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818)
from dual;
LN(1) LN(2)
LN(2.7182818)
---------
--------- -------------
0
.69314718 .99999999
(n1,n2)
返回一个以
n1
为底
n2
的对数
SQL> select
log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
(n1,n2)
返回一个
n1
除以
n2
的余数<
/p>
SQL> select
mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
返回<
/p>
n1
的
n2
次方
根
SQL> select
power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
和
TRUNC
按照指定的精度进行舍入
SQL> select
round(55.5),round(-55.4),trunc(55.5),trunc(-55.5)
from dual;
ROUND(55.5)
ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ -----------
------------
56 -55 55 -55
p>
取数字
n
的符号
,
大于
0
返回
1
,
小于
0
返回
-1,
等于
0
返回
0
SQL> select
sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100)
SIGN(0)
---------
---------- ---------
1 -1 0
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
?
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
p>
返回数字
n
的根
SQL> select
sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
返回数
字
n
的双曲正切值
SQL> select
tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2)
trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date(
199912,yyyymm),2),yyyymm) from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date(
199912,yyyymm),-2),yyyymm) from dual;
TO_CHA
------
199910
_DAY
返回日期的最后一天
SQL> select
to_char(sysdate,),to_char((sysdate)+1,) from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2004.05.09 2004.05.10
SQL> select
last_day(sysdate) from dual;
LAST_DAY(S
----------
31-5
月
-04
_BETWEEN(date2,date1)
给出
date2-date1
的月份
SQL> select months_be
tween(19-12
月
-1999,19-3
月
-1999) mon_between from dual;
MON_BETWEEN
-----------
9
SQL>selectmonths_between(to
_date(2000.05.20,),to_date(2005.05.20,yyyy.
)) mon_betw from dual;
MON_BETW
---------
-60
_TIME(date,this,that)
给出在
this
时区
=o
ther
时区的日期和时间
SQL> select to_char(sysdate,
hh24:mi:ss) bj_time,to_char(new_time
2 (sysdate,PDT
,GMT),
hh24:mi:ss) los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2004.05.09 11:05:32
2004.05.09 18:05:32
_DAY(date,day)
给
出日期
date
和星期
x
之后计算下一个星期的日期
SQL> select next_day('18-5
月<
/p>
-2001','
星期五
')
next_day from dual;
NEXT_DAY
----------
25-5
月
-01
E
用来得到系统的当前日期
SQL> select to_char(sysdate,dd-mm-yyyy
day) from dual;
TO_CHAR(SYSDATE,
-----------------
09-05-2004
星期日
trunc(date,fmt)
按
照给出的要求将日期截断
,
如果
fmt
=mi
表示保留分
,
截断秒
SQL> select
to_char(trunc(sysdate,hh), hh24:mi:ss) hh,
2
to_char(trunc(sysdate,mi), hh24:mi:ss) hhmm from
dual;
HH HHMM
------------------- -------------------
2004.05.09 11:00:00
2004.05.09 11:17:00
ROWID
将字符数据类型转换为
ROWID
类型
SQL> select
rowid,rowidtochar(rowid),ename from
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------
------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA
SMITH
AAAAfKAACAAAAEqAAB
AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC
WARD
AAAAfKAACAAAAEqAAD
AAAAfKAACAAAAEqAAD JONES
T(c,dset,sset)
将源字符串
sset
从一个语言字符集转换到另一个目的
dset
字符集<
/p>
SQL> select
convert(strutz,we8hp,f7dec)
conver
------
strutz
AW
将一个十六进制构成的字符串转换为二进制
EXT
将一个二进制构成的字符串转换为十六进制
OCHAR
将
ROWID
数据类型转换为字符类型
_CHAR(date,format)
SQL> select to_char(sysdate,yyyy/mm/dd
hh24:mi:ss) from dual;
TO_CHAR(SYSDATE,YY
-------------------
2004/05/09 21:14:41
? _DATE(string,format)
将字符串转化为
ORACLE
中的一个日期
p>
_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select
to_multi_byte(
高
) from dual;
TO
--
高
_NUMBER
将给出的字符转换为数字
SQL> select to_number(1999)
year from dual;
YEAR
---------
1999
AME(dir
,file)
指定一个外部二进制文件
SQL>insert into file_tb1
values(bfilename(lob_dir1,));
T(x,desc,source)
将
x
字段或
变量的源
source
转换为
desc
SQL> select
sid,serial#,username,decode(command,
2 0,none,
3
2,insert,
4 3,
5 select,
6
6,update,
7 7,delete,
8 8,drop,
9 other) cmd from v$$session where
type!=background;
SID
SERIAL# USERNAME CMD
--------- ---------
------------------------------ ------
1 1 none
2 1
none
3 1 none
4 1 none
5 1
none
6 1 none
7 1275 none
8
1275 none
9 20 GAO select
10 40 GAO none
(s,fmt,start,length)
DUMP
函数以
fmt
指定的内部数字格式返回一个
VARCHAR2
p>
类型的值
SQL> col global_name for a30
SQL> col dump_string for
a50
SQL> set lin 200
SQL> select
global_name,dump(global_name,1017,8,5) dump_string
from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------
--------------------------------------------------
Typ=1 Len=12
CharacterSet=ZHS16GBK: W
,O,R,L,D
_BLOB()
< br>和
EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
ST
返回一组表达式中的最大值
,
即比较字
符的编码大小
.
SQL>
select greatest(AA,AB,AC) from dual;
GR
--
AC
SQL> select greatest(
啊
,
安
,
天
< br>) from dual;
GR
--
天
返回一组表达式中的最小值
SQL> select least(
啊
,
安
,
天
) from dual;
LE
--
啊
返回标识当前用户的唯一整数
SQL> show user
USER
为
SQL> select username,user_id from
dba_users where user_id=uid;
USERNAME USER_ID
------------------------------
---------
GAO 25
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
GAO
N
返回当前用户环境的信息
,opt
可以是
:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,
LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA
查看当前用户是否是
DB
A
如果是则返回
true
SQL> select userenv(isdba) from dual;
USEREN
------
FALSE
SQL> select userenv(isdba)
from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select
userenv(sessionid) from dual;
USERENV(SESSIONID)
--------------------
152
ENTRYID
返回会话人口标志
SQL> select
userenv(entryid) from dual;
USERENV(ENTRYID)
------------------
0
INSTANCE
返回当前
INSTANCE
的标志
SQL>
select userenv(instance) from dual;
USERENV(INSTANCE)
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select
userenv(language) from dual;
USERENV(LANGUAGE)
------------------------------------------------ ----
SIMPLIFIED
CHINESE_16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv(lang)
from dual;
USERENV(LANG)
---------------------------
-------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select
userenv(terminal) from dual;
USERENV(TERMINA
----------------
GAO
VSIZE(X)
返回
X
的大
小
(
字节
)
数
SQL> select
vsize(user),user from dual;
VSIZE(USER) USER
-----------
------------------------------
6 SYSTEM
?
60.A
VG(DISTINCT|ALL)
all
表示对所有的值求平均值
,distinct
只对不同的值求平均值
SQLWKS> create table table3(xm
varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3
values(gao,1111.11);
SQLWKS> insert into table3
values(gao,1111.11);
SQLWKS> insert into table3
values(zhu,5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from 3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from 3;
AVG(ALLSAL)
-----------
2592.59
(DISTINCT|ALL)
求
最大值
,ALL
表示对所有的值求最大值
,DISTINCT
表示对不同的值求最大值
,
相同的只取一次
SQL> select max(distinct sal) from
MAX(DISTINCTSAL)
----------------
5000
(DISTINCT|ALL)
求最小值
,ALL
表示对所有的值求最
小值
,DISTINCT
表示对不同的值求最小值
,
相同的只取一次
SQL> select min(all sal) from 3;
MIN(ALLSAL)
-----------
1111.11
(distinct|all)
求
标准差
,ALL
表示对所有的值求标准差
,DISTINCT
表示只对不同的值求标准差
SQL> select stddev(sal)
from
STDDEV(SAL)
-----------
1182.5032
SQL>
select stddev(distinct sal) from
STDDEV(DISTINCTSAL)
-------------------
1229.951
CE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from
VARIANCE(SAL)
-------------
1398313.9
BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal)
from group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6
9400
对分组统计再加限制条件
SQL> select
deptno,count(*),sum(sal) from group by deptno
having count(*)>=5;
DEPTNO
COUNT(*) SUM(SAL)
---------
--------- ---------
20 5
10875
30 6 9400
SQL> select deptno,count(*),sum(sal)
from having count(*)>=5 group by deptno
DEPTNO COUNT(*) SUM(SAL)
--------- ---------
---------
20 5 10875
30 6 9400
BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from
order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10
MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20
ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30
TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950
68.
pl/sql
中的
case
语句
select
(case
when
DUMMY='X'
then
0
else
1
end)
as
flag
from
dual;
case
的第
1
种用法
:
case col when
'a' then 1
when 'b' then 2
else 0 end
这种用法跟
decode
一样没什么区别
case
的第<
/p>
2
种用法:
case when score <60 then 'd'
when score >=60 and score
<70 then 'c'
when score
>=70 and score <80 then 'b'
else 'a' end
(expr1, expr2)
NVL(expr1,
expr2)->expr1
为
NULL
,返回
ex
pr2
;不为
NULL
,返回
expr1
。注意两者的类型要一
致
NVL2 (expr1, expr2,
expr3) ->expr1
不为
NULL
< br>,返回
expr2
;为
NULL
,返回
expr3
。
< br>expr2
和
expr3
类型不
同的话,
expr3
会转换为
expr
2
的类型
NULLIF (expr1, expr2) ->
相等返回
NULL
,不等返回
expr1
?
Oracle
分析函数参考手册
<
/p>
========================================
=====
作者
:
xsb([url])[/url]
发表于
:2006.03.01 12:22
分类
: DW&BI
出处:
/post/419/33028
---------------------------
------------------------------------
Oracle
从
8.1.6
开始提供分析函数,分析函数用于计算基
于组的某种聚合值,它和聚合函数的不同之
处是对于每个组返回多行,
< br>
而聚合函数对于每个组只返回一行。
常用的分析函数如下所列
:
row_number() over(partition by ...
order by ...)
rank()
over(partition by ... order by ...)
dense_rank() over(partition by ...
order by ...)
count()
over(partition by ... order by ...)
max() over(partition by ... order by
...)
min() over(partition
by ... order by ...)
sum()
over(partition by ... order by ...)
avg() over(partition by ... order by
...)
first_value()
over(partition by ... order by ...)
last_value() over(partition by ...
order by ...)
lag()
over(partition by ... order by ...)
lead() over(partition by ... order by
...)
下面例子中使用的表来自
Orac
le
自带的
HR
用户下的表,如果没有
安装该用户,可以在
SYS
用户下
运行
$$ORACLE_HOME/de
mo/schema/human_resources/hr_
来创建。
除本文内容外,你还可参考:
ROLLUP
与
CUBE
[url]/post/419/29159[/url]
分析函数使用例子介绍:
[url]/post/419/44634[/url]
本文如果未指明,缺省是在
HR
p>
用户下运行例子。
开窗函数的的理解:
开窗函数指定了分析函数工作的数据窗口大小,
这个数据窗口大小可能会随着
行的变化而变化,
举例如下:
over
(
order by
salary
)
按照
salary
排序进行累计,
order
by
是个默认的开窗函数
over
(
partition by
deptno
)按照部门分区
over
(
order by
salary range between 50 preceding and 150
following
)
每行对应的数据窗口是之前行幅度值不超过
50
,
之后行幅度值不超过
150
over
(
order by
salary rows between 50 preceding and 150
following
)
每行对应的数据窗口是之前
50
行,之后
150
行
over
(
order by
salary rows between unbounded preceding and
unbounded following
)
每行对应的数据窗口是从第一行到最后一行,等效:
over
(
order by
salary range between unbounded preceding and
unbounded following
)
主要参考资料:
《
expert
one-on-one
》
Tom
Kyte
《
Oracle9i SQL Reference
》第
6
章
ohwww 2007-3-12 09:19
70
。
AVG
功能描述:用于计算一个组和数据窗口内表达式的平均值。
SAMPLE
:
下面的例子中列
c_mavg
计算员工表中每个员工的平均
薪水报告,
该平均值由当前员工和与之
具有相同经理的前一个和
后一个三者的平均数得来;
SELECT manager_id, last_name,
hire_date, salary,
AVG(salary) OVER (PARTITION BY
manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) AS c_mavg
FROM
employees;
MANAGER_ID
LAST_NAME HIRE_DATE SALARY C_MAVG
---------- -------------------------
--------- ---------- ----------
100 Kochhar 21-SEP-89 17000 17000
100 De Haan 13-JAN-93 17000
15000
100 Raphaely
07-DEC-94 11000 11966.6667
100 Kaufling 01-MAY-95 7900 10633.3333
100 Hartstein 17-FEB-96
13000 9633.33333
100 Weiss
18-JUL-96 8000 11666.6667
100 Russell 01-OCT-96 14000 11833.3333
71
。
CORR
功能描述:返回一对表达式的相关系数,它是如下的缩写:
COVAR_POP(expr1,expr2)/STDD
EV_POP(expr1)*STDDEV_POP(expr2))
从统计上讲,相关性是变量之间关联的强度,变量之间的关联意味着在某种程度
上一个变量的值可由其它的值进行预测。通过返回一个
-1~1
之间的一个数
,
相关
系数
给出了关联的强度,
0
表示不相关。
SAMPLE
:下例返回
1998
年月销售收入和月单位销售的关系的累积系数(本例在
SH
用户下运行)
SELECT ar_month_number
,
CORR (SUM(_sold),
SUM(ty_sold))
OVER (ORDER
BY ar_month_number) as CUM_CORR
FROM sales s, times t
WHERE _id = _id AND calendar_year =
1998
GROUP BY
ar_month_number
ORDER BY
ar_month_number;
CALENDAR_MONTH_NUMBER CUM_CORR
---------------------
----------
1
2 1
3 .994309382
4 .852040875
5 .846652204
6
.871250628
7 .910029803
8 .917556399
9 .920154356
10
.86720251
11 .844864765
12 .903542662
72
。
COVAR_POP
功能描述:返回一对表达式的总体协方差。
SAMPLE
:下例
CUM_COVP
返回定价和最小产品价格的累积总体协方差
< br>
SELECT product_id,
supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id,
supplier_id) AS CUM_COVP
,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id,
supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID
CUM_COVP CUM_COVS
---------- ----------- ----------
----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778
2554.16667
1825 103093
1926.25 2568.33333
2004
103086 1591.4 1989.25
2005
103086 1512.5 1815
2416
103088 1475.97959 1721.97619
.
.
73
。
COVAR_SAMP
功能描述:返回一对表达式的样本协方差
SAMPLE
:下例
CUM_COVS
返回定价和最小产品价格的累积样本协方差
< br>
SELECT product_id,
supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id,
supplier_id) AS CUM_COVP
,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id,
supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID
CUM_COVP CUM_COVS
---------- ----------- ----------
----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778
2554.16667
1825 103093
1926.25 2568.33333
2004
103086 1591.4 1989.25
2005
103086 1512.5 1815
2416
103088 1475.97959 1721.97619
? 74
。
COUNT
功能描述:对一组内发生的事情进行累积计数,如果指定
p>
*
或一些非空常数,
count
将对所有行计数,如
果指定一个表达式,
coun
t
返回表达式非空赋值的计数,当有相同值出现时,这些相
等的值都会被纳入被计算的值;可以使用
DISTINCT
来记
录去掉一组中完全
相同的数据后出现的行数。
SAMPLE
:下面例子中计算每个员工在按薪水排序中当前行附近薪
水在
[n-50,n+150]
之间的行数,
< br>n
表
示当前行的薪水
例如,
Philtanker
的薪水
2200
,排在他之前的行中薪水大于
等于
2200-50
的有
1
行,排在他之后的行
中薪水小于等于
2200
p>
+
150
的行
没有,所以
count
计数值
cnt3
为
2
(包括自己当前行)
;
cnt2
值相当于小于等于当前行的
SALARY
值的
所有行数
SELECT last_name, salary, COUNT(*) OVER
() AS cnt1,
COUNT(*) OVER
(ORDER BY salary) AS cnt2,
COUNT(*) OVER (ORDER BY salary RANGE
BETWEEN 50 PRECEDING
AND
150 FOLLOWING) AS cnt3 FROM employees;
LAST_NAME SALARY CNT1 CNT2 CNT3
-------------------------
---------- ---------- ---------- ----------
Olson 2100 107 1 3
Markle 2200 107 3 2
Philtanker 2200 107 3 2
Landry 2400 107 5 8
Gee 2400 107 5 8
Colmenares 2500 107 11 10
Patel 2500 107 11 10
.
.
75
。
CUME_DIST
功能描述:计算一行在组中的相对位置,
CUME_DIST
总是返回大于
0
、小于或等于
1
的数,该数表示该
行
在
N
行中的位置。例如,
在一个
3
行
的组中,返回的累计分布值为
1/3
、
2/3
、
3/3
< br>SAMPLE
:下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比
SELECT job_id,
last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY
salary) AS cume_dist
FROM
employees WHERE job_id LIKE 'PU%';
JOB_ID LAST_NAME SALARY CUME_DIST
----------
------------------------- ---------- ----------
PU_CLERK Colmenares 2500 .2
PU_CLERK Himuro 2600 .4
PU_CLERK Tobias 2800 .6
PU_CLERK Baida 2900 .8
PU_CLERK Khoo 3100 1
PU_MAN Raphaely 11000 1
76
。
DENSE_RANK
功能描述:根据
ORDER
BY
子句中表达式的值,从查询返回的每一行,计算它们与其它行的相
对位置。
组内的数据按
ORDER
B
Y
子句排序,然后给每一行赋一个号,从而形成一个序列,该序列从
1
开始,往
后累加。每次
ORDE
R
BY
表达式的值发生变化时,该序列也随之增加。有同样值
的行得到同样的数字序
号(认为
null
时相等的)
。密集的序列返回的时没有间隔的数
SAMPLE
:
下例中计算每个员工按部门分区再按薪水排序,
依次出现的序列号
< br>(注意与
RANK
函数的区别)
SELECT ment_id , _name, ,
DENSE_RANK()
OVER
(PARTITION BY ment_id ORDER BY ) as drank
FROM employees e,
departments d
WHERE ment_id
= ment_id
AND ment_id IN
('60', '90');
DEPARTMENT_ID
LAST_NAME SALARY DRANK
------------- -------------------------
---------- ----------
60
Lorentz 4200 1
60 Austin
4800 2
60 Pataballa 4800 2
60 Ernst 6000 3
60 Hunold 9000 4
90 Kochhar 17000 1
90 De Haan 17000 1
90 King 24000 2
77
。
FIRST
功能描述:从
DENSE_RANK
返回的集合中取出排在最前面的一个值的行(可能多行,因为值可能相等)
,
因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE
:下面
例子中
DENSE_RANK
按部门分区,再按佣金
commission_pct
排序,
FIRST
取出佣金
最低的对应的所有行,
然后前
面的
MAX
函数从这个集合中取出薪水最低的值;
LAST
取出佣金最高的对应
的所有行,然后前面的
MIN
函数从这个集合中取出薪水最高的值
SELECT last_name,
department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST
ORDER BY commission_pct)
OVER (PARTITION BY department_id)
MAX(salary) KEEP
(DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY
department_id)
FROM
employees
WHERE
department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID
SALARY Worst Best
------------------------- -------------
---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
Ande 80 6400 6100 14000
Lee 80 6800 6100 14000
Tuvault 80 7000 6100 14000
Sewall 80 7000 6100 14000
Marvins 80 7200 6100 14000
Bates 80 7300 6100 14000
.
.
.
78
。
FIRST_VALUE
功能描述:返回组中数据窗口的第一个值。
SAMPLE
:下面例子计算按部门
分区按薪水排序的数据窗口的第一个值对应的名字,如果薪水的第一个值
有多个,则从多
个对应的名字中取缺省排序的第一个名字
SELECT department_id, last_name,
salary, FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER
BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in(20,30);
DEPARTMENT_ID LAST_NAME
SALARY LOWEST_SAL
------------- -------------------------
---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000
Colmenares
79
。
LAG
功能描述:可以访问结果集中的其它行而不用进行自连接。它
允许去处理游标,就好像游标是一个数组一
样。在给定组中可参考当前行之前的行,这样
就可以从组中与当前行一起选择以前的行。
Offset
是一个
正
整数,其默认值为
1
,若索引超出窗
口的范围,就返回默认值(默认返回的是组中第一行)
,其相反的函数
< br>是
LEAD
SAMPLE<
/p>
:下面的例子中列
prev_sal
返回
按
hire_date
排序的前
1
p>
行的
salary
值
SELECT last_name,
hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY
hire_date) AS prev_sal
FROM
employees
WHERE job_id =
'PU_CLERK';
LAST_NAME
HIRE_DATE SALARY PREV_SAL
------------------------- ----------
---------- ----------
Khoo
18-5
月
-95 3100 0
Tobias
24-7
月
-97 2800
3100
Baida
24-12
月
-97 2900 2800
Himuro
15-11
月
-98 2600 2900
Colmenares
10-8
月
-99 2500
2600
80
。
LAST
功能描述:从
DENSE_RANK
返回的集合中取出排在最后面的一个值的行(可能多行,因为值可能相等)
,
因此完整的语法需要在开始处加上一个集合函数以从中取出记录
SAMPLE
:下面
例子中
DENSE_RANK
按部门分区,再按佣金
commission_pct
排序,
FIRST
取出佣金
最低的对应的所有行,
然后前
面的
MAX
函数从这个集合中取出薪水最低的值;
LAST
取出佣金最高的对应
的所有行,然后前面的
MIN
函数从这个集合中取出薪水最高的值
SELECT last_name,
department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST
ORDER BY commission_pct)
OVER (PARTITION BY department_id)
MAX(salary) KEEP
(DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY
department_id)
FROM
employees
WHERE
department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID
SALARY Worst Best
------------------------- -------------
---------- ---------- ----------
Fay 20 6000 6000 13000
-
-
-
-
-
-
-
-
-
上一篇:高考英语情景交际用法总结(完整)
下一篇:Oracle重要的几个常用函数(最精简)