Measures

Measures are the aggregated quantities of the model: what gets summed, counted, or averaged when a query groups by dimensions.

Overview

A measure is a field whose expression aggregates rows. The spec names dimensions, 0sql groups by them, and every measure on the query is computed over each group. Measures never appear in a GROUP BY; that is what separates them from dimensions.

Every measure resolves to a source table, that table’s grain, and the dimensions reachable from it. That is what lets 0sql decide, per query, which dimensions a measure can be grouped by, which table should serve it, and how to blend it with measures from other facts without double counting. A measure carries this by construction; nothing about it is configured per combination.

Measure Types

Five kinds of measure are modeled in YAML. They share every property below and differ in how the expression is written and how the planner evaluates it.

Three more things can change what a measure returns, none of them modeled in YAML. They are request-time constructs written into the query spec and are covered under the Query API:

• Calculations: a formula the caller writes over the query’s measures, such as a rate or a share.
• Decorated projections: year-over-year, moving average, percent of total, and other transforms on a projected measure.
• Segments: restrict a single measure to a cohort so it sits beside its unrestricted baseline.

Definition

- type: measure
  name: Total Revenue
  description: Sum of all order amounts
  data_type: decimal
  format: currency:2
  expression:
    sql: sum(amount)

Aggregation

The aggregate in expression.sql is passed straight through into the generated SQL. 0sql does not restrict you to a fixed set of functions or rewrite them: whatever aggregate the warehouse supports, in the warehouse’s own dialect, is a valid measure expression. sum, avg, count, min, and max work everywhere. Beyond those, use what the engine offers: approx_count_distinct or hll sketches on Snowflake, percentile_cont and median, stddev and variance, listagg or string_agg, bool_or, any_value, uniqExact and quantile on ClickHouse, and so on.

- type: measure
  name: Median Order Value
  data_type: decimal
  expression:
    sql: percentile_cont(0.5) within group (order by amount)

- type: measure
  name: Unique Visitors (approx)
  data_type: integer
  expression:
    sql: approx_count_distinct(visitor_id)

Two consequences of pass-through:

• The expression is dialect-specific. A measure is defined on one table in one datasource, so write it for that engine. When the same measure name is defined on a hot-tier table in a different engine, write that table’s expression in its dialect; the shared name is what makes them one measure (see One name, one concept).
• The planner still needs to recognize an aggregate. 0sql knows each adapter’s aggregate functions and uses that to tell a measure expression from a row-level one, which matters for grouping, for blending, and for calculations written on top of measures. Standard SQL aggregates and each engine’s own are recognized; a measure that references columns must contain a function call, or zsql check fails with Sql measure should have an aggregation function.

Conditional aggregation is plain SQL:

- type: measure
  name: Completed Orders
  data_type: integer
  expression:
    sql: count(case when status = 'completed' then 1 end)

Properties

Patterns

Ratio inside one table. Both sides aggregate the same rows, so a single expression is enough:

- type: measure
  name: Profit Margin
  data_type: decimal
  format: percent:1
  expression:
    sql: sum(profit) / nullif(sum(revenue), 0)

Ratio across measures. When the numerator and denominator are already measures, possibly on different tables, reference them by name so the planner blends them correctly:

- type: measure
  name: Revenue per Order
  data_type: decimal
  format: currency:2
  expression:
    sql: "[Total Revenue] / nullif([Order Count], 0)"

Share of total. A measure that ignores the query’s grouping for its denominator is an exclusion measure, not a ratio. See exclusions. For an ad-hoc share on one request, the percent-of-total decorator needs no modeling.

Balances and inventory. Summing a daily balance across a month is wrong; use a snapshot measure.

Guidelines

1. One aggregate per standard measure. Put math between measures in a compound measure, where the planner can blend it, rather than in one expression that assumes one table.
2. Guard denominators with nullif(..., 0).
3. Match data_type to the result, and let format handle display.
4. Write descriptions and synonyms. Synonyms resolve field references in specs and in fields?q= search; descriptions come back with the field so callers know what it means.
5. Same concept, same name. Define “Total Revenue” on every table that can serve it and let the planner choose; give a different concept a different name.
6. Test with zsql test: assert the SQL a projection produces, on every deploy.

Next Steps

• Compound measures
• Snapshot measures
• Exclusions and Inclusions
• Dimensions
• Expressions