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 strings to match ClickHouse's JSON serialization; 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'),
}

With that definition, callers use the semantic measure names:

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

Aggregation helpers

• measure.sum(field, opts?)
• measure.count(field, opts?)
• measure.countDistinct(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?)

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).

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.

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.',
  }),
}

When you promote a measure into a base metric, the metric inherits the measure label and description unless you override them on the metric.

const revenue = Orders.metric('revenue', { measure: 'revenue' });

For MCP, measures are exposed through get_dataset_schema for ad hoc dataset queries. When you promote a measure into a named metric, the metric can inherit the measure label and description for agent-facing KPI 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')],
    }),
  },
});