Group by

table.groupBy(keys).agg(spec) returns a new table with one row per group: the key columns, then one column per aggregation.

import { Engine } from 'vectrojs';

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

const byRegion = orders.groupBy('region').agg({
  orders: 'count()',
  revenue: 'sum(price * qty)',
  avgPrice: 'mean(price)',
});
console.log(byRegion.toRows());

await engine.close();

agg takes the same aggregations as on a whole table, and runs them all in one pass over the rows. The result is an ordinary table, so it can be filtered, sorted, joined or grouped again, and should be disposed when you are done with it in a long-running process.

Keys

A key is a column: a category (text) column, or numbers of any kind (whole numbers, big IDs, prices, negatives, dates). Pass a list for several keys; each distinct combination is a group.

import { Engine } from 'vectrojs';

const engine = new Engine();
const sales = engine.table({
  region: ['EU', 'EU', 'US', 'US', 'EU', 'US'],
  ts: ['2024-03-01T09:00:00Z', '2024-03-01T17:00:00Z', '2024-03-01T12:00:00Z',
       '2024-03-02T08:00:00Z', '2024-03-02T10:00:00Z', '2024-03-02T11:00:00Z'],
  amount: [10, 20, 30, 40, 50, 60],
}, { dates: ['ts'] });

// keys are columns: derive the day first
const byDay = sales.derive('day', 'dayStart(ts)')
  .groupBy(['region', 'day'])
  .agg({ total: 'sum(amount)', n: 'count()' });
console.log(byDay.toRows());

await engine.close();

To group by an expression (a day, a price band, a year), derive it as a column first, as above. A lazy query records the derive and the group-by and plans them together. SQL takes expressions in GROUP BY directly (GROUP BY DATE_TRUNC('day', ts)). Free-text columns from a CSV can’t be keys; load them as categories.

An aggregation can’t have the same name as a key.

Aggregations

In agg	Per group
count()	Rows
count(x)	Rows where x is present
count(cond)	Rows where the condition holds, e.g. count(price > 100)
sum(x)	Total of x, missing values skipped
mean(x)	Average of the values present
min(x), max(x)	Smallest and largest value present, NaN if none
countDistinct(x)	Distinct values of x, exact
approxDistinct(x)	Distinct values; exact on a table, a HyperLogLog estimate on split tables and streams

x is any expression: sum(price * qty), max(price - cost), sum(region == 'EU' ? amount : 0). Params go in a second argument: agg({ net: 'sum(price * (1 - vat))' }, { vat: 0.2 }).

Medians and percentiles per group are in SQL: MEDIAN(x), PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY x). See SQL. Distinct counts are covered on their own page.

Filter first

A filter before groupBy is applied inside the same pass: rows that don’t match are skipped, with no filtered copy made.

import { Engine } from 'vectrojs';

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

const g = orders
  .filter('price >= minPrice', { minPrice: 50 })
  .groupBy('region')
  .agg({ n: 'count()', customers: 'countDistinct(cust)', big: 'count(price > 100)' });
console.log(g.toRows());

// the result is a table: filter and sort it like any other
console.log(g.filter('n >= 2').toRows());

await engine.close();

To filter on an aggregate (SQL’s HAVING), filter the result, as in the last line.

The order of groups

Groups come out sorted by key, with no extra sort step:

• Numbers ascending. NaN (missing) is a group of its own and comes last; -0 groups with 0.
• Text in the column’s dictionary order: the order in which the labels first appeared when the table was made, not alphabetical.
• Several keys: by the first key, then the second, and so on.

For alphabetical order of a text key, sort the result: g.sort('region') (text sorts by label), or top(10, '-revenue') for the biggest groups. See Sort and top-k.

import { Engine } from 'vectrojs';

const engine = new Engine();
const t = engine.table({
  region: ['US', 'EU', 'APAC', 'EU'],
  score: [3, NaN, 1, 3],
  v: [1, 2, 3, 4],
});

console.log(t.groupBy('region').agg({ v: 'sum(v)' }).toRows().map((r) => r.region));        // [ 'US', 'EU', 'APAC' ]
console.log(t.groupBy('region').agg({ v: 'sum(v)' }).sort('region').toRows().map((r) => r.region)); // [ 'APAC', 'EU', 'US' ]
console.log(t.groupBy('score').agg({ n: 'count()' }).toRows());                             // 1, 3, then NaN

await engine.close();

Many groups

The same call handles 8 groups or millions; Vectro picks how to number the groups from the keys and the table’s size.

• Small keys (a category, whole numbers in a narrow range, or a combination of them) are used directly as group numbers. Each thread adds into its own totals, and the totals are merged at the end.
• Other keys with fewer groups are numbered by every thread at once, in one shared hash table.
• Many groups on a big table: every thread splits its rows into partitions by key range. Each partition is added up in a small hash table that fits in the CPU’s cache and its groups sorted, so the groups come out in key order without sorting the whole result.

On the partitioned path sums are added in row order, so the answer is the same on any number of threads.

import { Engine } from 'vectrojs';

const engine = new Engine();
const n = 2_000_000;
const orders = engine.table({
  account: Float64Array.from({ length: n }, (_, i) => 1e9 + ((i * 7919) % 400_000) * 13),
  price: Float64Array.from({ length: n }, (_, i) => (i % 97) + 0.5),
});

const t0 = performance.now();
const perAccount = orders.groupBy('account').agg({ orders: 'count()', spend: 'sum(price)' });
console.log(perAccount.length, 'groups in', Math.round(performance.now() - t0), 'ms');
console.log(perAccount.toRows(3));                       // smallest account IDs first
console.log(perAccount.top(1, '-spend').toRows());

perAccount.dispose();
await engine.close();

Measured on 10M rows on an Apple M1 Pro, against DuckDB on the same machine (answers checked):

Keys	Vectro	DuckDB
region + day (2,920 pairs)	8 ms	34 ms
price (100K amounts)	23 ms	42 ms
account (1M big IDs)	29 ms	61 ms
account + region (5.7M pairs)	42 ms	115 ms

Vectro’s groups also come back sorted by key; DuckDB’s come back unsorted. On datasets bigger than memory, keys that are labels or whole-number IDs add straight into one array per aggregation; other keys spill their partial groups to disk by key range past 8M groups.

Async

groupBy(keys).aggAsync(spec) runs on the worker threads without blocking the caller. It is the call to use in a server, and in the browser, where the blocking agg runs on the page’s thread only.

const g = await orders.groupBy('region').aggAsync({ revenue: 'sum(price * qty)' });
const rows = await g.toRowsAsync();
g.dispose();

On split tables, groupBy().aggAsync is the only form; each worker groups its own rows, the page merges the partial groups by key, and the result is a normal table on the page.