Mysql пересечение аккаунтов пользователей по ip-адресам

[BoBka-altRUist]

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

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

logs_table:

ip                user_id       data
127.0.0.1      8               2014-08-13
127.0.0.1      1               2014-08-13
127.0.0.1      2               2014-08-13
127.0.0.2      2               2014-08-13
127.0.0.2      3               2014-08-13
127.0.0.4      6               2014-08-16
127.0.0.5      7               2014-08-16
127.0.0.5      10             2014-08-17

CREATE TABLE IF NOT EXISTS `logs_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `data` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;

--
-- Дамп данных таблицы `logs_table`
--

INSERT INTO `logs_table` (`id`, `ip`, `user_id`, `data`) VALUES
(1, '127.0.0.1', 8, '2014-08-13'),
(2, '127.0.0.1', 1, '2014-08-13'),
(3, '127.0.0.1', 2, '2014-08-13'),
(4, '127.0.0.2', 2, '2014-08-13'),
(5, '127.0.0.2', 3, '2014-08-13'),
(6, '127.0.0.4', 6, '2014-08-16'),
(7, '127.0.0.5', 7, '2014-08-16'),
(8, '127.0.0.5', 10, '2014-08-17');

самое простое сгруппировать по ip (исключая из внимания дату, т.е. за все время):

SELECT COUNT( id ) AS ip_rows, logs_table.ip
FROM  `logs_table`
GROUP BY ip

ip_rows ip
3 127.0.0.1
2 127.0.0.2
1 127.0.0.4
2 127.0.0.5

т.е. получили сколько вхождений по каждому ip; можно отобрать только те где количество вхождений >1

SELECT *
FROM logs_table, (

SELECT COUNT( id ) AS ip_rows, logs_table.ip ip0
FROM  `logs_table`
GROUP BY ip
)q1
WHERE ip_rows >1
AND  `logs_table`.`ip` = ip0

id ip user_id data ip_rows ip0
1 127.0.0.1 8 2014-08-13 3 127.0.0.1
2 127.0.0.1 1 2014-08-13 3 127.0.0.1
3 127.0.0.1 2 2014-08-13 3 127.0.0.1
4 127.0.0.2 2 2014-08-13 2 127.0.0.2
5 127.0.0.2 3 2014-08-13 2 127.0.0.2
7 127.0.0.5 7 2014-08-16 2 127.0.0.5
8 127.0.0.5 10 2014-08-17 2 127.0.0.5

с отступами:

id       ip          user_id         data           ip_rows    ip0
1  127.0.0.1         8         2014-08-13         3         127.0.0.1
2  127.0.0.1         1         2014-08-13         3         127.0.0.1
3  127.0.0.1         2         2014-08-13         3         127.0.0.1
4  127.0.0.2         2         2014-08-13         2         127.0.0.2
5  127.0.0.2         3         2014-08-13         2         127.0.0.2
7  127.0.0.5         7         2014-08-16         2         127.0.0.5
8  127.0.0.5        10        2014-08-17         2         127.0.0.5

Но в данном случае находим пересечения только явно по ip (выделил разным цветом), а как найти пересечение по аккаунту user_id=2 выделено жирным (это строки id=3 и id=4) тогда получится что группа строк выделенных красным объединиться с группой строк синих.
----------------------------
у меня приходит мысль сделать что-то типа логического произведения таблицы самой на себя, т.е.:
находим по таблице повторные user_id, где user_id>1 (это user_id=2) получаем массив ip где user_id=2 (это: 127.0.0.2 и 127.0.0.1)
в цикле по этим ip проходим по всей таблице находя строки с этими ip-адресами где user_id<>2 и дописываем в исходную таблицу строки где ip=ip-адресам из массива (исключая искомый), а user_id=user_id-в найденной строке.

т.е. для ip = 127.0.0.2 и user_id<>2:
находим строки id=5, user_id у этой строки = 3 вставляем строку ip=127.0.0.1 | user_id=3

для ip = 127.0.0.1 и user_id<>2:
находим строки id=1, id=2, соответственно добавляем строки
ip=127.0.0.2 | user_id=8
ip=127.0.0.2 | user_id=1

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

проблема в том что как это сделать понимаю используя манипуляции на php, можно ли это упростить используя mysql запросы? и вообще может я изобретаю велосипед и давно уже есть какой-нибудь способ группировки group by по двум полям (используя логическое ИЛИ для этих полей - это было бы отличным решением)

и еще не совсем понимаю как еще при этом учитывать дату... ( т.е. мои размышления если и работают то только для всей таблицы, а если нужно взять к примеру за число 2014-08-13 ...
или может быть таблицу делать избыточной по всем датам (запуская по cron-у отмечая обработанные строки) и дату вставляемых строк указывать относительно найденных записей...

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

 

[rukhem]

Для хранения IP достаточно char(15)

 

[BoBka-altRUist]

да., ладно это не суть важно, можно еще и прокси туда записывать в строку (тогда этого будет мало), можно еще ip2long записать, и еще индексы использовать... это все уже частные случаи

 

[WMix]

group by user_id, ip ?

 

[BoBka-altRUist]

group by user_id, ip ?

группировка по двум полям же будет пытаться сделать группировку по одному полю и потом по другому (т.е. для данного примера ничего не изменится - будет выведена сама же таблица), вот если бы можно было задать чтобы mysql группировал так: `group by user_id "или" ip`... тогда это бы решило

 

[С.]

Ты сам прикинь сначала, как бы такой результат мог выглядеть. Например некая реляция бы выдавалась в позиции N (при группировке по user_id) ИЛИ в позиции М (по группировке по ip). Ерунда какая-то получается.

 

[antson]

BoBka-altRUist, а что ты будешь делать с посетителями у которых черный ип (прокси или нат у провайдера или на работе)?
ип + привязка к дате - это случай серых.
кроме того человек может заходить минимум из 3х мест (дом,работа,смартфон)

Если уж анализировать, то как минимум с разбивкой по часам + часовой пояс.
вечер+ночь вероятно из дома
день - с работы .
Трафик от опсосов мимо анализа.

P.S. Еще можно куку сажать, без привязки к логину и смотреть сочетания

 

[BoBka-altRUist]

Ты сам прикинь сначала, как бы такой результат мог выглядеть. Например некая реляция бы выдавалась в позиции N (при группировке по user_id) ИЛИ в позиции М (по группировке по ip). Ерунда какая-то получается.

да, ерунда (я это образно объяснил), возможно как вариант какое-то объединение двух запросов где в первом группировка по ip а во втором группировка по user_id, но я тоже смутно это понимаю, потому пока делаю как описал вначале, если будет найдено более красивое решение, я возможно им воспользуюсь.

BoBka-altRUist, а что ты будешь делать с посетителями у которых черный ип (прокси или нат у провайдера или на работе)?
ип + привязка к дате - это случай серых. кроме того человек может заходить минимум из 3х мест (дом,работа,смартфон) Если уж анализировать, то как минимум с разбивкой по часам + часовой пояс. вечер+ночь вероятно из дома день - с работы . Трафик от опсосов мимо анализа.
P.S. Еще можно куку сажать, без привязки к логину и смотреть сочетания

черный/серый ip, это не важно, пользователи обычно с разных регионов, а многие выходят с дедиков через удаленный рабочий стол, задача не стоит в точном 100% определении пользователя и что он это действительно он.
но если два аккаунта "засветились" на одном общем ip (например прокси на работе) то с большой долей вероятности можно сказать что эти пользователи "друзья/партнеры" или один и тот же пользователь, т.е. их можно объединить для мониторинга их финансовой деятельности.
а интернет-провайдеры обычно отдают два ip (своего прокси и внутренний своего клиента) вот можно брать внутренний или оба.
пулы у мобильных-провайдеров разделены по регионам (да, их можно не рассматривать в анализе),
кука не привязанная к аккаунту да, как доп. механизм - можно добавить, спасибо.

 

[BoBka-altRUist]

кстати вот наткнулся на задачу в чем-то схожую, и обсуждение похожее http://forums.mysql.com/read.php?10,229669,229669
только ему нужно получить немного другой результат...

для последователей (привожу только начальное и конечное сообщение):

Hello,

let's say I have a table with data transmittions between hosts:

source_ip | destination_ip | bytes
-----------------------------------
10.0.0.1 | 10.0.0.25 | 1234
10.0.0.2 | 10.0.0.18 | 2345
10.0.0.25 | 10.0.0.1 | 5678

Now I would like to sumarize data sent between each two hosts.
This means, that row 1 will be summed together with row 3 and
the result will be presented for example like this:

ip_1 | ip_2 | bytes_sum
---------------------------------
10.0.0.1 | 10.0.0.25 | 6912
10.0.0.2 | 10.0.0.18 | 2345

But I can't figure out how...

Thank you for your time.

you may need to refine this, but the idea is to keep it in the same order:
select if(source_ip > destination_ip, source_ip, destination_ip) as ip1,
if (source_ip > destination_ip, destination_ip, source_ip) as ip2,
sum(bytes)
from t
group by ip1, ip2;

Huu Da
MySQL flirter

 

[antson]

BoBka-altRUist,
select поля перечисленные в гроуп_бай и агрегатные функции
group by допустимо любое кол-во полей , с датой можно использовать datepart() , и даже логические условия можно задействовать case when then else endcase
having условие/я с использованием сравнений полей и агрегатных функций sum(),count(*) count distinct

 

[antson]

найти пары ид пользователей, которые входили с одного ип .
вывести только уникальные сочетания , т.е. из пары (1,2) и (2,1) показать только первое сочетание .

начинаем перекладывать на скл

select a.ip,a.id,b.id from table a, table b where a.ip=b.ip and a.id<b.id

если нужно выбрать список идишников в одно поле
select ip,group_concat(id) as all_ids from table group by ip

ты применительно к ип это хотел увидеть ? а то исходное описание чего и как считаешь меня совсем запутало

 

[С.]

возможно как вариант какое-то объединение двух запросов где в первом группировка по ip а во втором группировка по user_id

Это запросто -- UNION. Только зачем объединять два запроса, чтобы потом в РНР коде их разъединять.

 

[WMix]

Для хранения IP достаточно char(15)

достаточно unsigned int(10)

BoBka-altRUist, фигней занимаешься по ip человека не проверишь!