《Greenplum企业应用实战》一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　日志分析实战

1. 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所示。

1. 解析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

1. 用户浏览次数区间分析
   要计算浏览次数的分布，首先按照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)