大数据第53天—Mysql练习题12道之八-线上服务器访问日志-杨大伟
-- 有一个线上服务器访问日志格式如下（用sql答题）
-- 时间 接口 ip地址
-- 2016-11-09 11：22：05 /api/user/login 110.23.5.33
-- 2016-11-09 11：23：10 /api/user/detail 57.3.2.16
-- .....
-- 2016-11-09 23：59：40 /api/user/login 200.6.5.166
-- 求11月9号下午14点（14-15点），访问api/user/login接口的top10的ip地址
1 create table test_eight_serverlog 2 ( 3 server_time string COMMENT '时间', 4 server_api string comment '接口', 5 server_ip string COMMENT 'ip地址' 6 ) 7 row format delimited fields terminated by '\t';
1 insert into table test_eight_serverlog values ('2016-11-09 11:22:05','/api/user/login','110.23.5.33'); 2 insert into table test_eight_serverlog values ('2016-11-09 11:23:10','/api/user/detail','57.3.2.16'); 3 insert into table test_eight_serverlog values ('2016-11-09 14:59:40','/api/user/login','200.6.5.161'); 4 insert into table test_eight_serverlog values ('2016-11-09 14:22:05','/api/user/login','110.23.5.32'); 5 insert into table test_eight_serverlog values ('2016-11-09 14:23:10','/api/user/detail','57.3.2.13'); 6 insert into table test_eight_serverlog values ('2016-11-09 14:59:40','/api/user/login','200.6.5.164'); 7 insert into table test_eight_serverlog values ('2016-11-09 14:59:40','/api/user/login','200.6.5.165'); 8 insert into table test_eight_serverlog values ('2016-11-09 14:22:05','/api/user/login','110.23.5.36'); 9 insert into table test_eight_serverlog values ('2016-11-09 14:23:10','/api/user/detail','57.3.2.17'); 10 insert into table test_eight_serverlog values ('2016-11-09 14:59:40','/api/user/login','200.6.5.168'); 11 insert into table test_eight_serverlog values ('2016-11-09 14:59:40','/api/user/login','200.6.5.168'); 12 insert into table test_eight_serverlog values ('2016-11-09 14:22:05','/api/user/login','110.23.5.32'); 13 insert into table test_eight_serverlog values ('2016-11-09 14:23:10','/api/user/detail','57.3.2.13'); 14 insert into table test_eight_serverlog values ('2016-11-09 14:59:40','/api/user/login','200.6.5.164'); 15 insert into table test_eight_serverlog values ('2016-11-09 15:22:05','/api/user/login','110.23.5.33'); 16 insert into table test_eight_serverlog values ('2016-11-09 15:23:10','/api/user/detail','57.3.2.16'); 17 insert into table test_eight_serverlog values ('2016-11-09 15:59:40','/api/user/login','200.6.5.166');
1 select 2 server_ip, 3 count(*) visit_time 4 from test_eight_serverlog 5 where date_format(server_time,'yyyy-MM-dd HH')='2016-11-09 14' 6 and server_api = '/api/user/login' 7 group by server_ip 8 order by visit_time desc;
