# Decompose a series into trend and season

Model / timeseries

A variant of [Resample a time series](https://meridian.online/operations/resample-a-time-series).

Split a series into trend, seasonal and remainder parts.

Page: <https://meridian.online/operations/resample-a-time-series/decompose-a-series-into-trend-and-season>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

split quarterly series into trend, seasonal and remainder

```sql
-- two quarterly series of twelve quarters. a: a trend of 10 + 2t, a seasonal pattern of +3, -1, -4, +2 and a small remainder.
-- b: an exact line 100 - 3t plus the seasonal pattern +1, -2, +3, -2, with no remainder
CREATE TEMP TABLE series AS
SELECT 'a' AS s, t, 10 + 2 * t + [3, -1, -4, 2][(t - 1) % 4 + 1] + [0.5, -0.5, 0.0, 0.25, -0.25, 0.0, 0.5, -0.5, 0.25, 0.0, -0.25, 0.0][t] AS y FROM range(1, 13) r(t)
UNION ALL
SELECT 'b', t, 100 - 3 * t + [1, -2, 3, -2][(t - 1) % 4 + 1] FROM range(1, 13) r(t);
-- trend: a centred moving average over one season (four quarters, so the two ends count half); it needs two quarters on each side
CREATE TEMP TABLE trended AS
SELECT s, t, y, (0.5 * lag(y, 2) OVER o + lag(y) OVER o + y + lead(y) OVER o + 0.5 * lead(y, 2) OVER o) / 4 AS trend FROM series WINDOW o AS (PARTITION BY s ORDER BY t);
-- seasonal: the mean of what is left after the trend for each quarter of the year, centred so the four sum to 0 (for each series)
CREATE TEMP TABLE phases AS SELECT s, (t - 1) % 4 AS phase, avg(y - trend) AS raw FROM trended WHERE trend IS NOT NULL GROUP BY s, phase;
CREATE TEMP TABLE centred AS SELECT s, phase, raw - avg(raw) OVER (PARTITION BY s) AS seasonal FROM phases;
CREATE TEMP TABLE result AS
SELECT trended.s AS s, t, y, trend, seasonal, y - trend - seasonal AS remainder
FROM trended JOIN centred ON centred.s = trended.s AND centred.phase = (t - 1) % 4;
SELECT s, t, y, round(trend, 4) AS trend, round(seasonal, 4) AS seasonal, round(remainder, 4) AS remainder FROM result ORDER BY s, t;
-- the reference values for a were worked outside DuckDB with the same moving average; the four seasonal values of each series sum to 0, and the first
-- and last two quarters have no trend, so no remainder. A line passes through a centred moving average unchanged, so b gets its trend back exactly.
SELECT CASE WHEN ((SELECT count(*) = 24 AND count(trend) = 16 AND count(remainder) = 16 FROM result)
  AND (SELECT list(round(trend, 6) ORDER BY t) FILTER (WHERE trend IS NOT NULL) = [15.96875, 17.9375, 20.0625, 22.03125, 24.0, 26.0625, 27.96875, 29.9375] FROM result WHERE s = 'a')
  AND (SELECT map_from_entries(list(((t - 1) % 4, round(seasonal, 6)) ORDER BY t)) = MAP {0: 2.949219, 1: -1.019531, 2: -3.769531, 3: 1.839844} FROM result WHERE s = 'a' AND t <= 4)
  AND (SELECT list(round(remainder, 6) ORDER BY t) FILTER (WHERE remainder IS NOT NULL) = [-0.199219, 0.472656, -0.261719, -0.011719, 0.269531, -0.402344, 0.332031, 0.082031] FROM result WHERE s = 'a')
  AND (SELECT max(abs(trend - (100 - 3 * t))) < 1e-9 AND max(abs(remainder)) < 1e-9 FROM result WHERE s = 'b' AND trend IS NOT NULL)
  AND (SELECT map_from_entries(list(((t - 1) % 4, round(seasonal, 9)) ORDER BY t)) = MAP {0: 1.0, 1: -2.0, 2: 3.0, 3: -2.0} FROM result WHERE s = 'b' AND t <= 4)
  AND (SELECT max(abs(seasonal_sum)) < 1e-12 FROM (SELECT s, sum(seasonal) AS seasonal_sum FROM centred GROUP BY s))
  AND (SELECT max(abs(y - trend - seasonal - remainder)) < 1e-12 FROM result WHERE trend IS NOT NULL)) THEN 'as expected' ELSE error('each series should split into 8 trend values, seasonal values that sum to 0 and remainders that add back to the series, and b should give back its line and pattern exactly') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Alteryx Designer: [`AB Trend`](https://help.alteryx.com/current/en/designer/tools/ab-testing/ab-trend-tool.html) (read 2026-09-27)
- RapidMiner Studio: [`Classic Decomposition`](https://docs.rapidminer.com/latest/studio/operators/modeling/time_series/decomposition/classic_decomposition.html) (read 2026-09-27)
- RapidMiner Studio: [`STL Decomposition`](https://docs.rapidminer.com/latest/studio/operators/modeling/time_series/decomposition/stl_decomposition.html) (read 2026-09-27)