Aggregates and Hyperfunctions

Aggregates & hyperfunctions

The aggregate map in timeBucket takes resultName: { fn: "column", …options }. Aggregate columns are checked against the model's scalar fields at compile time (avg/sum/min/max and the numeric hyperfunctions require numeric columns), and each aggregate's result type is inferred.

Supported: avg, sum, min, max, count, stddev, stddevPop, variance, varPop, histogram, percentile, rate, delta, timeWeightedAverage, candlestick, stats, first, last.

Standard aggregates

aggregate: {
  avgTemp: { avg: "temperature" },
  spread:  { stddev: "temperature" },             // stddevPop / variance / varPop too — numeric like avg
  devices: { count: "deviceId", distinct: true }, // count(DISTINCT deviceId)
  dist:    { histogram: "temperature", min: 0, max: 100, buckets: 10 },
}

• avg / sum / min / max / count — the basics.
• stddev / stddevPop / variance / varPop — sample & population statistics, numeric like avg (default JS number; as: "string" for the exact decimal).
• count takes distinct: true for count(DISTINCT col).
• histogram returns a number[] of buckets + 2 counts — the first and last entries are the below-min / above-max overflow bins. Takes no as / fill.

Earliest / latest value (first / last)

first / last return the value of one column at the earliest / latest time in each bucket (first(value, time) / last(value, time)) — e.g. open/close per hour:

aggregate: {
  open:  { first: "temperature" }, // value at the earliest time in the bucket
  close: { last: "temperature" },  // value at the latest time
  tag:   { last: "label" },        // any column type — keeps the column's type
}

• Ordered by the model's time column by default; pass by: "<field>" to order by another column.
• Accept any column and keep that column's type. Take no as / fill; null in empty buckets under gapfill.

Exact output (as: "bigint" | "string")

By default every aggregate comes back as a JS number — sum/avg are computed as double precision, so a magnitude past 2^53 loses precision. Opt an individual aggregate into an exact type:

aggregate: {
  total:   { sum: "deviceId", as: "bigint" },    // → bigint  (exact integer)
  samples: { count: "deviceId", as: "bigint" },  // → bigint  (no overflow past ~2.1B rows)
  exact:   { avg: "temperature", as: "string" }, // → string  (exact decimal)
  fast:    { sum: "temperature" },               // → number  (default)
}

• as: "bigint" → Postgres ::bigint, a native JS bigint. Not available on avg (fractional).
• as: "string" → Postgres ::text, an exact decimal string (JSON-safe). Not available on count.
• as: "number" (default) → JS number.

The result-row type follows as, and disallowed combinations (avg + bigint, count + string) are compile errors.

Toolkit hyperfunctions

percentile, rate, delta, timeWeightedAverage, candlestick, and stats take no as / fill and require the timescaledb_toolkit extension — present on Tiger Cloud and the timescale/timescaledb-ha image, but not in the slim timescaledb image.

aggregate: {
  p95:     { percentile: "latency", p: 0.95 },     // approximate p95 (number)
  reqRate: { rate: "requestsTotal" },              // per-second rate of a reset-aware counter
  reqDelta:{ delta: "requestsTotal" },             // total change over the bucket
  twAvg:   { timeWeightedAverage: "gauge" },       // time-weighted average (LOCF; or method: "linear")
}

• percentile — approximate percentile (p is the fraction in [0, 1]), via approx_percentile / percentile_agg. Returns a number.
• rate (per-second) and delta (total change) wrap counter_agg for monotonic counters — both reset-aware (a counter resetting to a lower value is handled). Return a number.
• timeWeightedAverage — average(time_weight(...)), weighting each value by how long it held (ideal for irregularly-sampled gauges). method is "locf" (default) or "linear". Returns a number.

candlestick (OHLC)

candlestick returns an object { open, high, low, close, vwap } per bucket — it takes a price (candlestick) and a volume column (so it suits a model that has both). Not combinable with gapfill.

// on a Trade model with `time`, `price`, and `volume` columns:
const candles = await prisma.trade.timeBucket({
  bucket: "1 day",
  range: { start, end },
  aggregate: { ohlc: { candlestick: "price", volume: "volume" } },
});
// candles[i].ohlc -> { open, high, low, close, vwap }

stats (1-D summary)

stats returns the Toolkit stats_agg 1-D statistical summary as a single object per bucket — { average, sum, numVals, stddev, variance, skewness, kurtosis }. Note that, unlike the standalone stddev / variance ops (which are sample statistics), the stddev and variance here are population (the Toolkit default) — equivalent to stddevPop / varPop. skewness and kurtosis have no other path in the package, and stats_agg rolls up in continuous aggregates. Not combinable with gapfill.

const [row] = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  groupBy: ["deviceId"],
  aggregate: { temp: { stats: "temperature" } },
});
// row.temp -> { average, sum, numVals, stddev, variance, skewness, kurtosis }