Классификация SQL
У меня есть система, которая отслеживает, какие документы просматривают пользователи. У каждого документа есть свой идентификатор и кластер, к которому он принадлежит. Моя система отслеживает идентификатор сеанса и количество просмотров. Теперь я хотел бы построить SQL-запрос, который дал бы мне два столбца - идентификатор сеанса и классифицированный кластер. Алгоритм классификации прост:
1. select all sessions
2. for each session S
I. prepare an accumulator ACC for clusters
II. select the clusters of viewed documents for this session
III. for each cluster C accumulate the cluster count ( ACC[C]++ )
IV. find the maximum in the ACC. That is the cluster that the session was classified to
Табличные структуры следующие, я использую MySQL 5.5.16:
Session
+-------+-----------+--------------------+
| ID | sessionID | classified_cluster |
+-------+-----------+--------------------+
SessionDocument
+-------+-----------+------------+
| ID | sessionID | documentID |
+-------+-----------+------------+
Cluster
+-------+-------+
| ID | label |
+-------+-------+
ClusterDocument
+-------+-----------+------------+
| ID | clusterID | documentID |
+-------+-----------+------------+
В общем, я хочу чтобы выбрать кластеры для каждого сеанса, подсчитайте количество экземпляров каждого кластера для просмотренных документов и найдите максимальное количество экземпляров. Тогда идентификатор кластера, который произошел чаще всего, является результатом для сеанса, поэтому окончательный набор результатов содержит идентификатор сеанса и наиболее часто встречающийся кластер:
Результат
+-----------+-----------------------+
| sessionID | classifiedIntoCluster |
+-----------+-----------------------+
Мне удалось получить кластеры просмотренных документов для каждого сеанс (шаг 2 / II.) с таким запросом:
SELECT SD.session_id, CD.cluster_id
FROM cluster_document AS CD
INNER JOIN session_document AS SD
ON CD.document_id = SD.document_id
WHERE session_id IN (SELECT session_id FROM session)
Мне сложно понять остальное. Возможно ли это даже с вложенными запросами SELECT? Следует ли использовать курсор, и если да, может ли кто-нибудь показать пример с курсором? Любая помощь будет высоко ценится.
РЕДАКТИРОВАТЬ №1: добавлена реализация C #, дамп MySQL и ожидаемый результат
Реализация C #
private void ClassifyUsers() {
int nClusters = Database.SelectClusterCount(); //get number of clusters
DataSet sessions = Database.SelectSessions(); //get all sessions
foreach (DataRow session in sessions.Tables[0].Rows) { //foreach session
int[] acc = new int[nClusters]; //prepare an accumulator for each known cluster
string s_id = session["session_id"].ToString();
DataSet sessionClusters = Database.SelectSessionClusters(s_id); //get clusters for this session
foreach (DataRow cluster in sessionClusters.Tables[0].Rows) { //for each cluster
int c = Convert.ToInt32(cluster["cluster_id"].ToString()) - 1;
acc[c]++; //accumulate the cluster count
}
//find the maximum in the accumulator -> that is the most relevant cluster
int max = 0;
for (int j = 0; j < acc.Length; j++) {
if (acc[j] >= acc[max]) max = j;
}
max++;
Database.UpdateSessionCluster(s_id, max); //update the session with its new assigned cluster
}
}
Структура таблицы, тестовые данные и ожидаемый результат
Структура таблицы и тестовые данные
РЕДАКТИРОВАТЬ №2: добавлен меньший набор данных и дальнейшее пошаговое руководство
Вот меньший набор данных:
SESSION
session id | cluster
abc 0
def 0
ghi 0
jkl 0
mno 0
CLUSTER
cluster_id | label
1 A
2 B
3 C
4 D
5 E
SESSION_DOCUMENT
id | session_id | document_id
1 abc 1
2 def 5
3 jkl 3
4 ghi 4
5 mno 2
6 def 2
7 abc 5
8 ghi 3
CLUSTER_DOCUMENT
id | cluster_id | document_id
1 1 2
2 1 3
3 2 5
4 3 5
5 3 1
6 4 3
7 5 2
8 5 4
Подробное описание алгоритма
Шаг 1: получить кластеры для документов, просматриваемых сеансом
session_id | cluster_id | label | document_id
abc 3 C 1
abc 2 B 5
abc 3 C 5
-----
def 2 B 5
def 3 C 5
def 1 A 2
def 5 E 2
----
ghi 5 E 4
ghi 1 A 3
ghi 4 D 3
----
jkl 1 A 3
jkl 4 D 3
----
mno 1 A 2
mno 5 E 2
Шаг 2: подсчитать количество кластеров
session_id | cluster_id | label | occurrence
abc 3 C 2 <--- MAX
abc 2 B 1
----
def 2 B 1
def 3 C 1
def 1 A 1
def 5 E 1 <--- MAX
----
ghi 5 E 1
ghi 1 A 1
ghi 4 D 1 <--- MAX
----
jkl 1 A 1
jkl 4 D 1 <--- MAX
----
mno 1 A 1
mno 5 E 1 <--- MAX
Шаг 3 (конечный результат): найти максимальный кластер для каждого сеанса (см. Выше) и построить окончательный набор результатов (session_id, cluster_id):
session_id | cluster_id
abc 3
def 5
ghi 4
jkl 4
mno 5
РЕДАКТИРОВАТЬ № 3: Уточнение принятого ответа
Оба предоставленных ответа верны. Оба они предлагают решение проблемы. Я дал Мости Мостачо принятый ответ, потому что он первым предоставил решение и предоставил другую версию решения с VIEW . Решение от mankuTimma не уступает по качеству решению Мосты Мостачо. Таким образом, у нас есть два одинаково хороших решения, я просто выбрал Мосты Мостачо, потому что он был первым.
Спасибо им обоим за их вклад. .