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

3 minute read
code
,

img

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

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

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

Данный пост является продолжением первой части про анализ ресурса 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 - актуальная, также можно видеть изменения статуса, оценки и кол-во комментариев.

img

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

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

sudo systemctl status grafana-server

img

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

img

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

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

img

Начнем с простого запроса: кол-во заявок сгруппированное по статусам.
Строка 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;

img

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

img

Далее еще 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;

img

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

Но это все - базовые вещи, как на счет тепловой карты ?
В наших данных есть столбец 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

img

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

Итого мы имеем таблицу с отзывами и полем 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;

img

Результат визуализации этого запроса в grafana

img

Крупнее..

img

Подводя итог можно с уверенностью сказать, что имея данные - можно реализовать визуализацию в любом из доступых видов.