Time series анализ в PostgreSQL: временные ряды и тренды

Временные ряды — данные с временной меткой. PostgreSQL имеет мощный набор инструментов для анализа метрик, событий и трендов во времени.

generate_series: генерация временного ряда

Ключевая функция для работы с временными данными — generate_series. Позволяет заполнять пропуски в данных.

-- Серия дней за месяц
SELECT generate_series(
    '2026-03-01'::date,
    '2026-03-31'::date,
    INTERVAL '1 day'
)::date AS day;

-- Серия часов за день
SELECT generate_series(
    '2026-03-15 00:00'::timestamp,
    '2026-03-15 23:00'::timestamp,
    INTERVAL '1 hour'
) AS hour;

-- Серия месяцев за год
SELECT generate_series(
    '2026-01-01'::date,
    '2026-12-01'::date,
    INTERVAL '1 month'
)::date AS month;

Заполнение пропусков (Gap Filling)

Если в один из дней не было продаж, он пропадёт из GROUP BY. Решение:

-- Выручка по дням с заполнением нулей
WITH date_series AS (
    SELECT generate_series(
        '2026-03-01'::date,
        '2026-03-31'::date,
        INTERVAL '1 day'
    )::date AS day
),
daily_sales AS (
    SELECT
        created_at::date AS day,
        SUM(amount) AS revenue,
        COUNT(*) AS orders
    FROM orders
    WHERE status = 'completed'
      AND created_at >= '2026-03-01'
      AND created_at < '2026-04-01'
    GROUP BY 1
)
SELECT
    ds.day,
    COALESCE(s.revenue, 0) AS revenue,
    COALESCE(s.orders, 0) AS orders
FROM date_series ds
LEFT JOIN daily_sales s USING (day)
ORDER BY ds.day;

Скользящее среднее (Moving Average)

-- 7-дневное скользящее среднее выручки
SELECT
    day,
    revenue,
    ROUND(AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 0) AS ma_7d,
    ROUND(AVG(revenue) OVER (
        ORDER BY day
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ), 0) AS ma_30d
FROM daily_revenue
ORDER BY day;

-- Экспоненциальное скользящее среднее (EMA) через рекурсию
WITH RECURSIVE ema AS (
    -- Начало: первая точка
    SELECT day, revenue, revenue::NUMERIC AS ema_value, 1 AS rn
    FROM daily_revenue
    WHERE day = (SELECT MIN(day) FROM daily_revenue)

    UNION ALL

    SELECT d.day, d.revenue,
           0.1 * d.revenue + 0.9 * e.ema_value,  -- α=0.1
           e.rn + 1
    FROM daily_revenue d
    JOIN ema e ON d.day = e.day + 1
)
SELECT day, revenue, ROUND(ema_value, 0) AS ema
FROM ema
ORDER BY day;

Сравнение период-к-периоду

-- Выручка: текущий месяц vs предыдущий vs год назад
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month))
          / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100, 1) AS mom_pct,
    ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month))
          / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 1) AS yoy_pct
FROM monthly_revenue
ORDER BY month;

Сезонность: день недели и час

-- Выручка по дням недели (выявление паттернов)
SELECT
    TO_CHAR(created_at, 'ID') AS dow_number,  -- 1=Пн, 7=Вс
    TO_CHAR(created_at, 'Day') AS day_name,
    COUNT(*) AS orders,
    ROUND(AVG(amount), 0) AS avg_order,
    SUM(amount) AS total
FROM orders
WHERE status = 'completed'
  AND created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1;

-- Тепловая карта: час × день недели
SELECT
    EXTRACT(DOW FROM created_at) AS dow,  -- 0=Вс
    EXTRACT(HOUR FROM created_at) AS hour,
    COUNT(*) AS orders,
    ROUND(AVG(amount), 0) AS avg_order
FROM orders
WHERE status = 'completed'
GROUP BY 1, 2
ORDER BY 1, 2;

Обнаружение аномалий (Z-score)

-- Точки, которые сильно отличаются от среднего (Z-score > 2)
WITH stats AS (
    SELECT
        AVG(revenue) AS mean,
        STDDEV(revenue) AS std
    FROM daily_revenue
    WHERE day >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT
    dr.day,
    dr.revenue,
    ROUND((dr.revenue - stats.mean) / NULLIF(stats.std, 0), 2) AS z_score,
    CASE
        WHEN ABS((dr.revenue - stats.mean) / NULLIF(stats.std, 0)) > 3 THEN 'Аномалия'
        WHEN ABS((dr.revenue - stats.mean) / NULLIF(stats.std, 0)) > 2 THEN 'Подозрительно'
        ELSE 'Норма'
    END AS status
FROM daily_revenue dr
CROSS JOIN stats
WHERE dr.day >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY ABS((dr.revenue - stats.mean) / NULLIF(stats.std, 0)) DESC;

Нарастающий итог с процентом выполнения

-- Нарастающая выручка за месяц + прогноз
WITH daily AS (
    SELECT
        created_at::date AS day,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'completed'
      AND created_at >= DATE_TRUNC('month', CURRENT_DATE)
    GROUP BY 1
),
running AS (
    SELECT
        day,
        revenue,
        SUM(revenue) OVER (ORDER BY day) AS cumulative,
        EXTRACT(DAY FROM day) AS day_number,
        EXTRACT(DAY FROM DATE_TRUNC('month', day) + INTERVAL '1 month - 1 day') AS days_in_month
    FROM daily
)
SELECT
    day,
    revenue,
    cumulative,
    -- Линейный прогноз на конец месяца
    ROUND(cumulative / day_number * days_in_month, 0) AS projected_monthly,
    -- Цель (например, 1 000 000)
    ROUND(cumulative / 1000000 * 100, 1) AS pct_of_goal
FROM running
ORDER BY day;

Sessionization: группировка событий в сессии

-- Разбить события на сессии (новая сессия = пауза > 30 минут)
WITH events_with_lag AS (
    SELECT
        user_id,
        event_time,
        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
    FROM user_events
),
session_starts AS (
    SELECT
        user_id,
        event_time,
        -- 1 если начало новой сессии
        CASE WHEN event_time - prev_event_time > INTERVAL '30 minutes'
                  OR prev_event_time IS NULL
             THEN 1 ELSE 0 END AS is_new_session
    FROM events_with_lag
),
sessions AS (
    SELECT
        user_id,
        event_time,
        SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
    FROM session_starts
)
SELECT
    user_id,
    session_id,
    MIN(event_time) AS session_start,
    MAX(event_time) AS session_end,
    MAX(event_time) - MIN(event_time) AS duration,
    COUNT(*) AS events_count
FROM sessions
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

Временные диапазоны: tstzrange

PostgreSQL имеет встроенный тип для временных диапазонов:

-- Хранить периоды подписки
CREATE TABLE subscriptions (
    id      SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    period  TSTZRANGE NOT NULL,
    plan    TEXT
);

INSERT INTO subscriptions (user_id, period, plan) VALUES
(1, '[2026-01-01, 2026-04-01)', 'monthly'),
(1, '[2026-04-01, 2026-07-01)', 'monthly');

-- Активные подписки сейчас
SELECT * FROM subscriptions
WHERE period @> NOW();

-- Подписки в конкретный момент
SELECT * FROM subscriptions
WHERE period @> '2026-02-15'::timestamptz;

-- Пересекающиеся периоды (для проверки конфликтов)
SELECT a.id, b.id
FROM subscriptions a, subscriptions b
WHERE a.id < b.id
  AND a.user_id = b.user_id
  AND a.period && b.period;  -- оператор && = пересекаются

-- GiST индекс для быстрых диапазонных запросов
CREATE INDEX ON subscriptions USING gist(period);

Итог: инструменты для временных рядов

PostgreSQL — полноценный инструмент для анализа временных рядов. Для очень больших объёмов данных (миллиарды строк) рассмотрите TimescaleDB — расширение PostgreSQL, специально оптимизированное для time series.