Аналитика социальных в/д в Social API

[dyakovri]

Написать запросы для получения

• Списков пользователей VK, GitHub, Telegram из этих данных (например, сделать таблицу с колонками сервис (вк/телега/гх), имя пользователя, уникальный идентификатор пользователя)
• Списков сообществ/чатов/тд из VK, GitHub, Telegram (сервис (вк/телега/гх), название сообщества, уникальный идентификатор сообщества, тип сообщества)
• Списка действий в укороченном формате (сервис (вк/телега/гх), уникальный идентификатор пользователя, уникальный идентификатор сообщества, тип действия)

[dyakovri]

--ТЕЛЕГРАМ
--список сообществ
select DISTINCT 
 message  #>> '{message, chat, title}' as chat_name,
 message  #>> '{message, chat, id}' as chat_id,
 message #>> '{message, chat, type}' as chat_type,
   message #>> '{message, "forum_topic_created", name}' as channel_name
from nimatovlk.webhook_storage ws 
where "system" = 'TELEGRAM'
 and message #> '{message}' is not null
 and message #>> '{message, chat, type}' != 'private';
--Комментарий: список каналов Viribus Unitisа меньше, чем сейчас. Вероятно, мы работаем с базой данных, когда сообщество только развивалось. 

--список юзеров
select DISTINCT 
message #>> '{message, from, id}' as user_id,
message #>> '{message, from, username}' as user_nickname,
message #>> '{message, from, first_name}' as user_name,
message #>> '{message, from, last_name}' as user_surname
from nimatovlk.webhook_storage ws 
where "system" = 'TELEGRAM'
 and message #> '{message}' is not null
and message #>> '{message, chat, type}' != 'private';
 and message #>> '{message, from, is_bot}' in ('false');

--список действий. естественным путём собирается из 2 предыдущих запросов:
select  
 message  #>> '{message, chat, title}' as chat_name,
 message  #>> '{message, chat, id}' as chat_id,
 message #>> '{message, chat, type}' as chat_type,
   message #>> '{message, "forum_topic_created", name}' as channel_name,
   message #>> '{message, from, id}' as user_id,
message #>> '{message, from, username}' as user_nickname,
message #>> '{message, from, first_name}' as user_name,
message #>> '{message, from, last_name}' as user_surname
from nimatovlk.webhook_storage ws 
where "system" = 'TELEGRAM'
 and message #> '{message}' is not null
 and message #>> '{message, chat, type}' != 'private';

-- ВК
--список групп
select distinct
 message #>> '{group_id}' as group_id,
from nimatovlk.webhook_storage ws 
where "system" = 'VK'

--список действий
select  
message #>> '{group_id}' as group_id,
message #>> '{type}' as type,
coalesce(message #>> '{object, created_by}',  message #>> '{object, admin_id}', message #>> '{object, user_id}') as author
from nimatovlk.webhook_storage ws 
where "system" = 'VK';

--список юзеров
select distinct
coalesce(message #>> '{object, created_by}',  message #>> '{object, admin_id}', message #>> '{object, user_id}') as author
from nimatovlk.webhook_storage ws 
where "system" = 'VK';

--ГИТХАБ
--список юзеров
select distinct
message #>> '{pull_request, user, login}' as username,
message #>> '{pull_request, user, id}' as user_id
from nimatovlk.webhook_storage ws
where "system" = 'GITHUB'

--список действий
select
message #>> '{action}' as action,
message #>> '{pull_request, user, login}' as username,
message #>> '{pull_request, user, id}' as user_id,
SPLIT_PART(message #>> '{pull_request, url}','/',6) as url
from nimatovlk.webhook_storage ws
where "system" = 'GITHUB' and message #> '{pull_request}' is not null

--список сообществ
select distinct
SPLIT_PART(message #>> '{pull_request, url}','/',6) as url
from nimatovlk.webhook_storage ws
where "system" = 'GITHUB'

[antimoda1]

Для Гитхаба:

/*
--список юзеров
select distinct
message #>> '{pull_request, user, login}' as username,
message #>> '{pull_request, user, id}' as user_id
from nimatovlk.webhook_storage ws
where "system" = 'GITHUB'
*/

--список действий
select
message #>> '{action}' as action,
message #>> '{pull_request, user, login}' as username,
message #>> '{pull_request, user, id}' as user_id,
SPLIT_PART(message #>> '{pull_request, url}','/',6) as url
from nimatovlk.webhook_storage ws
where "system" = 'GITHUB' and message #> '{pull_request}' is not null

--список сообществ
/*
select distinct
SPLIT_PART(message #>> '{pull_request, url}','/',6) as url
from nimatovlk.webhook_storage ws
where "system" = 'GITHUB'
*/

Пришлось использовать функцию с делением строки. т.к. Гитхаб выводит URL как https://github.com/profcomff/dwh-pipelines/pulls/53, и этот уникальный номер в конце необходимо отрезать.

[dyakovri]

--ТЕЛЕГРАМ
--список сообществ
select DISTINCT 
 message  #>> '{message, chat, title}' as chat_name,
 message  #>> '{message, chat, id}' as chat_id,
 message #>> '{message, chat, type}' as chat_type
from nimatovlk.webhook_storage ws 
where "system" = 'TELEGRAM'
 and message #> '{message}' is not null
 and message #>> '{message, chat, type}' in ('group', 'supergroup');

Можешь добавить сюда еще список каналов? Они где-то в другом поле

Вместо in (group, supergroup) лучше != private, а то вдруг телега еще типов наделает)

[dyakovri]

--ТЕЛЕГРАМ
--список юзеров
select DISTINCT 
 --message #>> '{message, from}' as fromt
message #>> '{message, from, id}' as user_id,
coalesce(message #>> '{message, from, username}', coalesce(message #>> '{message, from, last_name}', ''::text) || coalesce(message #>> '{message, from, first_name}', ''::text)) as user_login
from nimatovlk.webhook_storage ws 
where "system" = 'TELEGRAM'
 and message #> '{message}' is not null
 and message #>> '{message, chat, type}' in ('group', 'supergroup')
 and message #>> '{message, from, is_bot}' in ('false');

Тут бы разделил ФИО и никнеймы
Давай никнеймы в одну колонку, а фамилии и имена в две других

[dyakovri]

-- ВК
--список групп
select distinct
 message #>> '{group_id}' as group_id,
message #>> '{type}' as type
from nimatovlk.webhook_storage ws 
where "system" = 'VK'

Это точно не список групп (кажется, ты скрипт не тот приложил)
Тут нужны группы и чаты ВК

[antimoda1]

Внёс изменения в исходный комментарий, где все запросы.

[antimoda1]

-- ВК
--список групп
select distinct
 message #>> '{group_id}' as group_id,
message #>> '{type}' as type
from nimatovlk.webhook_storage ws 
where "system" = 'VK'

Это точно не список групп (кажется, ты скрипт не тот приложил) Тут нужны группы и чаты ВК

Я вот посмотрел, там всего 102 записи Вк и всего одна группа https://vk.com/public222099060 . Все 102 действия - это её создание, добавление участников, авы, трали-вали. Так что это конкретно нет данных в БД.
Сделал select distinct system as syst
from nimatovlk.webhook_storage ws - вдруг там, помимо "VK", есть какое-нибудь "Vk" или "Vkontakte". Нет, только VK.

[dyakovri]

-- ВК
--список групп
select distinct
 message #>> '{group_id}' as group_id,
message #>> '{type}' as type
from nimatovlk.webhook_storage ws 
where "system" = 'VK'

Это точно не список групп (кажется, ты скрипт не тот приложил) Тут нужны группы и чаты ВК

Я вот посмотрел, там всего 102 записи Вк и всего одна группа https://vk.com/public222099060 . Все 102 действия - это её создание, добавление участников, авы, трали-вали. Так что это конкретно нет данных в БД. Сделал select distinct system as syst from nimatovlk.webhook_storage ws - вдруг там, помимо "VK", есть какое-нибудь "Vk" или "Vkontakte". Нет, только VK.

Там правда одна группа, и еще один чат. Мы только создали этого бота и @Arzangulyan еще не успел начать добавлять его в чатики

[antimoda1]

ОК. Тогда жду дальнейших указаний