使用MySQL计算IP地址列的变化 [英] Calculate variation of IP addresses column using MySQL

问题描述

我正在尝试检测使用代理滥用我的网站的人。

I'm trying to detect people using proxies to abuse my website.

他们通常会更改代理等等。但肯定有一种模式，他们多次使用一个代理地址。对于合法访问者而言，这远远超过正常情况。

Often they will change proxies and so forth. But there is definitely a pattern of them using one proxy address many times. Much more than is normal for legitimate visitors.

通常，我网站的大多数访问都是通过仅访问过一次或几次的唯一IP地址。不重复。

Usually most accessing of my website is by unique ip addresses that have only visited once or a few times. Not repeatedly.

假设我在列中有这些IP地址：

Let's say I have these ip addresses in a column:

89.46.74.56
89.46.74.56
89.46.74.56
91.14.37.249
104.233.103.6

这意味着有5个独特的5个。给出60％的唯一性得分。

That would mean there are 3 uniques out of 5. Giving a "uniqueness score" of 60%.

如何我会用MySQL有效地计算这个吗？

How would I calculate this efficiently using MySQL?

推荐答案

计划

• 通过ip获取计数分组


• 除以（交叉加入）总行数


• 从上面获取最大重复比率

设置

create table example
(
  id integer primary key auto_increment not null,
  ip varchar(13) not null
);

insert into example
( ip )
values
( '89.46.74.56'   ),
( '89.46.74.56'   ),
( '89.46.74.56'   ),
( '91.14.37.249'  ),
( '104.233.103.6' )
;

查询

select max(repeat_factor)
from
(
select ip, count(*) / rc.row_count as repeat_factor
from example
cross join ( select count(*) as row_count from example ) rc
group by ip
) q
;

输出

+--------------------+
| max(repeat_factor) |
+--------------------+
| 0.6                |
+--------------------+

sqlfiddle

sqlfiddle

这篇关于使用MySQL计算IP地址列的变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！