Filter, derive, aggregate

Narrow a table to the rows you want, add computed columns, and total them up, in one pass where possible.

These three calls do most of the work in a dashboard or report. They are built so that the common chain, filter then aggregate, reads each column once and makes no copy of the rows.

filter: a view, not a copy

table.filter(expr) returns a new table object over the same columns, with the condition attached. No rows are copied and nothing runs until you ask for a result.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US', 'EU'],
  price:  [120, 80, 45, 300, 99, 15],
  qty:    [2, 1, 4, 1, 3, 10],
});

const eu = orders.filter("region == 'EU'");
const bigEu = eu.filter('price * qty > 160');      // chained filters are joined with &&

console.log(eu.count(), bigEu.count());            // 3 2
console.log(bigEu.toRows());
console.log(orders.length, bigEu.length);          // 6 6: length counts the table's rows

await engine.close();

When you then call agg, sum or count, the filter and the aggregations compile together into one loop: sum(price) on a filtered table runs as sum(condition ? price : 0) over every row, on every core. That is why a filtered total on 10M rows takes about 3 ms (DuckDB on the same machine: about 10 ms).

On big tables the filter also uses zone maps: the min and max of every 16,384-row block let it skip blocks that can’t match, which pays off on data in time order.

agg: several results in one pass

agg takes an object of output name to aggregation and returns an object of numbers.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  price: [120, 80, NaN, 300, 99, 15],
  qty:   [2, 1, 4, 1, 3, 10],
});

console.log(orders.agg({
  rows: 'count()',
  priced: 'count(price)',            // rows where price is present
  large: 'count(qty >= 3)',          // rows where the condition holds
  revenue: 'sum(price * qty)',
  avgPrice: 'mean(price)',
  cheapest: 'min(price)',
  dearest: 'max(price)',
}));

// one aggregation: the shortcuts return the number itself
console.log(orders.sum('qty'), orders.mean('qty'), orders.count('price > 100'));

await engine.close();

The aggregations are sum, mean, min, max, count, countDistinct and approxDistinct, each around any expression. Missing values (NaN) are skipped as in SQL: mean divides by the values present, and count(x) counts them. When no row matches, sum and count give 0 and mean, min and max give NaN.

All of them run in the same pass however many there are. Up to 8 go into one compiled loop; more are run in batches of 8.

derive: a new column

derive(name, expr) computes the expression for every row, in one pass on every core, and returns a new table with the extra column. The original table is unchanged, and its columns are shared with the new one rather than copied.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  ts:    [new Date('2024-03-01'), new Date('2024-03-15'), new Date('2024-04-02')],
  price: [120, 80, 45],
  qty:   [2, 1, 4],
});

const t = orders
  .derive('total', 'price * qty')
  .derive('month', 'monthStart(ts)')
  .derive('band', 'total >= 200 ? 2 : 1');

console.log(t.toRows());
console.log(orders.columnNames);       // [ 'ts', 'price', 'qty' ]: unchanged

await engine.close();

The name must be new; derive('price', …) on a table that has price is an error.
A derived column can be used by later derives, filters and group-bys like any other.
monthStart(ts), dayStart(ts) and the other date starts give date columns. A plain copy of a text column (derive('r', 'region')) stays text. Everything else is a number column.
On a filtered table, derive computes every row and keeps the filter on the result.

A new column on 10M rows takes about 3.3 ms. If you only need the column inside one query, you don’t need to make it at all: sum(price * qty) computes the product as it goes. Lazy queries do this for you, putting derived expressions where they are used.

Params

Every call takes a second argument with values for names that aren’t columns, numbers or Dates:

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({ price: [120, 80, 45, 300], qty: [2, 1, 4, 1] });

for (const minPrice of [50, 100, 200]) {
  console.log(minPrice, orders.filter('price >= minPrice', { minPrice }).agg({ n: 'count()', revenue: 'sum(price * qty)' }));
}
console.log(orders.derive('net', 'price * (1 - vat)', { vat: 0.2 }).column('net'));

await engine.close();

The expression is compiled once; the values are passed to the compiled loop each time. See Params.

Getting the rows

A filter stays a view until something needs the rows themselves:

Call	Result
`collect()`	A new table holding only the matching rows (counted on every core, then copied straight to their places)
`toRows(limit)`	The matching rows as objects
`toColumns()`	The matching rows as arrays
`head(n)`	A table of the first `n` matching rows
`select('a', 'b')`	A view with fewer columns; no copy

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  id: [1, 2, 3, 4, 5],
  region: ['EU', 'US', 'EU', 'APAC', 'EU'],
  price: [120, 80, 45, 300, 99],
});

const eu = orders.filter("region == 'EU'").collect();
console.log(eu.length, eu.toRows());               // 3 rows, own memory
console.log(orders.filter('price > 90').select('id', 'price').toRows());
eu.dispose();

await engine.close();

Collect when you will run many queries over the same subset, or need it after the source is gone. Otherwise keep the view: aggregating a view costs no more than aggregating a collected table, and makes no copy. Filtering 10M rows into a new table takes about 5.4 ms.

Async versions

Each call that computes has an …Async twin: aggAsync, sumAsync, meanAsync, minAsync, maxAsync, countAsync, deriveAsync, collectAsync, toRowsAsync, toColumnsAsync, headAsync. filter and select compute nothing, so they have none.

const summary = await orders.filter("region == 'EU'").aggAsync({ revenue: 'sum(price * qty)' });
const withTotal = await orders.deriveAsync('total', 'price * qty');

In Node they let the event loop run while the worker threads compute. In the browser they are the only way to use more than one core, and on split tables they are the only calls there are.