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;