大数据第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 '	';

 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;