# Aggregations

An aggregation is picked at query time via colon syntax: `measure:agg`. It is
not baked into the measure definition.

## Built-in aggregations

| Aggregation | Example | SQL |
|-------------|---------|-----|
| `sum` | `revenue:sum` | `SUM(expr)` |
| `avg` | `revenue:avg` | `AVG(expr)` |
| `sum` / `avg` with `window` | `revenue:sum(window='90d')` | trailing range aggregate |
| `min` / `max` | `revenue:min` | `MIN(expr)` / `MAX(expr)` |
| `count` | `*:count` | `COUNT(*)` |
| `count` (non-null) | `email:count` | `COUNT(email)` |
| `count_distinct` | `customer_id:count_distinct` | `COUNT(DISTINCT customer_id)` |
| `median` | `latency:median` | `PERCENTILE_CONT(0.5) …` |
| `percentile` | `latency:percentile(p=0.95)` | `PERCENTILE_CONT(0.95) …` |
| `weighted_avg` | `price:weighted_avg(weight=quantity)` | `SUM(price*qty)/SUM(qty)` |
| `stddev_samp` | `latency:stddev_samp` | `STDDEV_SAMP(expr)` — NULL when N ≤ 1 |
| `stddev_pop` | `latency:stddev_pop` | `STDDEV_POP(expr)` — 0 at N=1, NULL at N=0 |
| `var_samp` | `latency:var_samp` | `VAR_SAMP(expr)` (or `VARIANCE` on SQLite/MySQL) |
| `var_pop` | `latency:var_pop` | `VAR_POP(expr)` (or `VARIANCE_POP` on SQLite/MySQL) |
| `corr` | `price:corr(other=quantity)` | `CORR(price, quantity)` — Pearson r |
| `covar_samp` | `price:covar_samp(other=quantity)` | `COVAR_SAMP(price, quantity)` — sample covariance |
| `covar_pop` | `price:covar_pop(other=quantity)` | `COVAR_POP(price, quantity)` — population covariance |
| `first` / `last` | `balance:last(updated_at)` | earliest / latest record's value |

## first and last — per-group snapshots

`first` and `last` return the value from the earliest or latest **record** in
each group, ordered by a time column. They need to know which time column.
Resolution:

1. Explicit argument: `balance:last(updated_at)` — highest priority.
2. Query's `main_time_dimension`.
3. Single entry in `time_dimensions`.
4. First time dim appearing in `filters`.
5. Model's `default_time_dimension`.

If none resolves, the aggregation errors at query time.

Don't confuse:

- `:first`/`:last` aggregation — per-group record's earliest/latest value.
- `first(x)`/`last(x)` transform — broadcasts the earliest/most recent bucket's
  aggregated value to every row. See `help(topic='transforms')`.

## Windowed sum and average

`sum` and `avg` accept `window='...'` for trailing time-window aggregations:

```json
{
  "source_model": "orders",
  "measures": [
    {"formula": "revenue:sum(window='30d')", "name": "revenue_30d"},
    {"formula": "revenue:avg(window='1y2m')", "name": "avg_14m"}
  ],
  "time_dimensions": [{"dimension": "created_at", "granularity": "month"}]
}
```

The window is applied to raw source rows and ends at each output bucket's end.
It can be larger than, equal to, or smaller than the query time granularity.
Duration syntax is compact: `y`, `m`, `w`, `d`, `h`, `min`, `s`, combinable as
in `1y2m3w5d6h7min8s`, `90d`, `6h`, or `15min`.

## Allowed aggregations (whitelist)

A column can restrict which aggregations make sense. Model-side:

```yaml
columns:
  - name: customer_id
    sql: customer_id
    type: number
    allowed_aggregations: [count, count_distinct]
```

`customer_id:avg` would then error with a clear message listing the valid
options. Validated at both model creation and query time.

```json
{
  "source_model": "orders",
  "measures": ["customer_id:count_distinct"]
}
```

## Custom aggregations

Defined at model level. `{value}` is the measure's SQL; named placeholders are
kwargs:

```yaml
aggregations:
  - name: trimmed_mean
    formula: "AVG(CASE WHEN {value} BETWEEN {lo} AND {hi} THEN {value} END)"
    params:
      - {name: lo, sql: "0"}
      - {name: hi, sql: "1000"}
```

Query time:

```json
{
  "source_model": "orders",
  "measures": [{"formula": "score:trimmed_mean(lo=10, hi=90)"}]
}
```

You can also override built-in defaults. If you declare `weighted_avg` with a
default `weight` of `quantity`, then `price:weighted_avg` uses it without the
arg, and `price:weighted_avg(weight=revenue)` overrides.

## See also

- `help(topic='formulas')` — where `:agg` fits in the broader formula language.
- `help(topic='transforms')` — `first()`/`last()` transforms vs `:first`/`:last` aggregations.
- `help(topic='models')` — declaring measures and their `allowed_aggregations`.
