Как посчитать detrend в SQL

Зачем detrend

Тренд в ряду создаёт mathematical headache: ACF медленно затухает, регрессии дают spurious результаты, ARIMA-модели нужна разностность. Detrend = вычесть тренд, оставив stationary колебания. После этого можно делать seasonality decomposition, ACF, anomaly detection.

Linear detrend

Простейший — fit linear regression y_t = α + β × t, residuals = detrended series.

detrended_t = y_t − (α + β × t)

Если тренд нелинейный — используют polynomial regression или LOESS.

Detrend в SQL

WITH base AS (
    SELECT
        event_date,
        value,
        ROW_NUMBER() OVER (ORDER BY event_date) AS t
    FROM daily_metrics
    WHERE metric_name = 'dau'
),
model AS (
    SELECT
        REGR_SLOPE(value, t) AS beta,
        REGR_INTERCEPT(value, t) AS alpha
    FROM base
)
SELECT
    b.event_date,
    b.value,
    m.alpha + m.beta * b.t AS trend,
    b.value - (m.alpha + m.beta * b.t) AS detrended
FROM base b
CROSS JOIN model m
ORDER BY b.event_date;

detrended — то, что используем для анализа сезонности и noise.

Differencing vs detrend

SELECT
    event_date,
    value,
    -- Differencing: y_t - y_{t-1}
    value - LAG(value, 1) OVER (ORDER BY event_date) AS first_diff,
    -- Linear detrend
    value - (intercept + slope * row_number) AS linear_detrended
FROM ...;

В большинстве cases для продуктовых метрик начинать с linear detrend.

Detrend и сезонность

Если в ряду одновременно тренд и сезонность — STL decomposition: trend + seasonal + remainder. В SQL обычно делают по шагам:

1. Linear detrend → получили detrended_t.
2. Average по weekday на детрендированном ряду → seasonal.
3. detrended − seasonal = residual.

WITH detrended AS (
    -- из предыдущего CTE
    SELECT event_date, EXTRACT(DOW FROM event_date)::INT AS dow, detrended_value FROM step1
),
seasonal AS (
    SELECT dow, AVG(detrended_value) AS seasonal_component
    FROM detrended
    GROUP BY dow
)
SELECT
    d.event_date,
    d.detrended_value,
    s.seasonal_component,
    d.detrended_value - s.seasonal_component AS residual
FROM detrended d
JOIN seasonal s USING (dow);

Частые ошибки

Ошибка 1. Detrend линейным на экспоненциальном росте. Если revenue растёт по экспоненте, linear detrend оставит U-shape residual. Log-transform до detrend.

Ошибка 2. Использовать detrended для прогноза напрямую. После detrend — модель на остатке, потом «вернуть» тренд для finale forecast.

Ошибка 3. Не сохранять trend parameters. Без α и β нельзя обратить detrend. Always store them.

Ошибка 4. Игнорировать changepoint. Если тренд сменился в середине ряда, простой linear detrend оставит большой residual.

Ошибка 5. Detrend перед stationarity test. ADF/KPSS тестирует tested series. На detrended иногда автоматически stationary, тест становится формальностью.

Связанные темы

• Как посчитать stationarity test в SQL
• Как посчитать seasonality в SQL
• Как посчитать linear regression в SQL
• Как посчитать moving-average в SQL

FAQ

Detrend vs differencing?

Detrend — для deterministic trend. Differencing — для stochastic.

Polynomial detrend нужен?

Если linear detrend оставляет видимый pattern, попробуйте quadratic.

Detrend и log-transform одновременно?

Часто да: log-transform для multiplicative тренда, потом linear detrend на logs.

Можно ли detrend сезонный ряд?

Да, но сначала seasonal decomposition. Detrend применяется к trend-компоненте.

Как обратить detrend?

Добавить trend обратно: y_t = detrended_t + α + β × t.