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

Зачем rolling sum

«Rolling 7-day revenue» = sum за last 7 days. В отличие от cumulative — окно фиксированное. Хорошо для time-series dashboards с MA-like smoothing.

Базовый расчёт

7-day rolling sum:

SELECT
    DATE,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_sum
FROM daily_revenue
ORDER BY DATE;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW = 7 rows total.

30-day:

ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

Rolling vs cumulative

SELECT
    DATE,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY DATE) AS cumulative,
    SUM(daily_revenue) OVER (ORDER BY DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d,
    SUM(daily_revenue) OVER (ORDER BY DATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d
FROM daily_revenue;

По группам

SELECT
    DATE,
    channel,
    daily_revenue,
    SUM(daily_revenue) OVER (
        PARTITION BY channel
        ORDER BY DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_channel
FROM channel_revenue
ORDER BY channel, DATE;

Trailing N days revenue

For dashboards: «Last 30 days revenue» updated daily:

SELECT
    DATE_TRUNC('day', transaction_date) AS day,
    SUM(amount) AS daily_revenue,
    SUM(SUM(amount)) OVER (
        ORDER BY DATE_TRUNC('day', transaction_date)
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS trailing_30d_revenue,
    SUM(SUM(amount)) OVER (
        ORDER BY DATE_TRUNC('day', transaction_date)
        ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
    ) AS trailing_90d_revenue
FROM transactions
WHERE status = 'paid'
  AND transaction_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 30;

(SUM(SUM(amount)) inner = daily agg, outer = window.)

RANGE vs ROWS

-- ROWS: strict 7 rows (works only if every date has row)
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

-- RANGE: 7 calendar days (handles missing dates)
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW

При gaps в датах RANGE безопаснее.

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

Ошибка 1. Edge of series. First 6 days в 7-day rolling = partial sum. Mark или exclude.

Ошибка 2. ROWS BETWEEN N PRECEDING (без CURRENT ROW). Just previous N — current excluded. Probably not what you want.

Ошибка 3. Missing dates. ROWS treats consecutive rows = consecutive dates. If gaps → wrong sum.

Ошибка 4. PARTITION BY забыл. Per-channel rolling sums «leak» across channels.

Ошибка 5. Mix daily and weekly. Inner agg = daily, outer rolling = 7 days. Не confuse with 7 weeks.

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

• Как посчитать cumulative sum в SQL
• Как посчитать moving average в SQL
• Window functions advanced
• Cumulative distinct на собесе DE

FAQ

Rolling vs cumulative — short version?

Rolling = last N. Cumulative = since start.

RANGE для безопасности при missing dates?

Yes — RANGE works with timestamp/date intervals. ROWS не aware of values.

Rolling 7-day для weekly cadence?

Yes — smooths weekday effects. Most common rolling window.

Window function performance?

Indexed (partition_col, order_col) ускоряет. Без index — full scan.

Rolling по hour?

Тот же подход. Hourly granularity, ROWS BETWEEN N PRECEDING.