首页 文章

Mysql - 每天每小时获取唯一不同ip的计数

提问于
浏览
0

要问的更新问题:我有一张日期和IP表 . 我想为每小时提取新的非重复性不同的ips,而不是不同的ip聚合小时数

我的 table 看起来像

Date          Time              IP
07-01-2018  08:00:00           1.1.1
07-02-2018  09:00:00           1.1.1
07-02-2018  10:00:00           2.2.2
07-03-2018  11:00:00           1.1.1
07-03-2018  12:00:00           2.2.2
07-03-2018  13:00:00           3.3.3

然后

07-01-2018 08:00:00, distinct count of ip should be 1 (1.1.1)
07-02-2018 09:00:00, new distinct count of ip should be 0
07-02-2018 10:00:00, new distinct count of ip should be 1 (2.2.2) 
07-03-2018 11:00:00, new distinct count of ip should be 0
07-03-2018 12:00:00, new distinct count of ip should be 0
07-03-2018 13:00:00, new distinct count of ip should be 1 (3.3.3)

结果我期待:Date Hour Counts 07-01-2018 08:00:00 1 07-02-2018 09:00:00 0 07-02-2018 10:00:00 1 07-03-2018 11: 00:00 0 07-03-2018 12:00:00 0 07-03-2018 13:00:00 1

Vlam的查询适用于每天的唯一计数,SELECT aa.date,count(distinct aa.ip)as count_ips FROM table1 aa where not exists(从table1 bb中选择bb.ip,其中bb.ip = aa.ip和bb.date <aa.date)group by aa.date order by aa.date asc;

现在我想分解到每个小时 . 任何建议?

提前致谢!

1 回答

  • 1

    我修改了原始SQL,只是添加了一个过滤来删除行中日期之前出现的所有IP地址,因为只应计算新的IP地址 .

    SELECT aa.date, count(distinct aa.ip) as count_ips FROM table1 aa
    where not exists
    (select bb.ip from table1 bb where bb.ip = aa.ip and bb.date < aa.date)
    group by aa.date order by aa.date asc;
    

相关问题