Numbers & Math Functions

Functions for mathematical calculations and numeric operations.

Basic Arithmetic

Function	Description	Example
`ABS(x)`	Absolute value	`ABS(-5)` → `5`
`SIGN(x)` / `SIGNUM(x)`	Sign (-1, 0, or 1)	`SIGN(-5)` → `-1`
`CEIL(x)`	Round up	`CEIL(4.2)` → `5`
`FLOOR(x)`	Round down	`FLOOR(4.8)` → `4`
`ROUND(x, d)`	Round to d decimals	`ROUND(3.14159, 2)` → `3.14`
`TRUNC(x, d)`	Truncate to d decimals	`TRUNC(3.99, 0)` → `3`

SELECT
  ABS(profit_loss) as magnitude,
  ROUND(price * 1.1, 2) as price_with_tax,
  CEIL(items / 10.0) as boxes_needed,
  FLOOR(hours_worked) as complete_hours
FROM transactions

Powers and Roots

Function	Description	Example
`POWER(x, y)` / `POW(x, y)`	x to the power of y	`POWER(2, 3)` → `8`
`SQRT(x)`	Square root	`SQRT(16)` → `4`
`CBRT(x)`	Cube root	`CBRT(27)` → `3`
`EXP(x)`	e to the power of x	`EXP(1)` → `2.718...`

SELECT
  SQRT(x*x + y*y) as distance,
  POWER(1 + rate, years) as growth_factor,
  CBRT(volume) as edge_length
FROM calculations

Logarithms

Function	Description	Example
`LN(x)`	Natural logarithm	`LN(2.718)` → `1`
`LOG(x)`	Base-10 logarithm	`LOG(100)` → `2`
`LOG(base, x)`	Logarithm with base	`LOG(2, 8)` → `3`
`LOG2(x)`	Base-2 logarithm	`LOG2(8)` → `3`
`LOG10(x)`	Base-10 logarithm	`LOG10(1000)` → `3`

SELECT
  LOG10(followers_count + 1) as log_followers,
  LN(price / previous_price) as log_return
FROM metrics

Trigonometry

Function	Description
`SIN(x)`, `COS(x)`, `TAN(x)`	Basic trig functions (radians)
`ASIN(x)`, `ACOS(x)`, `ATAN(x)`	Inverse trig functions
`ATAN2(y, x)`	Two-argument arctangent
`SINH(x)`, `COSH(x)`, `TANH(x)`	Hyperbolic functions
`DEGREES(x)`	Radians to degrees
`RADIANS(x)`	Degrees to radians
`PI()`	Value of π

SELECT
  DEGREES(ATAN2(dy, dx)) as heading_degrees,
  SIN(RADIANS(latitude)) as sin_lat,
  COS(RADIANS(longitude)) as cos_lon
FROM coordinates

Number Theory

Function	Description	Example
`GCD(a, b)`	Greatest common divisor	`GCD(12, 8)` → `4`
`LCM(a, b)`	Least common multiple	`LCM(4, 6)` → `12`
`FACTORIAL(n)`	Factorial	`FACTORIAL(5)` → `120`

SELECT
  GCD(numerator, denominator) as gcd,
  numerator / GCD(numerator, denominator) as reduced_num
FROM fractions

Rounding Functions

SELECT
  ROUND(3.14159, 2) as rounded,       -- 3.14
  ROUND(1234, -2) as to_hundreds,     -- 1200
  TRUNC(3.99) as truncated,           -- 3
  CEIL(4.01) as ceiling,              -- 5
  FLOOR(4.99) as floor_val            -- 4
FROM numbers

Random Numbers

Function	Description
`RANDOM()`	Random float between 0 and 1

-- Random sampling
SELECT * FROM events
WHERE RANDOM() < 0.01  -- ~1% sample
 
-- Random assignment
SELECT
  user_id,
  CASE
    WHEN RANDOM() < 0.5 THEN 'control'
    ELSE 'treatment'
  END as experiment_group
FROM users

Special Values

Function	Description
`ISNAN(x)`	Check if NaN
`ISZERO(x)`	Check if zero
`NANVL(x, default)`	Replace NaN with default

SELECT
  CASE WHEN ISNAN(value) THEN 0 ELSE value END as safe_value,
  NANVL(ratio, 1.0) as safe_ratio
FROM metrics

Practical Examples

Financial Calculations

SELECT
  order_id,
  subtotal,
  ROUND(subtotal * 0.08, 2) as tax,
  ROUND(subtotal * 1.08, 2) as total,
  ROUND(subtotal * tip_percent / 100, 2) as tip
FROM orders

Percentage and Ratios

SELECT
  category,
  TUMBLE(interval '1 hour') as window,
  COUNT(*) as count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as percentage
FROM events
GROUP BY category, window

Distance Calculations

-- Haversine distance (simplified)
SELECT
  order_id,
  SQRT(
    POWER(dest_lat - origin_lat, 2) +
    POWER(dest_lon - origin_lon, 2)
  ) * 111 as distance_km  -- Rough approximation
FROM deliveries

Binning Values

SELECT
  FLOOR(price / 10) * 10 as price_bucket,
  COUNT(*) as count
FROM products
GROUP BY FLOOR(price / 10), TUMBLE(interval '1 hour')

Growth Calculations

SELECT
  product_id,
  TUMBLE(interval '1 day') as window,
  SUM(sales) as current_sales,
  LAG(SUM(sales)) OVER (PARTITION BY product_id ORDER BY window) as prev_sales,
  ROUND(
    (SUM(sales) - LAG(SUM(sales)) OVER (PARTITION BY product_id ORDER BY window))
    / NULLIF(LAG(SUM(sales)) OVER (PARTITION BY product_id ORDER BY window), 0)
    * 100,
    2
  ) as growth_pct
FROM sales
GROUP BY product_id, window