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经典实例》