Array functions

This page documents functions for n-dimensional arrays. This isn't an exhaustive list of all functions that may take an array parameter. For example, financial functions are listed in their own section, whether or not they can take an array parameter.

array_avg

array_avg(array) returns the average of all the array elements. NULL elements don't contribute to either count or sum.

Parameter

• array - the array

Examples

SELECT array_avg(ARRAY[ [1.0, 1.0], [2.0, 2.0] ]);

array_avg
1.5

Average bid price across all 40 order book levels:

array_avg - average bid priceDemo this query

SELECT symbol, array_avg(bids[1]) AS avg_bid_price
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

array_build

array_build(nArrays, size, filler1 [, filler2, ...]) constructs a DOUBLE array at runtime, where the length and contents can vary per row.

Use array_build when the ARRAY[...] literal syntax is not enough, for example when you need to:

• Fill an array with a scalar - create a zero vector, or fill every position with a computed value (like array_max)
• Stack arrays into a 2D matrix - combine several 1D arrays into a single DOUBLE[][] for downstream array operations

Parameters

Parameter	Description
nArrays	How many sub-arrays the output has. Must be an integer literal (not a column, expression, or bind variable). 1 produces a 1D DOUBLE[]. 2 or more produces a 2D DOUBLE[][] with nArrays sub-arrays, each of length size.
size	Length of each sub-array. Can be an INT/LONG value, or a DOUBLE[] array whose element count is used as the length.
filler1 .. fillerN	One filler per sub-array (exactly nArrays fillers required). A scalar (DOUBLE/INT/LONG) is repeated for every position. A DOUBLE[] array is copied position-by-position: if shorter than size, remaining positions are NULL; if longer, excess elements are ignored.

All arguments except nArrays can be constants, declared variables, column references, or expressions evaluated per row.

Return type

• DOUBLE[] when nArrays is 1
• DOUBLE[][] when nArrays is 2 or more, where the first dimension has length nArrays, the second has length size

The output is always 1D or 2D. Passing a large nArrays (e.g. 100) produces a 2D array with 100 rows, not a 100-dimensional array.

Examples

Create an array filled with a scalar value:

array_build - scalar fillDemo this query

SELECT array_build(1, 3, 0) FROM long_sequence(1);

array_build
[0.0,0.0,0.0]

Variable-length fill - size from a column:

array_build - variable-length fillDemo this query

SELECT x, array_build(1, x::int, -1) FROM long_sequence(3);

x	array_build
1	[-1.0]
2	[-1.0,-1.0]
3	[-1.0,-1.0,-1.0]

Each row gets an array whose length equals x, filled with -1.

Broadcast a computed value across an array:

On the demo market_data table, bids is a DOUBLE[][] where bids[1] contains bid prices. The following creates an array the same length as bids[1], filled with its maximum value:

array_build - broadcast computed scalarDemo this query

SELECT array_build(1, bids[1], array_max(bids[1]))
FROM market_data
LIMIT 1;

Here bids[1] in the size position is a DOUBLE[], so its element count determines the output length. The filler array_max(bids[1]) is a scalar, so it is repeated in every position.

Copy an existing array:

When both size and the filler are the same DOUBLE[], the filler is copied position-by-position, effectively cloning the array:

array_build - copy an arrayDemo this query

SELECT array_build(1, bids[1], bids[1])
FROM market_data
LIMIT 1;

The result is a new DOUBLE[] with the same length and values as bids[1].

NULL padding when the filler array is shorter than size:

array_build - NULL paddingDemo this query

SELECT array_build(1, 5, ARRAY[10.0, 20.0, 30.0]) FROM long_sequence(1);

array_build
[10.0,20.0,30.0,null,null]

Positions beyond the filler's length are filled with NULL.

2D array with scalar fill:

array_build - 2D scalar fillDemo this query

SELECT array_build(2, 3, 1.0, 0.0) FROM long_sequence(1);

array_build
[[1.0,1.0,1.0],[0.0,0.0,0.0]]

Two fillers are required because nArrays is 2. The first filler (1.0) fills the first row, the second (0.0) fills the second row.

Combine existing arrays into a 2D matrix:

array_build - 2D from market dataDemo this query

SELECT array_build(2, bids[1], bids[1], asks[1])
FROM market_data
LIMIT 1;

Here bids[1] appears twice: in the size position it provides the output length (40 elements), and as the first filler it supplies the bid prices. asks[1] is the second filler. The result is a DOUBLE[2][40] where the first row contains bid prices and the second row contains ask prices.

Stack multiple array columns into a 2D array:

When a table has several DOUBLE[] columns, array_build can stack them into a single 2D array. The market_data table on the demo instance stores order book snapshots with bids and asks as DOUBLE[][] columns (run SHOW COLUMNS FROM market_data; on demo to see the schema). Each contains price and volume sub-arrays: bids[1] = bid prices, bids[2] = bid volumes, and likewise for asks.

The following packs all four sub-arrays into a single DOUBLE[4][N] array:

array_build - stack 4 arraysDemo this query

SELECT array_build(4, bids[1], bids[1], bids[2], asks[1], asks[2])
FROM market_data
LIMIT 1;

The size argument (bids[1]) is a DOUBLE[], so its element count determines the sub-array length. Each of the four fillers is also a DOUBLE[], copied position-by-position into its respective sub-array.

Constraints and edge cases

• nArrays must be at least 1. Passing 0 raises an error.
• size = 0 produces an empty array. size < 0 raises an error. If size evaluates to NULL, the result is a NULL array.
• Fillers must be scalars or 1D DOUBLE[] arrays. Multi-dimensional array fillers are not accepted.
• NULL values inside a filler array are copied as-is. A NULL filler array fills the entire row with NULL.
• Both nArrays and size are capped at 268,435,455. The total element count (nArrays × size) must also fit within memory limits.

array_count

array_count(array) returns the number of finite elements in the array. NULL elements do not contribute to the count.

Parameter

• array - the array

Examples

SELECT
  array_count(ARRAY[ [1.0, null], [null, 2.0] ]) c1,
  array_count(ARRAY[ [0.0/0.0, 1.0/0.0], [-1.0/0.0, 0.0/0.0] ]) c2;

c1	c2
2	0

Count the number of price levels on each side of the book:

array_count - order book levelsDemo this query

SELECT symbol, array_count(bids[1]) AS bid_levels, array_count(asks[1]) AS ask_levels
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

array_cum_sum

array_cum_sum(array) returns a 1D array of the cumulative sums over the array, traversing it in row-major order. The input array can have any dimensionality. The returned 1D array has the same number of elements as the input array. NULL elements behave as if they were zero.

Parameter

• array - the array

Examples

SELECT array_cum_sum(ARRAY[ [1.0, 1.0], [2.0, 2.0] ]);

array_cum_sum
ARRAY[1.0,2.0,4.0,6.0]

Cumulative bid volume (depth of book):

array_cum_sum - cumulative book depthDemo this query

SELECT symbol, array_cum_sum(bids[2]) AS cumulative_bid_volume
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

Each position shows the total volume available at that level and above.

array_elem_avg

array_elem_avg(array1, array2 [, ...]) or array_elem_avg(array) returns an array where each element is the average of the corresponding elements across the inputs. Works in both multi-argument and aggregate modes, with the same NULL handling, different-length, and multi-dimensional behavior as array_elem_min.

Uses Kahan compensated summation to minimize floating-point rounding errors.

Parameters

Multi-argument mode:

• array1, array2 [, ...] - two or more DOUBLE[] arrays

Aggregate mode:

• array - a DOUBLE[] column

Examples

SELECT array_elem_avg(ARRAY[10.0, 20.0, 30.0], ARRAY[30.0, 40.0, 50.0]);

array_elem_avg
[20.0,30.0,40.0]

Aggregate - average bid volume per level each hour:

array_elem_avg - average bid volume per levelDemo this query

SELECT timestamp, symbol, array_elem_avg(bids[2]) AS avg_bid_volume
FROM market_data
WHERE symbol = 'EURUSD'
  AND timestamp IN '$today'
SAMPLE BY 1h;

Each position in the result holds the average volume at that book level across all snapshots in the hour.

array_elem_max

array_elem_max(array1, array2 [, ...]) or array_elem_max(array) returns an array where each element is the maximum of the corresponding elements across the inputs. Works in both multi-argument and aggregate modes, with the same NULL handling, different-length, and multi-dimensional behavior as array_elem_min.

Parameters

Multi-argument mode:

• array1, array2 [, ...] - two or more DOUBLE[] arrays

Aggregate mode:

• array - a DOUBLE[] column

Examples

SELECT array_elem_max(ARRAY[1.0, 5.0, 3.0], ARRAY[4.0, 2.0, 6.0]);

array_elem_max
[4.0,5.0,6.0]

SELECT array_elem_max(
    ARRAY[[1.0, 8.0, 3.0], [5.0, 2.0, 9.0]],
    ARRAY[[4.0, 6.0, 7.0], [3.0, 8.0, 1.0]]
);

array_elem_max
[[4.0,8.0,7.0],[5.0,8.0,9.0]]

Aggregate - best bid price at each level over the hour:

array_elem_max - best bid per levelDemo this query

SELECT timestamp, symbol, array_elem_max(bids[1]) AS best_bid_per_level
FROM market_data
WHERE symbol = 'EURUSD'
  AND timestamp IN '$today'
SAMPLE BY 1h;

Each position holds the highest bid price seen at that book level during the hour.

array_elem_min

array_elem_min(array1, array2 [, ...]) or array_elem_min(array) returns an array where each element is the minimum of the corresponding elements across the inputs. Works in two modes:

• Multi-argument (per-row): pass two or more DOUBLE[] expressions. The function returns a single array that is the element-wise minimum of all arguments.
• Aggregate (GROUP BY / SAMPLE BY): pass a single DOUBLE[] column. The function aggregates across rows, returning one result array per group.

NULL arrays are skipped entirely. NULL elements within an array are skipped at that position. If every input at a given position is NULL, the result at that position is NULL.

When input arrays have different lengths, the output length is the maximum across all inputs. Positions beyond the end of a shorter array receive no contribution from that array.

N-dimensional arrays are supported. The output shape is the per-dimension maximum of all inputs. In multi-argument mode, all arguments must have the same number of dimensions.

Parameters

Multi-argument mode:

• array1 - a DOUBLE[] array
• array2 - a DOUBLE[] array
• ... - additional DOUBLE[] arrays (optional)

Aggregate mode:

• array - a DOUBLE[] column

Examples

Multi-argument - element-wise minimum of two arrays:

SELECT array_elem_min(ARRAY[1.0, 5.0, 3.0], ARRAY[4.0, 2.0, 6.0]);

array_elem_min
[1.0,2.0,3.0]

Multi-argument - arrays of different lengths:

SELECT array_elem_min(
    ARRAY[100.0, 200.0, 150.0],
    ARRAY[120.0, 180.0, 160.0, 90.0]
);

array_elem_min
[100.0,180.0,150.0,90.0]

The fourth position has only one contributing value.

Multi-argument - NULL elements are skipped:

SELECT array_elem_min(ARRAY[100.0, null], ARRAY[null, 200.0]);

array_elem_min
[100.0,200.0]

Each position takes the minimum over the values that are present (1 value each here, not 2).

Aggregate - worst bid price at each level over the hour:

array_elem_min - worst bid per levelDemo this query

SELECT timestamp, symbol, array_elem_min(bids[1]) AS worst_bid_per_level
FROM market_data
WHERE symbol = 'EURUSD'
  AND timestamp IN '$today'
SAMPLE BY 1h;

Each position holds the lowest bid price seen at that book level during the hour.

Multi-argument - 2D arrays:

SELECT array_elem_min(
    ARRAY[[1.0, 8.0, 3.0], [5.0, 2.0, 9.0]],
    ARRAY[[4.0, 6.0, 7.0], [3.0, 8.0, 1.0]]
);

array_elem_min
[[1.0,6.0,3.0],[3.0,2.0,1.0]]

array_elem_sum

array_elem_sum(array1, array2 [, ...]) or array_elem_sum(array) returns an array where each element is the sum of the corresponding elements across the inputs. Works in both multi-argument and aggregate modes, with the same NULL handling, different-length, and multi-dimensional behavior as array_elem_min.

Uses Kahan compensated summation to minimize floating-point rounding errors.

Parameters

Multi-argument mode:

• array1, array2 [, ...] - two or more DOUBLE[] arrays

Aggregate mode:

• array - a DOUBLE[] column

Examples

SELECT array_elem_sum(
    ARRAY[1.0, 2.0, 3.0],
    ARRAY[10.0, 20.0, 30.0]
);

array_elem_sum
[11.0,22.0,33.0]

Aggregate - total bid volume per level over the hour:

array_elem_sum - total volume per levelDemo this query

SELECT timestamp, symbol, array_elem_sum(bids[2]) AS total_bid_volume_per_level
FROM market_data
WHERE symbol = 'EURUSD'
  AND timestamp IN '$today'
SAMPLE BY 1h;

Each position holds the sum of all bid volumes seen at that level during the hour.

array_max

array_max(array) returns the maximum value from all the array elements. NULL elements and non-finite values (NaN, Infinity) are ignored. If the array contains no finite values, the function returns NULL.

Parameter

• array - the array

Examples

SELECT array_max(ARRAY[ [1.0, 5.0], [3.0, 2.0] ]);

array_max
5.0

Best bid and best ask from the full order book arrays:

array_max - best bid and askDemo this query

SELECT symbol, array_max(bids[1]) AS best_bid, array_min(asks[1]) AS best_ask
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

array_min

array_min(array) returns the minimum value from all the array elements. NULL elements and non-finite values (NaN, Infinity) are ignored. If the array contains no finite values, the function returns NULL.

Parameter

• array - the array

Examples

SELECT array_min(ARRAY[ [1.0, 5.0], [3.0, 2.0] ]);

array_min
1.0

Worst price on each side of the book:

array_min - deepest book levelsDemo this query

SELECT symbol, array_min(bids[1]) AS worst_bid, array_max(asks[1]) AS worst_ask
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

array_position

array_position(array, elem) returns the position of elem inside the 1D array. If elem doesn't appear in array, it returns NULL. If elem is NULL, it returns the position of the first NULL element, if any.

Parameters

• array - the 1D array
• elem - the element to look for

Examples

SELECT
  array_position(ARRAY[1.0, 2.0], 1.0) p1,
  array_position(ARRAY[1.0, 2.0], 3.0) p2;

p1	p2
1	NULL

Verify best_bid is always the first element of bids[1]:

array_position - find best_bid in the bookDemo this query

SELECT symbol, array_position(bids[1], best_bid) AS best_bid_position
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

Returns 1 for every row, confirming best_bid equals bids[1][1].

array_reverse

Syntax:

array_reverse(array) -> DOUBLE[]

Reverses the element order within the innermost dimension of a DOUBLE[] array. Unlike array_sort, which reorders elements by value, array_reverse preserves whatever ordering the array already has and flips it. This is useful when elements are ordered by an external criterion (such as ingestion timestamp, another column's values, or a prior sort) and you need the opposite direction.

For multi-dimensional arrays, each sub-array is reversed independently.

Parameters

Parameter	Type	Description
array	DOUBLE[] or DOUBLE[][]	The input array to reverse.

Return value

DOUBLE[] with the same dimensionality as the input. Returns NULL if the input is NULL. Empty arrays return empty arrays.

Examples

Reverse a simple array:

SELECT array_reverse(ARRAY[1.0, 2.0, 3.0]);

array_reverse
[3.0, 2.0, 1.0]

Reverse bid prices to get worst-to-best order:

array_reverse - bids worst to bestDemo this query

SELECT symbol, array_reverse(bids[1]) AS bids_worst_to_best
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

bids[1] stores prices best-first (descending). Reversing gives ascending order from the deepest level to top of book.

Reverse each row of a 2D array independently:

SELECT array_reverse(ARRAY[[1.0, 2.0], [3.0, 4.0]]);

array_reverse
[[2.0, 1.0], [4.0, 3.0]]

See also

• array_sort - Sort array elements by value

array_sort

Syntax:

array_sort(array) -> DOUBLE[]
array_sort(array, descending) -> DOUBLE[]
array_sort(array, descending, nullsFirst) -> DOUBLE[]

Sorts the elements of a DOUBLE[] array by value along the innermost dimension. Useful when you need elements ordered by value, for example to find the median, compute percentiles, or prepare input for insertion_point.

For multi-dimensional arrays, each sub-array is sorted independently.

Parameters

Parameter	Type	Description
array	DOUBLE[] or DOUBLE[][]	The input array to sort.
descending	BOOLEAN (optional, default false)	true sorts in descending order.
nullsFirst	BOOLEAN (optional)	true places null values before non-null values. Default: nulls last for ascending, nulls first for descending.

Return value

DOUBLE[] with the same dimensionality as the input. Returns NULL if the input is NULL. Empty arrays return empty arrays.

Examples

Sort ask prices descending:

array_sort - asks descendingDemo this query

SELECT symbol, array_sort(asks[1], true) AS asks_desc
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

asks[1] is stored ascending (best ask first). Sorting descending gives the same result as array_reverse here, but array_sort works on unsorted arrays too.

Descending sort:

SELECT array_sort(ARRAY[3.0, 1.0, 2.0], true);

array_sort
[3.0, 2.0, 1.0]

Control null placement:

SELECT
  array_sort(ARRAY[1.0, null, 2.0]) AS default_nulls,
  array_sort(ARRAY[1.0, null, 2.0], false, true) AS nulls_first;

default_nulls	nulls_first
[1.0, 2.0, null]	[null, 1.0, 2.0]

Sort each row of a 2D array independently:

SELECT array_sort(ARRAY[[3.0, 1.0, 2.0], [6.0, 4.0, 5.0]]);

array_sort
[[1.0, 2.0, 3.0], [4.0, 5.0, 6.0]]

See also

• array_reverse - Reverse array element order
• insertion_point - Binary search on a sorted array

array_stddev

array_stddev(array) returns the sample standard deviation of all the array elements. This is an alias for array_stddev_samp(). NULL elements and non-finite values (NaN, Infinity) are ignored. If the array contains fewer than 2 finite values, the function returns NULL.

Parameter

• array - the array

Examples

SELECT array_stddev(ARRAY[ [1.0, 2.0], [3.0, 4.0] ]);

array_stddev
1.29099445

Price dispersion across bid levels:

array_stddev - bid price dispersionDemo this query

SELECT symbol, array_stddev(bids[1]) AS bid_price_dispersion
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

A higher value means prices are more spread out across the 40 book levels.

array_stddev_pop

array_stddev_pop(array) returns the population standard deviation of all the array elements. NULL elements and non-finite values (NaN, Infinity) are ignored. The population standard deviation uses N in the denominator of the standard deviation formula. If the array contains no finite values, the function returns NULL.

Parameter

• array - the array

Examples

SELECT array_stddev_pop(ARRAY[ [1.0, 2.0], [3.0, 4.0] ]);

array_stddev_pop
1.11803399

Population stddev of ask volumes (treating the 40 levels as the full population):

array_stddev_pop - ask volume dispersionDemo this query

SELECT symbol, array_stddev_pop(asks[2]) AS ask_volume_stddev
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

array_stddev_samp

array_stddev_samp(array) returns the sample standard deviation of all the array elements. NULL elements and non-finite values (NaN, Infinity) are ignored. The sample standard deviation uses N-1 in the denominator of the standard deviation formula. If the array contains fewer than 2 finite values, the function returns NULL.

Parameter

• array - the array

Examples

SELECT array_stddev_samp(ARRAY[ [1.0, 2.0], [3.0, 4.0] ]);

array_stddev_samp
1.29099445

Sample stddev of bid prices across levels:

array_stddev_samp - bid price sample stddevDemo this query

SELECT symbol, array_stddev_samp(bids[1]) AS bid_price_stddev
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

array_sum

array_sum(array) returns the sum of all the array elements. NULL elements behave as if they were zero.

Parameter

• array - the array

Examples

SELECT array_sum(ARRAY[ [1.0, 1.0], [2.0, 2.0] ]);

array_sum
6.0

Total bid-side liquidity (sum of all bid volumes):

array_sum - total bid volumeDemo this query

SELECT symbol, array_sum(bids[2]) AS total_bid_volume
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

dim_length

dim_length(array, dim) returns the length of the n-dimensional array along dimension dim.

Parameters

• array - the array
• dim - the dimension (1-based) whose length to get

Examples

Get the length of the array along the 1st dimension.

SELECT dim_length(ARRAY[42, 42], 1);

dim_length
2

Inspect the structure of the order book arrays:

dim_length - order book dimensionsDemo this query

SELECT
  dim_length(bids, 1) AS num_sub_arrays,
  dim_length(bids, 2) AS levels_per_sub_array
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

Returns 2 and 40: the bids array has 2 sub-arrays (prices, volumes), each with 40 levels.

dot_product

dot_product(left_array, right_array) returns the dot-product of the two arrays, which must be of the same shape. The result is equal to array_sum(left_array * right_array).

Parameters

• left_array - the left array
• right_array - the right array

Examples

SELECT dot_product(
  ARRAY[ [3.0, 4.0], [2.0, 5.0] ],
  ARRAY[ [3.0, 4.0], [2.0, 5.0] ]
);

dot_product
54.0

Volume-weighted average bid price (VWAP):

dot_product - VWAP of bid sideDemo this query

SELECT symbol,
  dot_product(bids[1], bids[2]) / array_sum(bids[2]) AS vwap_bid
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

The dot product of prices and volumes divided by total volume gives the volume-weighted average price across all 40 bid levels.

flatten

flatten(array) flattens all the array's elements into a 1D array, in row-major order.

Parameters

• array - the array

Examples

Flatten a 2D array.

SELECT flatten(ARRAY[[1, 2], [3, 4]]);

flatten
[1.0,2.0,3.0,4.0]

Flatten the 2D bids array into a single 1D array (prices then volumes):

flatten - flatten order bookDemo this query

SELECT symbol, flatten(bids) AS bids_flat
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

The result is a 1D array of 80 elements: the 40 bid prices followed by the 40 bid volumes.

insertion_point

Finds the insertion point of the supplied value into a sorted 1D array. The array can be sorted ascending or descending, and the function auto-detects this.

warning

The array must be sorted, and must not contain NULLs, but this function doesn't enforce it. It runs a binary search for the value, and the behavior with an unsorted array is unspecified.

Parameters

• array - the 1D array
• value - the value whose insertion point to look for
• ahead_of_equal (optional, default false) - when true (false), returns the insertion point before (after) any elements equal to value

Examples

SELECT
  insertion_point(ARRAY[1.0, 2.0, 3.0], 2.5) i1,
  insertion_point(ARRAY[1.0, 2.0, 3.0], 2.0) i2,
  insertion_point(ARRAY[1.0, 2.0, 3.0], 2.0, true) i3;

i1	i2	i3
3	3	2

Find where the best bid would slot into the ask book:

insertion_point - bid in ask bookDemo this query

SELECT symbol, insertion_point(asks[1], best_bid) AS bid_in_ask_book
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

Since asks[1] is sorted ascending and the best bid is below the best ask, this returns 1 because the bid would go before all ask levels.

matmul

matmul(left_matrix, right_matrix) performs matrix multiplication. This is an operation from linear algebra.

A matrix is represented as a 2D array. We call the first matrix coordinate "row" and the second one "column".

left_matrix's number of columns (its dimension 2) must be equal to right_matrix's number of rows (its dimension 1).

The resulting matrix has the same number of rows as left_matrix and the same number of columns as right_matrix. The value at every (row, column) position in the result is equal to the sum of products of matching elements in the corresponding row of left_matrix and column of right_matrix. In a formula, with C = A x B:

$$C_{jk} = \sum_{i=1}^{n} A_{ji} B_{ik}$$

Parameters

• left_matrix: the left-hand matrix. Must be a 2D array
• right_matrix: the right-hand matrix. Must be a 2D array with as many rows as there are columns in left_matrix

Example

Multiply the matrices:

$$\begin{bmatrix} 1 & 2 \\ 3 & 4 \end{bmatrix} \times \begin{bmatrix} 2 & 3 \\ 2 & 3 \end{bmatrix} = \begin{bmatrix} 6 & 9 \\ 14 & 21 \end{bmatrix}$$

SELECT matmul(ARRAY[[1, 2], [3, 4]], ARRAY[[2, 3], [2, 3]]);

matmul
[[6.0,9.0],[14.0,21.0]]

shift

shift(array, distance, [fill_value]) shifts the elements in the array's last (deepest) dimension by distance. The distance can be positive (right shift) or negative (left shift). More formally, it moves elements from position i to i + distance, dropping elements whose resulting position is outside the array. It fills the holes created by shifting with fill_value, the default being NULL.

Parameters

• array - the array
• distance - the shift distance
• fill_value - the value to place in empty slots after shifting

Examples

SELECT shift(ARRAY[ [1.0, 2.0], [3.0, 4.0] ], 1);

shift
ARRAY[[null,1.0],[null,3.0]]

SELECT shift(ARRAY[ [1.0, 2.0], [3.0, 4.0] ], -1);

shift
ARRAY[[2.0,null],[4.0,null]]

SELECT shift(ARRAY[ [1.0, 2.0], [3.0, 4.0] ], -1, 10.0);

shift
ARRAY[[2.0,10.0],[4.0,10.0]]

Level-to-level price differences in the bid book:

shift - price differences between levelsDemo this query

SELECT symbol, bids[1] - shift(bids[1], 1, 0.0) AS bid_level_diffs
FROM market_data
WHERE symbol = 'EURUSD'
LIMIT -3;

Subtracting the shifted array from the original reveals the price drop between consecutive bid levels. The first element shows the full price (shifted in 0.0 as fill), subsequent elements show the tick-by-tick decrease.

transpose

transpose(array) transposes an array, reversing the order of its coordinates. This is most often used on a matrix, swapping its rows and columns.

Example

Transpose the matrix:

$$\begin{bmatrix} 1 & 2 \\ 3 & 4 \end{bmatrix} ^T = \begin{bmatrix} 1 & 3 \\ 2 & 4 \end{bmatrix}$$

SELECT transpose(ARRAY[[1, 2], [3, 4]]);

transpose
[[1.0,3.0],[2.0,4.0]]