# 9.3. 數學函式及運算子 本節提供了 PostgreSQL 的數學運算方式。對於沒有標準數學約定的型別（例如，日期/時間型別），我們將在後續部分中介紹具體的行為。 [Table 9.4](#table-9-4-mathematical-operators) 列出了可用的數學運算子。 ## **Table 9.4. Mathematical Operators** | Operator | Description | Example | Result | | | | | | -------- | ------------------------------------------------ | ----------- | --------- | -------- | ---- | -------- | --- | | `+` | addition | `2 + 3` | `5` | | | | | | `-` | subtraction | `2 - 3` | `-1` | | | | | | `*` | multiplication | `2 * 3` | `6` | | | | | | `/` | division (integer division truncates the result) | `4 / 2` | `2` | | | | | | `%` | modulo (remainder) | `5 % 4` | `1` | | | | | | `^` | exponentiation (associates left to right) | `2.0 ^ 3.0` | `8` | | | | | | \` | /\` | square root | \` | / 25.0\` | `5` | | | | \` | | /\` | cube root | \` | | / 27.0\` | `3` | | `!` | factorial | `5 !` | `120` | | | | | | `!!` | factorial (prefix operator) | `!! 5` | `120` | | | | | | `@` | absolute value | `@ -5.0` | `5` | | | | | | `&` | bitwise AND | `91 & 15` | `11` | | | | | | \` | \` | bitwise OR | \`32 | 3\` | `35` | | | | `#` | bitwise XOR | `17 # 5` | `20` | | | | | | `~` | bitwise NOT | `~1` | `-2` | | | | | | `<<` | bitwise shift left | `1 << 4` | `16` | | | | | | `>>` | bitwise shift right | `8 >> 2` | `2` | | | | | 位元運算子僅適用於整數資料型別，也可用於位元字串型別的位元和位元變化，如 [Table 9.14](https://docs.postgresql.tw/12/the-sql-language/bit-string-functions-and-operators#table-9-14-bit-string-operators) 所示。 [Table 9.5](#table-9-5-mathematical-functions) 列出了可用的數學函數。在該表中，dp 表示雙精確度。這些函數中的許多函數都提供了多種形式，且具有不同的參數型別。除非另有說明，否則函數的任何形式都將回傳與其參數相同的資料型別。使用雙精確度資料的功能主要以主機系統的 C 函式庫實作； 因此，邊界情況下的準確性和行為可能會因主機系統而有所差異。 ## **Table 9.5. Mathematical Functions** | Function | Return Type | Description | Example | Result | | ------------------------------------------------------------------------------------------ | ------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------- | ------------------- | | `abs(`*`x`*) | (same as input) | absolute value | `abs(-17.4)` | `17.4` | | `cbrt(dp`) | `dp` | cube root | `cbrt(27.0)` | `3` | | `ceil(dp` or `numeric`) | (same as input) | nearest integer greater than or equal to argument | `ceil(-42.8)` | `-42` | | `ceiling(dp` or `numeric`) | (same as input) | nearest integer greater than or equal to argument (same as `ceil`) | `ceiling(-95.3)` | `-95` | | `degrees(dp`) | `dp` | radians to degrees | `degrees(0.5)` | `28.6478897565412` | | `div(`*`y`* `numeric`, *`x`* `numeric`) | `numeric` | integer quotient of *`y`*/*`x`* | `div(9,4)` | `2` | | `exp(dp` or `numeric`) | (same as input) | exponential | `exp(1.0)` | `2.71828182845905` | | `floor(dp` or `numeric`) | (same as input) | nearest integer less than or equal to argument | `floor(-42.8)` | `-43` | | `ln(dp` or `numeric`) | (same as input) | natural logarithm | `ln(2.0)` | `0.693147180559945` | | `log(dp` or `numeric`) | (same as input) | base 10 logarithm | `log(100.0)` | `2` | | `log10(dp` or `numeric`) | (same as input) | base 10 logarithm | `log10(100.0)` | `2` | | `log(`*`b`* `numeric`, *`x`* `numeric`) | `numeric` | logarithm to base *`b`* | `log(2.0, 64.0)` | `6.0000000000` | | `mod(`*`y`*, *`x`*) | (same as argument types) | remainder of *`y`*/*`x`* | `mod(9,4)` | `1` | | `pi()` | `dp` | “π” constant | `pi()` | `3.14159265358979` | | `power(`*`a`* `dp`, *`b`* `dp`) | `dp` | *`a`* raised to the power of *`b`* | `power(9.0, 3.0)` | `729` | | `power(`*`a`* `numeric`, *`b`* `numeric`) | `numeric` | *`a`* raised to the power of *`b`* | `power(9.0, 3.0)` | `729` | | `radians(dp`) | `dp` | degrees to radians | `radians(45.0)` | `0.785398163397448` | | `round(dp` or `numeric`) | (same as input) | round to nearest integer | `round(42.4)` | `42` | | `round(`*`v`* `numeric`, *`s`* `int`) | `numeric` | round to *`s`* decimal places | `round(42.4382, 2)` | `42.44` | | `scale(numeric`) | `integer` | scale of the argument (the number of decimal digits in the fractional part) | `scale(8.41)` | `2` | | `sign(dp` or `numeric`) | (same as input) | sign of the argument (-1, 0, +1) | `sign(-8.4)` | `-1` | | `sqrt(dp` or `numeric`) | (same as input) | square root | `sqrt(2.0)` | `1.4142135623731` | | `trunc(dp` or `numeric`) | (same as input) | truncate toward zero | `trunc(42.8)` | `42` | | `trunc(`*`v`* `numeric`, *`s`* `int`) | `numeric` | truncate to *`s`* decimal places | `trunc(42.4382, 2)` | `42.43` | | `width_bucket(`*`operand`* `dp`, *`b1`* `dp`, *`b2`* `dp`, *`count`* `int`) | `int` | return the bucket number to which *`operand`* would be assigned in a histogram having *`count`* equal-width buckets spanning the range *`b1`* to *`b2`*; returns `0` or *`count`*+1 for an input outside the range | `width_bucket(5.35, 0.024, 10.06, 5)` | `3` | | `width_bucket(`*`operand`* `numeric`, *`b1`* `numeric`, *`b2`* `numeric`, *`count`* `int`) | `int` | return the bucket number to which *`operand`* would be assigned in a histogram having *`count`* equal-width buckets spanning the range *`b1`* to *`b2`*; returns `0` or *`count`*+1 for an input outside the range | `width_bucket(5.35, 0.024, 10.06, 5)` | `3` | | `width_bucket(`*`operand`* `anyelement`, *`thresholds`* `anyarray`) | `int` | return the bucket number to which *`operand`* would be assigned given an array listing the lower bounds of the buckets; returns `0` for an input less than the first lower bound; the *`thresholds`* array *must be sorted*, smallest first, or unexpected results will be obtained | `width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])` | `2` | [Table 9.6](https://www.postgresql.org/docs/12/functions-math.html#FUNCTIONS-MATH-RANDOM-TABLE) shows functions for generating random numbers. ## **Table 9.6. Random Functions** | Function | Return Type | Description | | ------------- | ----------- | -------------------------------------------------------------------------------- | | `random()` | `dp` | random value in the range 0.0 <= x < 1.0 | | `setseed(dp`) | `void` | set seed for subsequent `random()` calls (value between -1.0 and 1.0, inclusive) | The `random()` function uses a simple linear congruential algorithm. It is fast but not suitable for cryptographic applications; see the [pgcrypto](https://www.postgresql.org/docs/12/pgcrypto.html) module for a more secure alternative. If `setseed()` is called, the results of subsequent `random()` calls in the current session are repeatable by re-issuing `setseed()` with the same argument. [Table 9.7](https://www.postgresql.org/docs/12/functions-math.html#FUNCTIONS-MATH-TRIG-TABLE) shows the available trigonometric functions. All these functions take arguments and return values of type `double precision`. Each of the trigonometric functions comes in two variants, one that measures angles in radians and one that measures angles in degrees. ## **Table 9.7. Trigonometric Functions** | Function (radians) | Function (degrees) | Description | | --------------------- | ---------------------- | ------------------------------ | | `acos(`*`x`*) | `acosd(`*`x`*) | inverse cosine | | `asin(`*`x`*) | `asind(`*`x`*) | inverse sine | | `atan(`*`x`*) | `atand(`*`x`*) | inverse tangent | | `atan2(`*`y`*, *`x`*) | `atan2d(`*`y`*, *`x`*) | inverse tangent of *`y`*/*`x`* | | `cos(`*`x`*) | `cosd(`*`x`*) | cosine | | `cot(`*`x`*) | `cotd(`*`x`*) | cotangent | | `sin(`*`x`*) | `sind(`*`x`*) | sine | | `tan(`*`x`*) | `tand(`*`x`*) | tangent | ## Note Another way to work with angles measured in degrees is to use the unit transformation functions `radians()` and `degrees()` shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as `sind(30)`. [Table 9.8](https://www.postgresql.org/docs/12/functions-math.html#FUNCTIONS-MATH-HYP-TABLE) shows the available hyperbolic functions. All these functions take arguments and return values of type `double precision`. ## **Table 9.8. Hyperbolic Functions** | Function | Description | Example | Result | | -------------- | -------------------------- | ---------- | ------ | | `sinh(`*`x`*) | hyperbolic sine | `sinh(0)` | `0` | | `cosh(`*`x`*) | hyperbolic cosine | `cosh(0)` | `1` | | `tanh(`*`x`*) | hyperbolic tangent | `tanh(0)` | `0` | | `asinh(`*`x`*) | inverse hyperbolic sine | `asinh(0)` | `0` | | `acosh(`*`x`*) | inverse hyperbolic cosine | `acosh(1)` | `0` | | `atanh(`*`x`*) | inverse hyperbolic tangent | `atanh(0)` | `0` |