2021-09-27 17:33:01
Часовые столбики
Появилась задача нарисовать график, который будет показывать по-часовую агрегацию количества событий и сравнивать результат с теми же часами до других дней. В моём примере это -1, -7, -28 т.е. вчера, неделю назад и месяц назад.
Пример событий, для одного из графиков - успешные создания документов. т.е. нужно построить график столбиков (точек), просуммировав события за час и то же самое за вчера и позавчера, а потом наложить результаты друг на друга:
часы - события
0 - 10
1 - 24
2 - 13
...
Данные в нашем случае хранятся в postgresql, имеют колонку с необходимым статусом и колонку с таймштампом, когда событие случилось. Оказалось, что не прибегая ни к какому прометеусу, sql-exporter'у можно всё это нарисовать.
Для этого подключаем в качестве datasource вашу постгрю, указав специально созданного пользователя, которому нужно выдать select на необходимую таблицу.
Создаём как обычно дашборд, новый график и выбираем наш датасорс. При написании запроса я переключился в "text edit mode" потому что так сильно удобнее - писать запрос по сути как в саму постгрю. и далее можно создать нужные запросы.
первый запрос вот такой:
SELECT
$__timeGroupAlias(created_at,1h,0),
count(*) AS "0 day"
FROM smtdp_applicant
WHERE
created_at BETWEEN $__timeFrom()::timestamptz and $__timeTo()::timestamptz
and xxxx (наши условия фильтрования событий)
GROUP BY 1
ORDER BY 1
где
$__timeGroupAlias - специальная переменная графаны, куда мы передаём имя колонки с таймштампами (created_at) и параметр как мы хотим объединять (1h) - чтобы указать графане как мы хотим сгруппировать получившиеся после запроса value.
count(*) AS "0 day" - высчитываем количество событий и именуем этот результат на графике как "0 day"
$__timeFrom() и $__timeTo() - специальные переменные графаны в которые подставится таймштамп от масштаба графика. т.е. last 24h разложится на from и to, куда подставится таймштамп времени сколько сейчас и было сутки назад и постгрю полетит запрос xxxx::timestamptz.
А вот 2 и последующие запросы будут чуть интереснее:
SELECT
$__timeGroupAlias(created_at + interval '24 hours',1h,0),
count(*) AS "-1 day"
FROM smtdp_applicant
WHERE
created_at BETWEEN $__timeFrom()::timestamptz - INTERVAL '1 DAY' and $__timeTo()::timestamptz - INTERVAL '1 DAY'
and xxx
GROUP BY 1
ORDER BY 1
где
created_at BETWEEN $__timeFrom()::timestamptz - INTERVAL '1 DAY' and $__timeTo()::timestamptz - INTERVAL '1 DAY' - первая мякотка - мы сделаем запрос от нашего окна наблюдения на сутки влево. timestamptz нужно для того чтобы из этого таймштампа вычесть на уровне постгри сутки, 7 и 28. преобразование типов и всё такое.
$__timeGroupAlias(created_at + interval '24 hours',1h,0), - вторая мякотка. позволяет шифтануть полученный результат на указанный интервал. в нашем случае - на сутки вправо.
Таким образом мы получаем 2 запроса которые получают события за окно наблюдения "сутки" и "прошедшие сутки" и накладывают результат один на другой. повторяем то же самое для -7 и -28 и получаем нужный результат. Все картинки приложу отдельно.
#grafana #postgresql
1.6K viewsbykva, edited 14:33
А
