CASE … WHEN和cast类型转换(日志案例分析应用)

先补充两个知识点：CASE … WHEN和CAST类型转换

CASE … WHEN…的用法

创建数据库

创建员工表

create table emp(

empno int comment '员工编号',

ename string comment '员工姓名',

job string comment '员工职位',

mgr int comment '领导编号',

hiredate string comment '入职时间',

sal double comment '薪资',

comm double comment '奖金',

deptno int comment '部门编号'

)row format delimited fields terminated by '\t';

将数据上传

导入数据

load data local inpath '/data/test/emp.txt' into table emp;

查看数据

其中comm有很多null值

select comm from emp;

那怎么不让comm这列显示为Null呢？

就可以使用case when，语句

select empno,

case

when comm is NULL then sal+0

else sal+comm

end

from emp;

可以加别名，进行多层判断。语句

select empno,

case

when sal< 1000 then 'low'

when sal >=1000 and sal < 3000 then 'middle'

when sal >=3000 and sal < 5000 then 'high'

else 'very high'

end

from emp;

cast类型转换

查看下表结构

我们将double类型转换为string类型

语句

create table cast_table as select empno,ename ,job ,cast(sal as string) new_sal from emp;

查看表结构

日志案例需求分析:

指标：

（1）日期：最后统计分析的时候根据日期进行分组，可以建立分区表

（2）PV：count(url)

（3）UV: count(distinct guid)

（4）登录人数：user_id 有值，可以登录

（5）游客人数：user_id 无值，非登录人员

（6）平均访问时长：每个用户登录页面之后都会产生一个session_id，统计每个session会话平均的停留时间。求到访问时长：进入页面第一条时间戳，最后离开页面的最后一条时间戳。平均访问时长：按照session_id进行分组，求到平均访问时长。

（7）二跳率：一个用户在一个session会话中，点击的页面大于等于2的次数就是二跳率。求访问页面超过2的用户，统计PV大于等于2的用户再除以总的人数。

（8）独立ip：统计ip去重

开始将数据导入到Hive中

创建数据库

Create database track_log_ip;

使用数据库

创建表

create table log_ip_source(

id string,

url string,

referer string,

keyword string,

type string,

guid string,

pageId string,

moduleId string,

linkId string,

attachedInfo string,

sessionId string,

trackerU string,

trackerType string,

ip string,

trackerSrc string,

cookie string,

orderCode string,

trackTime string,

endUserId string,

firstLink string,

sessionViewNo string,

productId string,

curMerchantId string,

provinceId string,

cityId string,

fee string,

edmActivity string,

edmEmail string,

edmJobId string,

ieVersion string,

platform string,

internalKeyword string,

resultSum string,

currentPage string,

linkPosition string,

buttonPosition string

)partitioned by(date string)

row format delimited fields terminated by '\t';

导入数据

load data local inpath '/data/test/data1' into table log_ip_source partition(date='2015082818');

数据清洗

会话信息的关键指标：

trackerU：访问渠道：通过什么方式进入到网站：收藏夹、手输网址、论坛、博客等

landing_url着陆页：用户进入网站的第一个页面，需要获取第一条记录，分析同一个session会话中的第一个页面。

Landing_url_ref着陆页之前的页面：需要获取第一条记录

创建会话信息表

create table session_info(

session_id string ,

guid string ,

trackerU string ,

landing_url string ,

landing_url_ref string ,

user_id string ,

pv string ,

stay_time string ,

min_trackTime string ,

ip string ,

provinceId string

)partitioned by (date string)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

加载数据

针对每个会话进行一个group by sessionId

由于sessionid分组之后会得到多条记录 ,那么就是对于每个session里面统计pv

创建临时表

create table session_tmp as

select

sessionId session_id,

max(guid) guid,

max(endUserId) user_id,

count(distinct url) pv,

(unix_timestamp(max(trackTime)) - unix_timestamp(min(trackTime)) ) stay_time,

min(trackTime) min_trackTime,

max(ip) ip,

max(provinceId) provinceId

from log_ip_source where date='2015082818'

group by sessionId;

这三个字段

trackerU，landing_url，landing_url_ref

（1）从源表中获取每一条记录的trackerU，landing_url，landing_url_ref

（2）从源表中获取每一条记录的时间

（3）然后进行最小时间与源表当中最小时间的join，获取到trackerU，landing_url，landing_url_ref

创建第二张临时表

create table track_tmp as

select

sessionId session_id,

trackTime trackTime,

url landing_url,

referer landing_url_ref,

trackerU trackerU

from log_ip_source

where date='2015082818';

Join连接

insert overwrite table session_info partition(date='2015082818')

select

a.session_id session_id,

max(a.guid) guid,

max(b.trackerU) trackerU,

max(b.landing_url) landing_url,

max(b.landing_url_ref) landing_url_ref,

max(a.user_id) user_id,

max(a.pv) pv,

max(a.stay_time) stay_time,

max(a.min_trackTime) min_trackTime,

max(a.ip) ip,

max(a.provinceId) provinceId

from session_tmp a join track_tmp b on

a.session_id = b.session_id and a.min_trackTime = b.trackTime

group by a.session_id;

数据分析

语句

create table result as

select

date date,

sum(pv) pv,

count(distinct guid) guid,

count( distinct case when user_id is not null then guid else null end ) login_user,

count( distinct case when user_id is null then guid else null end ) visitor,

avg(stay_time) avg_time,

(count(case when pv>2 then session_id else null end)/count(session_id) ) session_jump,

count(distinct ip) ip

from session_info where date='2015082818'

group by date;

查看下结果

这个地方有问题，游客人数为0

重新写语句

create table qw as

select

date date,

sum(pv) PV,

count(distinct guid) UV,

count(distinct case when length(user_id)!=0 then guid else null end) login_user,

count(distinct case when length(user_id)=0 then guid else null end) visitor,

avg(stay_time) avg_time,

(count(case when pv>=2 then session_id else null end)/count(session_id)) second_jump,

count(distinct ip) IP

from session_info

where date='2015082818'

group by date;

查看结果