Создание матрицы хордовой диаграммы в MySql/GROUP BY Syntax

Я делаю некоторый анализ данных в MYSQL и надеюсь, что возможно следующее.

У меня есть таблица, которая документирует темы блогов пользователей следующим образом. Всего у меня 25 тем, и я хотел бы достаточно абстрагироваться, чтобы мы могли добавить темы позже и не повторять запрос (если возможно):

-----------------------
| user_id |  topic    |
-----------------------
|   01    |  art      | 
|   01    |  cooking  |
|   02    |  art      | 
|   03    |  outdoors |
|   03    |  art      |
|   03    |  cooking  |
-----------------------

Чтобы создать хордовую диаграмму (http://bl.ocks.org/4062006) из этих данных , мне нужно проанализировать отношения между любыми двумя темами, либо сгруппировав строки по двум темам, либо создав матрицу:

-----------------------------------------
| topic_combo            |  user_cnt    |
-----------------------------------------
|   art + cooking        |  2           | 
|   art + outdoors       |  1           |
|   cooking + outdoors   |  1           | 
-----------------------------------------

Или еще лучше...

---------------------------------------------
|          |  art  |  cooking  |  outdoors  |
---------------------------------------------
| art      |   3   |    2      |     1      |
| cooking  |   2   |    2      |     1      |
| outdoors |   1   |    1      |     1      |
---------------------------------------------

Возможны ли какие-либо из них в MYSQL? Если да, то как проще всего их сделать?


mysql analytics statistics chord-diagram table-statistics
person Wandering Digital    schedule 14.11.2012    source источник

Ответы (2)


arrow_upward
3
arrow_downward

Что ж, я не знаю, лучший ли это способ, но, по крайней мере, он один, пока вы не получите лучший ответ. Используйте JOIN и GROUP BY, как указано Гуванте.

SELECT a.topic AS at, b.topic AS bt, COUNT(*) AS c FROM a JOIN a AS b ON a.user_id=b.user_id GROUP BY a.topic, b.topic;

Это приведет к такому выводу:

at          bt          c
-----------------------------
art         art         3
art         cooking     2
art         outdoors    1
cooking     art         2
cooking     cooking     2
cooking     outdoors    1
outdoors    art         1
outdoors    cooking     1
outdoors    outdoors    1

Вы можете преобразовать его в матрицу, например. по PHP

while($data = mysql_fetch_object($sql)) {
  $matrix[$data->at][$data->bt] = $data->c;
}

Редактировать: Удалено дублирование, упомянутое в комментариях после подсказки друга.

person AmShaegar    schedule 14.11.2012
comment
Вам не нужно создавать дублирующую таблицу, просто выполните... from table_a a join table_a b... - person Matt Humphrey; 15.11.2012

arrow_upward
1
arrow_downward

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

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

Еще одним лакомым кусочком будет то, что вы можете сделать FROM из этой таблицы и JOIN из той же таблицы, чтобы создать набор всех комбинаций. Затем, выполнив GROUP BY для первой версии, вы можете получить запрос, который будет получать результаты из второй.

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

person Guvante    schedule 14.11.2012