kdax1Ldn

1drop table if exists usersessions;
2
3create table usersessions
4(
5  id                  bigint auto_increment comment 'ID',
6  created             datetime     not null comment 'Дата и время создания записи',
7  updated             datetime     null comment 'Дата и время обновления записи',
8  user_id             int          null comment 'ID игрока (swarm_user_id)',
9  fio                 varchar(255) null comment 'Фамилия Имя Отчество',
10  source              varchar(255) null comment 'Платформа, с которой инициирована сессия',
11  ip                  varchar(255) null comment 'IP-адрес, с которого была инициирована сессия',
12  marker              varchar(255) null comment 'Идентификатор устройства, с которого была инициирована сессия',
13  session_start       datetime     null comment 'Дата и время начала сессии',
14  session_finish      datetime     null comment 'Дата и время завершения сессии',
15  connection_provider varchar(255) null comment 'Наименование провайдера по IP',
16  ip_region           varchar(255) null comment 'Регион, к которому принадлежит IP-адрес',
17  constraint usersession_pk
18    primary key (id),
19  constraint usersession_user_id_fk
20    foreign key (user_id) references user (id)
21);
22
23
24
25alter table authlog
26  add fingerprint varchar(255) null;
27alter table authlog
28  add session_finish datetime null;
29alter table authlog
30  add connection_provider varchar(2000) null;
31alter table authlog
32  add ip_region varchar(2000) null;
33
34
35
36alter table authlog
37  modify session_finish datetime null;
38update authlog
39set session_finish = null
40where id > 0;
41
42
43update videotranslationlog
44set session_source = 4
45where session_source = 0;
46update videotranslationlog
47set session_source = 15
48where session_source = 1;
49update videotranslationlog
50set session_source = 16
51where session_source = 2;
52update videotranslationlog
53set session_source = 17
54where session_source = 3;
55update videotranslationlog
56set session_source = 18
57where session_source = 4;
58update videotranslationlog
59set session_source = 42
60where session_source = 5;
61
62select count(distinct user_id)
63from authlog
64group by fingerprint;
65
66select b.id,
67       b.fio,
68       b.user_id,
69       b.user_IP,
70       (select count(distinct a.user_id) FROM authlog AS a where a.user_IP = b.user_IP)
71from authlog AS b
72limit 20;
73
74select b.id,
75       b.user_id,
76       b.fio,
77       b.source_id,
78       b.user_IP,
79       (select count(distinct a.user_id) from AuthLog as a where a.user_IP = b.user_IP) as amount_by_IP,
80       b.fingerprint,
81       (select count(distinct c.user_id)
82        from AuthLog as c
83        where c.fingerprint = b.fingerprint)                                            as amount_by_fingerprint,
84       b.date_time,
85       b.session_finish,
86       b.connection_provider,
87       b.ip_region
88from AuthLog b
89order by b.id
90limit 100, 20;
91
92
93
94SELECT
95  al.amount_by_IP,
96  ac.amount_by_fingerprint,
97  T1.*
98FROM (
99       select b.id,
100              b.user_id,
101              b.fio,
102              b.source_id,
103              b.user_IP,
104
105              #        al.amount_by_IP,
106              #        CASE WHEN c.user_count IS NULL THEN 1 ELSE c.user_count END,
107              #        d.user_count,
108
109              b.fingerprint,
110              #        ac.amount_by_fingerprint,
111              b.date_time,
112              b.session_finish,
113              b.connection_provider,
114              b.ip_region
115
116       from AuthLog b
117            #        left join AuthLog c on b.user_IP = c.user_IP
118
119            #      left join `v$authlogdistinctip` `c` on (`b`.`user_IP` = `c`.`ip`)
120            #      left join `v$authlogdistinctip` `d` on (`b`.`user_IP` = `d`.`ip`)
121
122            #          left join  (select user_IP, count(distinct a.user_id) as amount_by_IP from AuthLog as a group by user_IP) as al
123            #                  on al.user_IP = b.user_IP
124            #        left join (select fingerprint, count(distinct c.user_id) as amount_by_fingerprint
125            #                   from AuthLog as c
126            #                   group by fingerprint) as ac
127            #                  on ac.fingerprint = b.fingerprint
128       order by b.id
129       limit 100, 20) T1
130
131
132       left JOIN (select user_IP, count(distinct a.user_id) as amount_by_IP from AuthLog as a group by user_IP) as al
133                 on al.user_IP = T1.user_IP
134
135       left join (select fingerprint, count(distinct c.user_id) as amount_by_fingerprint
136                  from AuthLog as c
137                  group by fingerprint) as ac
138                 on ac.fingerprint = T1.fingerprint
139;
140
141select `b`.`user_IP` AS `ip`,count(distinct `b`.`user_id`) AS `user_count`
142from `gamebet`.`authlog` `b`
143WHERE `b`.`user_IP` IN ('10.20.29.32', '10.20.29.153', '10.20.30.74')
144group by `b`.`user_IP`;
145
146#!!!!!!!!!!!!!!!!!!!!!
147select `b`.`user_IP` AS `ip`,count(distinct `b`.`user_id`) AS `user_count`
148from `gamebet`.`authlog` `b`
149group by `b`.`user_IP`
150having count(distinct `b`.`user_id`) > 1;
151
152select `b`.`user_IP` AS `ip`,count(distinct `b`.`user_id`) AS `user_count`
153from `gamebet`.`authlog` `b`
154group by `b`.`user_IP`
155order by user_count desc;
156
157
158select a.user_id, count(a.user_IP)
159from authlog as a
160group by a.user_id;
161
162
163select count(distinct a.user_id)
164from authlog as a
165where a.user_IP = '0:0:0:0:0:0:0:1'
166  and a.user_id <> 51171473
167group by a.user_IP;
168
169
170select a.user_id,
171       a.fio,
172       count(a.user_Id)
173from authlog as a
174where a.user_IP = '10.20.30.74'
175  and a.user_id <> 11984528
176  and a.is_success = true
177group by a.user_id
178order by count(a.user_id) desc;
179
180select a.user_id,
181       a.fio,
182       count(a.user_id)
183from authlog as a
184where a.fingerprint = '111'
185  and a.user_id <> 11984528
186  and a.is_success = true
187group by a.user_id;
188
189
190
191select a.user_id,
192       a.fio,
193       count(a.user_id),
194       b.count_fingerprint
195from authlog as a
196       left outer join (select user_id,
197                               fio,
198                               count(user_id) as count_fingerprint
199                        from authlog
200                        where fingerprint = '111'
201                          and user_id <> 11984528
202                          and is_success = true
203                        group by user_id) as b on (a.user_id = b.user_id)
204where (a.user_IP = '10.20.30.74' or a.fingerprint = '111')
205  and a.user_id <> 11984528
206  and a.is_success = true
207group by a.user_id
208order by count(a.user_id) desc;
209
210
211explain (
212          SELECT T1.*, T2.count_fingerprint
213          FROM
214            (select a.user_id,
215                    a.fio,
216                    count(a.user_id)
217             from authlog as a
218             where (a.user_IP = '10.20.30.74')
219               and a.user_id <> 11984528
220               and a.is_success = true
221             group by a.user_id
222             order by count(a.user_id) desc) AS T1
223
224              LEFT OUTER JOIN
225              (select user_id,
226                      fio,
227                      count(user_id) as count_fingerprint
228               from authlog
229               where fingerprint = '111'
230                 and user_id <> 11984528
231                 and is_success = true
232               group by user_id) AS T2
233              on (T1.user_id = T2.user_id)
234        )
235;
236
237
238#1
239select a.user_id,
240       a.fio,
241       a.user_IP,
242       count(a.user_id)
243from authlog as a
244where a.user_IP = '10.20.30.74'
245  and a.user_id <> 11984528
246  and a.is_success = true
247group by a.user_id
248order by count(a.user_id) desc;
249
250#2
251select user_id,
252       fio,
253       count(user_id) as count_fingerprint
254from authlog
255where fingerprint = '111'
256  and user_id <> 11984528
257  and is_success = true
258group by user_id;
259
260
261
262explain (select a.user_id,
263                a.fio,
264                count(a.user_id)
265         from authlog as a
266         where (a.user_IP = '10.20.30.74')
267           and a.user_id <> 11984528
268           and a.is_success = true
269         group by a.user_id
270         order by count(a.user_id) desc);
271
272
273
274explain (select user_id,
275                fio,
276                count(user_id) as count_fingerprint
277         from authlog
278         where fingerprint = '111'
279           and user_id <> 11984528
280           and is_success = true
281         group by user_id);
282
283analyze table authlog;
284
285
286select a.user_id,
287       a.fio,
288       count(a.user_id)
289from authlog as a
290where a.fingerprint = '111'
291  and a.user_id <> 11984528
292  and a.is_success = true
293group by a.user_id
294order by count(a.user_id) desc;
295
296
297
298select a.user_id, a.fio
299from authlog as a
300where a.user_IP = '10.20.30.74'
301   or a.fingerprint = '111'
302group by a.user_id;
303
304
305create view $authlogfingerprint as (select a.user_id, a.fio
306                                    from authlog as a
307                                    where a.user_IP = ?
308                                       or a.fingerprint = ?
309                                    group by a.user_id);
310
311
312select *
313from v$authlogdistinctip
314WHERE ip IN ('10.20.29.32', '10.20.29.153', '10.20.30.74');
315
316select count(distinct b.user_id)
317from authlog as b
318where 11984528 in
319      (select a.user_id
320       from authlog as a
321       where a.user_IP = '10.20.30.74'
322          or a.fingerprint = '111'
323       group by a.user_id);
324
325
326#поиск пользователей, для которых совпадают userIP, fingerprint
327select a.user_id, a.fio
328from authlog as a
329where a.user_IP = '10.20.30.74'
330   or a.fingerprint = '111'
331group by a.user_id;
332
333
334
335select b.id,
336       b.user_id,
337       b.fio,
338       b.source_id,
339       b.user_IP,
340       al.q,
341       b.fingerprint,
342       #        ac.amount_by_fingerprint,
343       b.date_time,
344       b.session_finish,
345       b.connection_provider,
346       b.ip_region
347from AuthLog b
348       left join (select user_IP, user_id, count(user_id) q from AuthLog group by user_ip) as al
349                 on al.user_IP = b.user_IP
350     #        left join (select fingerprint, count(distinct c.user_id) as amount_by_fingerprint
351     #                   from AuthLog as c
352     #                   group by fingerprint) as ac
353     #                  on ac.fingerprint = b.fingerprint
354order by b.id
355limit 100, 20;
356
357
358
359select b.id,
360       b.user_id,
361       b.fio,
362       b.source_id,
363       b.user_IP,
364       count(distinct a.user_id) as amount_by_IP,
365       b.fingerprint,
366       count(distinct c.user_id) as amount_by_fingerprint,
367       b.date_time,
368       b.session_finish,
369       b.connection_provider,
370       b.ip_region
371from AuthLog b
372       join AuthLog as a on a.user_IP = b.user_IP
373       join AuthLog as c on c.fingerprint = b.fingerprint
374order by b.id
375limit 100, 20;
376
377
378
379select b.id,
380       b.user_id,
381       b.fio,
382       b.source_id,
383       b.user_IP,
384       count(distinct a.user_id) as amount_by_IP,
385       b.fingerprint,
386       count(distinct c.user_id) as amount_by_fingerprint,
387       b.date_time,
388       b.session_finish,
389       b.connection_provider,
390       b.ip_region
391from AuthLog b
392       inner join authlog as a on a.user_IP = b.user_IP
393       inner join authlog as c on c.user_IP = b.user_IP
394order by b.id
395limit 20;
396
397
398
399select b.user_IP AS ip, count(distinct b.user_id) AS user_count
400from authlog b
401group by b.user_IP
402order by user_count desc;
403
404
405
406#создать скрипт
407CREATE or replace VIEW v$authlogdistinctfingerprint AS (
408  SELECT
409    b.fingerprint             AS fingerprint,
410    COUNT(DISTINCT b.user_id) AS fingerprint_count
411  FROM authlog b
412  GROUP BY b.fingerprint
413  HAVING COUNT(DISTINCT b.user_id) > 1);
414
415
416
417SELECT a.id, a.user_id, b.*, c.*
418FROM authlog a
419       LEFT outer JOIN v$authlogdistinctip b
420                       ON a.user_IP = b.ip
421
422
423       LEFT outer JOIN v$authlogdistinctfingerprint c
424                       ON a.fingerprint = c.fingerprint
425
426WHERE a.is_success = TRUE
427ORDER BY c.fingerprint DESC
428LIMIT 10;
429
430
431
432#создать скрипт
433create or replace view v$authlogdistinctipfingerprint as
434  (select a.id,
435          a.user_id,
436          a.fio,
437          a.source_id,
438          b.ip as user_ip,
439          COALESCE(b.user_count, 1) - 1        as user_ip_count,
440          f.fingerprint,
441          COALESCE(f.fingerprint_count, 1) - 1 as user_fingerprint_count,
442          a.date_time                          as session_start,
443          a.session_finish,
444          a.connection_provider,
445          a.ip_region
446   from authlog as a
447          left join v$authlogdistinctip as b on a.user_IP = b.ip
448          left join v$authlogdistinctfingerprint as f on a.fingerprint = f.fingerprint);
449
450
451
452#создать скрипт
453create or replace view v$authlogdistinctipfingerprint as
454  (select a.id,
455          a.user_id as userId,
456          a.fio,
457          a.source_id as source,
458          b.ip as userIP,
459          COALESCE(b.user_count, 1) - 1        as ipAmount,
460          f.fingerprint,
461          COALESCE(f.fingerprint_count, 1) - 1 as fingerprintAmount,
462          a.date_time                          as dateTime,
463          a.session_finish as sessionFinish,
464          a.connection_provider as connectionProvider,
465          a.ip_region as ipRegion
466   from authlog as a
467          left join v$authlogdistinctip as b on a.user_IP = b.ip
468          left join v$authlogdistinctfingerprint as f on a.fingerprint = f.fingerprint);
469
470
471
472select a.id,
473       a.user_id,
474       a.fio,
475       a.source_id,
476       b.ip,
477       COALESCE(b.user_count, 1) - 1        as user_ip_count,
478       f.fingerprint,
479       COALESCE(f.fingerprint_count, 1) - 1 as user_fingerprint_count,
480       a.date_time                          as session_start,
481       a.session_finish,
482       a.connection_provider,
483       a.ip_region
484from authlog as a
485       left join v$authlogdistinctip as b on a.user_IP = b.ip
486       left join v$authlogdistinctfingerprint as f on a.fingerprint = f.fingerprint;
487
488select distinct fingerprint
489from authlog;
490
491select user_id, fio, fingerprint
492from authlog
493where fingerprint is not null;
494
495
496SELECT
497  b.fingerprint             AS fingerprint,
498  COUNT(DISTINCT b.user_id) AS fingerprint_count
499FROM authlog b
500GROUP BY b.fingerprint
501HAVING COUNT(DISTINCT b.user_id) > 1;
502
503
504
505
506
507
508
509
510
511DROP TEMPORARY TABLE IF EXISTS tmp_del_authtokens;
512CREATE TEMPORARY TABLE tmp_del_authtokens
513(
514  id INT(11) NOT NULL PRIMARY KEY,
515  auth_token varchar(128)
516);
517
518drop temporary table if exists tmp_set_authlog_session_finish;
519create temporary table tmp_set_authlog_session_finish
520(
521  id int(11) not null primary key
522);
523
524SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
525START TRANSACTION;
526INSERT INTO tmp_del_authtokens(id, auth_token) (SELECT id, token
527                                    FROM auth_token
528                                    WHERE expiration_time < CURRENT_TIMESTAMP
529                                    ORDER BY expiration_time
530                                    LIMIT 50000);
531
532insert into tmp_set_authlog_session_finish(id) (select id
533                                                from authlog
534                                                where authlog.auth_token in (tmp_del_authtokens.auth_token));
535
536DELETE del
537FROM `auth_token` del
538       JOIN tmp_del_authtokens t ON t.id = del.id;
539
540update authlog
541set authlog.session_finish = CURRENT_TIMESTAMP
542where authlog.id in (tmp_set_authlog_session_finish.id)
543  and authlog.session_finish is null;
544COMMIT;
545DROP TEMPORARY TABLE IF EXISTS tmp_del_authtokens;
546drop temporary table if exists tmp_set_authlog_session_finish;
547
548
549
550
551
552
553
554
555create procedure PR$DELETE_OLD_AUTH_TOKENS()
556BEGIN
557  DROP TEMPORARY TABLE IF EXISTS tmp_del_authtokens;
558  CREATE TEMPORARY TABLE tmp_del_authtokens
559  (
560    id         INT(11) NOT NULL PRIMARY KEY,
561    auth_token varchar(128)
562  );
563
564  drop temporary table if exists tmp_set_authlog_session_finish;
565  create temporary table tmp_set_authlog_session_finish
566  (
567    id int(11) not null primary key
568  );
569
570  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
571  START TRANSACTION
572    ;
573    INSERT INTO tmp_del_authtokens(id, auth_token) (SELECT id, token
574                                                    FROM auth_token
575                                                    WHERE expiration_time < CURRENT_TIMESTAMP
576                                                    ORDER BY expiration_time
577                                                    LIMIT 50000);
578
579    insert into tmp_set_authlog_session_finish(id) (select id
580                                                    from authlog
581                                                           join tmp_del_authtokens t on t.auth_token = authlog.auth_token);
582
583    DELETE del
584    FROM `auth_token` del
585           JOIN tmp_del_authtokens t ON t.id = del.id;
586
587    update authlog
588      join tmp_set_authlog_session_finish t on t.id = authlog.id
589    set authlog.session_finish = CURRENT_TIMESTAMP
590    where authlog.session_finish is null;
591  COMMIT;
592  DROP TEMPORARY TABLE IF EXISTS tmp_del_authtokens;
593  drop temporary table if exists tmp_set_authlog_session_finish;
594END;