Think about semi-additivity

[hadley]

Add an optional nonadditive attribute to number(quantity) that names the dimensions across which summation is invalid, and the rule for collapsing each one.

account_balance: number(quantity) 
units: USD 
nonadditive: {snapshot_date: last}

Motivation

The current three types classify a column by which operations are meaningful. That classification assumes the answer is the same regardless of context, which holds for id and ordinal and mostly holds for quantity — but a large and important class of business measures breaks it.

An account balance is a number(quantity). It compares, it averages, and it sums correctly across accounts, across branches, across currencies. It does not sum across time: the $100 in an account on Monday is the same $100 that is there on Tuesday, so a three-day total counts the same money three times. Inventory on hand, headcount, backlog, open positions, and policies in force all behave identically.

Syntax

nonadditive: {snapshot_date: last}
nonadditive: {snapshot_date: last, diagnosis.diagnosis_id: none}

# or short-hand
nonadditive: [diagnosis_id]  # nonadditive := {diagnosis_id: none}

Rule	Reduction	Typical use
last	value at the maximum dimension value	closing balance, on-hand inventory
first	value at the minimum dimension value	opening balance
avg	mean over present values	average daily balance
max	maximum	peak utilisation
min	minimum	minimum balance compliance
none	no valid reduction	bridged dimensions

A list rather than a single dimension because the two mechanisms that produce semi-additivity are independent and co-occur. Snapshot grain makes a measure non-additive across time; bridge-table membership makes it non-additive across the bridged dimension, e.g. a claim amount that is both snapshotted daily and bridged to diagnoses is non-additive along two unrelated axes.

Validation rules

A conforming validator must reject:

1. nonadditive on any type other than number(quantity) — consistent with the existing treatment of units.
2. A named dimension that does not resolve to a column in the same table or a declared, reachable dimension.
3. A column naming itself as a non-additive dimension.
4. Duplicate keys within one mapping.
5. An unrecognised collapse rule.
6. An empty mapping or list — omit the attribute instead.

Examples

yaml
account_balance:
  type: number(quantity)
  nonadditive: {snapshot_date: last}

avg_daily_balance:
  type: number(quantity)
  units: "USD"
  nonadditive: {snapshot_date: avg}

inventory_on_hand:
  type: number(quantity)
  units: "units"
  nonadditive: {snapshot_date: last}

peak_sessions:
  type: number(quantity)
  units: "count"
  nonadditive: {snapshot_date: max}

headcount:
  type: number(quantity)
  units: "people"
  nonadditive: {effective_date: last}

claim_amount:
  type: number(quantity)
  units: "USD"
  nonadditive: [diagnosis_id]

Two things to note. account_balance and avg_daily_balance may read from the same physical column with different rules — the schema is where that distinction becomes visible rather than lore.