《Greenplum企业应用实战》一3.2　日志分析

本节书摘来自华章出版社《Greenplum企业应用实战》一书中的第3章，第3.2节，作者 何勇 陈晓峰，更多章节内容可以访问云栖社区“华章计算机”公众号查看

3.2　日志分析

日志分析是网站分析的基础，通过对网站浏览的日志进行分析，可以为网站优化提供数据支持，了解用户群以及用户浏览特性，对改进网站体验，提升流量有非常重要的意义。下面将通过Greenplum实现一个简单的网站浏览日志的分析。

3.2.1　应用场景描述

分析全网站每分钟的PV、UV，并导出到Excel中，画出折线图。解析URL，获取URL中的参数列表。通过URL取得member_id，然后统计当天浏览次数的用户分布，如浏览次数在1～5、6～10、11～50、51～100以及100次以上的这五个区间段分别有多少个用户。

3.2.2　数据Demo

为了简单起见，笔者对数据进行了一些预处理，只保留了几个字段，建表语句及字段描述如下：

DROP TABLE IF EXISTS log_path; CREATE table log_path( log_time timestamp(0) --浏览时间 ,cookie_id varchar(256) --浏览的cookie_id ,url varchar(1024) --浏览页面的url ,ip varchar(64) --用户ip ,refer_url varchar(1024) --来源的url，这里只保留域名 )distributed by(cookie_id);

Demo数据如下：

testDB=# select * from log_path limit 1; -[ RECORD 1 ]------------------------------------------------ log_time | 2012-07-14 23:44:58 cookie_id | 119.187.6.228.1337696430725.8 url | /china.alibaba.com/ims/chat_card_60.htm?member_id=scutshuxue&cssName=default ip | 119.178.198.222 refer_url | www2.im.alisoft.com

3.2.3　日志分析实战

PV、UV分布cookie_id可以视为唯一的用户标识，故UV可视为去重后的cookie_id数。SQL如下： SELECT TO_CHAR(log_time,'yyyy-mm-dd HH24:mi:00') ,COUNT(1) pv ,COUNT(DISTINCT cookie_id) uv FROM log_path GROUP BY 1 ORDER BY 1;

这里只是较少的样例数据，结果如下：

testDB=# select * from log_pv_uv_result; log_time | pv | uv ---------------------+------+------ 2012-07-14 23:01:00 | 4758 | 1699 2012-07-14 23:45:00 | 552 | 257 2012-07-14 23:03:00 | 1656 | 712 2012-07-14 23:34:00 | 5554 | 1878 2012-07-14 23:04:00 | 3504 | 1325 2012-07-14 23:00:00 | 12 | 6 2012-07-14 23:44:00 | 4498 | 1540 2012-07-14 23:33:00 | 4 | 2 (8 rows) 将数据导出成csv格式，在Excel中展现，

Copy命令的语法如下：

testDB=# copy log_pv_uv_result to '/tmp/log_pv_uv.csv' csv; COPY 8

在Excel中打开并画图，结果如图3-6所示。

解析URL参数解析URL，是指通过substring对URL进行正则表达式匹配，将域名取出，例如对于下面这个URL： http://page.china.alibaba.com/others/feedbackfromalitalk.html

正则表达式\w+://([\w.]+)可以将域名匹配出来。同样的，可以将参数后面关键字（member_id或memberId）的值获取出来，作为字段member_id。split_part函数可以将字符串按照某个字符串分割，然后获取其中一个子串。regexp_split_to_array函数可以将字符串按照某个字符串分割，然后转换为数组变量。

DROP TABLE IF EXISTS log_path_tmp1; CREATE TABLE log_path_tmp1 AS SELECT log_time ,cookie_id ,substring(url,E'\\w+://([\\w.]+)') AS host ,split_part(url,'?',1) AS url ,substring(url,E'member[_]?[i|I]d=(\\w+)') AS member_id ,regexp_split_to_array(split_part(url,'?',2),'&') AS paras ,ip ,refer_url FROM log_path DISTRIBUTED BY (cookie_id);

数据Demo的样例数据解析后结果如下：

testDB=# select * from log_path_tmp1 where member_id='scutshuxue' limit 1; -[ RECORD 1 ]-------------------------------------------- log_time | 2012-07-14 23:44:58 cookie_id | 119.187.6.228.1337696430725.8 host | china.alibaba.com url | http://china.alibaba.com/ims/chat_card_60.htm member_id | scutshuxue paras | {member_id=scutshuxue,cssName=default} ip | 119.178.198.222 refer_url | www2.im.alisoft.com 用户浏览次数区间分析要计算浏览次数的分布，首先按照cookie_id做聚合，计算出每个cookie_id的浏览次数，之后再用case when对数据进行分区，再聚合，SQL如下： SELECT CASE WHEN cnt>100 THEN '100+' WHEN cnt>50 THEN '51-100' WHEN cnt>10 THEN '11-50' WHEN cnt>5 THEN '6-10' ELSE '<=5' END tag ,COUNT(1) AS NUMBER FROM ( SELECT cookie_id,COUNT(1) cnt FROM log_path_tmp1 GROUP BY 1 )t GROUP BY 1;

结果如下：

tag | number -------+-------- 6-10 | 440 11-50 | 126 <=5 | 6501 (3 rows) 相关资源：敏捷开发V1.0.pptx