scripts/analytics/topic_distribution.R

#!/usr/bin/env Rscript
suppressPackageStartupMessages({ library(RPostgres); library(DBI) })
# Запускать из этой папки: cd scripts/analytics && Rscript topic_distribution.R
source("db_connect.R")
con <- zabota_connect()

cat("=== chat_dialog_topic coverage for ОРК, last 30d ===\n")
print(dbGetQuery(con, "
SELECT COUNT(*) AS topic_rows, MIN(t.dialog_id) AS min_id, MAX(t.dialog_id) AS max_id
FROM chat_dialog_topic t
JOIN mv_chat_dialog_features f ON f.dialog_id = t.dialog_id
WHERE f.created_at >= now() - INTERVAL '30 days'
  AND BTRIM(f.first_manager_dept) LIKE 'Отдел по работе с клиентами%'"))

cat("\n=== columns of chat_dialog_topic ===\n")
print(dbGetQuery(con, "SELECT column_name, data_type FROM information_schema.columns WHERE table_name='chat_dialog_topic' ORDER BY ordinal_position"))

cat("\n=== topic distribution ОРК last 30d ===\n")
print(dbGetQuery(con, "
SELECT t.topic_name, COUNT(*) AS n
FROM chat_dialog_topic t
JOIN mv_chat_dialog_features f ON f.dialog_id = t.dialog_id
WHERE f.created_at >= now() - INTERVAL '30 days'
  AND BTRIM(f.first_manager_dept) LIKE 'Отдел по работе с клиентами%'
GROUP BY 1 ORDER BY 2 DESC LIMIT 60"), max = 400)

dbDisconnect(con)