array vector

In DolphinDB, an array vector is a two-dimensional vector where each row is a variable-length vector. With array vectors, multiple columns with the same data type, such as columns of a stock’s best ask prices, can be stored in one single column. This can significantly reduce the complexity of some common queries and calculations. If there are a lot of duplicate data in multiple columns, it is also recommended to use an array vector to store these data as it improves data compression ratio and improves query performance.

Binary operations can be performed between an array vector and a scalar, a vector or another array vector.

Currently, array vectors can only be used in in-memory tables and DFS tables created with the TSDB storage engine. The OLAP storage engine does not support array vectors. Also, columns holding array vectors cannot be specified as the partitioning columns or sort columns (TSDB engine) of a partitioned table.

Creating an Array Vector

Use functions array or bigarray to initialize an empty array vector. When specifying the parameter dataType, append a pair of square brackets (“[]“) to the data type to indicate the creation of an array vector, for example, INT[]. The parameter initialSize must be 0.

Note that only the following data type categories are allowed: Logical, Integral (excluding INT128 and COMPRESS), Floating, DECIMAL, Temporal.

Conversion between Vectors and Array Vectors

From vectors to array vectors

(1) Use arrayVector to convert a vector to an array vector.

(2) Use fixedLengthArrayVector to combine multiple vectors into one array vector.

From array vectors to vectors:

Use flatten to convert an array vector to a vector.

$ a=array(INT[], 0, 10).append!([1 2 3, 4 5, 6 7 8, 9 10]);
$ a
[[1,2,3],[4,5],[6,7,8],[9,10]]

$ flatten(a)
[1,2,3,4,5,6,7,8,9,10]

Convert an Array Vectors into a matrix

Use function matrix to convert array vectors with each element of same length into matrices for further calculations.

$ bid = array(DOUBLE[], 0, 20).append!([1.4799 1.479 1.4787 1.4784 1.4667, 1.4796 1.479 1.4782 1.4781 1.4783, 1.4791 1.479 1.4785 1.4698 1.4720, 1.4699 1.469 1.4707 1.4704 1.4697, 1.4789 1.477 1.4780 1.4724 1.4669])
$ ask = array(DOUBLE[], 0, 20).append!([1.4821 1.4825 1.4828 1.4900 1.4792, 1.4818 1.482 1.4821 1.4818 1.4829, 1.4814 1.4818 1.482 1.4825 1.4823, 1.4891 1.4885 1.4898 1.4901 1.4799, 1.4811 1.4815 1.4818 1.4800 1.4799])

$ TradeDate = 2022.01.01 + 1..5
$ SecurityID = rand(`APPL`AMZN`IBM, 5)
$ t = table(SecurityID as `sid, TradeDate as `date, bid as `bid, ask as `ask)
$ result = select toArray(matrix(bid).corrMatrix()) as corr_bid,  toArray(matrix(ask).corrMatrix()) as corr_ask from t group by sid
$ corrMatrix_bid = result.corr_bid.matrix().avg().reshape(5:5)
$ corrMatrix_ask = result.corr_ask.matrix().avg().reshape(5:5)
$ corrMatrix_bid;

col1	col2	col3	col4	col5
1	0.9995	0.9984	0.7417	(0.1308)
0.9995	1	0.9997	0.7214	(0.1466)
0.9984	0.9997	1	0.7061	(0.1589)
0.7417	0.7214	0.7061	1	(0.0264)
(0.1308)	(0.1466)	(0.1589)	(0.0264)	1

$ corrMatrix_ask;

col1	col2	col3	col4	col5
1	0.9999	0.9997	0.9963	(0.9861)
0.9999	1	0.9999	0.9974	(0.9883)
0.9997	0.9999	1	0.9981	(0.9899)
0.9963	0.9974	0.9981	1	(0.9968)
(0.9861)	(0.9883)	(0.9899)	(0.9968)	1

Appending Data to an Array Vector

Use append! to append scalar, vector or array vector to an array vector. Updates and deletes are currently not supported for array vectors.

// append a scalar
$ a=array(DOUBLE[], 0, 10).append!(2.5);
$ print a;
[[2.5]]]

// append a tuple
$ a=array(DATE[], 0, 10).append!((2021.10.15, 2021.10.16, 2021.10.17));
$ print a;
[[2021.10.15],[2021.10.16],[2021.10.17]]

// append an array vector
$ a=array(INT[], 0, 10).append!([1 2 3, 4 5, 6 7 8, 9 10]);
$ print a;
[[1,2,3],[4,5],[6,7,8],[9,10]]

Retrieving Data from Array Vectors

Array Vector

$ av =array(DOUBLE[], 0, 10).append!([1.0, 2.1 4.1 6.8, 0.5 2.2 2]);
$ print av
[[1],[2.1,4.1,6.8],[0.5,2.2,2]]
$ typestr(av)
FAST DOUBLE[] VECTOR

Tuple

$ tp = [[1],[2.1,4.1,6.8],[0.5,2.2,2]]
$ typestr(tp)
ANY VECTOR

Despite some similarities, the way data is retrieved from a tuple and from an array vector is different.

Similarities:

You can use the row function to retrieve rows from both an array vector and a tuple.

Differences:

Array vectors only have column index. Therefore, to identify a column in the array vector, use av[index] where the index starts from 0. To identify a row in the array vector, we must use the row function via av.row(index).

To retrieve an individual element from a row in the array vector, first identify the column then the row via av[indexe1][index2]; or first identify the row then the column via av.row(index1)[index2].

$ av[1]
[,4.1,2.2]

$ tp[,1]
(,4.1,2.2)

$ av.row(1)
[2.1,4.1,6.8]

$ tp[1]
[2.1,4.1,6.8]

Importing Columns as Array Vectors (2.00.4 and later versions)

Currently, DolphinDB doesn’t support importing multiple columns from a text file into a single column. To import array vectors, combine the associated columns into one and separate the values in each row with a user-specified delimiter in the text file. For example, combine the 5 best ask prices (populated in 5 columns) into one column by “1.4799|1.479|1.4787|1.4796|1.479”.

When using loadText (ploadText) or loadTextEx to import data, specify the parameter arrayDelimiter (which is “|” in the above example) to separate the values in the column holding the array vector, so the values in the column can be recognized as array vector during the import.

Note: Before the import, use the schema parameter to update the data type of the columns holding the array vectors with the corresponding array vector data type.

$ bid = array(DOUBLE[], 0, 20).append!([1.4799 1.479 1.4787, 1.4796 1.479 1.4784, 1.4791 1.479 1.4784])
$ ask = array(DOUBLE[], 0, 20).append!([1.4821 1.4825 1.4828, 1.4818 1.482 1.4821, 1.4814 1.4818 1.482])
$ TradeDate = 2022.01.01 + 1..3
$ SecurityID = rand(`APPL`AMZN`IBM, 3)
$ t = table(SecurityID as `sid, TradeDate as `date, bid as `bid, ask as `ask)
$ t;
$ saveText(t,filename="D:/t.csv",delimiter=',',append=true)
$ path = "D:/t.csv"
$ schema=extractTextSchema(path);
$ update schema set type = "DOUBLE[]" where name="bid" or name ="ask"
$ t = loadText(path, schema=schema, arrayDelimiter=",")
$ t;

sid	date	bid	ask
AMZN	2022.01.02	[1.4799,1.479,1.4787]	[1.4821,1.4825,1.4828]
AMZN	2022.01.03	[1.4796,1.479,1.4784]	[1.4818,1.482,1.4821]
IBM	2022.01.04	[1.4791,1.479,1.4784]	[1.4814,1.4818,1.482]

Combining Columns into an Array Vector

Use fixedLengthArrayVector to store multiple columns from a table as a single column.

$ syms="A"+string(1..30)
$ datetimes=2019.01.01T00:00:00..2019.01.31T23:59:59
$ n=200
$ if(existsDatabase("dfs://stock")) {
$ dropDatabase("dfs://stock")
$ }
$ db=database("dfs://stock",RANGE,cutPoints(syms,3));
$ t=table(take(datetimes,n) as trade_time, take(syms,n) as sym,take(500+rand(10.0,n), n) as bid1, take(500+rand(20.0,n),n) as bid2)
$ quotes=db.createPartitionedTable(t,`quotes,`sym).append!(t)
$ t1=select sym, fixedLengthArrayVector(bid1,bid2) as bid from quotes

Concatenating Grouped Values into an Array Vector

The toArray function is used in conjunction with the group by clause in a SQL query. toArray concatenates the values in each group to create a row of the array vector, so the values of each group can be presented in one row.

$ ticker = `AAPL`IBM`IBM`AAPL`AMZN`AAPL`AMZN`IBM`AMZN
$ volume = 106 115 121 90 130 150 145 123 155;
$ t = table(ticker, volume);
$ t;

$ t1 = select toArray(volume) as volume_all from t group by ticker;
$ t1;

ticker	volume_all
AAPL	[106,90,150]
AMZN	[130,145,155]
IBM	[115,121,123]

Applying Row-Based Functions on Array Vectors

(1)Aggregate calculation

Support row-based functions, such as rowMax, rowStd , etc.

Support higher-order function byRow.

$ a=array(DOUBLE[], 0, 10).append!([8.3 1.2 5.6, 1.8 3.3, 0.1 2.4 6.8]);
$ rowMax(a);
[8.3, 3.3, 6.7]


$ bid = array(DOUBLE[], 0, 20).append!([1.4799 1.479 1.4787, 1.4796 1.479 1.4784, 1.4791 1.479 1.4784])
$ ask = array(DOUBLE[], 0, 20).append!([1.4821 1.4825 1.4828, 1.4818 1.482 1.4821, 1.4814 1.4818 1.482])
$ TradeDate = 2022.01.01 + 1..3
$ SecurityID = rand(`APPL`AMZN`IBM, 3)
$ t = table(SecurityID as `sid, TradeDate as `date, bid as `bid, ask as `ask)
$ t;

sid	date	bid	ask
IBM	2022.01.02	[1.4799,1.479,1.4787]	[1.4821,1.4825,1.4828]
APPL	2022.01.03	[1.4796,1.479,1.4784]	[1.4818,1.482,1.4821]
APPL	2022.01.04	[1.4791,1.479,1.4784]	[1.4814,1.4818,1.482]

$ select SecurityID, TradeDate, bid, ask,  rowAvg(bid) as bid_avg, rowAvg(ask) as ask_avg from t

SecurityID	TradeDate	bid	ask	bid_avg	ask_avg
IBM	2022.01.02	[1.4799,1.479,1.4787]	[1.4821,1.4825,1.4828]	1.4792	1.4825
APPL	2022.01.03	[1.4796,1.479,1.4784]	[1.4818,1.482,1.4821]	1.479	1.482
APPL	2022.01.04	[1.4791,1.479,1.4784]	[1.4814,1.4818,1.482]	1.4788	1.4817

Window functions (moving window, cumulative window, etc.)

Use the higher-order function byRow to perform window functions on an array vector.

$ select SecurityID, TradeDate, bid, ask, byRow(cumavg, bid) as bid_cum, byRow(cumavg, ask) as ask_cum from t

SecurityID	TradeDate	bid	ask	bid_cum	ask_cum
IBM	2022.01.02	[1.4799,1.479,1.4787]	[1.4821,1.4825,1.4828]	[1.4799,1.47945,1.4792]	[1.4821,1.4823,1.482467]
APPL	2022.01.03	[1.4796,1.479,1.4784]	[1.4818,1.482,1.4821]	[1.4796,1.4793,1.479]	[1.4818,1.4819,1.481967]
APPL	2022.01.04	[1.4791,1.479,1.4784]	[1.4814,1.4818,1.482]	[1.4791,1.47905,1.478833]	[1.4814,1.4816,1.481733]