How to use mathematical functions and operators-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports the mathematical functions and operators of PostgreSQL. For the full PostgreSQL reference, see Mathematical Functions and Operators.

Mathematical operators

Operator	Description	Example	Result
`+`	Addition	`2 + 3`	`5`
`-`	Subtraction	`2 - 3`	`-1`
`*`	Multiplication	`2 * 3`	`6`
`/`	Division. For integer operands, truncates the result toward zero.	`4 / 2`	`2`
`%`	Modulo (remainder)	`5 % 4`	`1`
`^`	Exponentiation (left-associative)	`2.0 ^ 3.0`	`8`
`\|/`	Square root	`\|/ 25.0`	`5`
`\|\|/`	Cube root	`\|\|/ 27.0`	`3`
`!`	Factorial	`5 !`	`120`
`!!`	Factorial as a 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`
`<<`	Left shift (fills vacant bits with zeros)	`1 << 4`	`16`
`>>`	Right shift (fills vacant bits with zeros)	`8 >> 2`	`2`

Mathematical functions

Rounding and truncation

Function	Return type	Description	Example	Result
`abs(x)`	Same as input	Absolute value	`abs(-17.4)`	`17.4`
`ceil(DOUBLE PRECISION or NUMERIC)`	Same as input	Rounds up to the nearest integer greater than or equal to the input	`ceil(-42.8)`	`-42`
`ceiling(DOUBLE PRECISION or NUMERIC)`	Same as input	Same as `ceil`	`ceiling(-95.3)`	`-95`
`floor(DOUBLE PRECISION or NUMERIC)`	Same as input	Rounds down to the nearest integer less than or equal to the input	`floor(-42.8)`	`-43`
`round(DOUBLE PRECISION or NUMERIC)`	Same as input	Rounds to the nearest integer	`round(42.4)`	`42`
`round(v NUMERIC, s INT)`	`NUMERIC`	Rounds to `s` decimal places	`round(42.4382, 2)`	`42.44`
`trunc(DOUBLE PRECISION or NUMERIC)`	Same as input	Truncates toward zero	`trunc(42.8)`	`42`
`trunc(v NUMERIC, s INT)`	`NUMERIC`	Truncates to `s` decimal places	`trunc(42.4382, 2)`	`42.43`
`sign(DOUBLE PRECISION or NUMERIC)`	Same as input	Returns the sign of a number: `-1` for negative, `0` for zero, `+1` for positive	`sign(-8.4)`	`-1`
`mod(y, x)`	Same as input	Remainder of `y` divided by `x`	`mod(9,4)`	`1`
`div(y NUMERIC, x NUMERIC)`	`NUMERIC`	Integer quotient of `y` divided by `x`	`div(9,4)`	`2`
`scale(NUMERIC)`	`INTEGER`	Number of decimal digits in the fractional part	`scale(8.41)`	`2`

Exponents and roots

Function	Return type	Description	Example	Result
`cbrt(DOUBLE PRECISION)`	`DOUBLE PRECISION`	Cube root	`cbrt(27.0)`	`3`
`exp(DOUBLE PRECISION or NUMERIC)`	Same as input	Exponential (`e^x`)	`exp(1.0)`	`2.71828182845905`
`power(a DOUBLE PRECISION, b DOUBLE PRECISION)`	`DOUBLE PRECISION`	`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`
`sqrt(DOUBLE PRECISION or NUMERIC)`	Same as input	Square root	`sqrt(2.0)`	`1.4142135623731`

Logarithmic

Function	Return type	Description	Example	Result
`ln(DOUBLE PRECISION or NUMERIC)`	Same as input	Natural logarithm	`ln(2.0)`	`0.693147180559945`
`log(DOUBLE PRECISION or NUMERIC)`	Same as input	Common (base-10) logarithm	`log(100.0)`	`2`
`log10(DOUBLE PRECISION or NUMERIC)`	Same as input	Common (base-10) logarithm. Same as `log`.	`log10(100.0)`	`2`
`log(b NUMERIC, x NUMERIC)`	`NUMERIC`	Logarithm of `x` to base `b`	`log(2.0, 64.0)`	`6.0000000000`

Angle conversion

Function	Return type	Description	Example	Result
`degrees(DOUBLE PRECISION)`	`DOUBLE PRECISION`	Converts radians to degrees	`degrees(0.5)`	`28.6478897565412`
`pi()`	`DOUBLE PRECISION`	The value of π	`pi()`	`3.14159265358979`
`radians(DOUBLE PRECISION)`	`DOUBLE PRECISION`	Converts degrees to radians	`radians(45.0)`	`0.785398163397448`

Histogram

Function	Return type	Description	Example	Result
`width_bucket(operand DOUBLE PRECISION, b1 DOUBLE PRECISION, b2 DOUBLE PRECISION, count INT)`	`INT`	Returns the bucket number for `operand` in a histogram of `count` equal-width buckets spanning `b1` to `b2`. Returns `0` or `count+1` for out-of-range values.	`width_bucket(5.35, 0.024, 10.06, 5)`	`3`
`width_bucket(operand NUMERIC, b1 NUMERIC, b2 NUMERIC, count INT)`	`INT`	Same as above with NUMERIC arguments.	`width_bucket(5.35, 0.024, 10.06, 5)`	`3`
`width_bucket(operand anyelement, thresholds anyarray)`	`INT`	Returns the bucket number based on an array of lower bounds. Returns `0` for values below the first bound. The `thresholds` array must be sorted in ascending order.	`width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])`	`2`

Example: Group sales data into price buckets

CREATE TABLE home_sales (
    sale_date DATE,
    price NUMERIC(11, 2)
);

INSERT INTO home_sales (sale_date, price) VALUES
    ('2024-01-15', 290000.00),
    ('2024-02-20', 410000.00),
    ('2024-03-05', 180000.00),
    ('2024-04-12', 575000.00);

SELECT
    sale_date,
    price,
    width_bucket(price, 100000, 600000, 5) AS price_bucket
FROM home_sales
ORDER BY sale_date;

Result:

sale_date	price	price_bucket
2024-01-15	290000.00	2
2024-02-20	410000.00	4
2024-03-05	180000.00	1
2024-04-12	575000.00	5

Usage notes for `width_bucket`:

For the range-based variants, values below b1 return 0, and values above b2 return count+1.
The thresholds array variant requires the array to be sorted in ascending order. Unsorted arrays produce undefined results.

Random functions

Function	Return type	Description
`random()`	`DOUBLE PRECISION`	Returns a random value in the range `[0.0, 1.0)`
`setseed(dp)`	`void`	Sets the seed for subsequent `random()` calls. Valid range: `[-1.0, 1.0]`.

Trigonometric functions

Each function has two variants: one that accepts and returns radians, and one that accepts and returns degrees (the d-suffixed version).

Function (radians)	Function (degrees)	Description
`acos(x)`	`acosd(x)`	Arccosine
`asin(x)`	`asind(x)`	Arcsine
`atan(x)`	`atand(x)`	Arctangent
`atan2(y, x)`	`atan2d(y, x)`	Arctangent of `y/x`
`cos(x)`	`cosd(x)`	Cosine
`cot(x)`	`cotd(x)`	Cotangent
`sin(x)`	`sind(x)`	Sine
`tan(x)`	`tand(x)`	Tangent

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`