Анализ отзывов с 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;

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

Крупнее..

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