oracle学习笔记（四）-- 数学函数

一、数学函数

名称	格式		功能	实例
abs	abs( number )		取绝对值	abs(-23) return 23 abs(-23.6) return 23.6
acos	acos( number )		arc cosine	acos(0.2) return 1.36943840600457 acos(0.35) return 1.21322522314939
asin	asin( number )		arc sine	asin(0.2) return 0.201357920790331 asin(-0.15) return -0.150568272776686
atan	atan( number		arc tangent	atan(0.35) return 0.336674819386727 atan(-0.15) return -0.148889947609497
atan2	atan2( n, m )		N,m 的 arc tangent	atan2(0.2, 0.3) return 0.588002603547568 atan2(0.35, -0.15) return 1.97568811307998
cos	cos( number)		cosine	cos(0.35) return 0.939372712847379 cos(-3.15) return -0.999964658471342
sin	sin( n )		sine	sin(3) return 0.141120008059867 sin(5.2) return -0.883454655720153
sinh	sinh( n )		Hyperbolic sine	sinh(3) return 10.0178749274099 sinh(5.2) return 90.6333626553652
tan	tan( n )		tangent	tan(3) return -0.142546543074278 tan(5.2) return -1.88564187751976 tan(-5.2) return 1.88564187751976
cosh	cosh( number )		hyperbolic cosine	cosh(0.35) return 1.06187781915599 cosh(-3.15) return 11.6894583539049
tanh(	tanh( n )		Hyperbolic  tangent	tanh(3) return 0.99505475368673 tanh(5.2) return 0.999939136886199
Avg	Avg( expression )		求平均	SELECT Avg(sales *  0.10) as "Average Commission" FROM orders;
bitand	bitand( expr1, expr2 )		二进制AND操作	bitand(5,3) return 1 bitand(15,7) return 7
ceil	ceil( number )		获得与当前数据最接近的整数	ceil(32.65) would return 33. ceil(-32.65) would return -32.
corr	corr( n ,m ) [ over (analytic_clause) ]		N,m都必须为number类型	select corr(quantity, commission) from sales; select max_entents, corr(max_trans, initial_extent) from data group by max_extents
cosh	cosh( number )		hyperbolic cosine	cosh(0.35) return 1.06187781915599 cosh(-3.15) return 11.6894583539049
covar_pop	covar_pop( expression1, expression2 )		特定的。参数都为number	select owner, covar_pop(avg_row_len, avg_space) from all_tables group by owner;
covar_samp	covar_samp( expression1, expression2 )		标准的。参数都为number	select owner, covar_samp(avg_row_len, avg_space) from all_tables group by owner
COUNT	COUNT		满足条件的记录行数	SELECT COUNT(expression) FROM tables WHERE predicates;
cume_dist	cume_dist( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )		相当于Aggregate 方法	select cume_dist(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;
cume_dist	cume_dist() OVER ( [ query_partition_clause] ORDER BY clause )		相当于Analytic方法	select employee_name, salary, cume_dist() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing';
dense_rank	dense_rank( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )		相当于Aggregate方法	select cume_dist(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;
dense_rank	dense_rank() OVER ( [ query_partition_clause] ORDER BY clause )		相当于Analytic方法	select employee_name, salary, cume_dist() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing';
rank	rank( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )		相当于Aggregate方法	select rank(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;
rank	rank() OVER ( [ query_partition_clause] ORDER BY clause )		相当于Analytic方法	select employee_name, salary, rank() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing';
exp	exp( number )			exp(3.1) return 22.1979512814416 exp(-3) would return 0.0497870683678639
EXTRACT	EXTRACT ({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }| { TIMEZONE_HOUR | TIMEZONE_MINUTE }| { TIMEZONE_REGION | TIMEZONE_ABBR }FROM { date_value | interval_value } )			extract(YEAR FROM DATE '2003-08-22') return 2003 extract(MONTH FROM DATE '2003-08-22') return 8
floor	floor( number )	获得小于或等于的最大的整数值		floor(5.9) return 5  floor(34.29) return 34 floor(-5.9) return -6
greatest	greatest( expr1, expr2, ... expr_n )	返回多个表达式中值最大的，Number返回最大值，string返回首字母最大的，如果相同一次类推		greatest(2, 5, 12, 3) return 12 greatest('2', '5', '12', '3') return '5' greatest('apples', 'oranges', 'bananas') return 'oranges' greatest('apples', 'applis', 'applas') return 'applis'
least	least( expr1, expr2, ... expr_n )	与 greatest对立的，取表达式中最小的。		least(2, 5, 12, 3) return 2 least('2', '5', '12', '3') return '12' least('apples', 'oranges', 'bananas') return 'apples' least('apples', 'applis', 'applas', null) return NULL
ln	ln( number )	自然对数		ln(20) return 2.99573227355399 ln(25) return 3.2188758248682
log	log( m, n ) m底数（不等于0,1），n对数	对数		log(10, 20) return 1.30102999566398 log(2, 15) return 3.90689059560852
MAX	SELECT MAX(expression ) FROM tables WHERE predicates;	获得记录中最大的
MIN	SELECT MIN(expression ) FROM tables WHERE predicates;	获得记录中最小的
median	median( expression ) [ OVER ( query partition clause ) ]	获得所有记录中前的		select median(salary) from employees where department = 'Marketing';
mod	mod( m, n )	取余		mod(11.6, 2) return 1.6 ；mod(-15, 4) return -3
power	power( m, n ) m底数，n指数	指数		power(3, 2) return 9 ；power(6.2, 3) return 238.328
remainder	remainder( m, n )			remainder(15, 6) return 3 remainder(15, 4) return -1 remainder(11.6, 2) return -0.4 rremainder(-15, 4) return 1
Round(number)	round( number, [ decimal_places ] )	数值格式化		round(125.315) return 125 round(125.315, 1) return 125.3
Round(date)	round( date, [ format ] )	日期格式化		round(to_date ('22-AUG-03'),'YEAR') return '01-JAN-04' round(to_date ('22-AUG-03'),'Q') return '01-OCT-03'
sign	sign( number ) number < 0, returns -1. number = 0, returns 0 number > 0, returns 1			sign(-23) return -1 sign(-0.001) return -1 sign(0) return 0
sin	sin( n )	sin		sin(3) return 0.141120008059867 sin(5.2) return -0.883454655720153
sqrt	sqrt( n )	求平方根		sqrt(9) return 3 sqrt(37) return 6.08276253029822
stddev	stddev( [ DISTINCT | ALL ] expression )	相当于Aggregate方法		select stddev(bonus) from employees;
stddev	stddev( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]	相当于Analytic方法		select employee_name, bonus, stddev(bonus) OVER (ORDER BY salary) from employees where department = 'Marketing';
SUM	SELECT SUM(expression ) FROM tables WHERE predicates;	求和		SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 25000;
var_pop	var_pop( expression )	Expression 必须是number		select var_pop(data_length) from all_tab_columns;
var_samp	var_samp( expression )	Expression 必须是number		select var_samp(char_length) from all_tab_columns;
variance	variance( expression )	Expression 必须是number		select variance(char_length) from all_tab_columns;

 

努力的学习了2天才终于把oracle的函数看完了，就这估计也不是很全，而且有些的理解也不一定正确，其实还有一些自己还没有理解是什么意思，看的有点晕了。算了先知道个大概把，在以后的使用过程中再好好的研究吧。以上的内容都是本人根据自己的理解所写的，所以说如果有不正确的地方还请大家给与出，不胜感激!