latestIndexedTable

Syntax

latestIndexedTable(keyColumns, timeColumn, [X1], [X2], .....)

or

latestIndexedTable(keyColumns, timeColumn, capacity:size, colNames, colTypes)

or

latestIndexedTable(keyColumns, timeColumn, table)

Arguments

latestIndexedTable has an additional parameter timeColumn compared to the function indexedTable.

timeColumn is a string indicating the column name of the time column. The time column can be integral or temporal type.

Details

Create an indexed table, which is a special type of in-memory table with primary key. The primary key can be one column or multiple columns. Compared to the indexedTable, latestIndexedTable adds a time column to determine whether to update records.

When a new record is appended to the indexed table, if its timestamp is smaller than that of the existing row which has the same primary key, it does not overwrite the existing row. latestIndexedTable deduplicates records with the same primary key based on the time column, which affects its writing performance (relatively slow compared with indexedTable).

Note: The primary key cannot be updated.

Refer to indexedTable for the optimization of query performance on latestIndexedTable.

Examples

Example 1. Create an indexed table.

Scenario 1:


    $ sym=`A`B`C`D`E
    $ id=5 4 3 2 1
    $ val=52 64 25 48 71
    $ timeCol = 2022.12.07T00:00:00.001+0..4
    $ t=latestIndexedTable(`sym`id,`timeCol,sym,id,timeCol,val)
    $ t;
sym id timeCol val
A 5 2022.12.07T00:00:00.001 52
B 4 2022.12.07T00:00:00.002 64
C 3 2022.12.07T00:00:00.003 25
D 2 2022.12.07T00:00:00.004 48
E 1 2022.12.07T00:00:00.005 71

Scenario 2:


    $ t=latestIndexedTable(`sym`id,1:0,`sym`id`val,[SYMBOL,INT,INT])
    $ insert into t values(`A`B`C`D`E,5 4 3 2 1,2022.12.07T00:00:00.001+0..4,52 64 25 48 71);

Scenario 3:


    $ tmp=table(sym, id, timeCol, val)
    $ t=latestIndexedTable(`sym`id, `timeCol, tmp);

Example 2. Update an indexed table.

If the new row has the same primary key value as an existing row, whether to update the record is determined by the time column.


    $ insert into t values(`A`A`E,5 5 1, 2022.12.07T00:00:00.001 2022.12.07T00:00:00.007 2022.12.07T00:00:00.003, 44 66 28);
    $ t;
sym id timeCol val
A 5 2022.12.07T00:00:00.007 66
B 4 2022.12.07T00:00:00.002 64
C 3 2022.12.07T00:00:00.003 25
D 2 2022.12.07T00:00:00.004 48
E 1 2022.12.07T00:00:00.005 71

Related functions: keyedTable, indexedTable, latestKeyedTable