Методы поиска взаимных голосов в рейтинговой системе

Question

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

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

Идеальным результатом был бы список пользователей, где рядом с каждым пользователем есть совпадающий пользователь и процент, показывающий, что, например, пользователь A набирает 10% своих голосов за пользователя B. Это, очевидно, в порядке убывания, так что пользователи с наибольшей тенденцией голосовать именно за одного другого пользователя находятся на вершине.

Итак, мой вопрос: возможно ли это в одних запросах или как далеко может зайти SQL?

Любые указатели на существующие реализации? (желательно PHP)

Answer 1

Только в качестве примера (с использованием MS SQL Server), но работает в любом варианте SQL, который поддерживает COUNT() OVER()

CREATE TABLE mytable(
   votefrom INTEGER
  ,voteto   INTEGER
);
INSERT INTO mytable(votefrom,voteto) VALUES (100,200);
INSERT INTO mytable(votefrom,voteto) VALUES (100,200);
INSERT INTO mytable(votefrom,voteto) VALUES (100,200);
INSERT INTO mytable(votefrom,voteto) VALUES (100,200);
INSERT INTO mytable(votefrom,voteto) VALUES (100,200);
INSERT INTO mytable(votefrom,voteto) VALUES (100,300);
INSERT INTO mytable(votefrom,voteto) VALUES (100,400);
INSERT INTO mytable(votefrom,voteto) VALUES (100,500);
INSERT INTO mytable(votefrom,voteto) VALUES (100,600);
INSERT INTO mytable(votefrom,voteto) VALUES (100,700);

запрос:

select distinct
       votefrom
     , voteto
     , count(*) over(partition by votefrom, voteto) fromto
     , count(*) over(partition by votefrom) fromtot
     , count(*) over(partition by votefrom, voteto) * 100 / count(*) over(partition by votefrom) pct
from mytable

результат:

+----+----------+--------+--------+---------+-----+
|    | votefrom | voteto | fromto | fromtot | pct |
+----+----------+--------+--------+---------+-----+
|  1 |      100 |    200 |      5 |      10 |  50 |
|  2 |      100 |    300 |      1 |      10 |  10 |
|  3 |      100 |    400 |      1 |      10 |  10 |
|  4 |      100 |    500 |      1 |      10 |  10 |
|  5 |      100 |    600 |      1 |      10 |  10 |
|  6 |      100 |    700 |      1 |      10 |  10 |
+----+----------+--------+--------+---------+-----+

см.: http://rextester.com/UESP57757

Синтаксис в MySQL будет совсем другим, в то время как dbms не поддерживает COUNT() OVER()