Подготовка данных к отчёту на примере заказов

Представим задачу: к началу месяца нужно посчитать количество оплаченных заказов и их сумму по месяцам. В выгрузке одна запись может повторяться после повторной загрузки, а статус заказа — меняться. Если сразу сгруппировать строки и сложить суммы, отчёт может посчитать один заказ несколько раз. Ниже разберём подход на примере PostgreSQL 16; названия таблицы и полей условные, их нужно сопоставить с вашей схемой.

Сначала зафиксируйте, что именно считает отчёт

До SQL договоритесь о трёх правилах. Иначе запрос будет выглядеть аккуратно, но отвечать не на тот вопрос.

• Единица учёта: заказ, а не строка выгрузки.
• Дата группировки: дата создания заказа, а не дата последней загрузки в базу.
• Смысл суммы: сумма заказов со статусом paid. Это валовая сумма оплаченных заказов, а не выручка после возвратов и комиссий.

В примере предполагается, что в таблице raw_orders могут лежать несколько снимков одного заказа. Самый свежий снимок показывает его текущий статус. Если нужен отчёт о статусе на конкретную прошлую дату, такого снимка может быть недостаточно: понадобится история изменений статусов.

Термин — снимок записи: сохранённое состояние объекта на момент загрузки. Несколько снимков одного заказа — не обязательно технические дубликаты: поздняя строка может содержать уже изменённый статус или сумму.

Главная проверка перед суммированием: вы понимаете, какую строку оставляете для каждого заказа и почему именно её.

Подготовьте поля и правила выбора строки

Для запроса ниже понадобятся order_id, status, amount, created_at, updated_at, loaded_at и уникальный source_row_id. Предполагается, что created_at хранит момент создания заказа и не меняется между снимками, а loaded_at показывает время загрузки строки. Если ваша система хранит даты иначе, сначала уточните это в источнике данных.

Для разрешения дублей используем оконную функцию row_number(): внутри каждого order_id сортируем строки от самой свежей загрузки к старой. При совпадении времён порядок задают updated_at и уникальный идентификатор строки. Без последнего критерия база может выбирать одну из равных строк непредсказуемо.

Три способа собрать отчёт

Для повторяемого отчёта выбираем второй вариант. В примере период — январь 2025 года, границы заданы в UTC, а PostgreSQL — версии 16. В рабочем отчёте замените даты и часовой пояс на согласованные для бизнеса.

WITH ranked AS (
    SELECT
        order_id,
        status,
        amount,
        created_at,
        updated_at,
        loaded_at,
        source_row_id,
        row_number() OVER (
            PARTITION BY order_id
            ORDER BY loaded_at DESC, updated_at DESC, source_row_id DESC
        ) AS rn
    FROM raw_orders
    WHERE created_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
      AND created_at < TIMESTAMPTZ '2025-02-01 00:00:00+00'
), latest AS (
    SELECT order_id, status, amount, created_at
    FROM ranked
    WHERE rn = 1
)
SELECT
    date_trunc('month', created_at AT TIME ZONE 'UTC') AS month_utc,
    count(*) AS paid_orders,
    sum(amount) AS gross_amount
FROM latest
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

Здесь важен порядок действий. Сначала запрос ранжирует снимки каждого заказа, затем оставляет один, и только после этого проверяет статус. Если отфильтровать status = 'paid' внутри CTE до ранжирования, заказ, который раньше был оплачен, а в последнем снимке отменён, всё ещё попадёт в отчёт: его старая строка станет единственной после фильтра.

Интервал дат задан как «не раньше начала января и раньше начала февраля». Такая полуоткрытая граница не захватывает лишнюю часть следующего месяца и не требует угадывать последнюю секунду суток. Поле created_at должно быть типа timestamptz; если в схеме другой тип или часовой пояс бизнеса не UTC, границы и группировку нужно согласовать с тем, как источник записывает время.

Проверьте результат до публикации

Сам запрос не доказывает, что отчёт верен. Перед передачей цифр проверьте несколько заказов вручную и убедитесь, что итог соответствует определению метрики.

1. Сверьте число строк и заказов. Для выбранного периода посчитайте уникальные order_id и сравните с числом заказов после ранжирования. Проверьте несколько ID, у которых в источнике больше одного снимка.
2. Проверьте спорные статусы. Возьмите заказ, у которого статус менялся, и убедитесь, что осталась последняя строка, включая отменённые заказы. Проверка пройдена, если статус в результате совпадает с актуальным снимком.
3. Сверьте суммы на небольшой выборке. Для нескольких заказов сложите суммы вручную по исходным строкам, оставив только последнюю запись и статус paid. Сумма выборки должна совпасть с логикой отчёта.
4. Проверьте крайние даты. Заказ ровно в начале месяца должен попасть в период, заказ ровно в начале следующего месяца — нет. Так проще заметить ошибку в границах.
5. Зафиксируйте, что исключено. Возвраты, частичные возвраты, налоги и комиссии в запросе не учитываются. Если отчёт должен показывать чистую выручку, его определение и данные нужно расширить.

Что обычно ломается

Оставляют только уникальные строки по всем столбцам

distinct уберёт полностью одинаковые строки, но не выберет актуальный снимок, если у повторов различаются статус или время загрузки. Для отчёта по заказам уникальность должна определяться бизнес-ключом, здесь это order_id, а выбор версии — явным правилом сортировки.

Фильтруют статус до выбора последней строки

Так можно вернуть старый статус, если актуальная запись уже говорит об отмене. Сначала выберите последнюю версию заказа, затем примените условие по статусу.

Смешивают часовые пояса

Заказ, созданный около полуночи, может попасть в разные календарные дни при группировке в UTC и в часовом поясе компании. Проверьте, какой календарь использует финансовый отчёт, и применяйте его последовательно к границам периода и группировке.

Считают валовую сумму чистой выручкой

Сумма оплаченных заказов не равна сумме после возвратов, скидок, налогов и комиссий. Назовите показатель точно и не подменяйте одно определение другим в подписи к графику.

Когда выбирать другой подход

Если источник уже гарантирует по одной актуальной записи на заказ и это подтверждено контрактом данных или проверкой загрузки, ранжирование может быть лишним. Если нужен отчёт на дату в прошлом, используйте историю событий или снимков и выбирайте запись, действовавшую на нужный момент. Если файл собирают разово и строк немного, допустима ручная очистка — но сохраните исходник и запишите правила, чтобы результат можно было проверить.

Для рабочей версии запроса полезно отдельно проверять предположения: уникален ли source_row_id, монотонно ли отражает свежесть loaded_at, стабилен ли created_at и действительно ли amount хранится в одной валюте. Если любое из них неверно, сначала измените правило подготовки данных, а не маскируйте расхождение итоговой формулой.

Что делать дальше

Согласуйте с владельцем отчёта определение суммы и часовой пояс, затем прогоните запрос на известной выборке заказов с повторами и сменой статуса. После публикации следите за долей повторных снимков, количеством строк с пустыми ключами и расхождением итогов с предыдущим периодом. Для функций окон и работы с датой и временем сверяйтесь с документацией именно PostgreSQL 16: детали выражений и типов важны для границ периода.