寻找每个产品的总点击量和独立点击量。

Question

我有如下表结构：

    id            productid             ip               hittime
 ---------------------------------------------------------------------------
     1                5               1.1.1.1           2011-05-03 06:55:11
     2                5               1.1.1.1           2011-05-03 06:57:11
     3                6               2.2.2.2           2011-05-03 07:30:00
     4                4               1.1.1.1           2011-05-03 07:32:54
     5                5               2.2.2.2           2011-05-03 07:55:00

现在我需要一个查询，输出每个产品的总访问量和独立访客数。

     productid              totalhits                uniquehits
 ------------------------------------------------------------------
        4                       1                        1
        5                       3                        2
        6                       1                        1

标准：

总命中数 = 属于特定产品的所有记录

唯一命中数 = 如果IP不同或者对于相同的IP，命中时间相差5分钟，则2次命中被鉴定为唯一命中

我该如何实现这个功能？

- I-M-JM

---

你尝试过什么但没有成功？ - alex

我尝试过，能够找到特定产品的计数（总点击量），但无法找到独立点击量。 - I-M-JM

SELECT productid, count(ip) as totalhits FROM product_hits group by productid - I-M-JM

为什么产品编号为4的唯一点击次数是2，而总点击次数是1？ - phoxis

这是我的错误，打错字了。 - I-M-JM

Answer 1

rMX的解决方案非常接近，相当聪明。他应该得到功劳，我只是稍微调整了一下，添加了几个缺失的部分：

select productid, count(*) totalhits, 
    count(distinct 
        concat(ip,
            date_format(hittime, '%Y%m%d%H'),
            round(date_format(hittime, '%i') / 5) * 5)
        ) uniquehits
from table
group by productid

我对rMX的想法做出了以下更改：

1. 将ceil（）更改为round（），因为ceil / floor会导致边缘情况被错误处理
2. 将round（）的结果乘以5。我认为rMX本来就是这个意思，只是忘记打字了。

编辑：乘以5真的没有必要。我的大脑有点混乱。不过将ceil（）更改为round（）仍然很重要。

- ean5533

Answer 2

更新>

select productid, count(*) totalhits, 
    count(distinct 
        concat(ip,
            date_format(hittime, '%Y%m%d%H'),
            ceil(date_format(hittime, '%i') / 5))
        ) uniquehits
from table
group by productid

我认为这应该可以工作。抱歉，我没有时间测试它。

- rMX

---

不行，这也不行。请检查问题中提到的唯一点击要求。 - I-M-JM

@I-M-JM：虽然您在帖子中已经指定了，但如果您修改示例输出以说明问题，那将会更好。人们通常看到示例并假设他们可以从示例中理解，而不太关注帖子的其余部分（这是错误的，但他们只是普通人，可能还有其他事情要做，因此想要快速）。 - Andriy M