话单数据仓库搭建（3）- 数据仓库DWS及ADS层

目标：统计当日、当周、当月活跃用户

活跃用户指的是在统计周期内由过通话记录的用户

1）建表语句

2）数据导入

以用户手机号为key进行聚合

3）查询导入结果

1）建表语句

2）数据导入

以用户手机号为key按周进行聚合

3）查询导入结果

1）建表语句

2）数据导入

以用户手机号为key按周进行聚合

3）查询导入结果

1.1.4 DWS层活跃用户加载数据脚本

1）在hadoop101的/home/hadoop/bin目录下创建脚本

在脚本中编写如下内容

2）增加脚本执行权限

3）脚本使用

4）查询结果

5）脚本执行时间

企业开发中一般在每日凌晨30分~1点

目标：当日、当周、当月活跃设备数

1）建表语句

2）导入数据

3）查询导入结果

1）在hadoop101的/home/hadoop/bin目录下创建脚本

在脚本中编写如下内容

2）增加脚本执行权限

3）脚本使用

4）脚本执行时间

企业开发中一般在每日凌晨30分~1点

5）查询导入结果

新增用户包括日新增用户、周新增用户、月新增用户。

1）建表语句

2）导入数据

用每日活跃用户表 left join 每日新增设备表，关联的条件是msisdn相等。如果是每日新增的用户，则在每日新增用户表中为null。

3）查询导入数据

1）在hadoop101的/home/hadoop/bin目录下创建脚本

在脚本中编写如下内容

2）增加脚本执行权限

3）脚本使用

4）脚本执行时间

企业开发中一般在每日凌晨30分~1点

5）查询导入结果

1）建表语句

2）导入数据

3）查询导入数据

1）在hadoop101的/home/hadoop/bin目录下创建脚本

在脚本中编写如下内容

2）增加脚本执行权限

3）脚本使用

4）脚本执行时间

企业开发中一般在每日凌晨30分~1点

5）查询导入结果

留存用户：某段时间内的新增用户（活跃用户），经过一段时间后，又继续使用应用的被认作是留存用户；

留存率：留存用户占当时新增用户（活跃用户）的比例即是留存率。

例如，2月10日新增用户100，这100人在2月11日启动过应用的有30人，2月12日启动过应用的有25人，2月13日启动过应用的有32人；

则2月10日新增用户次日的留存率是30/100 = 30%，两日留存率是25/100=25%，三日留存率是32/100=32%。

分析：假设今天是11日，要统计前1天也就是10日新增用户的留存率，公式如下：

10日新增用户的留存率= 10日的新增用户且11日活跃的 / 10日的新增用户

1）分母获取

（1）创建每日新增用户明细表

（2）通过每日新增用户明细表获取10日新增用户

2）分子获取

（1）10日新增的用户明细 join 11日活跃用户明细表=每日留存用户表

（2）对每日留存用户表count就得到了10日新增用户在11日的留存用户

3）留存率计算

（1） 10日的新增用户 且 11日活跃的用户表 与10日新增用户join，算出留存用户

1）建表语句

2）导入数据（每天计算前1天的新用户访问留存明细）

3）查询导入数据（每天计算前1天的新用户访问留存明细）

1）导入数据（每天计算前1,2,3，n天的新用户访问留存明细）

2）查询导入数据（每天计算前1,2,3天的新用户访问留存明细）

1）在hadoop101的/home/hadoop/bin目录下创建脚本

在脚本中编写如下内容

2）增加脚本执行权限

3）脚本使用

4）脚本执行时间

企业开发中一般在每日凌晨30分~1点

5）查询导入结果

1）建表语句

2）导入数据

3）查询导入数据

1）建表语句

2）导入数据

3）查询导入数据

分区表将数据组织成分区，主要可以提高数据的查询速度。

如果把一年或者一个月的日志文件存放在一个表下，那么数据量会非常的大，当查询这个表中某一天的日志文件的时候，查询速度还非常的慢，这时候可以采用分区表的方式，把这个表根据时间点再划分为小表。这样划分后，查询某一个时间点的日志文件就会快很多，因为这是不需要进行全表扫描。

Hive中的分区是根据“分区列”的值对表的数据进行粗略的划分，Hive中一个表对应一个目录，再根据分区列在这个表目录下创建子目录，每个子目录名就是分区列的名字。分区列定义与表中字段相似，但是与表中的字段无关，是独立的列。这样就加快了数据查询的速度，因为不会对这个表中进行全盘扫描了。

（1）建表语句

create table if not exists latte_d_test

(

uid string comment "用户ID",

vld_flg string comment "该条记录是否有效，1-有效，0-无效"

)

COMMENT "test表"

PARTITIONED BY (day STRING)

（2）设置分区表参数

set hive.exec.dynamic.partition = true

设置为true表示开启动态分区功能（默认为false）。

set hive.exec.dynamic.partition.mode = nonstrict

设置为nonstrict，表示允许所有分区都是动态的（默认为strict）。

（3）插入数据

insert overwrite table latte_d_test partition(day)

select uid,

'1',

'2016-10-10'

from test

---