Model / timeseries · All operations

Decompose a series into trend and season

A variant of Resample a time series

Split a series into trend, seasonal and remainder parts.

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Alteryx Designer (1)
  • AB Trendread 2026-09-27

    Compute trend and seasonality measures per unit, for matching controls to treatments

RapidMiner Studio (2)
  • Classic Decompositionread 2026-09-27

    Decompose a series into trend, seasonal and remainder by classical decomposition

  • STL Decompositionread 2026-09-27

    Decompose a series into trend, seasonal and remainder by STL (Loess)

Background

Decomposition of time series deconstructs a time series into several components, each representing one of the underlying categories of patterns.[1] A decomposition usually splits a series into a trend-cycle component, a seasonal component and a remainder component containing anything else.[2] In an additive decomposition the trend-cycle, seasonal and remainder components add up to the data, and in a multiplicative decomposition they multiply to it.[2]

When to use it

An additive decomposition suits a series whose seasonal fluctuations do not vary with its level, and a multiplicative one suits fluctuations proportional to the level.[2] Multiplicative decompositions are common with economic time series.[2] Decomposing the logarithm of a series additively is equivalent to a multiplicative decomposition of the original data.[2]

Removing the seasonal component from the data gives seasonally adjusted data.[2] Monthly unemployment data are usually seasonally adjusted to highlight variation due to the underlying state of the economy rather than seasonal variation.[2] Seasonally adjusted series still contain the remainder component, so they are not smooth, and the trend-cycle component is better for looking for turning points.[2] Decide before decomposing whether the question is about the trend or about the adjusted series, since the two answer different questions. (Meridian's view)

In a classical decomposition of monthly data, there is no trend-cycle estimate for the first six or the last six observations, and so no remainder for them either.[2] Classical decomposition assumes the seasonal component repeats from year to year, so it cannot capture a changing pattern such as electricity demand, which in many locations has moved its peak from winter to summer.[2] The classical method is not robust to a few unusual periods, such as monthly air passenger traffic during an industrial dispute.[2]

The method

The classical decomposition method originated in the 1920s and forms the starting point for most other methods of time series decomposition.[2] For a seasonal period m, classical decomposition estimates the trend-cycle with a moving average of order m followed by one of order 2 when m is even, or of order m alone when m is odd.[2] On quarterly data, a moving average of order 4 followed by one of order 2 gives each quarter of the year equal weight, so the seasonal variation is averaged out.[2] Classical additive decomposition then averages the detrended values for each season, adjusts those averages to add to zero, and takes the remainder as the data less the trend-cycle and seasonal estimates.[2] A classical multiplicative decomposition replaces the subtractions with divisions, and its seasonal indices are adjusted to add to m.[2]

Seasonal and Trend decomposition using Loess (STL) was developed by Cleveland et al. (1990), and loess is a method for estimating nonlinear relationships.[2] STL handles any type of seasonality, lets the seasonal component change over time at a rate the user controls, and can be made robust to outliers.[2] STL only provides additive decompositions, and it does not handle trading day or calendar variation automatically.[2] In RapidMiner, STL Decomposition smooths the cycle sub-series, the seasonal estimate and the deseasonalised series with Loess, and repeats the process several times to improve the estimates.[3] In RapidMiner, STL Decomposition is not defined for a series containing missing or infinite values, and returns all three components as missing for such a series.[3] Fill or drop the gaps in a series before an STL run, and check that the components came back. (Meridian's view)

The X-11 method originated in the US Census Bureau, was further developed by Statistics Canada, and is based on classical decomposition with extra steps that give trend-cycle estimates at the end points.[2] Most official statistics agencies use variants of the X-11 method or of other methods designed for quarterly and monthly data, which will not handle daily, weekly or hourly seasonality.[2]

Compared with the alternatives

In RapidMiner, the Classic Decomposition operator often over-smooths the trend and keeps a seasonal component of constant magnitude, while STL Decomposition lets the seasonal component change over time.[3] In RapidMiner, Classic Decomposition can perform a multiplicative decomposition, while STL Decomposition is only capable of an additive one.[3][4]

Sources

  1. [1]Decomposition of time series - Wikipediasecondary source
  2. [2]3 Time series decomposition – Forecasting: Principles and Practicesecondary source
  3. [3]STL Decomposition - Rapidminer Documentationdocumentation
  4. [4]Classic Decomposition - Rapidminer Documentationdocumentation