如何从MySQL表中检索10个最新的DISTINCT IP？

I have a table containing pagehit (normalized) data and I need to grab the 10 latest unique ips.

我有一个包含pagehit(规范化)数据的表,我需要抓住10个最新的独特ips。

I tried to do it like this:

我试着这样做:

SELECT * FROM spy_hits ORDER BY date desc GROUP BY ip LIMIT 10;

Which should give me this result:

哪个应该给我这个结果:

+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| id  | date       | ip  | browser | os | page | host | referer | query | agent |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| 354 | 1244442065 |   2 |       3 |  2 |   16 |    1 |      47 |    12 |     2 |
| 311 | 1244442000 |   1 |       2 |  1 |   16 |    1 |      36 |    12 |     1 |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
2 rows in set (0.00 sec)

That is the latest unique visitors to the site.

这是该网站的最新独立访问者。

But I get a syntax error instead of that result.

但是我得到语法错误而不是结果。

So I have to do this query:

所以我必须这样做:

SELECT * FROM spy_hits GROUP BY ip ORDER BY date desc LIMIT 10;

Which I thought would be ok. But it gives this result:

我认为这样会好的。但它给出了这个结果:

+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| id  | date       | ip  | browser | os | page | host | referer | query | agent |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
| 280 | 1242130841 |   2 |       3 |  2 |   16 |    1 |      47 |    12 |     2 |
| 268 | 1242130818 |   1 |       2 |  1 |   16 |    1 |      36 |    12 |     1 |
+-----+------------+-----+---------+----+------+------+---------+-------+-------+
2 rows in set (0.00 sec)

But that sets order by date after its grouped already so it grabs the first unique ips in the table which are also the oldest.

但是,它在已经分组之后按日期设置顺序,因此它抓取表中第一个也是最早的唯一ips。

So the table goes:

所以表格如下:

 id --- date
 268    1242130818    (Old)
  |      |
  V      V
 354    1244442065    (New)

But I want it to go like this before I do the group by:

但是我希望它在我做这个小组之前是这样的:

 id --- date
 354    1244442065    (New)
  ^       ^
  |       |
 268    1242130818    (Old)

I'm using PHP with it so if anyone has an idea of how to get the results with a PHP solution.

我正在使用PHP,所以如果有人知道如何使用PHP解决方案获得结果。

Cheers in advance :)

提前干杯:)

1 个解决方案

#1

If you have few DISTINCT IP's:

如果您的DISTINCT IP很少:

SELECT  ip, MAX(date) AS maxdate
FROM    (
        SELECT  ip, MAX(date) AS maxdate
        FROM    spy_hits
        GROUP BY
                ip
        )
ORDER BY
        maxdate DESC
LIMIT 10

If you have lots of DISTINCT IP's:

如果你有很多DISTINCT IP:

SELECT  *
FROM    spy_hits so
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    spy_hits si
        WHERE   si.ip = so.ip
                AND si.date > so.date
        )
ORDER BY
        date DESC
LIMIT 10

Creating two indexes on this table:

在此表上创建两个索引:

CREATE INDEX ix_spyhits_date ON spy_hits (date)
CREATE INDEX ix_spyhits_ip_date ON spy_hits (ip, date)

will improve these queries a lot.

将大大改善这些查询。

See this entry in my blog for performance details:

有关性能详情,请参阅我的博客中的此条目:

• Latest DISTINCT records

最新的DISTINCT记录