PostgreSQL - 數值函式

---

---

PostgreSQL 數值函式主要用於數值操作和/或數學計算。下表詳細列出了數值函式：

ABS(X)

ABS() 函式返回 X 的絕對值。請考慮以下示例：

testdb=# SELECT ABS(2);
+---------------------------------------------------------+
| ABS(2)                                                  |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

testdb=# SELECT ABS(-2);
+---------------------------------------------------------+
| ABS(2)                                                  |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ACOS(X)

此函式返回 X 的反餘弦值。X 的值必須介於 -1 和 1 之間，否則將返回 NULL。請考慮以下示例：

testdb=# SELECT ACOS(1);
+---------------------------------------------------------+
| ACOS(1)                                                 |
+---------------------------------------------------------+
| 0.000000                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ASIN(X)

ASIN() 函式返回 X 的反正弦值。X 的值必須在 -1 到 1 的範圍內，否則返回 NULL。

testdb=# SELECT ASIN(1);
+---------------------------------------------------------+
| ASIN(1)                                                 |
+---------------------------------------------------------+
| 1.5707963267949                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ATAN(X)

此函式返回 X 的反正切值。

testdb=# SELECT ATAN(1);
+---------------------------------------------------------+
| ATAN(1)                                                 |
+---------------------------------------------------------+
| 0.78539816339745                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ATAN2(Y,X)

此函式返回兩個引數 X 和 Y 的反正切值。它類似於 Y/X 的反正切值，只是使用了兩個引數的符號來查詢結果的象限。

testdb=# SELECT ATAN2(3,6);
+---------------------------------------------------------+
| ATAN2(3,6)                                              |
+---------------------------------------------------------+
| 0.46364760900081                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CEIL(X) / CEILING(X)

這些函式返回不小於 X 的最小整數值。請考慮以下示例：

testdb=# SELECT CEILING(3.46);
+---------------------------------------------------------+
| CEILING(3.46)                                           |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

testdb=# SELECT CEIL(-6.43);
+---------------------------------------------------------+
| CEIL(-6.43)                                             |
+---------------------------------------------------------+
| -6                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

COS(X)

此函式返回 X 的餘弦值。X 的值以弧度表示。

testdb=#SELECT COS(90);
+---------------------------------------------------------+
| COS(90)                                                 |
+---------------------------------------------------------+
| -0.44807361612917                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

COT(X)

此函式返回 X 的餘切值。請考慮以下示例：

testdb=#SELECT COT(1);
+---------------------------------------------------------+
| COT(1)                                                  |
+---------------------------------------------------------+
| 0.64209261593433                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

DEGREES(X)

此函式返回從弧度轉換為角度的 X 值。

testdb=#SELECT DEGREES(PI());
+---------------------------------------------------------+
| DEGREES(PI())                                           |
+---------------------------------------------------------+
| 180.000000                                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

EXP(X)

此函式返回 e（自然對數的底數）的 X 次冪的值。

testdb=#SELECT EXP(3);
+---------------------------------------------------------+
| EXP(3)                                                  |
+---------------------------------------------------------+
| 20.085537                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FLOOR(X)

此函式返回不大於 X 的最大整數值。

testdb=#SELECT FLOOR(7.55);
+---------------------------------------------------------+
| FLOOR(7.55)                                             |
+---------------------------------------------------------+
| 7                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

GREATEST(n1,n2,n3,..........)

GREATEST() 函式返回一組輸入引數 (n1、n2、n3 等) 中的最大值。以下示例使用 GREATEST() 函式從一組數值中返回最大數：

testdb=#SELECT GREATEST(3,5,1,8,33,99,34,55,67,43);
+---------------------------------------------------------+
| GREATEST(3,5,1,8,33,99,34,55,67,43)                     |
+---------------------------------------------------------+
| 99                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LEAST(N1,N2,N3,N4,......)

LEAST() 函式與 GREATEST() 函式相反。它的目的是從值列表 (N1、N2、N3 等) 中返回最小值項。以下示例顯示了 LEAST() 函式的正確用法和輸出：

testdb=#SELECT LEAST(3,5,1,8,33,99,34,55,67,43);
+---------------------------------------------------------+
| LEAST(3,5,1,8,33,99,34,55,67,43)                        |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOG(X) / LOG(B,X)

函式的單引數版本將返回 X 的自然對數。如果用兩個引數呼叫它，它將返回任意底數 B 的 X 的對數。請考慮以下示例：

testdb=#SELECT LOG(45);
+---------------------------------------------------------+
| LOG(45)                                                 |
+---------------------------------------------------------+
| 1.65321251377534                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

testdb=#SELECT LOG(2,65536);
+---------------------------------------------------------+
| LOG(2,65536)                                            |
+---------------------------------------------------------+
| 16.000000                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MOD(N,M)

此函式返回 N 除以 M 的餘數。請考慮以下示例：

testdb=#SELECT MOD(29,3);
+---------------------------------------------------------+
| MOD(29,3)                                               |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

PI()

此函式只返回 π 的值。SQL 內部儲存 π 的完整雙精度值。

testdb=#SELECT PI();
+---------------------------------------------------------+
| PI()                                                    |
+---------------------------------------------------------+
| 3.141593                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

POW(X,Y) / POWER(X,Y)

這兩個函式返回 X 的 Y 次冪的值。

testdb=# SELECT POWER(3,3);
+---------------------------------------------------------+
| POWER(3,3)                                              |
+---------------------------------------------------------+
| 27                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

RADIANS(X)

此函式返回 X 的值，從角度轉換為弧度。

testdb=#SELECT RADIANS(90);
+---------------------------------------------------------+
| RADIANS(90)                                             |
+---------------------------------------------------------+
|1.570796                                                 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ROUND(X) / ROUND(X,D)

此函式返回四捨五入到最接近整數的 X。如果提供第二個引數 D，則函式返回四捨五入到 D 位小數的 X。D 必須為正數，否則小數點右側的所有數字都將被刪除。請考慮以下示例：

testdb=#SELECT ROUND(5.693893);
+---------------------------------------------------------+
| ROUND(5.693893)                                         |
+---------------------------------------------------------+
| 6                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

testdb=#SELECT ROUND(5.693893,2);
+---------------------------------------------------------+
| ROUND(5.693893,2)                                       |
+---------------------------------------------------------+
| 5.69                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SIGN(X)

此函式返回 X 的符號（負數、零或正數）作為 -1、0 或 1。

testdb=#SELECT SIGN(-4.65);
+---------------------------------------------------------+
| SIGN(-4.65)                                             |
+---------------------------------------------------------+
| -1                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

testdb=#SELECT SIGN(0);
+---------------------------------------------------------+
| SIGN(0)                                                 |
+---------------------------------------------------------+
| 0                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

testdb=#SELECT SIGN(4.65);
+---------------------------------------------------------+
| SIGN(4.65)                                              |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SIN(X)

此函式返回 X 的正弦值。請考慮以下示例：

testdb=#SELECT SIN(90);
+---------------------------------------------------------+
| SIN(90)                                                 |
+---------------------------------------------------------+
| 0.893997                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQRT(X)

此函式返回 X 的非負平方根。請考慮以下示例：

testdb=#SELECT SQRT(49);
+---------------------------------------------------------+
| SQRT(49)                                                |
+---------------------------------------------------------+
| 7                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TAN(X)

此函式返回引數 X 的正切值，該引數以弧度表示。

testdb=#SELECT TAN(45);
+---------------------------------------------------------+
| TAN(45)                                                 |
+---------------------------------------------------------+
| 1.619775                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)