Mathematical functions

Propel provides a comprehensive set of mathematical functions to perform various calculations and transformations.

These functions cover basic arithmetic operations, trigonometry, logarithms, rounding, and more advanced mathematical concepts.

ClickHouse function reference

e

Returns the mathematical constant e (Euler’s number).

Syntax:

e()

Returns:

The value of e (approximately 2.71828…). [Float64]

Example:

SELECT
  e() AS eulers_number;

Result:

| eulers_number |
|---------------|
| 2.7182818284  |

This function is useful in mathematical calculations involving exponential growth or decay, such as compound interest in taco sales projections or the rate of salsa consumption at a taco party.

The e() function always returns the same value and does not take any arguments.

pi

Returns the mathematical constant π (pi).

Syntax:

pi()

Returns:

A Float64 value representing π (approximately 3.14159265358979323846).

Example:

SELECT
  pi() AS pi_value,
  round(pi(), 5) AS rounded_pi;

Result:

| pi_value            | rounded_pi |
|---------------------|------------|
| 3.141592653589793   | 3.14159    |

This example demonstrates the use of pi() to obtain the value of π and rounds it to 5 decimal places for easier reading.

The pi() function is useful in various mathematical and geometric calculations, such as computing the area or circumference of a circle.

exp

Returns e (Euler’s constant) raised to the power of the given argument.

Syntax:

exp(x)

Arguments:

x (numeric): The exponent. Can be any numeric type.

Returns:

Type: Float32 or Float64 depending on the input type.
The value of e^x.

Example:

SELECT
  exp(2) AS e_squared,
  exp(-1) AS e_inverse;

Result:

| e_squared           | e_inverse           |
|---------------------|---------------------|
| 7.389056098930650   | 0.3678794411714423  |

This example calculates e^2 and e^(-1), which are common values in mathematical and statistical calculations.

The exp function is particularly useful in various mathematical and statistical computations, such as calculating compound interest, modeling population growth, or computing probabilities in logistic regression.

Example:

SELECT
  taco_name,
  initial_popularity,
  exp(growth_rate) AS popularity_multiplier,
  initial_popularity * exp(growth_rate) AS projected_popularity
FROM
  taco_trends
WHERE
  year = 2023;

Result:

| taco_name      | initial_popularity | popularity_multiplier | projected_popularity |
|----------------|--------------------|-----------------------|----------------------|
| Spicy Carnitas | 100                | 2.718281828459045     | 271.8281828459045    |
| Veggie Delight | 50                 | 1.6487212707001282    | 82.43606353500641    |
| Fish Taco      | 75                 | 2.225540928492468     | 166.91556963693512   |

In this example, we use the exp function to model the growth in popularity of different taco varieties. The growth_rate represents the natural log of the popularity multiplier, so exp(growth_rate) gives us the actual multiplier. We then use this to calculate the projected popularity based on the initial popularity and the growth rate.

log

Returns the natural logarithm of the argument.

Syntax:

log(x)

Alias:

ln(x)

Arguments:

x (numeric): A number. Type: UInt*, Int*, Float*, or Decimal*.

Returns:

The natural logarithm of x. Type: Float*.

Example:

SELECT
  log(2.718281828459045);

Result:

| log(2.718281828459045) |
|------------------------|
| 1.0000000000000002     |

This example calculates the natural logarithm of e (Euler’s number), which is approximately 1.

If x is negative or zero, the function will return nan.
For very small values of x close to 1, consider using log1p(x) for better numerical accuracy.

Example:

SELECT
    taco_name,
    taco_price,
    log(taco_price) AS log_price
FROM
    taco_menu
WHERE
    taco_price > 0
ORDER BY
    log_price DESC
LIMIT 5;

Result:

| taco_name      | taco_price | log_price         |
|----------------|------------|-------------------|
| Supreme Taco   | 15.99      | 2.7718922590148   |
| Seafood Fiesta | 12.50      | 2.5257286443083   |
| Veggie Delight | 9.99       | 2.3016502429368   |
| Classic Beef   | 7.50       | 2.0149030205423   |
| Chicken Crunch | 6.75       | 1.9095425048501   |

In this example, we calculate the natural logarithm of taco prices. This could be useful for various statistical analyses or for creating a logarithmic scale of prices.

exp2

Returns 2 raised to the power of the given argument.

Syntax:

exp2(x)

Arguments:

x (numeric): The exponent. Can be any numeric type: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal.

Returns:

2^x
Type: Float32 or Float64 depending on the input type

Example:

SELECT
  exp2(3) AS taco_power;

Result:

| taco_power |
|------------|
| 8          |

This example calculates 2^3, which represents the number of possible taco combinations with 3 binary toppings (e.g., cheese, lettuce, salsa).

For integer results, consider using the intExp2 function instead.

intExp2

Calculates 2 raised to the power of the given argument.

Syntax:

intExp2(x)

Arguments:

x (numeric): The exponent. Can be any numeric type.

Returns:

The result of 2^x as a UInt64.

Example:

SELECT
  intExp2(3) AS taco_power;

Result:

| taco_power |
|------------|
| 8          |

In this example, we calculate 2^3, which represents the number of possible taco combinations with 3 binary toppings (e.g., cheese, lettuce, salsa).

This function is similar to exp2, but returns an integer result. It’s useful when you need an exact integer power of 2, such as for bit manipulations or when dealing with data sizes.

log2

Calculates the binary logarithm (base-2 logarithm) of the argument.

Syntax:

log2(x)

Arguments:

x (numeric): A number to calculate the binary logarithm for. Can be any of these types: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal.

Returns:

The binary logarithm of x.
Type: Float64

Example:

SELECT
    log2(8) AS binary_logarithm,
    log2(0.5) AS negative_result;

Result:

| binary_logarithm | negative_result |
|------------------|-----------------|
| 3                | -1              |

In this example:

log2(8) returns 3 because 2³ = 8
log2(0.5) returns -1 because 2⁻¹ = 0.5

If x is negative or zero, the function will return nan (Not a Number).
This function is particularly useful in computer science and information theory contexts, such as calculating the number of bits needed to represent a number or measuring information content.

exp10

Returns 10 raised to the power of the given argument.

Syntax:

exp10(x)

Arguments:

x (numeric): The exponent. Can be any numeric type: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal.

Returns:

10 raised to the power of x.

Type: Float32 or Float64, depending on the input type.

Example:

SELECT
  exp10(3) AS ten_cubed,
  exp10(-1) AS one_tenth;

Result:

| ten_cubed | one_tenth |
|-----------|-----------|
| 1000      | 0.1       |

This example calculates 10^3 and 10^(-1), demonstrating both positive and negative exponents.

For integer results, consider using intExp10 instead, which returns a UInt64.

intExp10

Returns 10 raised to the power of the given argument.

Syntax:

intExp10(x)

Arguments:

x (numeric): The exponent. Can be any numeric type.

Returns:

The result of 10^x as a UInt64.

Example:

SELECT
  intExp10(3) AS taco_power;

Result:

| taco_power |
|------------|
| 1000       |

In this example, we calculate 10^3, which represents the number of possible taco combinations if we had 10 choices for each of 3 taco components.

This function is similar to exp10, but returns an integer result. It’s useful when you need an exact integer value for powers of 10, which is common in certain calculations or when dealing with large numbers.

log10

Calculates the base-10 logarithm of a number.

Syntax:

log10(x)

Arguments:

x (numeric): The input value. Can be any numeric type: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal.

Returns:

The base-10 logarithm of x. Type: Float64.

Example:

SELECT
  log10(100) AS log_result,
  log10(taco_price) AS taco_log
FROM
  taco_menu
WHERE
  taco_name = 'Spicy Carnitas';

Result:

| log_result | taco_log |
|------------|----------|
| 2          | 0.778151 |

In this example:

log10(100) returns 2, as 10^2 = 100.
log10(taco_price) calculates the logarithm of the taco price, assuming the ‘Spicy Carnitas’ taco costs $6.00 (as 10^0.778151 ≈ 6).

The log10 function is particularly useful for dealing with data that spans multiple orders of magnitude, such as comparing taco sales across different restaurant sizes or market populations.

sqrt

Calculates the square root of a number.

Syntax:

sqrt(x)

Arguments:

x (numeric): The input value. Can be any numeric type: UInt*, Int*, Float*, or Decimal*.

Returns:

The square root of x. Type: Float32 or Float64, depending on the input type.

Example:

SELECT
  sqrt(taco_price) AS square_root_price
FROM
  taco_menu
WHERE
  taco_name = 'Carne Asada Supreme';

Result:

| square_root_price |
|-------------------|
| 2.449489742783178 |

In this example, we calculate the square root of the price for the ‘Carne Asada Supreme’ taco. If the price was $6, the square root would be approximately 2.45.

For negative input values, the function returns NaN (Not a Number).
For NULL input, the function returns NULL.

cbrt

Returns the cubic root of the argument.

Syntax:

cbrt(x)

Arguments:

x (numeric): A numeric value. Supported types: Int, UInt, Float, or Decimal.

Returns:

The cubic root of x.
Type: Float32 or Float64, depending on the input type.

Example:

SELECT
    cbrt(27) AS cubic_root_of_27,
    cbrt(8.0) AS cubic_root_of_8;

Result:

| cubic_root_of_27 | cubic_root_of_8  |
|-------------------|-----------------|
| 3                 | 2               |

This example calculates the cubic root of 27 (which is 3) and the cubic root of 8 (which is 2).

The cbrt function is particularly useful when dealing with volumes or three-dimensional calculations. For example, you could use it to calculate the side length of a cubic taco box given its volume:

SELECT
    order_id,
    volume_cm3,
    cbrt(volume_cm3) AS side_length_cm
FROM
    taco_box_orders
WHERE
    volume_cm3 > 0;

This query would return the side length of a cubic box for each taco order, assuming the volume is given in cubic centimeters.

erf

Returns the error function of the argument.

Syntax:

erf(x)

Arguments:

x (numeric): A numeric value. Supports UInt*, Int*, Float*, and Decimal* types.

Returns:

The error function value of x. Type: Float64.

Description:

The error function erf(x) is a special mathematical function related to the probability that a random variable with normal distribution takes a value between 0 and x√2.

For non-negative x, erf(x√2) is the probability that a random variable with a normal distribution of mean 0 and variance 1/2 falls in the range [−x, x].

Example:

SELECT
  erf(2 / sqrt(2)) AS probability_within_2_std_dev;

Result:

| probability_within_2_std_dev |
|------------------------------|
| 0.9544997361036416           |

This example calculates the probability that a value from a standard normal distribution falls within 2 standard deviations of the mean.

The erf function is closely related to the normal distribution and is often used in statistical analysis and machine learning applications.

erfc

Returns the complementary error function of the argument.

Syntax:

erfc(x)

Arguments:

x (numeric): The input value. Type: Float32, Float64, Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64 or Decimal.

Returns:

A number close to 1−erf(x) without loss of precision for large x values. Type: Float64.

Example:

SELECT
  erfc(1.5) AS complementary_error;

Result:

| complementary_error  |
|----------------------|
| 0.15729920705028513  |

This example calculates the complementary error function for the value 1.5.

The erfc function is particularly useful in statistics and probability theory, especially when dealing with normally distributed data. It’s often used in scenarios where high precision is required for large input values, as it avoids the loss of significance that can occur when subtracting erf(x) from 1 for large x

lgamma

Returns the logarithm of the gamma function.

Syntax:

lgamma(x)

Arguments:

x (numeric): A numeric value. Can be any of:
- Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64
- Float32, Float64
- Decimal32, Decimal64, Decimal128, Decimal256

Returns:

The logarithm of the gamma function of x.
Type: Float64

Example:

SELECT
  lgamma(5.5) AS gamma_log;

Result:

| gamma_log            |
|----------------------|
| 3.9019393856929816   |

This example calculates the logarithm of the gamma function for 5.5, which is useful in various statistical and mathematical computations.

The lgamma function is particularly useful when dealing with large factorials or in statistical distributions where the gamma function appears, such as in the chi-squared distribution or in Bayesian statistics.

Syntax:

tgamma(x)

Arguments:

x (numeric): A numeric value. Type: (U)Int*, Float*, or Decimal*.

Returns:

The gamma function of x. Type: Float64.

Example:

SELECT
  tgamma(5.5) AS gamma_result;

Result:

| gamma_result |
|--------------|
| 52.34277778  |

This example calculates the gamma function of 5.5, which is approximately 52.34277778.

The gamma function is an extension of the factorial function to real and complex numbers. For positive integers, tgamma(n) is equal to (n-1)!.

Example:

SELECT
    taco_id,
    taco_rating,
    tgamma(taco_rating) AS taco_gamma_score
FROM
    taco_reviews
WHERE
    taco_rating BETWEEN 1 AND 5
LIMIT 5;

Result:

| taco_id | taco_rating | taco_gamma_score |
|---------|-------------|------------------|
| 1       | 4           | 6                |
| 2       | 3.5         | 3.323351         |
| 3       | 5           | 24               |
| 4       | 2.5         | 1.329340         |
| 5       | 4.5         | 11.631728        |

In this example, we calculate a “taco gamma score” based on the taco rating. This could be used as a fun way to emphasize the differences between ratings, as the gamma function grows very quickly for larger inputs.

sin

Returns the sine of the argument.

Syntax:

sin(x)

Arguments:

x (numeric): The angle in radians. Accepts UInt*, Int*, Float*, or Decimal* types.

Returns:

The sine of x. Type: Float64.

Example:

SELECT
  sin(pi() / 4) AS sine_of_45_degrees;

Result:

| sine_of_45_degrees  |
|---------------------|
| 0.7071067811865475  |

This example calculates the sine of 45 degrees (π/4 radians), which is approximately 0.7071.

The sin function operates on radians, not degrees. To convert degrees to radians, you can use the radians function or multiply by π/180.

cos

Returns the cosine of the argument.

Syntax:

cos(x)

Arguments:

x (numeric): The angle in radians. Accepts numeric types: Int, UInt, Float, or Decimal.

Returns:

The cosine of x.
Type: Float64

Example:

SELECT cos(pi()) AS cosine_of_pi;

Result:

| cosine_of_pi |
|--------------|
| -1           |

This example calculates the cosine of π, which is -1.

Example:

SELECT
    taco_id,
    taco_angle,
    cos(taco_angle) AS taco_cosine
FROM
    taco_rotations
WHERE
    taco_id IN (1, 2, 3);

Result:

| taco_id | taco_angle | taco_cosine   |
|---------|------------|---------------|
| 1       | 0          | 1             |
| 2       | 1.5708     | 2.220446e-16  |
| 3       | 3.1416     | -1            |

In this example, we calculate the cosine for different taco rotation angles. The taco_angle is in radians, where:

0 radians (0°) gives a cosine of 1
π/2 radians (90°) gives a cosine very close to 0
π radians (180°) gives a cosine of -1

The slight deviation from 0 for π/2 is due to floating-point precision limitations.

tan

Returns the tangent of the argument.

Syntax:

tan(x)

Arguments:

x (numeric): The angle in radians. Accepts numeric types: Int, UInt, Float, or Decimal.

Returns:

The tangent of x.
Type: Float64

Example:

SELECT
  tan(pi() / 4) AS taco_angle;

Result:

| taco_angle         |
|--------------------|
| 0.9999999999999999 |

This example calculates the tangent of π/4 radians (45 degrees), which is approximately 1, representing the slope of a perfectly diagonal taco shell.

The tan function can produce very large values for angles close to π/2 + πn, where n is an integer. Be cautious when working with angles near these values.

asin

Returns the arc sine of the argument.

Syntax:

asin(x)

Arguments:

x (numeric): The value to calculate the arc sine for. Acceptable types: UInt*, Int*, Float*, or Decimal*.

Returns:

The arc sine of x in radians. Type: Float32 or Float64.

Example:

SELECT
  asin(0.5) AS arc_sine_of_half;

Result:

| arc_sine_of_half    |
|---------------------|
| 0.5235987755982989  |

This example calculates the arc sine of 0.5, which is approximately π/6 radians or 30 degrees.

The function expects input values in the range [-1, 1].
For values outside this range, the function returns NaN (Not a Number).
The returned value is in radians, not degrees.

Example:

SELECT
  taco_filling,
  filling_weight,
  asin(filling_weight / 100) AS filling_angle
FROM
  taco_ingredients
WHERE
  filling_weight <= 100;

Result:

| taco_filling | filling_weight | filling_angle       |
|--------------|----------------|---------------------|
| Carnitas     | 75             | 0.8480620789814816  |
| Guacamole    | 50             | 0.5235987755982989  |
| Salsa        | 25             | 0.2526802551420816  |

In this whimsical example, we’re using asin to calculate a “filling angle” based on the weight of taco fillings. The angle could represent how high the filling might stack in the taco shell, with heavier fillings resulting in a higher angle.

acos

Returns the arc cosine of the argument.

Syntax:

acos(x)

Arguments:

x (numeric): A number between -1 and 1. Can be any numeric type: Int, Float, or Decimal.

Returns:

The arc cosine of x in radians, ranging from 0 to π.
Type: Float64

Example:

SELECT
  acos(0.5) AS taco_angle;

Result:

| taco_angle            |
|-----------------------|
| 1.0471975511965979    |

In this example, we calculate the arc cosine of 0.5, which could represent the angle of a folded taco shell. The result is approximately 1.047 radians or about 60 degrees.

If the input value is outside the range [-1, 1], the function returns nan (Not a Number).

atan

Returns the arc tangent of the argument.

Syntax:

atan(x)

Arguments:

x (numeric): The input value. Can be any numeric type: UInt*, Int*, Float*, or Decimal*.

Returns:

The arc tangent of x in radians. Type: Float64.

Example:

SELECT
  atan(1) AS taco_angle;

Result:

| taco_angle            |
|-----------------------|
| 0.7853981633974483    |

In this example, we calculate the arc tangent of 1, which represents the angle (in radians) of a perfectly folded taco shell at 45 degrees.

The returned value is in the range -π/2 to π/2 radians.

pow

Calculates x raised to the power of y.

Syntax:

pow(x, y)

Alias:

power(x, y)

Arguments:

x (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal): Base.
y (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal): Exponent.

Returns:

The result of raising x to the power of y.

Type: Float64

Example:

SELECT
  pow(2, 3) AS result;

Result:

| result |
|--------|
| 8      |

This example calculates 2 raised to the power of 3, which equals 8.

Example:

SELECT
  taco_type,
  spice_level,
  pow(2, spice_level) AS spiciness_factor
FROM
  taco_menu
WHERE
  spice_level > 0;

Result:

| taco_type    | spice_level | spiciness_factor |
|--------------|-------------|------------------|
| Jalapeño     | 2           | 4                |
| Habanero     | 3           | 8                |
| Ghost Pepper | 4           | 16               |

In this example, we calculate a “spiciness factor” for different taco types by raising 2 to the power of their spice level. This creates an exponential scale to represent the perceived increase in spiciness.

The pow function is particularly useful when you need to perform exponential calculations or create non-linear scales based on input values.

Syntax:

cosh(x)

Arguments:

x (Float64): The angle, in radians. Values from the interval: −∞ < x < +∞.

Returns:

The hyperbolic cosine of x. Values from the interval: 1 ≤ cosh(x) < +∞.
Type: Float64.

Example:

SELECT
  cosh(0) AS result;

Result:

| result |
|--------|
| 1      |

This example calculates the hyperbolic cosine of 0, which is always 1.

The cosh function is commonly used in various mathematical and physical calculations, particularly in problems involving hyperbolic geometry or signal processing.

acosh

Calculates the inverse hyperbolic cosine of a number.

Syntax

acosh(x)

Arguments

x (Float64): The input value. Must be greater than or equal to 1.

Returns

The inverse hyperbolic cosine of x. Type: Float64.

Description

The acosh function returns the inverse hyperbolic cosine of x. This is the value whose hyperbolic cosine is x.

For input values less than 1, the function returns NaN (Not a Number).

Example

SELECT
  acosh(1) AS acosh_1,
  acosh(2) AS acosh_2,
  acosh(10) AS acosh_10;

Result:

| acosh_1 | acosh_2        | acosh_10       |
|---------|----------------|----------------|
| 0       | 1.3169578969248| 2.9932228461264|

Example:

SELECT
  taco_stack_height,
  acosh(taco_stack_height) AS stack_complexity
FROM
  (SELECT arrayJoin([1, 2, 5, 10]) AS taco_stack_height)
WHERE
  taco_stack_height >= 1;

Result:

| taco_stack_height | stack_complexity |
|-------------------|------------------|
| 1                 | 0                |
| 2                 | 1.3169578969248  |
| 5                 | 2.2924316695612  |
| 10                | 2.9932228461264  |

This example shows how the “complexity” of stacking tacos increases as the stack height grows, using the acosh function as a metaphor for stack complexity.

sinh

Returns the hyperbolic sine of the argument.

Syntax:

sinh(x)

Arguments:

x (Float64): The angle, in radians. Values from the interval: −∞ < x < +∞.

Returns:

The hyperbolic sine of x. Type: Float64.
Values from the interval: −∞ < sinh(x) < +∞.

Example:

SELECT
  sinh(0) AS hyperbolic_sine_of_zero;

Result:

| hyperbolic_sine_of_zero |
|-------------------------|
| 0                       |

This example calculates the hyperbolic sine of 0, which is 0.

The sinh function is particularly useful in various mathematical and physical calculations, especially those involving exponential growth or decay, such as in signal processing or solving differential equations.

asinh

Calculates the inverse hyperbolic sine of a number.

Syntax

asinh(x)

Arguments

x (numeric): The value to calculate the inverse hyperbolic sine for. Can be any numeric type: Int, Float, or Decimal.

Returns

The inverse hyperbolic sine of x.
Type: Float64

Description

The asinh function computes the inverse hyperbolic sine of x. It is defined for all real numbers and is the inverse function of hyperbolic sine (sinh).

For small values of x, asinh(x) is approximately equal to x.

Example

SELECT
  asinh(0) AS zero,
  round(asinh(1), 7) AS one,
  round(asinh(-2), 7) AS negative_two
FROM
  (SELECT arrayJoin([0, 1, -2]) AS x)

Result:

| zero | one       | negative_two |
|------|-----------|--------------|
| 0    | 0.8813736 | -1.4436355   |

In this example:

asinh(0) returns exactly 0.
asinh(1) is approximately 0.8813736, rounded to 7 decimal places.
asinh(-2) is approximately -1.4436355, rounded to 7 decimal places.

The asinh function is particularly useful in statistical and scientific computations, especially when dealing with hyperbolic geometries or certain types of distributions.

tanh

Returns the hyperbolic tangent of the argument.

Syntax:

tanh(x)

Arguments:

x (numeric): The angle, in radians. Values from the interval: −∞ < x < +∞. Supports UInt*, Int*, Float*, or Decimal* types.

Returns:

Values from the interval: −1 < tanh(x) < 1.
Type: Float64

Example:

SELECT
  tanh(0) AS result;

Result:

| result |
|--------|
| 0      |

This example calculates the hyperbolic tangent of 0, which is 0.

The tanh function is commonly used in machine learning, particularly in neural networks as an activation function. It maps input values to output values between -1 and 1, with a steeper slope near 0.

Example:

SELECT
  taco_spiciness,
  tanh(taco_spiciness) AS normalized_spiciness
FROM
  (SELECT arrayJoin([-2, -1, 0, 1, 2]) AS taco_spiciness)
ORDER BY
  taco_spiciness;

Result:

| taco_spiciness | normalized_spiciness |
|----------------|----------------------|
| -2             | -0.9640275800758169  |
| -1             | -0.7615941559557649  |
| 0              | 0                    |
| 1              | 0.7615941559557649   |
| 2              | 0.9640275800758169   |

In this example, we use tanh to normalize taco spiciness ratings. The original ratings range from -2 (very mild) to 2 (extremely spicy), and tanh maps these to values between -1 and 1, providing a normalized measure of spiciness that can be useful for comparisons or further calculations.

atanh

Returns the inverse hyperbolic tangent of the argument.

Syntax:

atanh(x)

Arguments:

x (numeric): Hyperbolic tangent of angle. Values from the interval: -1 < x < 1. Supports UInt*, Int*, Float*, or Decimal* types.

Returns:

The angle, in radians. Values from the interval: -∞ < atanh(x) < +∞.
Type: Float64

Example:

SELECT
  atanh(0.5) AS inverse_hyperbolic_tangent;

Result:

| inverse_hyperbolic_tangent |
|----------------------------|
| 0.5493061443340548         |

This example calculates the inverse hyperbolic tangent of 0.5, which represents the angle (in radians) whose hyperbolic tangent is 0.5.

The atanh function is particularly useful in various mathematical and scientific computations, especially in fields like physics and engineering where hyperbolic functions are commonly used.

atan2

Returns the angle in radians between the positive x-axis and the ray from the origin to the point (y, x) in the Euclidean plane.

Syntax:

atan2(y, x)

Arguments:

y (Float64): y-coordinate of the point.
x (Float64): x-coordinate of the point.

Returns:

The angle θ in radians, such that -π < θ ≤ π.
Type: Float64

Example:

SELECT
  atan2(2, 1) AS angle_radians,
  degrees(atan2(2, 1)) AS angle_degrees;

Result:

| angle_radians | angle_degrees |
|---------------|---------------|
| 1.1071487177  | 63.4349       |

This example calculates the angle between the x-axis and a line from (0,0) to (1,2), both in radians and degrees. It’s like finding the angle of a taco shell when held at that position!

The atan2 function is particularly useful in scenarios where you need to calculate angles or directions, such as in navigation systems or when working with polar coordinates.

hypot

Calculates the length of the hypotenuse of a right-angle triangle.

Syntax:

hypot(x, y)

Arguments:

x (Float64): The length of the first cathetus.
y (Float64): The length of the second cathetus.

Returns:

The length of the hypotenuse. [Float64]

Description:

This function computes the length of the hypotenuse of a right-angle triangle using the formula: sqrt(x^2 + y^2). It’s designed to avoid problems that can occur when squaring very large or very small numbers.

Example:

SELECT
  hypot(3, 4) AS hypotenuse_length;

Result:

| hypotenuse_length |
|-------------------|
| 5                 |

In this example, we calculate the length of the hypotenuse for a right-angle triangle with catheti of length 3 and 4 (representing a 3-4-5 triangle often used in carpentry).

Example:

SELECT
  taco_stand_id,
  latitude AS lat,
  longitude AS lon,
  hypot(latitude - 34.0522, longitude - 118.2437) AS distance_from_la
FROM
  taco_stands
ORDER BY
  distance_from_la
LIMIT 5;

Result:

| taco_stand_id | lat     | lon       | distance_from_la |
|---------------|---------|-----------|------------------|
| 42            | 34.0500 | -118.250  | 0.006557         |
| 17            | 34.0550 | -118.240  | 0.003162         |
| 23            | 34.0510 | -118.245  | 0.001414         |
| 8             | 34.0530 | -118.242  | 0.001000         |
| 31            | 34.0520 | -118.244  | 0.000316         |

This example uses hypot to calculate the straight-line distance between taco stands and the center of Los Angeles (latitude 34.0522, longitude -118.2437). The results are ordered to show the closest stands first.

This example assumes latitude and longitude are stored as decimal degrees. In real-world applications, more sophisticated geospatial functions would typically be used for accurate distance calculations on a spherical surface.

log1p

Calculates log(1+x). This calculation is more accurate than log(1+x) for small values of x.

Syntax:

log1p(x)

Arguments:

x (numeric): A value from the interval: -1 < x < +∞. Can be any numeric type: Int, Float, or Decimal.

Returns:

The natural logarithm of 1 plus the argument. Type: Float64.
Values range from -∞ < log1p(x) < +∞.

Example:

SELECT
  log1p(0) AS result;

Result:

| result |
|--------|
| 0      |

This example calculates log1p(0), which is equivalent to log(1+0), resulting in 0.

Example:

SELECT
  taco_name,
  price,
  log1p(price) AS log_price_plus_one
FROM
  taco_menu
WHERE
  price > 0
LIMIT 5;

Result:

| taco_name         | price | log_price_plus_one |
|-------------------|-------|--------------------|
| Classic Beef Taco | 3.99  | 1.6094379124341003 |
| Chicken Fajita    | 4.50  | 1.7047480922384253 |
| Veggie Delight    | 3.75  | 1.5649643564565795 |
| Spicy Shrimp      | 5.25  | 1.8325814637483102 |
| Fish Taco         | 4.75  | 1.7471984242918863 |

In this example, we calculate log1p of taco prices. This transformation can be useful for various statistical analyses or machine learning models where you want to compress the range of prices while maintaining the ability to differentiate between small price differences.

The log1p function is particularly useful when dealing with values close to zero, as it provides better numerical stability compared to calculating log(1+x) directly.

sign

Returns the sign of a numeric value.

Syntax:

sign(x)

Arguments:

x (numeric): A numeric value. Supports all numeric types in ClickHouse.

Returns:

-1 for x < 0
0 for x = 0
1 for x > 0

Type: Int8

Example:

SELECT
  sign(taco_price - 5) AS price_comparison
FROM
  taco_menu;

Result:

| price_comparison |
|------------------|
| -1               |
| 0                |
| 1                |

In this example:

The sign function compares each taco price to $5.
-1 indicates the taco is cheaper than $5.
0 indicates the taco costs exactly $5.
1 indicates the taco is more expensive than $5.

This function is useful for quickly categorizing numeric values based on their sign, which can be helpful in financial calculations, comparisons, or data analysis tasks.

sigmoid

Returns the sigmoid function value for the given input.

Syntax:

sigmoid(x)

Arguments:

x (numeric): The input value. Can be any numeric type: UInt*, Int*, Float*, or Decimal*.

Returns:

The sigmoid function value for x, which is always between 0 and 1. Type: Float64.

Example:

SELECT
  x,
  round(sigmoid(x), 5) AS sigmoid_value
FROM
  (SELECT arrayJoin([-2, -1, 0, 1, 2]) AS x)
ORDER BY x;

Result:

| x   | sigmoid_value |
|-----|---------------|
| -2  | 0.11920       |
| -1  | 0.26894       |
|  0  | 0.50000       |
|  1  | 0.73106       |
|  2  | 0.88080       |

This example demonstrates how the sigmoid function maps input values to the range (0, 1), with 0 mapping to 0.5 and increasingly positive or negative values approaching 1 or 0 respectively.

The sigmoid function is commonly used in machine learning, particularly in logistic regression and neural networks, as an activation function. It’s useful for transforming any input into a probability-like output between 0 and 1.

degrees

Converts radians to degrees.

Syntax:

degrees(x)

Arguments:

x (numeric): Input value in radians. Can be any numeric type: Int*, UInt*, Float*, or Decimal*.

Returns:

The angle in degrees. Type: Float64.

Example:

SELECT
  degrees(pi()) AS pi_degrees;

Result:

| degrees(pi())  |
|----------------|
| 180            |

This example converts π radians to degrees, resulting in 180 degrees.

Example:

SELECT
  taco_name,
  rotation_angle,
  degrees(rotation_angle) AS rotation_degrees
FROM
  taco_spinner
WHERE
  degrees(rotation_angle) > 180;

Result:

| taco_name     | rotation_angle | rotation_degrees |
|---------------|----------------|------------------|
| Spinning Al   | 3.4906585      | 200              |
| Pastor Twirl  | 4.7123889      | 270              |
| Carnitas Spin | 5.2359878      | 300              |

In this example, we convert the rotation angle of tacos on a spinner from radians to degrees, filtering for tacos that have rotated more than 180 degrees.

radians

Converts degrees to radians.

Syntax:

radians(x)

Arguments:

x (numeric): Input in degrees. Can be any numeric type: UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, Float64, or Decimal.

Returns:

The value in radians. Type: Float64.

Example:

SELECT
  radians(180) AS radians_180;

Result:

| radians_180         |
|---------------------|
| 3.141592653589793   |

This example converts 180 degrees to radians, resulting in π (approximately 3.14159).

The radians function is useful when you need to convert angle measurements from degrees to radians for trigonometric calculations or when working with circular data in a taco analysis, such as calculating the optimal angle for folding a taco shell.

factorial

Computes the factorial of an integer value.

Syntax:

factorial(n)

Arguments:

n (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64): An integer value.

Returns:

The factorial of the input value. [UInt64]

Details:

The factorial of 0 is 1.
For any negative input value, the function returns 1.
The maximum allowed input value is 20. Values of 21 or greater will cause an exception.

Example:

SELECT
  factorial(5) AS five_factorial,
  factorial(0) AS zero_factorial,
  factorial(-3) AS negative_factorial,
  factorial(20) AS max_factorial
FROM
  taco_orders
LIMIT 1;

Result:

| five_factorial | zero_factorial | negative_factorial | max_factorial      |
|----------------|----------------|--------------------|--------------------|
| 120            | 1              | 1                  | 2432902008176640000|

In this example:

five_factorial calculates 5! which is 5 * 4 * 3 * 2 * 1 = 120.
zero_factorial demonstrates that 0! is defined as 1.
negative_factorial shows that the function returns 1 for negative inputs.
max_factorial calculates 20!, which is the largest factorial the function can compute.

Attempting to calculate the factorial of a number greater than 20 will result in an exception. For example, SELECT factorial(21) would throw an error.

widthBucket

Returns the bucket number into which the operand value falls in a histogram with count equal-width buckets spanning the range from low to high.

Syntax:

widthBucket(operand, low, high, count)

Alias:

WIDTH_BUCKET

Arguments:

operand (numeric): The value to be bucketed. Can be any native number type.
low (numeric): The lower bound of the range. Can be any native number type.
high (numeric): The upper bound of the range. Can be any native number type.
count (UInt64): The number of buckets. Must be an unsigned native integer and cannot be zero.

Returns:

The bucket number (1-based) as a UInt64.
Returns 0 if operand < low.
Returns count + 1 if operand >= high.

Example:

SELECT
  widthBucket(taco_price, 1, 10, 3) AS price_bucket,
  COUNT(*) AS taco_count
FROM
  taco_menu
GROUP BY
  price_bucket
ORDER BY
  price_bucket;

Result:

| price_bucket | taco_count |
|--------------|------------|
| 1            | 5          |
| 2            | 8          |
| 3            | 3          |
| 4            | 1          |

In this example:

Bucket 1 represents tacos priced from $1.00 to$ 3.99
Bucket 2 represents tacos priced from $4.00 to$ 6.99
Bucket 3 represents tacos priced from $7.00 to$ 9.99
Bucket 4 represents tacos priced $10.00 and above

This function is useful for creating histograms or for grouping continuous data into discrete buckets for analysis.

proportionsZTest

Performs a two-proportion Z-test, which is a statistical test for comparing proportions from two populations.

Syntax:

proportionsZTest(successes_x, successes_y, trials_x, trials_y, conf_level, pool_type)

Arguments:

successes_x (UInt64): Number of successes in population x.
successes_y (UInt64): Number of successes in population y.
trials_x (UInt64): Number of trials in population x.
trials_y (UInt64): Number of trials in population y.
conf_level (Float64): Confidence level for the test (e.g., 0.95 for 95% confidence).
pool_type (String): Method for estimating standard error. Can be either ‘unpooled’ or ‘pooled’.

Returns:

A tuple containing four Float64 values:

z_stat - The Z statistic.
p_val - The p-value.
ci_low - The lower bound of the confidence interval.
ci_high - The upper bound of the confidence interval.

In the pooled version, the two proportions are averaged, and only one proportion is used to estimate the standard error.
In the unpooled version, the two proportions are used separately.

Example:

SELECT
  proportionsZTest(10, 11, 100, 101, 0.95, 'unpooled') AS test_result
FROM
  taco_sales;

Result:

| test_result                                                                        |
|------------------------------------------------------------------------------------|
| (-0.20656724435948853,0.8363478437079654,-0.09345975390115283,0.07563797172293502) |

In this example, we’re comparing the success rates of two different taco recipes. The function returns the Z-statistic, p-value, and confidence interval for the difference in proportions.

Introduction

Statements

Functions

​ClickHouse function reference

​e

​pi

​exp

​log

​exp2

​intExp2

​log2

​exp10

​intExp10

​log10

​sqrt

​cbrt

​erf

​erfc

​lgamma

​sin

​cos

​tan

​asin

​acos

​atan

​pow

​acosh

​sinh

​asinh

​tanh

​atanh

​atan2

​hypot

​log1p

​sign

​sigmoid

​degrees

​radians

​factorial

​widthBucket

​proportionsZTest

ClickHouse function reference

e

pi

exp

log

exp2

intExp2

log2

exp10

intExp10

log10

sqrt

cbrt

erf

erfc

lgamma

sin

cos

tan

asin

acos

atan

pow

acosh

sinh

asinh

tanh

atanh

atan2

hypot

log1p

sign

sigmoid

degrees

radians

factorial

widthBucket

proportionsZTest