开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： 网站流量日志分析--统计分析--受访分析热门页面 topN】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/696/detail/12237
网站流量日志分析--统计分析--受访分析热门页面 topN
内容介绍：
一、各页面访问统计
二、热门页面统计
一、各页面访问统计
1、统计分析中的受访分析，受访指的是受到访问，网站哪个页面被别人浏览，因此它从页面角度开展分析。主要是针对数据中的 request 进行统计分析，比如各页面 PV，各页面 UV 等。
2、打开数据，跟受访相关的字段叫 request，字段所描述的数据就是指本次请求，请求的是哪个资源，哪个页面，如果想要更加精准统计需要把里面请求图片的过滤掉，如果没有请求斜杠表示首页。根据页面受访分析，也可以开展各种各样的的指标分析，比如各页面相关的基础指标统计，比如网站只有首页和两个详情页，统计今天到网站中三个页面到底每个页面分别产生的 pv 多少，uv 多少，统计pv根据页面进行分组，分组完之后统计次数，count 即可。如果统计 uv 就是统计的去重ip，以上指标无非就是根据页面的字段 group by。因为在这种情况下，页面就是分组维度，例如:
统计各页面 pv，明确指标忽略静态资源的影响，就是不管请求图片还是请求什么都当做请求页面，请求页面的pv，需要根据 request 进行分组，相同的到同一组，比如请求的是 git3图片，另外如果是同样的图片，就属于同一个页面，根据页面分组，分组维度就是 request，分完组之后进行 count 统计，统计出来就是每个页面的pv，uv，如果 count request 统计就是 pv，如果 count 去重的 ip 就是 uv。
select request as request,count(request) as request counts from
ods_ weblog detail group by request having request is not null order by request counts desc limit 20;
二、热门页面统计
1、统计每日最热门的页面 top10
drop table dw. hotpages everyday;
create table dw. hotpages. everyday(day string,url string,pvs string);
insert into table dw hotpages, everyday
select '201 30918',a.request,a.request counts from
(select request as request,count(request) as request. counts from ods. weblog, detail where datestr= '20181101'
group by request having request is not null) a
order by a.request counts desc limit 10;
2、梳理，出现 top n 问题，想到 Over 分组函数，或者窗口函数。
（1）数据表：dw_weblog_detail
每天的页面根据天气分组，根据的页面进行统计，统计完之后，按照标号去记，从需求上没有会话相关的概念，明显可以排除跟点击流相关的表，因此需要是 detail宽表。
（2）分组维度：时间维度(day) day 比较特殊还是表的分区字段、页面维度(request)
天除了是分组字段以外，宽表是分区表，1号2号3号哪一天还是分区字段，特殊就使得计算指标的时候，可以采用更加方便的形式。最热门页面top10，有a页面，b页面，d页面，c页面，根据不同页面进行分组，所以还有第二个维度是页面维度。
（3）度量值：count(*)--->top10
3、打开画图板，画一个宽表，它是一个分区表，分区维度就是天，20181101，当下数量数据等于20181101随着数仓引进值，2号，3号，4号，20181102、20181103、20181104，当下场景想到row_number分组窗口函数，可以根据天进行分组，pvs 进行排序，取前几个就是top几，思路正确，但是在当下需求中，天非常的特殊。统计每一天，不需要 group by 就可以找到每一天，因为天是分区字段。
突破: day是表的分区字段通过 where 分区查询即可过滤出指定的day内容，
需要统计20181101，datestr=20181101即可，再根据 request 的页面进行分组统计，统计完之后，取当中第十个就非常方便。
4、写 sql
起别名，分区过滤，保证拿到的是这一天，再次强调在序列中没有考虑静态资源的问题，忽略它里面是图片还是 gs 都把它看成页面。注意跟现实实际需求的区别，根据它进行分组完成之后，在每个组内进行count统计，起别名，如果想知道是哪个页面，也可以把 t.request 写上。计算没问题，但是拿到的是所有的页面，所有的结果。在数据中有些 request 为空，所以进行过滤，保证当中有空的不参加分组，要的不是整体的热门页面，而是所有热门页面中 top10最多的前十个，把所有页面请求数做倒序排序，只取前十条，就是 top10的问题，排序完成之后它只要热门页面前十个或者热门页面前五个，查询结果中指定返回的条数非常方便的语法是 limit 语法。这种方式计算比 row_number 方式计算更加方便。整个问题的突破点在于时间维度的天，它是分区字段，如果统计每个小时热门页面，那么就要使用 row_number 函数进行统计。
select
t. request,count(*) as pvs
from dw. _weblog detail t where t.datestr ="20181101" group by t. request having
t.request is not null order by pvs desc limit 10;
复制指标执行，20181101热门页面最多的是斜杠首页，比较符合客观规律，一家公司如果不出意外访问最多的的页面应该是首页，如果要贴近实战，过滤静态资源，要再加一个条件，where=true，这是统计每天最热门页面时最佳简便的一种方式，结合数据做考虑，不要死板的套用某些语法工具。
5、保存数据结果，打开的参考资料，项目资料，点击数据分析，点击受访分析。
（1）创建表为 dw_ hotpages_ everyday，每天的热门页面，里面有字段天，url 就是 request，pvs。首先创建临时表，或者中间表。
create table dw_ hotpages_ everyday (day string,url string,pvs string)
（2）把查询语句结果进行 insert 插入，嵌套查询，把查询的返回结果，做嵌套起个别名叫做 a，把 a 表中只取前十条插入到创建的表中，相当于可以把数据结果保存方便后面的使用。
insert into table dw_ hotpages_ everyday
select ' 20181101' ,a. request,a. request_ counts from
(select request as request, count (request) as request_ counts from dw_ weblog_ detail where datestr='20181101' group by request having request is not null ) a
order by a. request_ counts desc limit 10 ;
（3）复制语句，打开 hive 终端进行执行，创建完之后就可以打开创建表，验证数据是否被保存。
Select from dw_hotpages_everyday
可以发现的数据已经被保存，在页面上进行可视化展示报表将会非常方便，还可以做其他力度的精准控制，每小时每月每天每分每秒，如果有需要就可以进行 group by 分组统计，这是受访页面的统计分析。
