Distinct counts

“How many different customers ordered this month?” is a distinct count. Vectro has two: countDistinct(expr), which is exact, and approxDistinct(expr), which is exact on a normal table and switches to small mergeable sketches where an exact answer would be expensive.

import { Engine } from 'vectrojs';

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

console.log(orders.agg({ customers: 'countDistinct(customer)', orders: 'count()' }));
console.log(orders.groupBy('region').agg({ customers: 'countDistinct(customer)' }).toRows());
await engine.close();

The NaN customer doesn’t count: missing numbers are left out, as in SQL. On a text column the empty string '' is counted like any other label (see missing values).

countDistinct

countDistinct(expr) works in agg, in groupBy().agg, and in lazy queries. The argument is any expression, so countDistinct(floor(price)) or countDistinct(customer * 1000 + store) are fine, and text columns count their labels.

How it counts depends on the values:

• Whole numbers in a small range (IDs, codes, days) are marked in a byte map, one byte per (group, value), on every thread.
• Values a whole step apart (IDs like 1e9 + 7919 * k) get the byte map too.
• Other values are numbered in a hash table first, or, when there are many of them, sorted by key range on every thread (as a group-by with many groups does) and counted in runs.

Measured against DuckDB on 10M rows (compare/bench-distinct.mjs):

Distinct count	Vectro	DuckDB
365 days	4 ms	4 ms
regions per day	7 ms	31 ms
1M accounts	9 ms	44 ms
1M random IDs up to 2^40, no common step	24 ms	46 ms
accounts per region (5.7M pairs)	30 ms	85 ms

In SQL

COUNT(DISTINCT x) is countDistinct, and APPROX_COUNT_DISTINCT(x) is approxDistinct. SELECT DISTINCT returns the distinct rows themselves.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  customer: [1, 2, 1, 3, 2, 5, 3, 4],
  ts: ['2024-01-03', '2024-01-15', '2024-02-01', '2024-02-10',
       '2024-02-20', '2024-03-05', '2024-03-06', '2024-03-30'].map((d) => new Date(d)),
});

console.log(sql(`
  SELECT DATE_TRUNC('month', ts) AS month,
         COUNT(DISTINCT customer) AS customers,
         APPROX_COUNT_DISTINCT(customer) AS approx
  FROM orders GROUP BY 1 ORDER BY 1`, { orders }).toRows());
await engine.close();

The README’s SQL benchmark has “distinct customers per month” over 10M orders at 30.8 ms (DuckDB 70.6 ms).

approxDistinct

On a normal table, approxDistinct gives exactly the same answer as countDistinct. It differs where data is spread out:

• On split tables and streams, each worker holds its own rows. countDistinct there merges the workers’ distinct values exactly, which means bringing every distinct value to one place. approxDistinct uses HyperLogLog sketches instead: 4 KB each, within about 1.6%, merged across workers by taking the maximum of each register.
• In live windows over a stream, each worker keeps one sketch per time slice and group. Sketches merge, so “distinct customers in the last 5 minutes” is a read of a few sketches, not a pass over the rows.
• On a dataset, approxDistinct counts exactly.

import { SplitEngine } from 'vectrojs';

const engine = new SplitEngine({ workers: 2 });
const n = 200_000;
const visits = await engine.tableAsync({
  user: Float64Array.from({ length: n }, (_, i) => (i * 7919) % 50_000),
});

console.log(await visits.aggAsync({
  exact:  'countDistinct(user)',
  approx: 'approxDistinct(user)',
}));
await engine.close();

DistinctSketch

DistinctSketch is the same sketch as an object, for when you want to count distinct values yourself: across processes, machines, or days you’ve already thrown away. Add values, merge sketches, ship them as 4,096 bytes.

Method	Does
new DistinctSketch()	an empty sketch
add(value)	adds a number or a string (NaN, null and undefined are skipped)
merge(other)	adds another sketch’s values into this one
estimate()	the estimated number of distinct values
toBytes()	the 4,096 registers, a Uint8Array you can store or send
DistinctSketch.fromBytes(bytes)	a sketch back from those bytes

import { DistinctSketch } from 'vectrojs';

const monday = new DistinctSketch();
for (let id = 0; id < 100_000; id++) monday.add(id);

const tuesday = new DistinctSketch();
for (let id = 50_000; id < 150_000; id++) tuesday.add(id);

// e.g. stored yesterday, read back today
const both = DistinctSketch.fromBytes(monday.toBytes()).merge(tuesday);
console.log(monday.estimate(), tuesday.estimate(), both.estimate()); // about 100K, 100K, 150K

Values hash by what they are, not by how a table stores them: numbers by their bits (-0 as 0), text by its characters. The same label gives the same hash in every worker’s dictionary and every process, which is what makes merged sketches correct. See the API reference.

Limits

On a dataset bigger than memory, exact distinct counts of values that aren’t IDs in a small range don’t spill to disk, so the distinct values must fit in memory.