开发者学堂课程【大数据分析之企业级网站流量运营分析系统开发实战（第四阶段）： 网站流量日志分析--统计分析--多维统计分析--来访 referer、时间维度】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/696/detail/12223
网站流量日志分析--统计分析--多维统计分析--来访 referer、时间维度
1、按照 referer 来访和时间维度进行相关指标统计分析，涉及到两个维度的聚集，一个是 referer，人从哪里来，来访的维度，一个是时间维度，看具体的指标。
2、--统计每小时各来访 url 产生的 pv 量，对指标进行梳理，确定维度，数据表，分组字段。
3、梳理
按照来访维度、时间维度分析
统计每/时各来访 url 产生的 pv 量，当中每和各都是敏感的字眼，从分组维度分组字段的基础上小时和 url 是分组字段。
数据表：dw_ weblog_ detail
每小时来访 url 这里不涉及 session 会话的概念，排除点击流模型表，优先考虑 detail 事实宽表。
分组字段：时间维度、来访 referer（url）
到数据中看是否有和来访相关的字段，打开数据，在 detail 表中浏览，前面是时间，后面有字段是 http referer，而且 referer 还做了预处理，把里面 host 和 path 做了提取，referer 所表明的是从哪个网站哪个链接哪个页面，可能来自于同一家公司网站，但是来自于不同的页面，因此 referer 字段是确实存在的。
度量值：count（）
count* 统计 pv，countdistinctip 统计 uv，指标都是互通的，以 pv 做相关的例子。
select
count(*) as pvs分完组之后，每个组做一个 count 统计
from dw_weblog_ detail t where t . datestr ="20181101" group byt.hour ,t.http_ referer;宽表，起别名，分区过滤，通过这一条件拿到今天的结果，进行分组，注意有两个维度，顺序对结果没有影响，只不过影响执行的过程，refereru 如何写还要看表中是如何表示的。
把表保存到临时表中。
4、打开 sql 脚本中，把今天的结果创建一个表保存下来，表名是 dw_ pvs_ referer_ everyhour，每小时来访的 referer 所 产生的 pvs，需要 referer_url，referer_host，month，day，hour，pvs 字段，跟来访维度相关的有两个referer_url，referer_host，跟时间相关的有 month，day，hour，最后一个是 pvs统计，在查询时需要返回这些字段。如果查询 count(*) ，只满足最后一个字段，url 是标准的格式，host 是它当中的一部分，既要取得 referer_url 又要取得 host，需求是根据 referer_url 进行分组的，要想出线 host，必须出现在分组段中，它出现是否会影响结果。
select
t.http_ referer ,t.ref _host,count(*) as pvs
from dw_ weblog_ detail t where t.datestr ="20181101" group by
t.hour ,t.http_ referer，t.ref_ host;
要想让它出现在这里，要如何解决它不是分组字段的问题，现在执行会报错，非法引用错误，把 host 字段作为分组字段存在，需要自己确定是否会对结果产生影响。来自同一个网站同一个 host 不一定来自于同一页面，需求是根据 referer 进行分组的，来自于同一个页面同一个路径下当中的 host100%一样，因此 host 出现不会改变最后的分组条件，因为 t.http_ referer，t.ref_ host两个条件是包含关系，最终起决定作用的是 referer 一样，同一个 referer 中 host 一样，反过来则不是，为了前面能出现这个字段，所以把 host 作为分组字段。除了 referer 维度，还有时间维度，month，day 出现在分组中也不会影响结果，分区过滤时指定的数据一定是 20181101，group by 接 month，day 都不会影响，根据 hour 分才会起决定作用。
5、回到 sql 脚本中。
group by http_referer, ref_host , month , day , hour，前面两个字段属于来访维度，后面三个字段属于时间维度，因为 referer 中包含 host，所以起决定作用的是 referer，referer 一样，host 一定一样，时间维度中起决定作用的是hour，month 通过分区过滤一定相同，所以这就保证了最终返回结果中可以出现month 以及 host，但又不改变最终结果，在做数据处理时发现，有些 referer 是丢失的字段，它所取得的 host 就是空的结果，在业务中可能需要把空的排除出去进行过滤，用 having 做分组过滤，把 refhostisnotnull 留下来，排除下来就是为空的，最后进行排序，是否排序根据需求定，比如统计完之后根据它的倒序排序，时间正序排序，满足结果。
from dw weblog_ detail
group by http_ referer,ref host , month , day , hour
having ref host is not null
order by hour asc,day asc , month asc,pv_ referer_ cnt desc ;
6、把 sql 进行保存，创建中间临时表 pvs referer everyhour ，复制建表语句，打开 hive 终端做执行，使用 insert 加 select 语句，把分析的所选择的sql语句插入进去，插入到创建的 pvs referer everyhour 中，分区是20181101，从宽表中根据时间维度进行分组，分组完把为空的过滤排序，把排序结果插入到表中，保证查询返回的字段跟待插入一致，第一个字段是 referer 对应 url，第二个字段是 host 对应host，month，hour，pv一一对应插入到数据就不会有错。
7/执行，没有过多的难度，要心细，思路要清晰，确定在众多的分组字段中哪一个是起最终决定的，查看结果。Select from dw_ pvs_ referer everyhour limit 10；把反馈的结果在终端进行复制，打开新的 notepad++页面粘贴，就可以看到相关统计，为了看的更清楚，可以改为 select * from dw_pvs_referer_everyhour limit 20，把结果复制出来，
11月2号0点当中产生最多的是26.21.20等倒序排序，还有1点，2点3点等每一个来访，
如上图是来访的 url 和来访的 host，来自同一网站叫 blog. fens .me，但是可能来自于不同的资源不同的路径，反之如果来自同一个路径，一定来自同一个网站，url包含 host，url 一样，host 一样，反之 host 一样，url 不一定一样，区分包含关系对分组的影响，根据来访的 referer，时间维度两个聚集统计相关的 pv 值，把当中的 pv count 作为 count ip，就是统计 uv 值，来访的每小时 url 产生 pv 量。
