Measures

Measures are aggregations over fields in a dataset. Callers can request values such as revenue, order count, average order amount, minimum value, or maximum value. Measure and metric result columns are returned as string | null: every non-null aggregate is normalized to a string across ClickHouse result types and execution backends, while SQL NULL remains null. Convert numeric values at the application boundary when needed.

The object key is the semantic API name. The first argument is the backing field to aggregate. If that field is a dimension, hypequery uses the dimension mapping to resolve the ClickHouse column.

measures: {
  revenue: measure.sum('amount', {
    label: 'Revenue',
    description: 'Total order amount before refunds.',
  }),
  orderCount: measure.count('id'),
  uniqueCustomers: measure.countDistinct('customerId'),
  averageAmount: measure.avg('amount'),
  minAmount: measure.min('amount'),
  maxAmount: measure.max('amount'),
}

Derived measures

Use measure.derived in the same measures object to calculate a value from base aggregations. Each uses entry maps a formula input alias to the name of a base measure in this dataset. The formula is symbolic, not a SQL string.

import {
  dataset, dimension, divide, measure, nullIfZero,
} from '@hypequery/datasets';

const Orders = dataset('orders', {
  source: 'orders',
  dimensions: { country: dimension.string() },
  measures: {
    revenue: measure.sum('amount'),
    orderCount: measure.count('id'),
    averageOrderValue: measure.derived({
      uses: { revenue: 'revenue', orders: 'orderCount' },
      formula: ({ revenue, orders }) =>
        divide(revenue, nullIfZero(orders)),
      label: 'Average Order Value',
    }),
  },
});

await analytics.execute(Orders, {
  dimensions: ['country'],
  measures: ['averageOrderValue'],
  orderBy: [{ field: 'averageOrderValue', direction: 'desc' }],
});

The dataset definition type-checks dependency names against its base-measure keys: a typo, a measure from another dataset, or a derived measure used as an input is rejected. Formula inputs are typed from the uses aliases. At runtime, definition validation also checks references, aliases, and cycles. The query aggregates revenue and orderCount first, computes averageOrderValue, then orders the result; dependencies are not returned unless explicitly selected. A zero order count produces null.

Derived measures are included in dataset contracts and survive deployment rehydration. Existing standalone metrics remain available for local and self-hosted use; for new dataset-owned calculations, prefer this API. See Metrics for the older named-metric API.

Deployable formulas

Local queries accept every formula the helpers build. A deployment contract accepts a narrower subset, and hypequery deploy refuses a dataset that steps outside it:

• A derived measure may reference only base measures. A derived measure built on another derived measure, such as round(averageOrderValue, 2), is not deployable; inline the inner formula instead.
• A literal may appear only as the round decimals or as a coalesce fallback. multiply(revenue, 2) or nullIfZero(0) are not deployable, and the coalesce fallback must be a number.

assertDeployable(dataset) runs the same check on its own, and buildProtocolDeploymentContract runs it before building the contract, so the error names the dataset and measure rather than a contract path:

import { assertDeployable } from '@hypequery/datasets';

assertDeployable(Orders);
// Error: orders.doubled: a numeric literal cannot be an arithmetic operand in a
// deployable formula; literals are only accepted as round decimals or a
// coalesce fallback

With that definition, callers use the semantic measure names:

await analytics.execute(Orders, {
  dimensions: ['country'],
  measures: ['revenue', 'orderCount'],
});

All declared measure kinds are available on the returned dataset's measures registry, including Orders.measures.averageOrderValue. The older Orders.derivedMeasures property remains as a deprecated compatibility alias. Queries that omit measures still select only base aggregations; select derived and window measures explicitly. Standalone dataset.metric() definitions can only wrap base measures.

Window measures

Declare time windows in the same measures object:

const Orders = dataset('orders', {
  source: 'orders',
  timeKey: 'createdAt',
  dimensions: { createdAt: dimension.timestamp() },
  measures: {
    revenue: measure.sum('amount'),
    trailingRevenue: measure.trailing('revenue', { amount: 7, unit: 'day' }),
    monthToDateRevenue: measure.toDate('revenue', 'month'),
    runningRevenue: measure.cumulative('revenue'),
  },
});

Orders.measures.runningRevenue; // The window definition, alongside revenue.

Each window references a base measure on its dataset and requires timeKey. cumulative supports only sum, count, min, and max. A derived formula may reference a base or window measure, but cannot reference another derived measure. Deployment contracts are stricter still: see Deployable formulas.

Execute windows with createDatasetClient({ queryBuilder }). Select a grain and exactly one bounded filter on the dataset's time field:

import { createDatasetClient } from '@hypequery/datasets';

const client = createDatasetClient({ queryBuilder: db, timezone: 'UTC' });
const result = await client.execute(Orders, {
  by: 'day',
  timezone: 'America/New_York',
  measures: ['revenue', 'trailingRevenue', 'monthToDateRevenue', 'runningRevenue'],
  filters: [
    { field: 'createdAt', operator: 'gte', value: '2026-01-01' },
    { field: 'createdAt', operator: 'lt', value: '2026-02-01' },
  ],
});

Use ISO timestamps with between, or paired gt/gte and lt/lte bounds. The range selects output buckets. Windows include lookback rows and the whole current bucket, with an exclusive end; base measures respect the original filters, including partial buckets.

Buckets are filled for each dimension combination in the scanned rows. Missing values are null; counts are '0'. Distinct counts and averages reaggregate source rows across the window. Segments, tenant scope, non-time filters, and measure filters apply throughout lookback.

Trailing intervals must span whole query buckets: weeks can become days, but months cannot. To-date periods must be strictly coarser than the query grain and span whole buckets. Each row may contribute to at most 1,000 buckets, and the series must fit the query or dataset result limit.

Buckets and timezone-free bounds use the query's timezone, then the client default (UTC). Explicit offsets represent instants. Use offsets inside repeated DST hours. Their local period labels can match, so labels are not unique identifiers. Ranges or lookback crossing a skipped calendar bucket are rejected; use a timezone: 'UTC' for those ranges.

Period comparisons

Use measure.shift to compare a measure with an earlier period. Positive intervals always look backwards. Select it directly or use it in a formula:

import { createDatasetClient } from '@hypequery/datasets';

const Orders = dataset('orders', {
  source: 'orders',
  timeKey: 'createdAt',
  dimensions: { createdAt: dimension.timestamp() },
  measures: {
    revenue: measure.sum('amount'),
    priorYearRevenue: measure.shift('revenue', { amount: 1, unit: 'year' }),
    ytdRevenue: measure.toDate('revenue', 'year'),
    priorYearYtd: measure.shift('ytdRevenue', { amount: 1, unit: 'year' }),
    revenueGrowth: measure.derived({
      uses: { current: 'revenue', prior: 'priorYearRevenue' },
      formula: ({ current, prior }) =>
        divide(subtract(current, prior), nullIfZero(prior)),
    }),
  },
});

const client = createDatasetClient({ queryBuilder: db });
const result = await client.execute(Orders, {
  by: 'month',
  timezone: 'America/New_York',
  measures: ['revenue', 'priorYearRevenue', 'revenueGrowth'],
  filters: [
    { field: 'createdAt', operator: 'gte', value: '2026-01-01' },
    { field: 'createdAt', operator: 'lt', value: '2027-01-01' },
  ],
});

A result such as { revenue: '60', priorYearRevenue: '30', revenueGrowth: '1' } means revenue grew by 100%. Missing prior values or a zero denominator produce null growth with the formula above.

Comparisons preserve the selected part of each bucket and the filter operators: January 1–14 compares with January 1–14 last year. Full buckets compare with full buckets. Buckets and local bounds use the query timezone (UTC by default).

Calendar intervals (month, quarter, year) also support daily and finer comparisons. Missing dates clamp to the target month's last day: February 29 compares with February 28 in a non-leap year, and March 30–31 both compare with February's last day. A full shifted bucket retains its width; partial buckets retain their selected local times. Weekly shifts retain seven calendar days. Other intervals must span whole query buckets. Catalogs and agent projections expose supportedGrains, including restrictions inherited by formulas.

Shifts share windows' filling, non-time filters, tenant scope, and result limits. Missing counts are '0'; other missing values are null. Calendar subtraction uses the query timezone and ClickHouse's clamping; skipped calendar buckets are rejected. Calendar shifts at minute or hour grain also reject a nonexistent local time in the earlier period, such as an hour skipped by daylight saving. Minute and hour grains require a timestamp column.

Shifts can wrap base measures, windows, formulas, or other shifts on the same dataset. priorYearYtd above recalculates YTD in the prior year. Shifted formulas recalculate their inputs before applying the formula. Dependencies cannot form cycles. Standalone comparison metrics are not supported yet.

Aggregation helpers

• measure.shift(measureName, interval, opts?)
• measure.sum(field, opts?)
• measure.count(field, opts?)
• measure.countDistinct(field, opts?)
• measure.approxCountDistinct(field, opts?)
• measure.avg(field, opts?)
• measure.min(field, opts?)
• measure.max(field, opts?)
• measure.percentile(field, level, opts?)
• measure.median(field, opts?)
• measure.argMax(field, by, opts?)
• measure.argMin(field, by, opts?)
• measure.stddev(field, opts?)
• measure.variance(field, opts?)
• measure.merge(stateFunction, field, opts?)

The helper determines the aggregation. The field should be the semantic field name when it exists in dimensions, or the physical column name when you are aggregating a raw table column.

percentile uses ClickHouse's approximate quantile(level) aggregation, where level is between 0 and 1. median is shorthand for percentile(field, 0.5). stddev and variance use sample statistics (stddevSamp and varSamp).

approxCountDistinct estimates the number of distinct values with ClickHouse uniq. It uses bounded memory, so it stays fast on high-cardinality columns such as user or session IDs. Use countDistinct when the number has to be exact. Catalogs, the semantic contract and the agent-safe catalog mark these measures, and any derived measure that uses one, with approximate: true. That lets agents and UIs present the value as an estimate.

argMax and argMin return the value of field from the row where by is greatest or smallest. The by field can use a semantic dimension name, so column mappings are respected:

measures: {
  p95Latency: measure.percentile('latencyMs', 0.95),
  medianLatency: measure.median('latencyMs'),
  latestStatus: measure.argMax('status', 'createdAt'),
  firstStatus: measure.argMin('status', 'createdAt'),
  latencyStddev: measure.stddev('latencyMs'),
  latencyVariance: measure.variance('latencyMs'),
}

The percentile, standard deviation, and variance helpers support measure-local filters. Filters are intentionally not supported on argMax or argMin because NULL handling for conditional arg aggregates differs across ClickHouse versions. Use a query-level filter when filtering an arg aggregate.

Aggregate-state tables

An AggregatingMergeTree table, usually filled by a materialized view, stores partial aggregation states rather than values. Read an AggregateFunction(<fn>, <type>) column with measure.merge, which applies the matching -Merge combinator. Read a SimpleAggregateFunction(<fn>, <type>) column with the plain helper for the same function.

// CREATE TABLE daily_active_users (
//   day Date, tenant_id UUID,
//   users AggregateFunction(uniq, Nullable(UUID)),
//   events SimpleAggregateFunction(sum, UInt64)
// ) ENGINE = AggregatingMergeTree ORDER BY (tenant_id, day)
const DailyActiveUsers = dataset('dailyActiveUsers', {
  source: 'daily_active_users',
  timeKey: 'day',
  dimensions: { day: dimension.timestamp(), tenantId: dimension.string({ column: 'tenant_id' }) },
  measures: {
    activeUsers: measure.merge('uniq', 'users'),   // uniqMerge(users)
    events: measure.sum('events'),                  // SUM(events)
    weeklyActiveUsers: measure.trailing('activeUsers', { amount: 7, unit: 'day' }),
  },
});

Merging states across rows is what makes the result correct at any grain: the weekly window above merges seven days of uniq states into one distinct count rather than adding seven daily counts.

Supported state functions are uniq, uniqExact, uniqCombined, uniqCombined64, uniqHLL12, uniqTheta, count, sum, avg, min, max, any, anyLast and groupBitmap. Parametric states such as quantile(0.9) are not supported. Merges of estimating states (uniq, uniqCombined, uniqHLL12, uniqTheta) are marked approximate, and count-like merges read 0 over an empty bucket.

A merge measure reads the state column directly, so it takes no measure filters or sql. It can't be selected through a relationship, accumulated with measure.cumulative, evaluated by a semantic backend such as the in-memory backend. Query it with createDatasetClient({ queryBuilder }).

Merge measures publish to the deployment contract with their state function (RFC 0022), so a hosted deployment such as hypequery Cloud (not live yet) can serve them. A metric over a merge measure can't be published yet: query the measure itself.

Options

Most measure helpers accept the following options object. argMax and argMin accept sql, label, and description, but not filters.

type MeasureOptions = {
  sql?: string;
  label?: string;
  description?: string;
  filters?: MetricFilter[];
};

Field mapping

Measures reference fields by name. When the field is a dimension with a column mapping, the measure follows that mapping.

dimensions: {
  customerId: dimension.string({ column: 'customer_id' }),
  amount: dimension.number(),
},
measures: {
  uniqueCustomers: measure.countDistinct('customerId'),
  revenue: measure.sum('amount'),
}

In this example, callers use uniqueCustomers, and the generated SQL counts distinct customer_id.

Labels and descriptions

Use label and description for human-readable metadata. These do not change SQL generation.

measures: {
  revenue: measure.sum('amount', {
    label: 'Revenue',
    description: 'Total order amount before refunds.',
  }),
}

For MCP, measure labels and descriptions are exposed through get_dataset_schema for ad hoc dataset queries.

SQL-backed measures

Use sql when the aggregation should operate on an expression rather than a simple field.

measures: {
  taxedRevenue: measure.sum('amount', {
    sql: 'amount * 1.2',
  }),
}

The field argument still names the logical backing field, while sql provides the expression to aggregate. Use SQL-backed measures sparingly. Schema compatibility checks can inspect simple fields, but complex SQL expressions can only produce warnings because they cannot be fully verified from the table schema.

SQL-backed measures require builder-backed execution. Generic semantic backend plans reject SQL-backed measures because cross-backend adapters cannot assume the same SQL expression syntax.

Filtered measures

Use filtered measures for common business states instead of repeating the same filters in every query.

import { dataset, dimension, eq, measure } from '@hypequery/datasets';

const Orders = dataset('orders', {
  source: 'orders',
  dimensions: {
    status: dimension.string(),
  },
  measures: {
    completedRevenue: measure.sum('amount', {
      filters: [eq('status', 'completed')],
    }),
    refundedRevenue: measure.sum('amount', {
      filters: [eq('status', 'refunded')],
    }),
  },
});