Table Indexing

Question: A table is two data structures in one. It is considered an array of dictionaries, as well as a dictionary of arrays. For the former, each element is a dictionary that holds one value per key. The keys represent the columns and the values represent the column data for that row. For the latter, each key maps to an array of values. The keys represent the columns and the values represent the column data for all rows. You can manipulate a table using both keys and numeric indices, keys will yield columns and numbers will yield rows. Knowing how to use each indexing method in tandem with each other will result in the fastest retrieval of data. Generally, if you're retrieving a subset of columns and a subset of rows, the fastest method of retrieval would be column retrieval first and then row retrieval. If you are retrieving all columns, it is faster not to lookup columns. If you are retreiving all rows, it is faster not to lookup rows. Using the table 't' created below, write the equivalent of 'update px:100f from t where px>60f' using the apply/amend operator '@'. Write two forms, one applying column indices first, and the other applying row indices first. Notice the speed difference between the two forms.

More Information:

https://code.kx.com/q/ref/apply/#apply-at-index-at

Example

                                
                                q)t:([]time:asc n?.z.N;sym:upper n?`2;px:n?100f;size:(n:100000000)?200j) / 100 mil rows
q)i:til count t
q)\t (`time`sym#t) i / column lookup first, then row lookup
350
q)\t `time`sym#t i / rows lookup first then column lookup, significantly slower
694
q)((`time`sym#t) i) ~ `time`sym#t i
1b

Solution

                                
                                ######## solution.q ########

t:([]time:asc n?.z.N;sym:upper n?`2;px:n?100f;size:(n:100000000)?200j); / 100 mil rows
data:@[t;`px;@[;where 60f<t`px;:;100f]]

Explanation: The update statement updates all 'px' values to 100 where 'px' is greater than 60. We can break this down into the following steps: Use apply and index into the table using the 'px' column and apply to this column another apply, and for this apply index into the appropriate rows and assign their values to 100. You can do this the reverse way too, and index into the rows first and apply the change on the 'px' column for each row, however this is extremely slow. This is because the row indexing will iteratively lookup the rows first, and then column lookup will iteratively lookup the column key for each row. Compare this to the first method which looks up the column first which is only one lookup, and then iteratively looks up each row value. Notice the speed difference between the update statement and the first method, they are very similar which may give some hint as to how update performs under the hood.

Code:

https://bitbucket.org/alvikabir/dailyq/src/master/Intermediate/table-indexing

Tags:

data structures dictionaries tables

Searchable Tags

algorithms api architecture asynchronous c csv data structures dictionaries disk feedhandler finance functions ingestion ipc iterators machine learning math multithreading optimizations realtime shared library sql statistics streaming strings tables temporal utility websockets

daily q

Table Indexing

Example

Solution

Tags:

Searchable Tags