oracle中的数值函数整理

主要分为三块介绍（单值函数、聚合函数、列表函数）

一、单值函数（比较简单，看一遍基本也就理解记住了）

1、基本加减乘车没有什么可说的，只需要注意一点，任何值与null一起运算 ，结果都为null，因为null代表着未知值，与null进行加减乘除运算得到的还是未知值，返回的结果还是null。
select 3+null ,3-null,3*null,3/null from dual --执行结果都是null

nvl(cgl,0)-nvl(cglcom,0)  先置为0再进行计算

2、nvl和nvl2 空值置换函数

 

3、abs 绝对值函数

select abs(2.1),,abs(-2.1) from dual--执行结果都是2.1

4、ceil 、 floor 这两个也比较简单啦
ceil 只入不舍函数
floor 只舍不入函数
select ceil(-2.6),ceil(2.6), floor(-2.6),floor(2.6) from dual
执行结果依次为 -2 3 -3 2
5、round 和 trunc 又是一对有关系的函数
round 按照给定精度进行舍入 两个参数，第一个代表要处理的数值，第二个代表精度，第二个参数可以省略，省略的时候默认值为0
trunc 按照给定精度截取数字 两个参数，第一个代表要处理的数值，第二个代表精度，第二个参数可以省略，省略的时候默认值为0
select round(2.615),round(-2.615),round(2.615,0),round(-2.615,0) ,round(2.615 ,1),round(-2.615,1),round(2.615 ,2),round(-2.615,2) from dual
执行结果依次为 3 -3 3 -32.6-2.62.62-2.62
select trunc(2.615),trunc(-2.615),trunc(2.615,0),trunc(-2.615,0) ,trunc(2.615 ,1),trunc(-2.615,1),trunc(2.615 ,2),trunc(-2.615,2) from dual
执行结果依次为1 2 -2 2 -22.6-2.62.61-2.61
注意 ： round 和 trunc 这两个函数的第二个参数可以为负值，就是从小数点开始往左数
select round(248.1,-1),round(-248.1,-1),round(248.1,-2),round(-248.1,-2) from dual
执行结果依次为 250 -250 200 -200
select trunc(248.1,-1),trunc(-248.1,-1),trunc(248.1,-2),trunc(-248.1,-2) from dual
执行结果依次为 240 -240 200 -200
6、mod 取余函数
select mod(100,5),mod(22,23),mod(10,4),mod(-10,4),mod(-21.12,7),mod(100,0),mod(100,-21),mod(0,2) from dual

执行结果依次为 0 22 2 -2 -0.12 10016 0
需要注意的是数值为负值和0的情况
根据 mod(value,1) 是否等于0可以判断value值是否为整数。
7、power 和 sqrt 分别是求平方和求平方根的意思
select power(3,2),power(2,-2),SQRT(9),sqrt(0) from dual
执行结果依次为 9 0.25 3 0
需要注意的地方：
sqrt是求平方根的,它就一个参数,而且必须是非负数。
oracle中 0.5可以省略成.5 今天看到.5这个字样的时候着实纠结了一会
8、sign 判断一个数值是正数 还是负数 还是0 正数返回1负数返回-1 0返回0 这个还是比较常用的
select sign(2.1),sign(0),sign(-2.1) from dual
执行结果依次为 1 0 -1
9、其余的一些幂运算 、 三角函数运算的函数就不介绍了,平时的商业开发很少用到,科技工作中会比较常用吧。
二、聚集函数

我们需要一个表来演示相关的例子了,建一个表table_a(代表不同城市早、中、晚的气温数据，数据的合理性就先不要管了)

name	time	num_morning	num_nooning	num_night
哈尔滨	2013-12-14	21.0		11.0
哈尔滨	2013-12-15	22.0	28.0	35.0
哈尔滨	2013-12-16
哈尔滨	2013-12-17		36.0	22.0
哈尔滨	2013-12-18	23.0	71.0	11.0
北 京	2013-12-14	33.0	33.0	30.0
北 京	2013-12-15	25.0	27.0
北 京	2013-12-16	31.0	31.0	12.0
北 京	2013-12-17	44.0	25.0	31.0
北 京	2013-12-18	24.0	25.0	23.0
绥 化	2013-12-14	19.0	18.0	21.0
绥 化	2013-12-15	24.0	24.0	21.0
绥 化	2013-12-16	25.0	25.0	24.0
绥 化	2013-12-17	24.0	24.0	31.0
绥 化	2013-12-18	21.0	19.0	12.0

1、聚集函数中对于null的态度是 忽略

举个例子看看： 我们查询一下各个城市最近5天每天早上的平均气温

select name ,avg(num_morning) from table_a group by name 执行的结果是22 说明它忽略了两天为空的记录,根据三天计算的平均值

执行结果：

name	avg(num_morning)
绥 化	22.6
哈尔滨	22
北 京	31.4

再举个例子:表中有几条哈尔滨早上的气温纪录

select count(num_morning) from table_a where name ='哈尔滨'; 执行结果是3

注意：count(num_morning)可以忽略num_morning为null的数据使用count(*)就不能忽略null了 即时所有字段都为null,count(*)也不会忽略这行（起码还有rowid）

2、常用的聚合函数 max min sum avg count 平时很常用了 举个例子不多说了。

(1)、查询各个城市白天的最高气温、最低气温、总和 、统计天数、平均气温

select name, max(num_morning) 最高气温,min(num_morning) 最低气温,sum(num_morning) 总和,count(num_morning) 统计天数,avg(num_morning) 平均气温 from table_a group by name

执行结果：

name	最高气温	最低气温	总和	统计天数	平均气温
绥 化	25	19	113	5	22.6
哈尔滨	23	21	66	3	22
北 京	44	24	157	5	31.4

3、stddev(标准差函数) variance(标准方差函数) 两者格式与上面的max sum等一致。

4、聚合函数中的 distinct

所有的聚合函数都有一个distinct与all选项。

distinct 表示该列有相同的值的时候只取一列。举个例子说明下：

select count(distinct name),count(name),count(*),count(all name ) from table_a

执行结果： 3 15 15 15

三、列表函数

聚合函数与列表函数的区别：聚合函数是作用在多行的同一列上，组合函数是作用在一行的多个列上，很明显了吧！不明显的话下面有对应的例子，看完保证明显。

1、greatest(取多个数值的最大值) least（取多个数值的最小值）

例子： 取出 1 ，3，2，1，5，4 几个数中的最大值和最小值

select greatest(1,3,2,1,5,4),least(1,3,2,1,5,4) from dual 返回值 5 1

可以对数值进行比较,对字符串进行比较

看完上面的例子再让我们求各个城市每天的最高气温 、最低气温是多少就很简单了，不写了

注意：这两个函数对null的态度跟单值函数一样,当成未知，结果都会返回null

2、coalesce 参数是一串值，返回是第一个出现的不为空的值，如果全都为空 那么返回null

select coalesce(null,'','',null,'1',null) from dual 返回 1

注意：各个参数的类型必须一致。

四、运算优先级

1、符号优先级：圆括号优先级最高、其次是乘除法、再其次是加减法、当优先级相同的时候是从左向右执行。

2、and 和 or的优先级比较： and 优先级 高于 or

举个例子：查询早上气温大于中午并且早上气温大于晚上 或者 中午气温大于晚上的数据

我有两种写法：

select * from table_a where num_morning>num_nooning and num_morning>num_night or num_nooning>num_night;

select * from table_a where num_nooning>num_night or num_morning>num_nooning and num_morning>num_night

经测试两个sql返回结果都一样：

1 哈尔滨 2013-12-17 36.022.0
2 哈尔滨 2013-12-18 23.071.011.0
3 北 京 2013-12-14 33.033.030.0
4 北 京 2013-12-16 31.031.012.0
5 北 京 2013-12-17 44.025.031.0
6 北 京 2013-12-18 24.025.023.0
7 绥 化 2013-12-15 24.024.021.0
8 绥 化 2013-12-16 25.025.024.0
9 绥 化 2013-12-18 21.019.012.0