Math Operations¶

RayforceDB provides comprehensive mathematical operations for working with numeric and temporal data types. These functions operate on Vectors and Scalar.

Binary Arithmetic Operations¶

Add `+`¶

Performs addition on its arguments. Works with scalars, vectors, and temporal types.

(+ 1 2)
3

(+ [1 2 3] 3)
[4 5 6]

(+ [100 200 300] [10 20 30])
[110 220 330]

(+ 2024.03.20 5)
2024.03.25

Subtract `-`¶

Performs subtraction on its arguments. Works with scalars, vectors, and temporal types.

(- 1 2)
-1

(- [1 2 3] 3)
[-2 -1 0]

(- [100 200 300] [10 20 30])
[90 180 270]

(- 2024.03.20 5)
2024.03.15

Multiply `*`¶

Performs multiplication on its arguments. Works with scalars, vectors, and temporal types.

(* 1 2)
2

(* [1 2 3] 3)
[3 6 9]

(* [100 200 300] [2 3 4])
[200 600 1200]

(* 3i 02:15:07.000)
06:45:21.000

Divide `/`¶

Performs integer division, returning the quotient.

(/ 10 3)
3

(/ [10 -10 3] 2)
[5 -5 1]

(/ [100 200 300] [2 4 5])
[50 50 60]

Division by Zero

Division by zero returns null (0Nl). For floating-point division, use the div function.

Modulo `%`¶

Performs modulo operation, returning the remainder of division.

(% 10 3)
1

(% [10 -10 3] 3)
[1 2 0]

(% [100 200 300] [7 13 17])
[2 5 11]

Aggregation Functions¶

Aggregation functions operate on Vectors and return scalar values.

Sum¶

Calculates the sum of values in a Vector.

(sum [1 2 3])
6

(sum [1.2 2.2 3.2])
6.60

(sum [100 200 300 150])
750

Avg¶

Calculates the average (mean) value of a Vector.

(avg [1.0 2.0 3.0])
2.00

(avg [-24 12 6])
-2.00

(avg [150.25 300.50 125.75])
192.17

Med¶

Calculates the median (middle value) of a Vector.

(med [3 1 2])
2.00

(med [3 1 2 4])
2.50

(med [150 300 125 200])
175

The med function works with integer vectors. For floating-point values, convert to integers or use integer representations.

Dev¶

Calculates the standard deviation of a Vector.

(dev [1 2 3 4 50])
19.03

(dev [1i 2i])
0.5

(dev [150.25 300.50 125.75])
77.25

Min¶

Returns the minimum value in a Vector.

(min [1 2 3])
1

(min [10.5 2.3 7.8])
2.3

(min [150.25 300.50 125.75])
125.75

Max¶

Returns the maximum value in a Vector.

(max [1 2 3])
3

(max [10.5 2.3 7.8])
10.5

(max [150.25 300.50 125.75])
300.50

Rounding Functions¶

Ceil¶

Rounds a value up to the nearest integer greater than or equal to the given value.

(ceil 1.5)
2.00

(ceil [1.1 2.5 -1.1])
[2.00 3.00 -1.00]

(ceil [150.25 300.49 125.01])
[151.00 301.00 126.00]

Floor¶

Returns the largest integer less than or equal to a given number.

(floor 1.5)
1.00

(floor [1.1 2.5 -1.1])
[1.00 2.00 -2.00]

(floor [150.25 300.49 125.99])
[150.00 300.00 125.00]

Round¶

Rounds a number to the nearest integer. If halfway between two integers, rounds away from zero.

(round 1.5)
2.00

(round [-1.5 -1.1 0.0 1.1 1.5])
[-2.00 -1.00 0.00 1.00 2.00]

(round [150.25 300.49 125.75])
[150.00 300.00 126.00]

Special Functions¶

Xbar¶

Rounds values down to the nearest multiple of a specified bin width.

(xbar 17 5)
15

(xbar [10 11 12 13 14] 3)
[9 9 12 12 12]

;; Round timestamps to 1-minute buckets (60000 milliseconds)
(xbar [09:00:15.000 09:01:30.000 09:02:45.000] 60000)
[09:00:00.000 09:01:00.000 09:02:00.000]

;; Round prices to nearest 5 increment
(xbar [152.30 157.80 163.20] 5)
[150.00 155.00 160.00]