| 2008-08-11 | 00:35:45 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:45 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:48 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:48 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:57 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:57 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:12 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:12 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:16 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:16 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:26 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:26 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:09 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:09 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:18 | /pagerror.gif | 61.134.63.252 | 304
| 2008-08-11 | 00:35:45 | /pagerror.gif | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:45 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:48 | /pagerror.gif | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:48 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:57 | /pagerror.gif | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:57 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:37:12 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:37:12 | /pagerror.gif | 61.134.63.253 | 304 |
按照ip地址对adate,atime进行统计数目,如果时间在两分钟之内算一次,如果超过这个时间段,算为另外一次,
得到结果(ip,时间长度),生命上边的数据使我排序的结果,原数据无序,date可能不是同一天。有时是另一天。
61.134.63.252 01:21
61.134.63.252 00:09
61.134.63.253 01:27
| 2008-08-11 | 00:35:45 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:48 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:48 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:57 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:57 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:12 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:12 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:16 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:16 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:26 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:26 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:09 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:09 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:18 | /pagerror.gif | 61.134.63.252 | 304
| 2008-08-11 | 00:35:45 | /pagerror.gif | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:45 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:48 | /pagerror.gif | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:48 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:57 | /pagerror.gif | 61.134.63.253 | 304 |
| 2008-08-11 | 00:35:57 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:37:12 | /iisstart.htm | 61.134.63.253 | 304 |
| 2008-08-11 | 00:37:12 | /pagerror.gif | 61.134.63.253 | 304 |
按照ip地址对adate,atime进行统计数目,如果时间在两分钟之内算一次,如果超过这个时间段,算为另外一次,
得到结果(ip,时间长度),生命上边的数据使我排序的结果,原数据无序,date可能不是同一天。有时是另一天。
61.134.63.252 01:21
61.134.63.252 00:09
61.134.63.253 01:27
解决方案 »
- 恢复乱码的mysql脚本
- 各位在数据量增大的时候用什么技术改进的呢?
- mysql 字符变量默认设置(windows)
- 关于mysql登录问题
- 用java通过odbc连mysql,读取的中文数据为乱码。
- mysql连接出现的奇怪的问题
- 这个数据库设计要求能达到吗?
- 与mysql建立连接编译通不过,请高手帮忙
- 需求:有的文章关联多张图片,有的文章无关联图片,怎么查询的时候把所有文章都查询出来呢?
- centos安装XtraBackup失败,版本冲突怎么办?
- Checking for corrupt, not cleanly closed and upgrade needing tables.
- PHPMyAdmin 访问被拒绝 如何解?
比如在2008-08-11(date) 00:35:45(time)开始,若访问时间在00:37:45之内,算一次,求出最小和最大
时间点的时间间隔,01:21(分:秒)
超过2分钟,算另一次访问,也求出时间间隔,对于每一个均求出结果。。
数据是无序的。就是2分钟算一次,求出时间间隔。
ip timelength
61.134.63.252 01:21
61.134.63.252 00:09
61.134.63.253 01:27
SELECT a.ip,TIME_FORMAT(a.TIME,'%H-%i') as gs,timediff(max(b.time),min(a.time)) as cb,
max(b.time),min(a.time)
FROM TT1B a
left join tt1b b on addtime(a.time,'00:02:00')>b.time and a.ip=b.ip
group by a.ip,TIME_FORMAT(a.TIME, '%H-%i')
order by a.ip,a.time) c where cb>0为什么没有
2008-08-11 | 00:37:12
2008-08-11 | 00:38:1801:06
| 2008-08-11 | 00:35:45 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:48 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:48 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:57 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:35:57 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:12 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:12 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:16 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:16 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:26 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:37:26 | /pagerror.gif | 61.134.63.252 | 304
这是一个ip61.134.6.252 00:35:45 00:37:26 在两分钟之内,算一次时间间隔
时间超过两分钟00:38:09
| 2008-08-11 | 00:38:09 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:09 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:38:18 | /pagerror.gif | 61.134.63.252 | 304
算第二次,算时间间隔
可能还有
| 2008-08-11 | 00:48:09 | /pagerror.gif | 61.134.63.252 | 304 |
| 2008-08-11 | 00:48:09 | /iisstart.htm | 61.134.63.252 | 304 |
| 2008-08-11 | 00:48:18 | /pagerror.gif | 61.134.63.252 | 304
没有在 00:38:09 的两分钟字内,算第三次时间间隔
对于另一cip
ip61.134.6.253 同样的计算方法
http://topic.csdn.net/u/20090605/13/c949a06d-95ed-43a4-9edf-a41db374fd55.html几个网站,忘带u盘了。
('2008-08-11', '00:35:45 ','61.134.63.252'),
('2008-08-11', '00:35:48 ','61.134.63.252'),
('2008-08-11', '00:35:57 ','61.134.63.252'),
('2008-08-11', '00:37:12 ','61.134.63.252'),
('2008-08-11', '00:37:16 ','61.134.63.252'),
('2008-08-11', '00:37:26 ','61.134.63.252'),
('2008-08-11', '00:38:09 ','61.134.63.252'),
('2008-08-11', '00:38:18 ','61.134.63.252'),
('2008-08-12', '00:37:26 ','61.134.63.252'),
('2008-08-12', '00:38:09 ','61.134.63.252'),
('2008-08-12', '00:38:18 ','61.134.63.252'),
('2008-08-11', '00:37:26 ','61.134.63.253'),
('2008-08-11', '00:38:09 ','61.134.63.253'),
('2008-08-11', '00:38:18 ','61.134.63.253')
最后结果
61.134.63.252 01:21
61.134.63.252 00:29
61.134.63.252 01:52
61.134.63.253 00:52
测试数据
是如下记录 :('2008-08-11', '00:35:45 ','61.134.63.252'),
('2008-08-11', '00:35:48 ','61.134.63.252'),
('2008-08-11', '00:35:57 ','61.134.63.252'),
('2008-08-11', '00:37:12 ','61.134.63.252'),
('2008-08-11', '00:37:16 ','61.134.63.252'),
('2008-08-11', '00:37:26 ','61.134.63.252'),
最大:37:26 最小:35:45 ---> 2分- 19s => 01:41呀
set nocount on
insert into @s select '2008-08-11', '00:35:45 ','61.134.63.252'
insert into @s select '2008-08-11', '00:35:48 ','61.134.63.252'
insert into @s select '2008-08-11', '00:35:57 ','61.134.63.252'
insert into @s select '2008-08-11', '00:37:12 ','61.134.63.252'
insert into @s select '2008-08-11', '00:37:16 ','61.134.63.252'
insert into @s select '2008-08-11', '00:37:26 ','61.134.63.252'
insert into @s select '2008-08-11', '00:38:09 ','61.134.63.252'
insert into @s select '2008-08-11', '00:38:18 ','61.134.63.252'
insert into @s select '2008-08-12', '00:37:26 ','61.134.63.252'
insert into @s select '2008-08-12', '00:38:09 ','61.134.63.252'
insert into @s select '2008-08-12', '00:38:18 ','61.134.63.252'
insert into @s select '2008-08-11', '00:37:26 ','61.134.63.253'
insert into @s select '2008-08-11', '00:38:09 ','61.134.63.253'
insert into @s select '2008-08-11', '00:38:18 ','61.134.63.253' select *,rowid = 0 into #
from @s a
declare @i int,@t datetime,@d datetime
set @I = 0
select top 1 @t =col2 from #
update #
set rowid = @i,@i = case when datediff(ss,@t ,col2) between 0 and 120 and col2 > @d then @I else @I + 1 end,
@t = case when datediff(ss,@t ,col2) between 0 and 120 and col2 > @d then @t else col2 end,
@d = col2select ip,convert(char(10),max(col2) -min(col2),108)
from #
group by rowid,ip
drop table #set nocount offip
-------------------- ----------
61.134.63.252 00:01:41
61.134.63.252 00:00:09
61.134.63.252 00:00:52
61.134.63.253 00:00:52
mysql 么有top 有limit,没有rowid。
create table tt1(adate date,atime time,cip varchar(20),id integer default 0 )
as ( select * from test order by cip, cast(concat_ws(' ',adate,atime) as datetime))
建立表要注意order by,我第一次就是没有注意cip
存储过程
begin
declare var1,var2 datetime;
declare var3,var4 varchar(20);
declare found boolean default true;
declare cur cursor for
select cast(concat_ws(' ',adate,atime) as datetime),cip from tt1;
declare continue handler for not found
set found=false;
select cast(concat_ws(' ',adate,atime) as datetime) ,cip into var1,var3 from tt1 limit 1;
set @i=0;
open cur;
fetch cur into var2,var4;
while found do
if (timestampdiff(second,var1,var2))<120 then
update tt1 set id=@i where cast(concat_ws(' ',adate,atime) as datetime)=var2 and cip=var4;
end if;
if (timestampdiff(second,var1,var2))>120 and var3=var4 then
set @i:=@i+1;
update tt1 set id=@i where cast(concat_ws(' ',adate,atime) as datetime)=var2 and cip=var4;
set var1=var2;
end if;
if var3!=var4 then
set @i:=@i+1;
update tt1 set id=@i where cast(concat_ws(' ',adate,atime) as datetime)=var2 and cip=var4;
set var1=var2;
set var3=var4;
end if;
fetch cur into var2,var4;
end while;
close cur;
select cip,(subtime(max(atime),min(atime)))
as timelength from tt1 group by id,cip;
end
mysql> call timelength();
+---------------+------------+
| cip | timelength |
+---------------+------------+
| 61.134.63.252 | 00:01:41 |
| 61.134.63.252 | 00:00:09 |
| 61.134.63.252 | 00:00:52 |
| 61.134.63.253 | 00:00:52 |
+---------------+------------+
十分感谢。另外如果不用游标,能否利用自连接,,感觉游标会较慢
mysql> DROP TABLE IF EXISTS tb;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE tb (col1 DATE,col2 TIME,IP VARCHAR(15));
Query OK, 0 rows affected (0.06 sec)mysql> INSERT INTO tb
-> SELECT '2008-08-11','00:35:45','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:35:48','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:35:57','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:37:12','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:37:16','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:37:26','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:38:09','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:38:18','61.134.63.252' UNION ALL
-> SELECT '2008-08-12','00:37:26','61.134.63.252' UNION ALL
-> SELECT '2008-08-12','00:38:09','61.134.63.252' UNION ALL
-> SELECT '2008-08-12','00:38:18','61.134.63.252' UNION ALL
-> SELECT '2008-08-11','00:37:26','61.134.63.253' UNION ALL
-> SELECT '2008-08-11','00:38:09','61.134.63.253' UNION ALL
-> SELECT '2008-08-11','00:38:18','61.134.63.253';
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0mysql>
mysql> -- SQL查询如下:
mysql> select col1,col2,ip into @col1,@col2,@ip from tb limit 1;
Query OK, 1 row affected (0.00 sec)mysql>
mysql> create temporary table tmp(col2 time,flag time,col1 date,ip varchar(15));
Query OK, 0 rows affected (0.02 sec)mysql> insert into tmp
-> select
-> col2,
-> @col2:=case when col1=@col1 and ip=@ip and time_format(col2,'%H:%i:00') <= time_format(sec_to_time(time_to_sec(cast(@col2 as
time))+120),'%H:%i:00')
-> then cast(@col2 as time) else cast(col2 as time) end,
-> @col1:=col1,
-> @ip := ip
-> from tb;
Query OK, 14 rows affected (0.00 sec)
Records: 14 Duplicates: 0 Warnings: 0mysql>
mysql> select
-> ip,sec_to_time(time_to_sec(max(col2))-time_to_sec(min(col2))) AS col2
-> from tmp
-> group by ip,col1,flag
-> order by ip;
+---------------+----------+
| ip | col2 |
+---------------+----------+
| 61.134.63.252 | 00:01:41 |
| 61.134.63.252 | 00:00:09 |
| 61.134.63.252 | 00:00:52 |
| 61.134.63.253 | 00:00:52 |
+---------------+----------+
4 rows in set (0.00 sec)mysql>
mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)