oracle数值处理,Oracle经典实例——数值处理

1.计算平均值

select avg(field1) avg_field1 from table_name;

AVG函数会忽略null值，如果计算的字段中有null值，直接使用AVG的计算结果会不正确

2.查找最大值和最小值

使用MAX和MIN函数查找最大值和最小值：

select max(field),min(field) from table_name;

使用GROUP BY对字段进行分组后查找最大值、最小值：

select field1,max(field2),min(field2) from table_name group by field1;

MAX和MIN函数会忽略null值，当GROUP BY查询结果中有null分组时，最值将返回null

3.求和

针对整个表求和，直接使用SUM函数：

select sum(field) from table_name;

把数据分组再进行求和：

select field1,sum(field2) from table_name group by field1;

SUM函数会忽略null值，对于null分组，求和返回的值为null

4.计算行数

计算整个表的总行数，直接使用COUNT即可：

select count(*) from table_name;

使用GROUP BY把数据分成多个组：

select field1,count(field2) from table_name group by field1;

COUNT函数会包含null值，使用符号*或者常量参数的时候就会包含null值；

如果参数列的值都为null或表里没有任何数据时，COUNT函数就会返回0

5.累计求和

使用SUM函数的窗口函数版本进行累计求和：

select field1, field2, sun(field2) over(order by field2, field3) running_total

from table_name

order by 2;

其中，有唯一性约束的field3字段的加入是为了保证求和结果中不包含重复值

6.计算累计乘积

使用窗口函数SUM OVER，并利用对数来模拟乘法：

select field1,field2,field3,

exp(sum(ln(field3)over(order by field1,field2)) as running_prod

from table_name where condition;

不能使用SQL计算负数和0的对数，应避免将0和负数传递给LN函数

7.计算累计差

使用窗口函数SUM OVER计算累计差：

select field1,field2,

sum(case when rn = 1 then field2 else -field2 end) over(order by field2, field3) running_diff

from (select fiel1,field2,field3,row_number() over(order by field2, field3) rn

from table_name where conddition);

8.计算众数

使用聚合函数MAX的KEEP扩展来查找field列的众数：

select max(field) keep(dense_rank first order by cnt desc) field

from (select field, count(*) cnt

from table_name

where condition

group by field);

如果有多个众数，KEEP只会保留数值最大的那一个

9.计算中位数

使用函数MEDIAN：

select median(field) from table_name where condition;

10.计算百分比

使用内嵌视图和窗口函数SUM OVER得到field2字段的总和和field1字段满足的条件的总和，然后在外层执行除法和乘法：

select distinct (f10 / total) * 100 pct

from (select field1,

sum(field2) over() total,

sum(field2) over(partition by field1) f10

from table_name)

where comdition;

11.聚合null值

使用COALESCE函数把null值转换为0，以保证使用聚合函数时结果不会出错：

select avg(coalesce(field,0)) avg_field from table_name where condition;

12.计算平均值时去掉最大值和最小值

select avg(field)

from (select field,

min(field) over() min_field,

max(field) over() max_field

from table_name)

where field not in (min_field, max_field);

13.将含有字母和数字的字符串转换为数字

使用函数TRANSLATE和REPLACE从含有字母和数字的字符串里提取数字字符：

select cast(replace(translate('zghj123sdjf321',

'abcdefghijklmnopqrstuvwxyz',

rpad('#', 26, '#')),

'#') as integer) num

from dual;

整理自《SQL经典实例》