oracle条件函数,oracle常用函数详解详细
oracle数据库中函数怎么使用
oracle数据库中函数较多，而且每个函数都有自己的用法，所以要根据具体的函数查询具体的使用方法，比如：
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
使用方法：
1、比较大小
select
decode(sign(变量1-变量2),-1,变量1,变量2)
from
dual;
--取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2、此函数用在SQL语句中，功能介绍如下：
Decode函数与一系列嵌套的
IF-THEN-ELSE语句相似。base_exp与compare1,compare2等等依次进行比较。如果base_exp和
第i
个compare项匹配，就返回第i
个对应的value
。如果base_exp与任何的compare值都不匹配，则返回default。每个compare值顺次求值，如果发现一个匹配，则剩下的compare值（如果还有的话）就都不再求值。一个为NULL的base_exp被认为和NULL
compare值等价。如果需要的话，每一个compare值都被转换成和第一个compare
值相同的数据类型，这个数据类型也是返回值的类型。
oracle中常用的函数有哪些？
常用的看你使用程度了\x0d\x0a\x0d\x0a常用的有日期处理函数(month_between,add_months,next_day,extract...)\x0d\x0a转换函数(to_number,to_char,to_date)\x0d\x0a字符处理函数(substr,replace,trim,upper,lower,concat,instr...)\x0d\x0a数学函数(我基本没用过,所以无法提供 rondom,trunc)\x0d\x0a逻辑函数(coalesce,nvl..)\x0d\x0a聚集函数(sum,avg,max,min)
110个oracle常用函数总结(8)
RATIO_TO_REPORT功能描述 该函数计算expression/(sum(expression))的值 它给出相对于总数的百分比 即当前行对sum(expression)的贡献
SAMPLE 下例计算每个员工的工资占该类员工总工资的百分比
SELECT last_name salary RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = PU_CLERK ;
LAST_NAME SALARY RR
Khoo Baida Tobias Himuro Colmenares
REGR_ (Linear Regression) Functions功能描述 这些线性回归函数适合最小二乘法回归线 有 个不同的回归函数可使用
REGR_SLOPE 返回斜率 等于COVAR_POP(expr expr ) / VAR_POP(expr ) REGR_INTERCEPT 返回回归线的y截距 等于AVG(expr ) REGR_SLOPE(expr expr ) * AVG(expr )
REGR_COUNT 返回用于填充回归线的非空数字对的数目
REGR_R 返回回归线的决定系数 计算式为
If VAR_POP(expr ) = then return NULL
If VAR_POP(expr ) = and VAR_POP(expr ) != then return If VAR_POP(expr ) and VAR_POP(expr != then return POWER(CORR(expr expr) ) REGR_AVGX 计算回归线的自变量(expr )的平均值 去掉了空对(expr expr )后 等于AVG(expr ) REGR_AVGY 计算回归线的应变量(expr )的平均值 去掉了空对(expr expr )后 等于AVG(expr ) REGR_SXX 返回值等于REGR_COUNT(expr expr ) * VAR_POP(expr ) REGR_SYY 返回值等于REGR_COUNT(expr expr ) * VAR_POP(expr ) REGR_SXY: 返回值等于REGR_COUNT(expr expr ) * COVAR_POP(expr expr )
（下面的例子都是在SH用户下完成的）
SAMPLE 下例计算 年最后三个星期中两种产品（ 和 ）在周末的销售量中已开发票数量和总数量的累积斜率和回归线的截距
SELECT t fiscal_month_number Month t day_number_in_month Day REGR_SLOPE(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_month_desc t day_number_in_month) AS CUM_SLOPE REGR_INTERCEPT(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_month_desc t day_number_in_month) AS CUM_ICPT FROM sales s times t WHERE s time_id = t time_id AND s prod_id IN ( ) AND t fiscal_year= AND t fiscal_week_number IN ( ) AND t day_number_in_week IN ( ) ORDER BY t fiscal_month_desc t day_number_in_month;
Month Day CUM_SLOPE CUM_ICPT
SAMPLE 下例计算 年 月每天的累积交易数量
SELECT UNIQUE t day_number_in_month REGR_COUNT(s amount_sold s quantity_sold) OVER (PARTITION BY t fiscal_month_number ORDER BY t day_number_in_month)
Regr_Count
FROM sales s times t WHERE s time_id = t time_id AND t fiscal_year = AND t fiscal_month_number = ;
DAY_NUMBER_IN_MONTH Regr_Count
SAMPLE 下例计算 年每月销售量中已开发票数量和总数量的累积回归线决定系数
SELECT t fiscal_month_number REGR_R (SUM(s amount_sold) SUM(s quantity_sold)) OVER (ORDER BY t fiscal_month_number) Regr_R FROM sales s times t WHERE s time_id = t time_id AND t fiscal_year = GROUP BY t fiscal_month_number ORDER BY t fiscal_month_number;
FISCAL_MONTH_NUMBER Regr_R
SAMPLE 下例计算 年 月最后两周产品 的销售量中已开发票数量和总数量的累积平均值
SELECT t day_number_in_month REGR_AVGY(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_month_desc t day_number_in_month) Regr_AvgY REGR_AVGX(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_month_desc t day_number_in_month)
Regr_AvgX
FROM sales s times t WHERE s time_id = t time_id AND s prod_id = AND t fiscal_month_desc = AND t fiscal_week_number IN ( ) ORDER BY t day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX
SAMPLE 下例计算产品 和 在 年 月周末销售量中已开发票数量和总数量的累积REGR_SXY REGR_SXX and REGR_SYY统计值
SELECT t day_number_in_month REGR_SXY(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_year t fiscal_month_desc) Regr_sxy REGR_SYY(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_year t fiscal_month_desc) Regr_syy REGR_SXX(s amount_sold s quantity_sold) OVER (ORDER BY t fiscal_year t fiscal_month_desc) Regr_sxx FROM sales s times t WHERE s time_id = t time_id AND prod_id IN ( ) AND t fiscal_month_desc = AND t day_number_in_week IN ( ) ORDER BY t day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx
ROW_NUMBER功能描述 返回有序组中一行的偏移量 从而可用于按特定标准排序的行号
SAMPLE 下例返回每个员工再在每个部门中按员工号排序后的顺序号
SELECT department_id last_name employee_id ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id ;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
Whalen
Hartstein
Fay
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Mavris
STDDEV功能描述 计算当前行关于组的标准偏离 （Standard Deviation）
SAMPLE 下例返回部门 按雇佣日期排序的薪水值的累积标准偏离
SELECT last_name hire_date salary
STDDEV(salary) OVER (ORDER BY hire_date) StdDev
FROM employees
WHERE department_id = ;
LAST_NAME HIRE_DATE SALARY StdDev
Raphaely 月
Khoo 月 Tobias 月 Baida 月 Himuro 月 Colmenares 月
STDDEV_POP功能描述 该函数计算总体标准偏离 并返回总体变量的平方根 其返回值与VAR_POP函数的平方根相同 （Standard Deviation－Population）
SAMPLE 下例返回部门 的薪水值的总体标准偏差
SELECT department_id last_name salary
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in ( );
DEPARTMENT_ID LAST_NAME SALARY POP_STD
Hartstein
Fay
Raphaely Khoo Baida Colmenares Himuro Tobias Hunold Ernst Austin Pataballa Lorentz
STDDEV_SAMP功能描述 该函数计算累积样本标准偏离 并返回总体变量的平方根 其返回值与VAR_POP函数的平方根相同 （Standard Deviation－Sample）
SAMPLE 下例返回部门 的薪水值的样本标准偏差
SELECT department_id last_name hire_date salary
STDDEV_SAMP(salary) OVER
(PARTITION BY department_id ORDER BY hire_date
ROWS BEEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
WHERE department_id in ( );
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
Hartstein 月
Fay 月
Raphaely 月
Khoo 月 Tobias 月 Baida 月 Himuro 月 Colmenares 月
Hunold 月
Ernst 月 Austin 月 Pataballa 月 Lorentz 月
SUM功能描述 该函数计算组中表达式的累积和
SAMPLE 下例计算同一经理下员工的薪水累积值
SELECT manager_id last_name salary
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in ( );
MANAGER_ID LAST_NAME SALARY L_CSUM
Whalen
Mavris
Baer
Greenberg
Higgins
Lorentz
Austin
Pataballa
Ernst
Popp
Sciarra
Urman
Chen
Faviet
VAR_POP功能描述 （Variance Population）该函数返回非空集合的总体变量（忽略null） VAR_POP进行如下计算
(SUM(expr ) SUM(expr) / COUNT(expr)) / COUNT(expr)
SAMPLE 下例计算 年每月销售的累积总体和样本变量（本例在SH用户下运行）
SELECT t calendar_month_desc VAR_POP(SUM(s amount_sold)) OVER (ORDER BY t calendar_month_desc) Var_Pop VAR_SAMP(SUM(s amount_sold)) OVER (ORDER BY t calendar_month_desc) Var_Samp FROM sales s times t WHERE s time_id = t time_id AND t calendar_year = GROUP BY t calendar_month_desc;
CALENDAR Var_Pop Var_Samp
E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+
VAR_SAMP功能描述 （Variance Sample）该函数返回非空集合的样本变量（忽略null） VAR_POP进行如下计算
(SUM(expr*expr) SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr) )
SAMPLE 下例计算 年每月销售的累积总体和样本变量
SELECT t calendar_month_desc VAR_POP(SUM(s amount_sold)) OVER (ORDER BY t calendar_month_desc) Var_Pop VAR_SAMP(SUM(s amount_sold)) OVER (ORDER BY t calendar_month_desc) Var_Samp FROM sales s times t WHERE s time_id = t time_id AND t calendar_year = GROUP BY t calendar_month_desc;
CALENDAR Var_Pop Var_Samp
E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+ E+
VARIANCE功能描述 该函数返回表达式的变量 Oracle计算该变量如下 如果表达式中行数为 则返回 如果表达式中行数大于 则返回VAR_SAMP
SAMPLE 下例返回部门 按雇佣日期排序的薪水值的累积变化
SELECT last_name salary VARIANCE(salary)
OVER (ORDER BY hire_date) Variance
FROM employees
WHERE department_id = ;
LAST_NAME SALARY Variance
Raphaely
Khoo
Tobias Baida
Himuro
lishixinzhi/Article/program/Oracle/201311/17896