Trend Analysis¶
OBML v2.6 ships four trend-analysis primitives. Two extend existing surfaces; two are new metric types or aggregations. All are deliberately additive: a v2.5 model continues to compile the same SQL.
| Primitive | Where it lives |
|---|---|
| Per-dimension partitioning on rolling windows | MetricType.CUMULATIVE + partitionBy: |
| Rank, lag, lead, ntile, first/last value | New MetricType.WINDOW |
| Two-column & spread statistical aggregates | New aggregation: values on Measure |
| Composition of any of the above | Existing MetricType.DERIVED — unchanged |
Each primitive is independently shippable; combining them across derived metrics is the multiplier.
1. Partitioned rolling windows¶
A cumulative metric already supports running totals (unbounded), rolling
windows (window: N), and grain-to-date resets (grainToDate: month).
v2.6 adds partitionBy: — every entry is a dimension defined in the
model and present in the query's SELECT.
metrics:
Revenue MA12 by Country:
type: cumulative
measure: Revenue
timeDimension: order_month
cumulativeType: avg
window: 12
partitionBy: [Country]
Each row reads the 12 calendar months ending at its own month, within its own country, as a self-join of the aggregated rows (abridged, Postgres):
SELECT cur."Country", cur."order_month",
AVG(prior."Revenue") AS "Revenue MA12 by Country"
FROM cumulative_base AS cur
JOIN cumulative_base AS prior
ON cur."Country" = prior."Country"
AND <months from prior."order_month" to cur."order_month"> BETWEEN 0 AND 11
GROUP BY cur."Country", cur."order_month"
The window counts months, not rows: a country with no sales in some month
still averages over the 12 months ending at each one, and the months without
data contribute nothing. A ROWS BETWEEN 11 PRECEDING frame would reach back
past the gap. (RANGE frames with an offset would say the same, but Dremio
rejects them, so every engine gets the join.)
A cumulative metric is always partitioned by the query's other selected
dimensions as well: Country, order_month, Revenue YTD accumulates per
country even without partitionBy. Dimensions over the same date column at
another grain (order_year next to order_month) are positions on the time
axis, not groups, and do not partition. partitionBy adds keys on top; a key
the query already selects is not repeated.
Rolling over a span of time: windowSpan¶
window: N combines the per-period values, which is right for a sum and
wrong for a distinct count: three months of monthly active customers summed
count a customer once per month. windowSpan evaluates the measure over its
rows in a span of time ending at each period's end instead:
metrics:
Active Customers 90d:
type: cumulative
measure: Customer Count # count_distinct
timeDimension: order_month
windowSpan: {count: 90, unit: day}
Each shown month joins the measure's rows by their date (abridged, Postgres):
SELECT k."order_month", COUNT(DISTINCT r."Customer Count") AS "Active Customers 90d"
FROM span_keys_1 AS k
LEFT JOIN span_1_rows AS r
ON r."order_date" >= k."order_month" + INTERVAL '1 month' - INTERVAL '90 day'
AND r."order_date" < k."order_month" + INTERVAL '1 month'
GROUP BY k."order_month"
Any aggregation works, avg, median and first / last included, and
the span need not be a whole number of periods. Other selected dimensions
give each group its own window, a time filter picks the months shown without
clipping the span, and cumulativeType, window, grainToDate and
partitionBy do not apply beside it.
2. Window metrics — RANK, LAG, LEAD, NTILE, FIRST/LAST VALUE¶
A new MetricType.WINDOW covers the single-row-output window functions
(ranking, offsetting, positional) and moving aggregates over a frame of
rows. The windowFunction: field discriminates.
Rank within a partition¶
metrics:
Revenue Rank by Quarter:
type: window
windowFunction: dense_rank
measure: Revenue
orderDirection: desc
partitionBy: [Quarter]
Lag — prior-period value, side by side¶
metrics:
Revenue Prior Month:
type: window
windowFunction: lag
measure: Revenue
offset: 1
timeDimension: order_month
partitionBy: [Country]
LAG and LEAD accept an optional defaultValue: field — emits the
three-argument form when set.
NTILE — bucket into quartiles / deciles¶
metrics:
Revenue Quartile:
type: window
windowFunction: ntile
measure: Revenue
buckets: 4
partitionBy: [Year]
Moving aggregates over rows: frame¶
sum, avg, min, max and count aggregate the base measure over a
frame of the result's rows: the current row and the preceding rows before
it, ordered by the time dimension.
metrics:
Revenue Moving Avg 3:
type: window
windowFunction: avg
measure: Revenue
timeDimension: order_month
frame: {preceding: 2}
AVG("Revenue") OVER (
PARTITION BY "Country" -- the query's other dimensions
ORDER BY "order_month" ASC NULLS LAST
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS "Revenue Moving Avg 3"
The frame counts rows, not calendar periods: with no orders in February,
March's three rows are December, January and March. A cumulative metric
with window: 3 counts calendar months instead (January to March), and
windowSpan the source rows in a span of time. Like a cumulative metric,
the frame is partitioned by the query's other dimensions and partitionBy,
and a dimension over the same date column at another grain orders the rows
rather than partitioning them.
Like every window metric, the frame reads the rows the query returns: a
where on the time dimension clips the frame, so the first month shown
averages over itself alone. timeDimension is required and must be
selected; offset, buckets, defaultValue and orderDirection do not
apply, and the frame is refused with ROLLUP / CUBE (WINDOW_FRAME_NOT_SUPPORTED)
and with fill.
Composition — moving-average crossover¶
The killer feature is composing window metrics through DERIVED:
metrics:
Revenue MA3:
type: cumulative
measure: Revenue
timeDimension: order_month
cumulativeType: avg
window: 3
partitionBy: [Country]
Revenue MA12:
type: cumulative
measure: Revenue
timeDimension: order_month
cumulativeType: avg
window: 12
partitionBy: [Country]
MA Crossover Signal:
type: derived
expression: "CASE WHEN {[Revenue MA3]} > {[Revenue MA12]} THEN 1 ELSE -1 END"
No new compiler logic — DERIVED already composes any metric by name.
Validation rules (window)¶
| Rule | Error code |
|---|---|
windowFunction: required |
INVALID_METRIC |
partitionBy: entry must resolve to a model dimension in SELECT |
UNKNOWN_PARTITION_DIMENSION |
lag / lead require offset >= 1 and timeDimension: |
INVALID_LAG_LEAD |
ntile requires buckets >= 2 |
INVALID_NTILE_BUCKETS |
sum / avg / min / max / count require frame and timeDimension; frame is refused elsewhere |
model load error |
A frame with ROLLUP / CUBE |
WINDOW_FRAME_NOT_SUPPORTED |
3. Statistical aggregates on Measure¶
Nine new values for Measure.aggregation: four single-column and five
two-column. No new metric type.
| Aggregation | SQL | Columns |
|---|---|---|
stddev, stddev_samp |
STDDEV_SAMP(x) |
1 |
stddev_pop |
STDDEV_POP(x) |
1 |
variance, var_samp |
VAR_SAMP(x) |
1 |
var_pop |
VAR_POP(x) |
1 |
corr |
CORR(x, y) |
2 |
covar_pop |
COVAR_POP(x, y) |
2 |
covar_samp |
COVAR_SAMP(x, y) |
2 |
regr_slope |
REGR_SLOPE(y, x) |
2 |
regr_intercept |
REGR_INTERCEPT(y, x) |
2 |
measures:
Daily Revenue StdDev:
aggregation: stddev
columns:
- { dataObject: Orders, column: revenue }
Revenue Spend Correlation:
aggregation: corr
columns:
- { dataObject: Orders, column: revenue }
- { dataObject: Marketing, column: spend }
Arity is enforced at model-load time — single-column aggregates with two
columns (or vice-versa) raise INVALID_AGGREGATION_INPUTS.
Dialect coverage¶
| Aggregation | Postgres | Snowflake | BigQuery | Databricks | DuckDB | ClickHouse | MySQL | Dremio |
|---|---|---|---|---|---|---|---|---|
| stddev / variance family | yes | yes | yes | yes | yes | yes | yes | yes |
| covar_pop / covar_samp | yes | yes | yes | yes | yes | yes | no | yes |
| corr | yes | yes | yes | yes | yes | yes | no | no |
| regr_slope / regr_intercept | yes | yes | no | yes | yes | no | no | no |
Each answers the SQL standard's statistic on every dialect, which takes some rendering:
stddevandvarianceare the sample statistics. MySQL's ownSTDDEVandVARIANCEare the population ones, so OBSL emitsSTDDEV_SAMPandVAR_SAMPthere.- ClickHouse's
varSampfamily subtracts sums of squares, which cancel on values far from zero (the variance of 10^9 + 0..9 came back as 0). OBSL emits the numerically stablevarSampStable,covarSampStable,corrStableand so on. - Where the standard leaves a statistic undefined (a sample statistic of one
value, a correlation of a constant column, a slope over a constant
x), the answer is NULL. DuckDB, BigQuery and ClickHouse answerNaNorinfthere, and Databricks fails the query withDIVIDE_BY_ZEROwhen acorrsits beside a covariance or regression, so OBSL guards each. - Dremio cannot parse
CORRorREGR_*, so they are refused at compile time. - Dremio's
STDDEV,VARIANCEand covariances, and Snowflake's covariances,CORRand regressions, subtract sums of squares or products as doubles: on Dremio the variance of 10^9 + 0..9 came back as 0 and a covariance of 18.5 there as 142.2; on Snowflake that covariance came back as 0 and the correlation and regression NULL. Over integers, and over decimal columns declared withsqlPrecisionandsqlScale, OBSL computes them there from exact moments instead: each value as a whole number of its last place, the count, sums and sums of squares and products inDECIMAL(38, 0), shifted by the integer means, rounded once to a double. A pair counts where both of its values are. Snowflake's own variance of aNUMBERis exact and is kept. - PostgreSQL's covariances,
CORRand regressions, and MySQL'sSTDDEVandVARIANCE, read their values as doubles, so an integer or a decimal past a double's 53 bits loses its last digits first: over 10^17 + 0..5 PostgreSQL's covariance came back as 0 and its correlation NULL, and MySQL's variance as 0. OBSL computes them from the same exact moments, inNUMERICon PostgreSQL and inDECIMAL(65, 0)on MySQL. PostgreSQL's own variance of an integer or a decimal isNUMERIC, exact, and is kept. - A group whose sum of squares reaches 10^37 of those units (10^63 on MySQL,
10^300 on PostgreSQL, within the double the result is rounded to) keeps the
engine's own statistic, since a larger sum would wrap silently on Dremio, and
so does a decimal of 38 places, or of more than 150 on PostgreSQL, whose
powers of ten no double holds. Snowflake raises past 128 bits in
any
SUMa query computes, so there a value of 10^13 units or more is left out of the exact sums and its group keeps Snowflake's own. - The regression intercept is the mean of the first argument less the slope times the second's, in doubles: exact to a double's precision of those two terms, as on the engines that compute it themselves.
Precision: Snowflake and Dremio over doubles, and over a decimal column without its declared places, also subtract sums of squares and have no stable function, so on values far from zero, relative to their spread, their result loses most or all of its digits.
Unsupported combinations raise UNSUPPORTED_AGGREGATION_FOR_DIALECT at
compile time. There is no silent fallback. If you need regression on
BigQuery / ClickHouse / MySQL, write a DERIVED metric with the
two-aggregate identity:
4. Worked example — multi-region trend dashboard¶
dimensions:
Country: { dataObject: Orders, column: country, resultType: string }
Quarter: { dataObject: Orders, column: order_date, resultType: date, timeGrain: quarter }
measures:
Revenue:
aggregation: sum
columns: [{ dataObject: Orders, column: revenue }]
metrics:
Revenue MA4 by Country:
type: cumulative
measure: Revenue
timeDimension: Quarter
cumulativeType: avg
window: 4
partitionBy: [Country]
Revenue Rank by Quarter:
type: window
windowFunction: dense_rank
measure: Revenue
orderDirection: desc
partitionBy: [Quarter]
Revenue Prior Quarter:
type: window
windowFunction: lag
measure: Revenue
offset: 1
timeDimension: Quarter
partitionBy: [Country]
QoQ Delta:
type: derived
expression: "{[Revenue]} - {[Revenue Prior Quarter]}"
One model, four trend primitives, every dialect — no SQL hand-written.
Out of scope (deliberate)¶
- Fiscal calendars (4-4-5, custom year start) — tracked separately; touches PoP, cumulative, and date dimension grain everywhere.
- Forecasting / extrapolation (linear regression beyond the
regr_*aggregates, ARIMA, exponential smoothing) — belongs in the BI tool or downstream analytics layer. - Semi-additive measures (the total of period-end balances across accounts, period-average) are no separate primitive: a
lastmeasure reaggregated withsumper account. Withpopulation: dimensionandcarryForward: true, an account with no snapshot in a period carries its earlier balance. See Semi-additive measures.