Postgresql 中文操作指南
9.3. Mathematical Functions and Operators #
为 PostgreSQL 的许多类型提供了数学运算符。对于没有标准数学约定的类型（例如日期/时间类型），我们将在后面的部分中描述实际行为。
Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.
Table 9.4 显示了标准数字类型可用的数学运算符。除非另有说明，否则标为接受 numeric_type 的运算符适用于所有类型 smallint、integer、bigint、numeric、real 和 double precision。标为接受 integral_type 的运算符适用于类型 smallint、integer 和 bigint。除非另有说明，运算符的每种形式返回与它的参数相同的数据类型。涉及多个参数数据类型的调用（例如 integer + numeric）通过使用出现在这些列表中较后的类型来解析。
Table 9.4 shows the mathematical operators that are available for the standard numeric types. Unless otherwise noted, operators shown as accepting numeric_type are available for all the types smallint, integer, bigint, numeric, real, and double precision. Operators shown as accepting integral_type are available for the types smallint, integer, and bigint. Except where noted, each form of an operator returns the same data type as its argument(s). Calls involving multiple argument data types, such as integer + numeric, are resolved by using the type appearing later in these lists.
Table 9.4. Mathematical Operators
Operator Description Example(s) |
numeric_type + numeric_type → numeric_type Addition 2 + 3 → 5 |
+ numeric_type → numeric_type Unary plus (no operation) + 3.5 → 3.5 |
numeric_type - numeric_type → numeric_type Subtraction 2 - 3 → -1 |
- numeric_type → numeric_type Negation - (-4) → 4 |
numeric_type * numeric_type → numeric_type Multiplication 2 * 3 → 6 |
numeric_type / numeric_type → numeric_type Division (for integral types, division truncates the result towards zero) 5.0 / 2 → 2.5000000000000000 5 / 2 → 2 (-5) / 2 → -2 |
numeric_type % numeric_type → numeric_type Modulo (remainder); available for smallint, integer, bigint, and numeric 5 % 4 → 1 |
numeric ^ numeric → numeric double precision ^ double precision → double precision Exponentiation 2 ^ 3 → 8 Unlike typical mathematical practice, multiple uses of ^ will associate left to right by default: 2 ^ 3 ^ 3 → 512 2 ^ (3 ^ 3) → 134217728 |
_ |
/_ double precision → double precision Square root _ |
/ 25.0_ → 5 |
_ |
/_ double precision → double precision Cube root _ |
/ 64.0_ → 4 |
@ numeric_type → numeric_type Absolute value @ -5.0 → 5.0 |
integral_type & integral_type → integral_type Bitwise AND 91 & 15 → 11 |
integral_type _ |
_ integral_type → integral_type Bitwise OR _32 |
3_ → 35 |
integral_type # integral_type → integral_type Bitwise exclusive OR 17 # 5 → 20 |
~ integral_type → integral_type Bitwise NOT ~1 → -2 |
integral_type << integer → integral_type Bitwise shift left 1 << 4 → 16 |
integral_type >> integer → integral_type Bitwise shift right 8 >> 2 → 2 |
Table 9.5 显示了可用的数学函数。其中许多函数以具有不同参数类型的多种形式提供。除非另有说明，函数的任何给定形式返回与它的参数相同的数据类型；交叉类型的情况以与上面运算符中解释相同的方式解决。处理 double precision 数据的函数大多在主机系统的 C 库之上实现；因此，准确性和边界情况的行为可能因主机系统而异。
Table 9.5 shows the available mathematical functions. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument(s); cross-type cases are resolved in the same way as explained above for operators. The functions working with double precision data are mostly implemented on top of the host system’s C library; accuracy and behavior in boundary cases can therefore vary depending on the host system.
Table 9.5. Mathematical Functions
Function Description Example(s) |
abs ( numeric_type ) → numeric_type Absolute value abs(-17.4) → 17.4 |
cbrt ( double precision ) → double precision Cube root cbrt(64.0) → 4 |
ceil ( numeric ) → numeric ceil ( double precision ) → double precision Nearest integer greater than or equal to argument ceil(42.2) → 43 ceil(-42.8) → -42 |
ceiling ( numeric ) → numeric ceiling ( double precision ) → double precision Nearest integer greater than or equal to argument (same as ceil) ceiling(95.3) → 96 |
degrees ( double precision ) → double precision Converts radians to degrees degrees(0.5) → 28.64788975654116 |
div ( y numeric, x numeric ) → numeric Integer quotient of y/x (truncates towards zero) div(9, 4) → 2 |
erf ( double precision ) → double precision Error function erf(1.0) → 0.8427007929497149 |
erfc ( double precision ) → double precision Complementary error function (1 - erf(x), without loss of precision for large inputs) erfc(1.0) → 0.15729920705028513 |
exp ( numeric ) → numeric exp ( double precision ) → double precision Exponential (e raised to the given power) exp(1.0) → 2.7182818284590452 |
factorial ( bigint ) → numeric Factorial factorial(5) → 120 |
floor ( numeric ) → numeric floor ( double precision ) → double precision Nearest integer less than or equal to argument floor(42.8) → 42 floor(-42.8) → -43 |
gcd ( numeric_type, numeric_type ) → numeric_type Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns 0 if both inputs are zero; available for integer, bigint, and numeric gcd(1071, 462) → 21 |
lcm ( numeric_type, numeric_type ) → numeric_type Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns 0 if either input is zero; available for integer, bigint, and numeric lcm(1071, 462) → 23562 |
ln ( numeric ) → numeric ln ( double precision ) → double precision Natural logarithm ln(2.0) → 0.6931471805599453 |
log ( numeric ) → numeric log ( double precision ) → double precision Base 10 logarithm log(100) → 2 |
log10 ( numeric ) → numeric log10 ( double precision ) → double precision Base 10 logarithm (same as log) log10(1000) → 3 |
log ( b numeric, x numeric ) → numeric Logarithm of x to base b log(2.0, 64.0) → 6.0000000000000000 |
min_scale ( numeric ) → integer Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely min_scale(8.4100) → 2 |
mod ( y numeric_type, x numeric_type ) → numeric_type Remainder of y/x; available for smallint, integer, bigint, and numeric mod(9, 4) → 1 |
pi ( ) → double precision Approximate value of π pi() → 3.141592653589793 |
power ( a numeric, b numeric ) → numeric power ( a double precision, b double precision ) → double precision a raised to the power of b power(9, 3) → 729 |
radians ( double precision ) → double precision Converts degrees to radians radians(45.0) → 0.7853981633974483 |
round ( numeric ) → numeric round ( double precision ) → double precision Rounds to nearest integer. For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule. round(42.4) → 42 |
round ( v numeric, s integer ) → numeric Rounds v to s decimal places. Ties are broken by rounding away from zero. round(42.4382, 2) → 42.44 round(1234.56, -1) → 1230 |
scale ( numeric ) → integer Scale of the argument (the number of decimal digits in the fractional part) scale(8.4100) → 4 |
sign ( numeric ) → numeric sign ( double precision ) → double precision Sign of the argument (-1, 0, or +1) sign(-8.4) → -1 |
sqrt ( numeric ) → numeric sqrt ( double precision ) → double precision Square root sqrt(2) → 1.4142135623730951 |
trim_scale ( numeric ) → numeric Reduces the value’s scale (number of fractional decimal digits) by removing trailing zeroes trim_scale(8.4100) → 8.41 |
trunc ( numeric ) → numeric trunc ( double precision ) → double precision Truncates to integer (towards zero) trunc(42.8) → 42 trunc(-42.8) → -42 |
trunc ( v numeric, s integer ) → numeric Truncates v to s decimal places trunc(42.4382, 2) → 42.43 |
width_bucket ( operand numeric, low numeric, high numeric, count integer ) → integer width_bucket ( operand double precision, low double precision, high double precision, count integer ) → integer Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 or _count+1_ for an input outside that range. width_bucket(5.35, 0.024, 10.06, 5) → 3 |
width_bucket ( operand anycompatible, thresholds anycompatiblearray ) → integer Returns the number of the bucket in which operand falls given an array listing the lower bounds of the buckets. Returns 0 for an input less than the first lower bound. operand and the array elements can be of any type having standard comparison operators. 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 显示了用于生成随机数的函数。
Table 9.6 shows functions for generating random numbers.
Table 9.6. Random Functions
Function Description Example(s) |
random ( ) → double precision Returns a random value in the range 0.0 ⇐ x < 1.0 random() → 0.897124072839091 |
random_normal ( [ mean double precision [, stddev double precision ]] ) → double precision Returns a random value from the normal distribution with the given parameters; mean defaults to 0.0 and stddev defaults to 1.0 random_normal(0.0, 1.0) → 0.051285419 |
setseed ( double precision ) → void Sets the seed for subsequent random() and random_normal() calls; argument must be between -1.0 and 1.0, inclusive setseed(0.12345) |
random() 函数使用确定性伪随机数生成器。它速度很快，但不适合加密应用程序；请参见 pgcrypto 模块以获得更安全的替代方法。如果调用 setseed()，则通过使用相同的参数重新发出 setseed()，可以在当前会话中重复后续 random() 调用的结果系列。在同一次会话中没有任何先前的 setseed() 调用时，第一个 random() 调用从一个平台相关的随机比特源中获取一个种子。这些说明同样适用于 random_normal()。
The random() function uses a deterministic pseudo-random number generator. It is fast but not suitable for cryptographic applications; see the pgcrypto module for a more secure alternative. If setseed() is called, the series of results of subsequent random() calls in the current session can be repeated by re-issuing setseed() with the same argument. Without any prior setseed() call in the same session, the first random() call obtains a seed from a platform-dependent source of random bits. These remarks hold equally for random_normal().
Table 9.7 显示了可用的三角函数。每个函数有两个变体，一个以弧度测量角度，另一个以度数测量角度。
Table 9.7 shows the available trigonometric functions. Each of these functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.
Table 9.7. Trigonometric Functions
Function Description Example(s) |
acos ( double precision ) → double precision Inverse cosine, result in radians acos(1) → 0 |
acosd ( double precision ) → double precision Inverse cosine, result in degrees acosd(0.5) → 60 |
asin ( double precision ) → double precision Inverse sine, result in radians asin(1) → 1.5707963267948966 |
asind ( double precision ) → double precision Inverse sine, result in degrees asind(0.5) → 30 |
atan ( double precision ) → double precision Inverse tangent, result in radians atan(1) → 0.7853981633974483 |
atand ( double precision ) → double precision Inverse tangent, result in degrees atand(1) → 45 |
atan2 ( y double precision, x double precision ) → double precision Inverse tangent of y/x, result in radians atan2(1, 0) → 1.5707963267948966 |
atan2d ( y double precision, x double precision ) → double precision Inverse tangent of y/x, result in degrees atan2d(1, 0) → 90 |
cos ( double precision ) → double precision Cosine, argument in radians cos(0) → 1 |
cosd ( double precision ) → double precision Cosine, argument in degrees cosd(60) → 0.5 |
cot ( double precision ) → double precision Cotangent, argument in radians cot(0.5) → 1.830487721712452 |
cotd ( double precision ) → double precision Cotangent, argument in degrees cotd(45) → 1 |
sin ( double precision ) → double precision Sine, argument in radians sin(1) → 0.8414709848078965 |
sind ( double precision ) → double precision Sine, argument in degrees sind(30) → 0.5 |
tan ( double precision ) → double precision Tangent, argument in radians tan(1) → 1.5574077246549023 |
tand ( double precision ) → double precision Tangent, argument in degrees tand(45) → 1 |
Note
处理以度为单位测量的角度的另一种方法是使用前面所示的单位转换函数 radians() 和 degrees()。但是，更倾向于使用基于度的三角函数，因为这样可以避免 sind(30) 等特殊情况的舍入误差。
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 显示了可用的双曲函数。
Table 9.8 shows the available hyperbolic functions.
Table 9.8. Hyperbolic Functions
Function Description Example(s) |
sinh ( double precision ) → double precision Hyperbolic sine sinh(1) → 1.1752011936438014 |
cosh ( double precision ) → double precision Hyperbolic cosine cosh(0) → 1 |
tanh ( double precision ) → double precision Hyperbolic tangent tanh(1) → 0.7615941559557649 |
asinh ( double precision ) → double precision Inverse hyperbolic sine asinh(1) → 0.881373587019543 |
acosh ( double precision ) → double precision Inverse hyperbolic cosine acosh(1) → 0 |
atanh ( double precision ) → double precision Inverse hyperbolic tangent atanh(0.5) → 0.5493061443340548 |