IT

Анализ отзывов с banki.ru [Часть 2] Визуализация в Grafana

Анализ отзывов с banki.ru

Данный материал представлен в информационно-ознакомительных целях.

Список частей:

1 часть: Сбор данных ссылка
2 часть: Визуализация ссылка
3 часть: Мониторинг загрузок ссылка
4 часть: Создание сайта и RESTAPI ссылка

Короткое вступление

Данный пост является продолжением первой части про анализ ресурса banki.ru - страницы с отзывами клиентов.

В прошлой статье мы закончили на реализации скрипта, который собирал данные по 1 банку в 2 таблицы: с текущими данными и историей изменений. Давайте посмотрим, как выглядят данные спустя время в историчной таблице.

select  	id  	, title 	, create_dt 	, score 	, status  	, comments 	, action 	, valid_from 	, coalesce( 		lead(valid_from) over(partition by id order by valid_from asc), '2999-12-31' 	) as valid_to from  	home.dth_banki_responses  where  	id = 10561062 order by  	valid_from asc;

На скриншоте видно, что на 1 отзыв 10561062 приходятся несколько строк, где строка №1 - первая, а строка №5 - актуальная, также можно видеть изменения статуса, оценки и кол-во комментариев.

Алгоритм работает - двигаемся дальше. Что мы имеем - сбор данных с последующим сохранением в таблицы, результат котрого можно видеть только в них, поэтому давайте добавим визуализацию, использовать будем инструмент Grafana.

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

sudo systemctl status grafana-server

Проверим доступность сервиса в браузере по 3000 порту - работает

`Postgresql` как источник данных

В настройках добавим нашу БД в качестве data source - теперь можно писать sql запросы и визуализировать их с помощью средств Grafana

Начнем с простого запроса: кол-во заявок сгруппированное по статусам.

Строка extract(epoch from now()) необходима для обязательной временной метки, без нее визуализация работать не будет.

select  	status 	, count(*) as "Кол-во" 	, extract(epoch from now()) as "time" from  	home.dt_banki_responses where 	date(create_dt) >= '2021-01-01'  group by status, extract(epoch from now()) order by 2 desc;

Можем изменить стиль отображения на круговую диаграмму (не меняя sql-запроса)

Далее еще 1 простой запрос, с помощью которого можно будет визуализировать кол-во заявок на временной шкале

select      count(*) as value     , extract(epoch from date(create_dt)) as "time" from home.dt_banki_responses  where date(create_dt) between date('2021-04-01') and now() group by 2 order by 2;

Тепловая карта

Но это все - базовые вещи, как на счет тепловой карты ?
В наших данных есть столбец city, но данные в нем хранятся в следующем виде:
г. Омск (Омская область) - город + область, приведем к виду Омск.

select  	city 	, substring(city, '\((.*?)\)') as area 	, split_part(city, '(',1) 	, substr(split_part(city, '(',1), 4, length(split_part(city, '(',1))-1) as raw_city 	, extract(epoch from now()) as "time" from home.dt_banki_responses

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

Итого мы имеем таблицу с отзывами и полем city -> raw_city и справочник координат home.dt_geo_full_coordinates, соединим их в 1 таблицу.

with prepare_ds as ( 	select  		city 		, substring(city, '\((.*?)\)') as area 		, substr(split_part(city, '(',1), 4, length(split_part(city, '(',1))-1) as raw_city 		, extract(epoch from now()) as "time" 	from home.dt_banki_responses ), geo as  ( 	select  		case when city = '' then region else city end as city 		, latitude 		, longitude 	from home.dt_geo_full_coordinates ) , final_ds as ( 	select  		m.raw_city 		, g.latitude 		, g.longitude 		, m.time 	from  		prepare_ds m left join geo g 		on trim(upper(m.raw_city)) = trim(upper(g.city)) ) select  	count(*) as cnt 	, raw_city 	, latitude 	, longitude 	, time  from final_ds group by  	raw_city, latitude, longitude, time order by 1 desc;