本节书摘来自华章出版社《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)
