How Can the `group` Function Be Used as an Alternative to `select ... by sym`?

Question

Show how group can be used to speed up a "last price by sym" aggregation. How does it compare to the standard select approach, and when would you use it?

Solution

/ A table to experiment with
rttrade: ([] sym:`g#1000000?`AAPL`GOOG`MSFT`IBM; price:1000000?100.0)

/ Standard approach — select last price by sym
\ts select last price by sym from rttrade

/ Using group directly — bypass the query engine for the aggregation
\ts {1!([]sym:key x;price:value x)} rttrade[`price] last each group[rttrade`sym]

/ Breaking down the group[] approach:
groups: group rttrade`sym
/ Returns a dictionary: sym -> list of row positions
/ e.g. `AAPL -> 0 5 12 99 ...; `GOOG -> 1 3 7 ...

/ Then: index into price column, take last
rttrade[`price] last each groups
/ Equivalent to: for each sym, get the last row's price

/ Reconstruct as a table
1!([] sym:key groups; price:value rttrade[`price] last each groups)

Explanation

The group function takes a list and returns a dictionary mapping each distinct value to a list of all its row positions — this is the same data structure that the g# attribute maintains internally. When g# is applied to a column, kdb+ maintains this inverted index automatically. Calling group on a column computes the same structure on demand.

Why group[] can be faster:

select last price by sym from rttrade goes through the full qSQL query engine: it evaluates the by clause, groups rows, applies the aggregation, and constructs the result table. This involves memory allocations proportional to the input table size.

The group[] approach bypasses the query engine entirely:

1. group rttrade`sym returns the inverted index — and when sym has the grouped attribute, kdb+ already holds that index, so group is almost free
2. last each on integer lists returns the last position per sym — a very fast operation
3. A direct column index (rttrade[`price]) retrieves just the needed values

KX's white paper Columnar database and query optimization measured this on its test data: select last price by sym took 24ms and 33.6MB of workspace without an attribute, and 15ms and 33.6MB with g# on sym. The group version took 0ms and about 15KB — over 2,000× less memory.

When to use the group[] approach:

• High-frequency aggregation queries where latency or workspace matters
• When computing multiple aggregations simultaneously (group once, apply multiple last each, max each, etc.)
• In custom analytics code where qSQL's overhead is measurable

When to stick with select ... by:

• Code clarity matters more than squeezing out the last millisecond
• The result order matters: select ... by returns the keys sorted, while group returns them in order of first appearance
• The aggregation is complex (e.g., wavg, multi-column aggregations) — the functional form becomes harder to read

Relationship to g#:

g# and group hold the same structure — a dictionary mapping each distinct value to its row positions. When the list has the grouped attribute, group can use the index kdb+ already maintains: on a 10-million-row symbol column, group took about 200ms without g# and close to 0ms with it in a quick test on KDB-X. Without g#, group still works but has to build the index by scanning the whole list.