每天分享一个sql，帮助大家找到sql的快乐

需求

有一张表，其中一个字段是由时间、接口、ip和其他字段组成的
求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址

建表语句

create table log(
    log_detail string
)
ROW format delimited FIELDS TERMINATED BY "\t"
;

数据

insert overwrite table log values
("2016-11-09 14:22:05 /api/user/login 110.23.5.33 a *"),
("2016-11-09 11:23:10 /api/user/detail 57.3.2.16 a *"),
("2016-11-09 14:59:40 /api/user/login 200.6.5.166 a *"),
("2016-11-09 14:22:05 /api/user/login 110.23.5.34 a *"),
("2016-11-09 14:22:05 /api/user/login 110.23.5.34 a *"),
("2016-11-09 14:22:05 /api/user/login 110.23.5.34 a *"),
("2016-11-09 11:23:10 /api/user/detail 57.3.2.16 a *"),
("2016-11-09 23:59:40 /api/user/login 200.6.5.166 a *"),
("2016-11-09 14:22:05 /api/user/login 110.23.5.34 a *"),
("2016-11-09 11:23:10 /api/user/detail 57.3.2.16 a *"),
("2016-11-09 23:59:40 /api/user/login 200.6.5.166 a *"),
("2016-11-09 14:22:05 /api/user/login 110.23.5.35 a *"),
("2016-11-09 14:23:10 /api/user/detail 57.3.2.16 a *"),
("2016-11-09 23:59:40 /api/user/login 200.6.5.166 a *"),
("2016-11-09 14:59:40 /api/user/login 200.6.5.166 a *"),
("2016-11-09 14:59:40 /api/user/login 200.6.5.166 a *");

实现

select
    t2.interface,
    t2.ip,
    t2.ip_visit_count,
    row_number() over( order by t2.ip_visit_count desc) as rk
from
    (select
        t1.interface,
        t1.ip,
        count(*) as ip_visit_count
    from
        (select
            split(log_detail," ")[0] as rq,
            split(log_detail," ")[1] as hour,
            split(log_detail," ")[2] as interface,
            split(log_detail," ")[3] as ip
        from
            log
        )t1
    where t1.interface = "/api/user/login"
        and date_format(t1.rq,"yyyy-MM-dd")= "2016-11-09" 
        and substring(t1.hour,0,2) >= 14
        and substring(t1.hour,0,2) <= 15
    group by t1.interface,t1.ip
    limit 10
    )t2
;

结果

Total MapReduce CPU Time Spent: 7 seconds 110 msec
OK
t2.interface	t2.ip	t2.ip_visit_count	rk
/api/user/login	110.23.5.34	4	1
/api/user/login	200.6.5.166	3	2
/api/user/login	110.23.5.33	1	3
/api/user/login	110.23.5.35	1	4
Time taken: 48.843 seconds, Fetched: 4 row(s)

分析

1、分析需求，需要时间、接口、ip三个字段
2、观察字符串的格式，选择合适的分割符切割
3、使用split函数切割字符串，将一列转换为三列
4、根据ip分组，统计每个ip的个数，这里group by时，需要group by t1.interface,t1.ip这样写
5、全局排序，需要开窗，开窗时不需要指定分区键，即不需要partition by
6、如下扩展对观察数据、切割、取数，需要注意，明确每一步操作的结果是什么

扩展

#split切割、数组取数
select
	split(log_detail," ")[0] as time,
    split(log_detail," ")[1] as interface,
    split(log_detail," ")[2] as ip
from
  	log
OK
#这里结果并不对，观察数据，切割会将时间切成两部分，所以如上实现部分sql是正确的
#结果
time	interface	ip
2016-11-09	14:22:05	/api/user/login
2016-11-09	11:23:10	/api/user/detail
2016-11-09	14:59:40	/api/user/login
2016-11-09	14:22:05	/api/user/login
2016-11-09	14:22:05	/api/user/login
2016-11-09	14:22:05	/api/user/login
2016-11-09	11:23:10	/api/user/detail
2016-11-09	23:59:40	/api/user/login
2016-11-09	14:22:05	/api/user/login
2016-11-09	11:23:10	/api/user/detail
2016-11-09	23:59:40	/api/user/login
2016-11-09	14:22:05	/api/user/login
2016-11-09	14:23:10	/api/user/detail
2016-11-09	23:59:40	/api/user/login
2016-11-09	14:59:40	/api/user/login
2016-11-09	14:59:40	/api/user/login
Time taken: 0.3 seconds, Fetched: 16 row(s)

where date_format(t1.time,"yyyy-MM-dd HH") >= concat(year(current_date()),"-","11-09 14")
          and
          date_format(t1.time,"yyyy-MM-dd HH") <= concat(year(current_date()),"-","11-09 15")
          and
          ip = "/api/user/login"
          
#关于排序
因为最后需要全局排序，topn序号，那么如何选择排序时机

#测试一（没有达到预期的结果）
在group by时对ip_visit_count 降序排序，这时也是全局排序，但是在row_number()Over()开窗时，默认是升序
hive (default)> select
              >     t2.interface,
              >     t2.ip,
              >     t2.ip_visit_count,
              >     row_number() over() as rk
              > from
              >     (select
              >         t1.interface,
              >         t1.ip,
              >         count(*) as ip_visit_count
              >     from
              >         (select
              >             split(log_detail," ")[0] as rq,
              >             split(log_detail," ")[1] as hour,
              >             split(log_detail," ")[2] as interface,
              >             split(log_detail," ")[3] as ip
              >         from
              >             log
              >         )t1
              >     where t1.interface = "/api/user/login"
              >         and date_format(t1.rq,"yyyy-MM-dd")= "2016-11-09" 
              >         and substring(t1.hour,0,2) >= 14
              >         and substring(t1.hour,0,2) <= 15
              >     group by t1.interface,t1.ip
              >     order by ip_visit_count desc
              >     limit 10
              >     )t2
              > ;

Total MapReduce CPU Time Spent: 7 seconds 100 msec
OK
t2.interface	t2.ip	t2.ip_visit_count	rk
/api/user/login	110.23.5.33	1	1
/api/user/login	110.23.5.35	1	2
/api/user/login	200.6.5.166	3	3
/api/user/login	110.23.5.34	4	4

还有其他测试情况，篇幅有限，就不展示了

总结一下：
1、第一次排序和最终排序字段不冲突是可以的
2、如果第一次排序和最终排序实现目标一致，那么最后排序即可

知识点

1、split(字符串，分割符)：使用分割符切割字符串，返回一个数组
2、row_number()：row_number是基于over()开窗函数的一个不重复的序号，如上结果所示，即便结果相同，也会顺延,序号自增
3、substring(字符串，起始位置，步长):根据起始位置和步长切割字符串

?