oracle中的数值函数整理

对Oracle的数值函数多知道一些还是有好处的,做商业报表类的项目的时候会经常用到。 起码要知道oracle都给我们提供了哪些处理函数，不要到时候需要用的时候却不知道某个函数的存在而导致开发过程绕弯子。今天我就捧着一本《oracle database11g完全参考手册》将oracle提供给我们的数值函数从头捋了一遍。

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

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

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

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(代表不同城市早、中、晚的气温数据，数据的合理性就先不要管了)

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

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

执行结果：

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

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

执行结果：

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

例子结果充分说明了and的优先级要大于or 优先级要是怕弄错的话就加括号强制先后执行就不怕了。