Sort a Table and Apply the Parted Attribute

Question

Given the following table:

t:([] sym:`MSFT`AAPL`GOOG`AAPL`MSFT`GOOG;
      time:12:03:00 12:01:00 12:02:00 12:00:00 12:04:00 12:05:00;
      price:410.1 185.2 142.8 185.0 411.0 143.2)

Result:

sym  time     price
-------------------
MSFT 12:03:00 410.1
AAPL 12:01:00 185.2
GOOG 12:02:00 142.8
AAPL 12:00:00 185
MSFT 12:04:00 411
GOOG 12:05:00 143.2

Sort the table in memory by sym and time in ascending order, then apply the parted attribute to the sym column.

Solution

`sym`time xasc `t
@[`t;`sym;`p#]

Alternative syntax for applying the parted attribute:

update `p#sym from `t

Explanation

The table is first sorted using xasc:

`sym`time xasc `t

Because the table name is passed as a symbol (`t), the table is sorted in place.

After sorting, t becomes:

sym  time     price
-------------------
AAPL 12:00:00 185
AAPL 12:01:00 185.2
GOOG 12:02:00 142.8
GOOG 12:05:00 143.2
MSFT 12:03:00 410.1
MSFT 12:04:00 411

The important point is that all rows for the same sym are now contiguous.

The parted attribute (`p#) indicates that equal values are stored together in contiguous blocks.

It can then be applied to the sym column:

@[`t;`sym;`p#]

or:

update `p#sym from `t

You can verify the attribute using:

attr t[`sym]

Result:

`p

or by checking the table metadata:

meta t

Result:

c    | t f a
-----| -----
sym  | s   p
time | v
price| f

Why Sort First?

The parted attribute should only be applied when equal values are contiguous.

Before sorting, the sym column is:

`MSFT`AAPL`GOOG`AAPL`MSFT`GOOG

The same symbols appear in multiple separate groups, so the column is not parted.

After sorting, the sym column is:

`AAPL`AAPL`GOOG`GOOG`MSFT`MSFT

Each symbol appears in one contiguous block, so the parted attribute is valid.

Summary

Use:

`sym`time xasc `t

to sort the table in place by sym and time.

Then use:

@[`t;`sym;`p#]

to apply the parted attribute to the sym column.

This is useful when preparing tables for efficient grouped access, especially when data is naturally partitioned by symbol.