Oracle_高阶函数

1. Ratio_to_report

语法：RATIO_TO_REPORT ( ratio_expression ) OVER ( [ PARTITION BY partition_expression ] )

用途：ratio_to_report是一个分析函数，用来计算一个值在一个数据集中所占的比例。如果expr为null，则整个函数的值也会为null。 

这个数据由query_patition_clause决定，如果不写这个子句，这个函数会计算查询后返回的所有行。根据over窗口函数的作用区间，求出作用区间中的单个值在整个区间的总值的比重。ratio_to_report()括号中是分子，over()括号中就是分母，分母缺失就是整个占比。

注意

1. expr表达式不能进行ratio_to_report函数的嵌套，但是可以使用其他的函数；
2. 当sal的值乘以一个常数值的时候并不影响计算结果，计算结果与sal没有乘100的计算结果一致。

select ename,sal,depno,ratio_to_report(sal * 100) over(partition by deptno)

 from emp

over()中为空值时，表示占所有的百分比。

例子

--求每个月销售额占比

select area,month,sum(amount) amount,

       round(ratio_to_report(sum(amount)) over(partition by area),4) * 100 ||'%' area_pct

from testa

group by area, month;

--查询每个员工占所在部门的工资比例

select e.department_id,e.employee_id,e.salary,sum(e.salary) over(partition by e.department_id) sum_salary,

  round(ratio_to_report(e.salary) over(partition by e.department_id),4) * 100 || '%' ratio_salary

from employees e;

2. Coalsece

语法：Coalesce(expr1,expr2,...,exprn)

含义：coaleace返回参数列表中第一个非空表达式，必须指定最少两个参数，如果所有的参数都是null，则返回null.

oracle使用短路运算，它依次对每个表达式求值判断是否为空值，不是对所有表达式都求职后灾进行判断第一个非空值。

coalesce函数是nvl函数的扩展。可以使用coalesce函数替换一个case语句，

例；

Coalesce(expr1,expr2);

--等价于

Case when expr1 is not null

Then expr1

Else

Expr2

End;

注意：空值加任何值都是空值，空值乘以任何值都是空值，以此类推；

         所有表达式必须是相同类型的，或者可以隐性转换为相同的数据类型

空值相关的函数

Nvl

Nvl(commission_pct,0)

如果第一个参数为null，则返回第二个参数，如果第一个参数为非null，则返回第一个参数。

--示例：计算年薪（工资+提成）
Select last_name,salary,nvl(commission_pct,0),
(salary * 12) + (salary * 12 *nvl(commission_pct,0)) annual_salary
From employees
Where last_name=’Maltos’;

Nvl2

Nvl2()commission_pct,’sal_comm’,’sal’)

如果第一个参数为null，则返回第三个参数，如果第一个参数为非null，则返回第二个参数

--示例：

Select last_name,salary,commission_pct,

Nvl2(commission_pct,’sal+comm’,’sal’) income

From employees

Where department_id in(50,80);

Nullif

Nullif(length(firsy_name),length(last_name))

如果两个参数值相等，则返回null，如果两个参数不等，则返回第一个表达式值

--示例：

SELECT FIRST_NAME ,LENGTH(FIRST_NAME) "EXPR1",

       LAST_NAME, LENGTH(LAST_NAME) "expr2",

  nullif(length(first_name),length(last_name)) result

FROM EMPLOYEES;

If-THEN-ELSE------条件表达式

CASE语句（SQL标准，编写复杂）

--示例：

SELECT  LAST_NAME,JOB_ID,SALARY,

       CASE JOB_ID

WHEN 'IT_PROG'

THEN 1.10*SALARY

                   WHEN 'ST_CLERK'

THEN 1.15*SALARY

                 WHEN 'SA_REP'

THEN 1.20*SALARY

   ELSE SALARY END "REVISED SALARY"

FROM EMPLOYEES;

decode函数

DECODE(COL|EXPRESSION,SERACH1,RESULT1

[SEARCH2,RESULT1,...,]

[,DEFAULT])

如果 search1 = expression 则返回result1，如果 search2 = expression 则返回result2

DECODE函数的作用：它可以将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。 

区别于SQL的其它函数，DECODE函数还能识别和操作空值。