SQL-запросы

«Отчёты» → «Запросы» — SQL-запросы к базе отчётов: свои сводки и графики по копии заданий, элементов очередей и по пользовательским отчётам. Писать и сохранять запросы могут разработчики, владелец области и аудитор; сохранённые общие запросы выполняют все, кто видит отчёты области.

Как устроено #

У каждой рабочей области в базе отчётов своя схема. В ней:

• копия данных оркестратора — таблицы jobs, queue_items, processes, robots, queues и представления process_daily, robot_daily. Сервер обновляет копию каждые 5 минут, поэтому самые свежие задания появляются в запросах с задержкой до 5 минут. Аргументы и результат заданий, тексты ошибок, данные (payload) и результат элементов очередей в копию не копируются;
• таблицы пользовательских отчётов — r_<ключ отчёта>, например r_payments для отчёта „Платежи“.

Запрос выполняется от роли рабочей области: он видит только свою схему и не видит основную базу оркестратора и схемы других областей.

Схема #

Справа в редакторе — «Схема» со всеми таблицами и колонками области; щелчок вставляет имя в запрос. Время во всех таблицах — timestamptz; дни в process_daily и robot_daily — по часовому поясу организации.

jobs — задания #

queue_items — элементы очередей #

process_daily и robot_daily — итоги по дням #

Дни считаются по jobs.created_at в часовом поясе организации.

Справочники #

r_<ключ> — пользовательские отчёты #

Колонки полей называются ключами полей (number, amount, paid_on, status), типы — как в определении отчёта: текст и список — text, целое — bigint, число с дробью — numeric, дата — date, дата и время — timestamptz, да/нет — boolean, JSON — jsonb. Скрытые поля остаются колонками. Системные колонки каждой таблицы:

Параметры #

Пишите :имя прямо в тексте: WHERE day BETWEEN :from AND :to. Под редактором появятся «Параметры»: «Подпись», «Тип» («Текст», «Целое», «Число», «Дата», «Дата и время», «Да / нет») и «По умолчанию». У :from, :to и :process подписи готовые. Значения передаются серверу отдельно от текста — подставить в запрос чужой SQL через параметр нельзя. Если PostgreSQL не может понять тип параметра, приведите его: :from::date.

Относительные даты. Значением параметра «Дата» или «Дата и время» может быть слово — оно считается в часовом поясе организации в момент выполнения:

Для «Дата» подставляется день, для «Дата и время» — полночь этого дня (now — сам момент). Пример „за вчера“: WHERE _created_at >= :from AND _created_at < :to со значениями from = yesterday, to = today. В панели те же значения выбираются из списка: „вчера“, „начало месяца“ и т. д.

Ограничения #

Ошибка PostgreSQL показывается со строкой и столбцом — «Показать в тексте» подсветит место. «План» показывает EXPLAIN без выполнения запроса.

Сохранить запрос и график #

«Сохранить» → «Название», «Описание», «Доступ»: «Вся область» (общий запрос — выполняют все, кто видит отчёты области, меняют те, кому можно писать SQL) или «Только я». Параметры с подписями и значениями по умолчанию сохраняются вместе с запросом.

График. На вкладке «График» результата выберите «Вид» — «Столбцы», «Линия», «Площадь» или «Круговая», «Ось X» и «Значения» (до 8 числовых колонок; у круговой — первая колонка, до 8 долей). График сохраняется вместе с запросом.

Сохранённый запрос открывается на своей странице: значения параметров, таблица, график и выгрузка. Его же можно рассылать по расписанию, проверять правилом и загружать в очередь. Через API — POST /api/v1/reports/queries/{id}/run и /export (см. ниже).

Из Power BI и Excel #

Результаты запросов можно забирать в BI-системы двумя способами.

Через API #

POST /api/v1/reports/sql/run (свой SQL), POST /api/v1/reports/sql/export (выгрузка CSV или Excel), POST /api/v1/reports/queries/{id}/run и /export (сохранённый запрос с параметрами). Хватает токена «Только чтение»: запросы только читают. Права — как у владельца токена: свой SQL — разработчик, владелец области, аудитор, администратор; сохранённый общий запрос — все, кто видит отчёты области. Примеры — в API. В Power BI это „Получить данные“ → „Веб“ с заголовком Authorization: Bearer tlp_…; проще всего выгрузка csv сохранённого запроса.

Прямое подключение BI #

Power BI, Excel и другие BI-системы подключаются к базе отчётов напрямую через коннектор PostgreSQL ролью своей рабочей области:

1. Администратор открывает «Настройки» → «Отчёты»: «Сервер», «Порт», «База данных» и в таблице «Роли рабочих областей» — «Роль (логин)» нужной области. «Показать пароль» показывает пароль один раз, «Скопировать пароль» — в буфер обмена; каждый показ записывается в аудит.
2. В Power BI — „Получить данные“ → „База данных PostgreSQL“: сервер (адрес:порт), база данных, затем „База данных“ → имя роли и пароль. В Excel — „Данные“ → „Получить данные“ → „Из базы данных“ → „Из базы данных PostgreSQL“.
3. Таблицы и представления — те же, что в схеме, в схеме ws_<идентификатор области>; она же схема по умолчанию для роли.

Роль только читает (default_transaction_read_only) и видит только схему своей области; запрос длиннее 60 с прерывается (statement_timeout), одновременно — не больше 4 подключений вместе с сервером. Подробности для администратора (сетевой доступ к PostgreSQL, смена пароля) — в Базе отчётов.

Примеры запросов #

Все примеры выполняются на схеме области как есть. Таблица r_payments — отчёт „Платежи“ (поля number, amount, paid_on, status), как в образце samples/orchestrator/08_report_writer.

1. Успешность процессов за период — :from, :to типа «Дата»; график «Столбцы» по process.

SELECT process,
       sum(runs) AS runs,
       sum(succeeded) AS succeeded,
       sum(failed) AS failed,
       round(100.0 * sum(succeeded) / nullif(sum(runs) - sum(cancelled), 0), 1) AS success_pct
FROM process_daily
WHERE day BETWEEN :from AND :to
GROUP BY process
ORDER BY runs DESC

2. Неуспешные платежи за вчера — :from = yesterday, :to = today типа «Дата и время».

SELECT number, amount, paid_on, _robot AS robot, _job_number AS job
FROM r_payments
WHERE status = 'Ошибка'
  AND _created_at >= :from AND _created_at < :to
ORDER BY amount DESC NULLS LAST

3. Сумма платежей по дням — :from = startOfMonth; график «Линия», ось X — day, значения — paid, failed.

SELECT (_created_at AT TIME ZONE 'Asia/Bishkek')::date AS day,
       coalesce(sum(amount) FILTER (WHERE status = 'Оплачен'), 0) AS paid,
       coalesce(sum(amount) FILTER (WHERE status = 'Ошибка'), 0) AS failed
FROM r_payments
WHERE _created_at >= :from
GROUP BY 1
ORDER BY 1

4. Платежи по роботам и время заданий — соединение отчёта с заданиями по _job_id.

SELECT j.robot,
       count(*) AS payments,
       sum(p.amount) AS total,
       round(avg(j.run_duration_ms) / 1000.0, 1) AS avg_job_s
FROM r_payments p
JOIN jobs j ON j.id = p._job_id
WHERE j.created_at >= :from AND j.created_at < :to
GROUP BY j.robot
ORDER BY payments DESC

5. Коды ошибок заданий за 7 дней и их доля — без параметров; график «Круговая».

SELECT coalesce(error_code, 'без кода') AS error_code,
       count(*) AS jobs,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS share_pct
FROM jobs
WHERE status IN ('failed', 'lost')
  AND created_at >= now() - interval '7 days'
GROUP BY 1
ORDER BY jobs DESC
LIMIT 10

6. Элементы очередей в срок и позже срока

SELECT queue,
       count(*) AS finished,
       count(*) FILTER (WHERE finished_at > deadline) AS late,
       round(100.0 * count(*) FILTER (WHERE finished_at <= deadline)
             / nullif(count(deadline), 0), 1) AS in_time_pct
FROM queue_items
WHERE status IN ('succeeded', 'failed', 'abandoned')
  AND finished_at >= :from AND finished_at < :to
GROUP BY queue
ORDER BY late DESC

7. Часы работы роботов по дням и режимам — график «Площадь».

SELECT d.day,
       r.mode,
       sum(d.jobs) AS jobs,
       round(sum(d.busy_ms) / 3600000.0, 1) AS busy_hours
FROM robot_daily d
JOIN robots r ON r.id = d.robot_id
WHERE d.day BETWEEN :from AND :to
GROUP BY d.day, r.mode
ORDER BY d.day, r.mode

8. Включённые процессы, которые не запускались 7 дней

SELECT p.name
FROM processes p
WHERE p.is_enabled AND NOT p.archived
  AND NOT EXISTS (SELECT 1 FROM jobs j
                  WHERE j.process_id = p.id AND j.created_at >= now() - interval '7 days')
ORDER BY p.name

9. Сколько запусков спасли автоповторы

SELECT process,
       count(*) FILTER (WHERE source = 'retry' AND status = 'succeeded') AS ok_after_retry,
       count(*) FILTER (WHERE source = 'retry') AS retries
FROM jobs
WHERE created_at >= :from
GROUP BY process
ORDER BY ok_after_retry DESC

10. Дубликаты номера платежа — запрос для проверки качества данных: правильный результат — ни одной строки.

SELECT number, count(*) AS records
FROM r_payments
GROUP BY number
HAVING count(*) > 1