PostgreSQL数据库中数字相关函数

PostgreSQL的数字函数主要用于数字操纵和/或数学计算。下表详列的数字函数：

名称	描述
ABS()	Returns the absolute value of numeric expression.
ACOS()	Returns the arccosine of numeric expression. Returns NULL if the value is not in the range -1 to 1.
ASIN()	Returns the arcsine of numeric expression. Returns NULL if value is not in the range -1 to 1
ATAN()	Returns the arctangent of numeric expression.
ATAN2()	Returns the arctangent of the two variables passed to it.
CEIL()	Returns the smallest integer value that is not less than passed numeric expression
CEILING()	Returns the smallest integer value that is not less than passed numeric expression
COS()	Returns the cosine of passed numeric expression. The numeric expression should be expressed in radians.
COT()	Returns the cotangent of passed numeric expression.
DEGREES()	Returns numeric expression converted from radians to degrees.
EXP()	Returns the base of the natural logarithm (e) raised to the power of passed numeric expression.
FLOOR()	Returns the largest integer value that is not greater than passed numeric expression.
GREATEST()	Returns the largest value of the input expressions.
LEAST()	Returns the minimum-valued input when given two or more.
LOG()	Returns the natural logarithm of the passed numeric expression.
MOD()	Returns the remainder of one expression by diving by another expression.
PI()	Returns the value of pi
POW()	Returns the value of one expression raised to the power of another expression
POWER()	Returns the value of one expression raised to the power of another expression
RADIANS()	Returns the value of passed expression converted from degrees to radians.
ROUND()	Returns numeric expression rounded to an integer. Can be used to round an expression to a number of decimal points
SIN()	Returns the sine of numeric expression given in radians.
SQRT()	Returns the non-negative square root of numeric expression.
TAN()	Returns the tangent of numeric expression expressed in radians.

ABS(X)

The ABS() function returns the absolute value of X. Consider the following example:

testdb "pun">=# SELECT ABS "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> ABS() "pln">                                                  |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
testdb=# "pln"> SELECT ABS(-);
"pun">+---------------------------------------------------------+
| "pln"> ABS() "pln">                                                  |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
ACOS(X)

This function returns the arccosine of X. The value of X must range between .1 and 1 or NULL will be returned. Consider the following example:

testdb "pun">=# SELECT ACOS "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> ACOS() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> 0.000000 "pln">                                                |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
ASIN(X)

The ASIN() function returns the arcsine of X. The value of X must be in the range of .1 to 1 or NULL is returned.

testdb "pun">=# SELECT ASIN "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> ASIN() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> 1.5707963267949 "pln">                                          "pun">|
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
ATAN(X)

This function returns the arctangent of X.

testdb "pun">=# SELECT ATAN "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> ATAN() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> 0.78539816339745 "pln">                                         "pun">|
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
ATAN2(Y,X)

This function returns the arctangent of the two arguments: X and Y. It is similar to the arctangent of Y/X, except that the signs of both are used to find the quadrant of the result.

testdb "pun">=# SELECT ATAN2 "pun">( "pun">, "pun">);
"pun">+---------------------------------------------------------+
| "pln"> ATAN2(,) "pln">                                              |
"pun">+---------------------------------------------------------+
| "pln"> 0.46364760900081 "pln">                                         "pun">|
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
CEIL(X) CEILING(X)

These function return the smallest integer value that is not smaller than X. Consider the following example:

testdb "pun">=# SELECT CEILING "pun">(3.46 "pun">);
"pun">+---------------------------------------------------------+
| "pln"> CEILING( "lit">3.46) "pln">                                           |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
testdb=# "pln"> SELECT CEIL(- "lit">6.43);
"pun">+---------------------------------------------------------+
| "pln"> CEIL(- "lit">6.43) "pln">                                             |
"pun">+---------------------------------------------------------+
| "pln"> - "pln">                                                      |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
COS(X)

This function returns the cosine of X. The value of X is given in radians.

testdb "pun">=#SELECT COS "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> COS() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> - "lit">0.44807361612917 "pln">                                        "pun">|
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
COT(X)

This function returns the cotangent of X. Consider the following example:

testdb "pun">=#SELECT COT "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> COT() "pln">                                                  |
"pun">+---------------------------------------------------------+
| "pln"> 0.64209261593433 "pln">                                         "pun">|
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
DEGREES(X)

This function returns the value of X converted from radians to degrees.

testdb "pun">=#SELECT DEGREES "pun">(PI "pun">());
"pun">+---------------------------------------------------------+
| "pln"> DEGREES( "pln">PI()) "pln">                                           |
"pun">+---------------------------------------------------------+
| "pln"> 180.000000 "pln">                                              |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
EXP(X)

This function returns the value of e (the base of the natural logarithm) raised to the power of X.

testdb "pun">=#SELECT EXP "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> EXP() "pln">                                                  |
"pun">+---------------------------------------------------------+
| "pln"> 20.085537 "pln">                                               |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
FLOOR(X)

This function returns the largest integer value that is not greater than X.

testdb "pun">=#SELECT FLOOR "pun">(7.55 "pun">);
"pun">+---------------------------------------------------------+
| "pln"> FLOOR( "lit">7.55) "pln">                                             |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
GREATEST(n1,n2,n3,..........)

The GREATEST() function returns the greatest value in the set of input parameters (n1, n2, n3, and so on). The following example uses the GREATEST() function to return the largest number from a set of numeric values:

testdb "pun">=#SELECT GREATEST "pun">( "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">);
"pun">+---------------------------------------------------------+
| "pln"> GREATEST(,,,,,,,,,) "pln">                      "pun">|
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                      |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
LEAST(N1,N2,N3,N4,......)

The LEAST() function is the opposite of the GREATEST() function. Its purpose is to return the least-valued item from the value list (N1, N2, N3, and so on). The following example shows the proper usage and output for the LEAST() function:

testdb "pun">=#SELECT LEAST "pun">( "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">, "pun">);
"pun">+---------------------------------------------------------+
| "pln"> LEAST(,,,,,,,,,) "pln">                         "pun">|
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
LOG(X) LOG(B,X)

The single argument version of the function will return the natural logarithm of X. If it is called with two arguments, it returns the logarithm of X for an arbitrary base B. Consider the following example:

testdb "pun">=#SELECT LOG "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> LOG() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> 1.65321251377534 "pln">                                               |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
testdb=# "pln">SELECT LOG(,);
"pun">+---------------------------------------------------------+
| "pln"> LOG(,) "pln">                                            |
"pun">+---------------------------------------------------------+
| "pln"> 16.000000 "pln">                                               |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
MOD(N,M)

This function returns the remainder of N divided by M. Consider the following example:

testdb "pun">=#SELECT MOD "pun">( "pun">, "pun">);
"pun">+---------------------------------------------------------+
| "pln"> MOD(,) "pln">                                               |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
PI()

This function simply returns the value of pi. SQL internally stores the full double-precision value of pi.

testdb "pun">=#SELECT PI "pun">();
"pun">+---------------------------------------------------------+
| "pln"> PI() "pln">                                                    |
"pun">+---------------------------------------------------------+
| "pln"> 3.141593 "pln">                                                |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
POW(X,Y) POWER(X,Y)

These two functions return the value of X raised to the power of Y.

testdb "pun">=# SELECT POWER "pun">( "pun">, "pun">);
"pun">+---------------------------------------------------------+
| "pln"> POWER(,) "pln">                                              |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                      |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
RADIANS(X)

This function returns the value of X, converted from degrees to radians.

testdb "pun">=#SELECT RADIANS "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> RADIANS() "pln">                                             |
"pun">+---------------------------------------------------------+
| "lit">1.570796 "pln">                                                 |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
ROUND(X) ROUND(X,D)

This function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed. Consider the following example:

testdb "pun">=#SELECT ROUND "pun">(5.693893 "pun">);
"pun">+---------------------------------------------------------+
| "pln"> ROUND( "lit">5.693893) "pln">                                          "pun">|
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
testdb=# "pln">SELECT ROUND( "lit">5.693893,);
"pun">+---------------------------------------------------------+
| "pln"> ROUND( "lit">5.693893,) "pln">                                        "pun">|
"pun">+---------------------------------------------------------+
| "pln"> 5.69 "pln">                                                    |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
SIGN(X)

This function returns the sign of X (negative, zero, or positive) as .1, 0, or 1.

testdb "pun">=#SELECT SIGN "pun">(-4.65 "pun">);
"pun">+---------------------------------------------------------+
| "pln"> SIGN(- "lit">4.65) "pln">                                             |
"pun">+---------------------------------------------------------+
| "pln"> - "pln">                                                      |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
testdb=# "pln">SELECT SIGN();
"pun">+---------------------------------------------------------+
| "pln"> SIGN() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
testdb=# "pln">SELECT SIGN( "lit">4.65);
"pun">+---------------------------------------------------------+
| "pln"> SIGN( "lit">4.65) "pln">                                              |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
SIN(X)

This function returns the sine of X. Consider the following example:

testdb "pun">=#SELECT SIN "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> SIN() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> 0.893997 "pln">                                                |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
SQRT(X)

This function returns the non-negative square root of X. Consider the following example:

testdb "pun">=#SELECT SQRT "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> SQRT() "pln">                                                |
"pun">+---------------------------------------------------------+
| "pln">  "pln">                                                       |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)
TAN(X)

This function returns the tangent of the argument X, which is expressed in radians.

testdb "pun">=#SELECT TAN "pun">( "pun">);
"pun">+---------------------------------------------------------+
| "pln"> TAN() "pln">                                                 |
"pun">+---------------------------------------------------------+
| "pln"> 1.619775 "pln">                                                |
"pun">+---------------------------------------------------------+
"pln"> row in "pln"> set "pln"> ( "lit">0.00 sec "pun">)