6.5 离线数仓—ADS层流量主题需求的实现

---

前言

前面完成了DWS层的设计和开发，下面进行ADS层流量主题的设计和开发。

一、ADS层设计

1.分区规划

ADS层没有进行分区规划，因为ADS层一天产生的数据量比较小，如果按天进行分区，会产生很多个小文件。

2.数据存储

ADS层没有进行列式存储和数据压缩。
列式存储的方式更方便于只查某几列的情况，这里到时候要导出到数据库中，一行一行导出，所以采用列式存储没有什么意义。
数据压缩一般在数据量较大的情况下使用，这里数据量很小，没有必要采用数据压缩。

二、各渠道流量统计

1.需求说明

统计周期	统计周期	统计粒度	指标	说明
需求1	最近1/7/30日	渠道	访客数	统计访问人数
需求2	最近1/7/30日	渠道	会话平均停留时长	统计每个会话平均停留时长
需求3	最近1/7/30日	渠道	会话平均浏览页面数	统计每个会话平均浏览页面数
需求4	最近1/7/30日	渠道	会话总数	统计会话总数
需求5	最近1/7/30日	渠道	跳出率	只有一个页面的会话的比例

2.需求分析

五个需求通用字段：渠道、日期范围
需求1额外字段：访客人数（通过mid获取）
需求2额外字段：会话平均停留时长
需求3额外字段：会话平均浏览页面数
需求4额外字段：会话总数
需求5额外字段：跳出率（浏览单个页面次数/浏览多个页面次数）

3.建表语句

根据需要的字段，得到建表语句：

DROP TABLE IF EXISTS ads_traffic_stats_by_channel;
CREATE EXTERNAL TABLE ads_traffic_stats_by_channel
(
    `dt`               STRING COMMENT '统计日期',
    `recent_days`      BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `channel`          STRING COMMENT '渠道',
    `uv_count`         BIGINT COMMENT '访客人数',
    `avg_duration_sec` BIGINT COMMENT '会话平均停留时长，单位为秒',
    `avg_page_count`   BIGINT COMMENT '会话平均浏览页面数',
    `sv_count`         BIGINT COMMENT '会话数',
    `bounce_rate`      DECIMAL(16, 2) COMMENT '跳出率'
) COMMENT '各渠道流量统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_traffic_stats_by_channel/';

4.需求实现

4.1初始思路

先不考虑统计的是几天的数据，假设先统计1天内的数据，我们要先通过所需要的字段找出要从哪张表里获取数据。
首先，dwd层的流量域页面浏览事务事实表里面完全可以拿到mid、channel、session_id、during_time所有的字段；其次，dws层中的流量域会话粒度页面浏览最近1日汇总表也可以拿到所有的字段，且数据量大大减少，因此采用dws__traffic_session_page_view_1d这张表。

4.2初始思路实现

select
  dt,
  channel,
  count(distinct(mid_id)),
  avg(during_time_1d)/1000,
  avg(page_count_1d),
  count(*),
  count(if(page_count_1d=1),1,null