Spaces:
Sleeping
Sleeping
File size: 4,104 Bytes
d300ab1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | #!/usr/bin/env Rscript
# Stratified sample of «Отдел по работе с клиентами» dialogs (last N days),
# grouped by already-classified topic, for building the Забота KB.
suppressPackageStartupMessages({ library(RPostgres); library(DBI); library(data.table) })
# Запускать из этой папки: cd scripts/analytics && Rscript sample_dialogs.R
source("db_connect.R")
LOOKBACK <- as.integer(Sys.getenv("LOOKBACK_DAYS", "30"))
PER_TOPIC <- as.integer(Sys.getenv("PER_TOPIC", "14"))
OUT <- Sys.getenv("OUT_DIR", "/tmp/zabota_sample")
dir.create(OUT, recursive = TRUE, showWarnings = FALSE)
con <- zabota_connect()
cat("connected\n")
sql <- sprintf("
WITH d AS (
SELECT f.dialog_id,
f.\"chatId\" AS chat_id,
f.created_at,
t.topic_name,
f.msg_count
FROM mv_chat_dialog_features f
JOIN chat_dialog_topic t ON t.dialog_id = f.dialog_id
WHERE f.created_at >= now() - INTERVAL '%d days'
AND BTRIM(f.first_manager_dept) LIKE 'Отдел по работе с клиентами%%'
AND f.msg_count > 1
),
msgs AS (
SELECT dm.dialog_id,
m.date AS message_date,
m.\"senderRole\" AS sender_role,
m.text AS message_text
FROM mv_chat_dialog_messages dm
JOIN mv_coconut_chat_message m ON m.id = dm.message_id
WHERE m.type IS DISTINCT FROM 'system'
AND NULLIF(BTRIM(m.text), '') IS NOT NULL
AND m.\"senderRole\" IN ('client','support')
),
agg AS (
SELECT dialog_id,
COUNT(*) FILTER (WHERE sender_role='support') AS n_support,
COUNT(*) FILTER (WHERE sender_role='client') AS n_client
FROM msgs GROUP BY 1
),
picked AS (
SELECT d.*, a.n_support, a.n_client,
row_number() OVER (PARTITION BY d.topic_name
ORDER BY a.n_support DESC, d.created_at DESC) AS rn
FROM d JOIN agg a ON a.dialog_id = d.dialog_id
WHERE a.n_support >= 1 AND a.n_client >= 1
)
SELECT p.dialog_id, p.chat_id, p.created_at, p.topic_name, p.n_support, p.n_client,
m.message_date, m.sender_role, m.message_text
FROM picked p
JOIN msgs m ON m.dialog_id = p.dialog_id
WHERE p.rn <= %d
ORDER BY p.topic_name, p.dialog_id, m.message_date
", LOOKBACK, PER_TOPIC)
dt <- as.data.table(dbGetQuery(con, sql))
cat("rows:", nrow(dt), " dialogs:", uniqueN(dt$dialog_id), "\n")
dbDisconnect(con)
# --- light PII masking: emails and RU mobile numbers only ---
# Deliberately narrow: dates, times and prices must survive intact, they are the
# substance of the answers we are mining.
mask <- function(x) {
x <- as.character(x)
x <- gsub("[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}", "<email>", x, perl = TRUE)
x <- gsub("(?<![\\d.,])(?:\\+7|\\b8|\\b7)[\\s\\-()]*9\\d{2}[\\s\\-()]*\\d{3}[\\s\\-()]*\\d{2}[\\s\\-()]*\\d{2}(?![\\d])",
"<phone>", x, perl = TRUE)
x <- gsub("(?<![\\d.,+])9\\d{9}(?![\\d])", "<phone>", x, perl = TRUE)
x
}
dt[, message_text := mask(message_text)]
dt[, message_text := substr(message_text, 1, 900)]
fwrite(dt, file.path(OUT, "dialogs_raw.csv"))
label <- function(r) ifelse(r == "client", "КЛИЕНТ", "МЕНЕДЖЕР")
slug <- function(s) {
s <- gsub("[^\\p{L}\\p{N}]+", "_", s, perl = TRUE)
substr(tolower(s), 1, 48)
}
setorder(dt, topic_name, dialog_id, message_date)
topics <- dt[, .(n_dialogs = uniqueN(dialog_id)), by = topic_name][order(-n_dialogs)]
print(topics)
fwrite(topics, file.path(OUT, "topics_sampled.csv"))
for (tp in topics$topic_name) {
sub <- dt[topic_name == tp]
lines <- c(sprintf("# Тема: %s", tp),
sprintf("_Диалогов в выборке: %d_", uniqueN(sub$dialog_id)), "")
for (did in unique(sub$dialog_id)) {
d <- sub[dialog_id == did]
lines <- c(lines,
sprintf("## dialog %s — %s", did, format(d$created_at[1], "%Y-%m-%d")),
sprintf("[%s] %s", label(d$sender_role), gsub("[\r\n]+", " / ", d$message_text)),
"")
}
writeLines(lines, file.path(OUT, paste0("topic_", slug(tp), ".md")))
}
cat("written to", OUT, "\n")
|