Functional Select with Aggregations

Question

Given a table t with a column b, write the functional select statement equivalent of:

For example:

t:([] a:`x`y`z; b:10 20 30)

select minB:min b, avgB:avg b from t

Solution

?[t;();0b;`minB`avgB!((min;`b);(avg;`b))]

Explanation

The functional form of select is ?[table;where;by;select]. The select clause is a dictionary mapping each output column name to the calculation that produces it:

`minB`avgB!((min;`b);(avg;`b))

Each value is itself a 2-item list (function;column) — (min;b)tells?to applyminto columnb, and (avg;b) to apply avg to column b. This pattern, a function paired with the column(s) it should run on, is how the functional form represents any aggregation, not just the simple min/avg calls hardcoded in regular qSQL syntax.

As with the previous question, the benefit of the functional form is that the aggregation dictionary can be constructed programmatically — built up column by column in a loop or generated from a list of required statistics — rather than written out by hand every time.