 |
|
EnterpriseDB: Numeric Functions
Oracle Tips by
Burleson
|
Most
numeric functions can take either a number or a double and will, in
most cases return the same data type. I'm not going to provide
examples for each numeric function. If you need them, it's enough to
know they're there.
ABS:
Return the Absolute Value.
*
Parameter 1 = NUMBER
* Returns = NUMBER
CEIL:
Return the CEILING:
*
Parameter 1 = NUMBER
* Returns = NUMBER
FLOOR:
Return the FLOOR:
*
Parameter 1 = NUMBER
* Returns = NUMBER
DEGREES: Convert Radian to Degrees:
*
Parameter 1 = NUMBER
* Returns = NUMBER
EXP:
Return the exponential:
*
Parameter 1 = NUMBER
* Returns = NUMBER
LN:
Return the Natural Logarithm:
*
Parameter 1 = NUMBER
* Returns = NUMBER
LOG:
Return the Base 10 Logarithm:
*
Parameter 1 = NUMBER
* Returns = NUMBER
LOG:
Return the Logarithm to the base:
*
Parameter 1 = NUMBER, base
* Parameter 2 = NUMBER
* Returns = NUMBER
MOD:
Return the Modulus of two numbers:
*
Parameter 1 = NUMBER
* Parameter 2 = NUMBER
* Returns = NUMBER
PI:
Returns PI:
*
Returns = NUMBER
POWER:
Return a number raised to a power:
*
Parameter 1 = NUMBER
* Parameter 2 = NUMBER, Power
* Returns = NUMBER
RADIANS: Convert Degrees to Radian:
*
Parameter 1 = NUMBER
* Returns = NUMBER
RANDOM:
Return a random number:
*
Returns = NUMBER
SETSEED:
Sets a seed for calls to Random:
*
Parameter 1 = NUMBER
* Returns = NUMBER
ROUND:
Round a number to nearest integer or to number of decimals:
*
Parameter 1 = NUMBER
* Parameter 2 = NUMBER, Optional, Round to integer if NULL
* Returns = NUMBER
SIGN:
Return the sign of a number:
*
Parameter 1 = NUMBER
* Returns = NUMBER (+1, -1 or 0)
SQRT:
Return the Square Root of a number::
*
Parameter 1 = NUMBER
* Returns = NUMBER
ACOS:
Return the Inverse Cosine:
*
Parameter 1 = NUMBER
* Returns = NUMBER
ASIN:
Return the Inverse Sine:
*
Parameter 1 = NUMBER
* Returns = NUMBER
ATAN:
Return the Inverse Tangent:
*
Parameter 1 = NUMBER
* Returns = NUMBER
ATAN2:
Return the Inverse with input:
*
Parameter 1 = NUMBER
* Parameter 2 = NUMBER
* Returns = NUMBER ATAN(Parameter 1 / Parameter 2)
COS:
Return the Cosine:
*
Parameter 1 = NUMBER
* Returns = NUMBER
COT:
Return the Cotangent:
*
Parameter 1 = NUMBER
* Returns = NUMBER
SIN:
Return the Sine:
*
Parameter 1 = NUMBER
* Returns = NUMBER
TAN:
Return the Tangent:
*
Parameter 1 = NUMBER
* Returns = NUMBER
Aggregate Functions
AVG:
Return the average value of a column (NULLs are excluded):
*
Parameter 1 = Numeric Column or Expression
* Returns = NUMBER
COUNT:
Return the number of rows (NULLS are excluded):
*
Parameter 1 = Numeric Column.Text or Expression. Can use * as the
expression in which case nulls will be counted. In all other cases,
NULL will be excluded.
* Returns = NUMBER
MAX:
Return the maximum value in the column:
*
Parameter 1 = Numeric Column , Text or Expression
* Returns = NUMBER
MIN:
Return the minimum value in the column:
*
Parameter 1 = Numeric Column, Text or Expression
* Returns = NUMBER
SUM:
Return the sum of the supplied column for all rows:
*
Parameter 1 = Numeric Column or Expression
* Returns = NUMBER
STDDEV:
Return the standard deviation of the result set:
*
Parameter 1 = Numeric Column or Expression
* Returns = NUMBER
VARIANCE: Return the variance of the result set:
*
Parameter 1 = Numeric Column or Expression
* Returns = NUMBER
SELECT AVG(in_val) avg_val,
COUNT(in_val) cnt_val,
COUNT(*) cnt_all,
MAX(in_val) max_val,
MIN(in_val) min_val,
SUM(in_val) sum_val,
STDDEV(in_val) stdev_val,
VARIANCE(in_val) var_val
FROM (
SELECT 1 in_val
UNION
SELECT 2
UNION
SELECT NULL
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8 ) AS data_input;
edb=# SELECT AVG(in_val) avg_val,
edb-# COUNT(in_val) cnt_val,
edb-# COUNT(*) cnt_all,
edb-# MAX(in_val) max_val,
edb-# MIN(in_val) min_val,
edb-# SUM(in_val) sum_val,
edb-# STDDEV(in_val) stdev_val,
edb-# VARIANCE(in_val) var_val
edb-# FROM (
edb(# SELECT 1 in_val
edb(# UNION
edb(# SELECT 2
edb(# UNION
edb(# SELECT NULL
edb(# UNION
edb(# SELECT 3
edb(# UNION
edb(# SELECT 4
edb(# UNION
edb(# SELECT 5
edb(# UNION
edb(# SELECT 6
edb(# UNION
edb(# SELECT 7
edb(# UNION
edb(# SELECT 8 ) AS data_input;avg_val | cnt_val | cnt_all |
max_val | min_val | sum_val | stdev_val | var_val
--------------------+---------+---------+---------+---------+---------+----------
4.5000000000000000 | 8 | 9 | 8 | 1 | 36 |
2.4494897427831781 | 6.00000000000
(1 row)
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
