Расчет перекрытия в MySQL

Question

Я пытаюсь выяснить, какие классы имеют наибольшее совпадение между ними. Данные хранятся в MySQL, и у каждого учащегося есть отдельная строка в базе данных для каждого класса, который он / она посещает (я не настраивал его и не могу его изменить). Я вставил упрощенную версию таблицы ниже. На самом деле существует около 20 различных курсов.

CREATE TABLE classes
(`student_id` int, `class` varchar(13));
INSERT INTO classes
(`student_id`, `class`)
VALUES
(55421, 'algebra'),
(27494, 'algebra'),
(64934, 'algebra'),
(65364, 'algebra'),
(21102, 'algebra'),
(90734, 'algebra'),
(20103, 'algebra'),
(57450, 'gym'),
(76411, 'gym'),
(24918, 'gym'),
(65364, 'gym'),
(55421, 'gym'),
(89607, 'world_history'),
(54522, 'world_history'),
(49581, 'world_history'),
(84155, 'world_history'),
(55421, 'world_history'),
(57450, 'world_history');

В конце концов я хочу использовать Circos ( здесь фон), но я был бы рад любому методу, который позволял бы мне понимать и показывать людям, где больше и меньше всего совпадений. This is off the top of my head, but I was thinking that I could use an output table with one row and one column for each course and the number of overlaps listed where different classes intersect. Where each course intersected with itself could show the number of people who have no overlap with any other category.

Answer 1

Вы можете сделать это, генерируя результаты для представления ссылок: src -> dst = nb

1) Получить матрицу

select c1.class src_class, c2.class dst_class
from (select distinct class from classes) c1
join (select distinct class from classes) c2
order by src_class, dst_class

"Выбрать отдельный класс" не является необходимым для генерации матрицы, вы можете просто выбрать классы и GROUP BY. Но на шаге 2 нам нужны эти уникальные результаты.

Результат:

src_class      dst_class
-----------------------------
algebra        algebra
algebra        gym
algebra        world_history
gym            algebra
gym            gym
gym            world_history
world_history  algebra
world_history  gym
world_history  world_history

2) Присоединиться к списку студентов, которые соответствуют источника и назначения

select c1.class src_class, c2.class dst_class, count(v.student_id) overlap
from (select distinct class from classes) c1
join (select distinct class from classes) c2
left join classes v on
(
    v.class = c1.class
    and v.student_id in (select student_id from classes
                         where class = c2.class)
)
group by src_class, dst_class
order by src_class, dst_class

Отдельные значения (шаг 1) позволяют нам получить все классы, даже если они не являются ссылками (и вместо этого поставить 0).

Результат:

src_class      dst_class      overlap
-------------------------------------
algebra        algebra           7
algebra        gym               2
algebra        world_history     1
gym            algebra           2
gym            gym               5
gym            world_history     2
world_history  algebra           1
world_history  gym               2
world_history  world_history     6

3 - Сделайте другой расчет, если классы равны

select c1.class src_class, c2.class dst_class, count(v.student_id) overlap
from (select distinct class from classes) c1
join (select distinct class from classes) c2
left join classes v on
(
    v.class = c1.class and
    (
        -- When classes are equals
        -- Students presents only in that class
        (c1.class = c2.class
         and 1 = (select count(*) from classes
                  where student_id = v.student_id))
    or
        -- When classes are differents
        -- Students present in both classes
        (c1.class != c2.class
         and v.student_id in (select student_id from classes
                              where class = c2.class))
    )
)
group by src_class, dst_class
order by src_class, dst_class

Результат:

src_class      dst_class      overlap
-------------------------------------
algebra        algebra           5
algebra        gym               2
algebra        world_history     1
gym            algebra           2
gym            gym               2
gym            world_history     2
world_history  algebra           1
world_history  gym               2
world_history  world_history     4

Answer 2

Просто используйте самостоятельное объединение и агрегацию:

select c1.class, c2.class, count(*)
from classes c1 join
     classes c2
     on c1.student_id = c2.student_id
group by c1.class, c2.class;

Это не производит это в совершенно том же формате.