Parent Child ID Mapping
Question: Parent-child entity relationships can be found in many instances of data. In financial instituions, these relationships exist in order data which models incoming and outgoing orders. For example, prime brokers create orders for clients that leverage the broker's trade execution services. The broker's order management system can decide that an order needs to be split up into multiple orders, thus creating new orders under the existing order. These are known as child orders (or slices). In this case, to obtain a view of the original order, the original order and all descending child orders must be queried. Data needs to be modeled in such a way for this type of parent-child relationship to exist. The most efficient way to model data like this is by having a parent ID column and order ID column. With this model, insertion and lookup of data is fast and columns are integer columns of uniform length and size. Implementing in reverse by having an order ID column and child ID column would be inefficient because the child ID column would be a mixed column of varying length, which is slower to process in general than a column of uniform size and length. Given the order table below, define a function 'getByID' which takes in a table (we will pass in order table) and ID (integer), and returns a table with that order and all descendants of that order. Assume the columns pid (parent ID) and id for every table.
Example
q)show order:([]time:00:00+til 9;pid:0N 1 1 3 0N 5 5 0N 3;id:1+til 9;name:`custom1`vwap`custom3`pov`custom2`twap`sweep`sweep`sweep)
time pid id name
--------------------
00:00 1 custom1
00:01 1 2 vwap
00:02 1 3 custom3
00:03 3 4 pov
00:04 5 custom2
00:05 5 6 twap
00:06 5 7 sweep
00:07 8 sweep
00:08 3 9 sweep
// all orders originating from id 1
q)getByID[order;1]
time pid id name
--------------------
00:00 1 custom1
00:01 1 2 vwap
00:02 1 3 custom3
00:03 3 4 pov
00:08 3 9 sweep
// all orders originating from id 4
q)getByID[order;4]
time pid id name
-----------------
00:03 3 4 pov
// all orders originating from ids 1 or 5
q)getByID[order;1 5]
time pid id name
--------------------
00:00 1 custom1
00:01 1 2 vwap
00:02 1 3 custom3
00:03 3 4 pov
00:04 5 custom2
00:05 5 6 twap
00:06 5 7 sweep
00:08 3 9 sweep
// all orders
q)getByID[order;0N]
time pid id name
--------------------
00:00 1 custom1
00:01 1 2 vwap
00:02 1 3 custom3
00:03 3 4 pov
00:04 5 custom2
00:05 5 6 twap
00:06 5 7 sweep
00:07 8 sweep
00:08 3 9 sweep