Напишите SQL-запрос (PostgreSQL), который для дашборда считает 7-дневное скользящее среднее выручки по дням, и объясните, как обработать первые дни ряда, где полных 7 дней ещё нет?

Короткий ответ

Скользящее среднее считается оконной функцией AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW); для первых дней окно неполное, и нужно явно решить — показывать частичное среднее или скрывать точку до набора полного окна.

Как это работает подробнее

Оконная функция вычисляет среднее в «скользящем» фрейме относительно текущей строки, упорядоченной по дате, не схлопывая строки, как это делает GROUP BY.

Пример кода

query.sqlКопировать

WITH daily AS (
    SELECT order_date, SUM(amount) AS revenue
    FROM orders
    GROUP BY order_date
)
SELECT
    order_date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS revenue_ma7,
    COUNT(*) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS window_size
FROM daily
ORDER BY order_date;

Что использовать на практике

• Рекомендация. По умолчанию PostgreSQL включит неполные фреймы в начале ряда, что может визуально выглядеть как заниженное/завышенное сглаживание.
• Рекомендация. Если нужны только полноценные 7-дневные средние, отфильтруйте первые дни.
• Рекомендация. При пропусках в датах (нет данных за день) фрейм по ROWS сдвинется некорректно относительно календарных дней — нужен RANGE по дате или предварительная генерация полного ряда дат.

Подводные камни

• Риск. Путаница между фреймом ROWS и RANGE при наличии пропусков в датах.
• Риск. Игнорирование часового пояса order_date.
• Риск. Показ значений с неполным окном как полностью сопоставимых со значениями на полном окне без пометки.