12.数据仓库搭建之ADS层搭建

数据仓库搭建之ADS层搭建

该数据仓库ADS层的搭建是针对需求而定的，我们会针对相关人员所提出的需要，来设计ADS层的表，以及如果ADS层很多需求的派生指标都相同的话，我们也会考虑在DWS层进一步建表先进行数据的汇聚，然后再在ADS层进行取数。

该层的设计我们不再使用列式存储，因为我们这一层的每一行数据大多都是关于某一天的相关指标，不需要进行列的聚合统计等。

1.流量主题需求

流量主题当中的需求共有两个，分别是各渠道流量统计以及路径分析。

1.1各渠道流量统计

具体的需求说明如下所示：

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

1.1.1建表语句

我们可以观察到该需求当中的这些指标的统计周期和统计粒度都是相同的，因此，我们对于这五个指标，只需要建一张表就可以了，该张表当中，每一行代表最近n日某渠道的相关指标。

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/';

1.1.2数据装载

思路分析：我们在进行该需求时，由于统计的周期有1/7/30日，统计粒度为渠道，我们可以先不考虑这些限制条件，先分析每日的访客数、会话平均停留时长 、会话平均浏览页面数 、会话总数以及跳出率，之后再加上这些限制条件即可。

我们取数的第一步，首先是确定需要从那些表当中取数。大多数情况下，我们是从DWD层和DIM层取数，而如果我们建立了指标体系，并且在DWS层有部分预聚合的结果，那么我们也可以从DWS层之间取到中间结果接着计算。

如果我们从DWD层取数，我们需要的表是流量域的页面浏览表。该表当中一行数据代表访客一次页面浏览的记录，我们通过对count(distinct mid)可以得到每日访客的人数，而对于会话相关的指标而言，我们需要先将表中的数据进行聚合层会话粒度，每一行的粒度代表一次会话，然后再求这些相关的指标即可（如何确定一次会话在之前DWS层有所提及），至此，我们便可以求出每日的访客数、会话平均停留时长 、会话平均浏览页面数 、会话总数以及跳出率。

如果我们从DWS层取数，我们需要的表是dws_traffic_session_page_view_1d表，该表是流量域会话粒度的近一日页面访问表，该表的每一行代表一次会话的记录。每行记录当中包括有用户渠道等维度信息以及该会话持续的时间以及本次会话所访问的页数。我们可以通过该表中的数据进行相关聚合，来求出上述的指标。而对于近7/30日指标，我们需要拿到dws_traffic_session_page_view_1d表中7/30个分区的数据，然后进行聚合即可。

为了保证数据的不重写以及防止小文件的产生，我们最终数据的装载语句如下所示：

insert overwrite table ads_traffic_stats_by_channel
select * from ads_traffic_stats_by_channel
union
select
    '2022-05-01' dt,
    recent_days,
    channel,
    cast(count(distinct(mid_id)) as bigint) uv_count,
    cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec,
    cast(avg(page_count_1d) as bigint) avg_page_count,
    cast(count(*) as bigint) sv_count,
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate
from dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
where dt>=date_add('2022-05-01',-recent_days+1)
group by recent_days,channel;

其中

dws_traffic_session_page_view_1d lateral view explode(array(1,7,30))tmp as recent_days

是为了将一份数据炸裂成三份，然后通过新增的recent_days列来分组，之后在每组当中计算相关的指标。

1.2路径分析

用户路径分析，顾名思义，就是指用户在APP或网站中的访问路径。为了衡量网站优化的效果或营销推广的效果，以及了解用户行为偏好，时常要对访问路径进行分析。

用户访问路径的可视化通常使用桑基图。桑基图需要我们提供每种页面跳转的次数，每个跳转由source/target表示，source指跳转起始页面，target表示跳转终到页面。

1.2.1建表语句

DROP TABLE IF EXISTS ads_page_path;
CREATE EXTERNAL TABLE ads_page_path
(
    `dt`          STRING COMMENT '统计日期',
    `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `source`      STRING COMMENT '跳转起始页面ID',
    `target`      STRING COMMENT '跳转终到页面ID',
    `path_count`  BIGINT COMMENT '跳转次数'
) COMMENT '页面浏览路径分析'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_page_path/';

1.2.2数据装载

思路分析：我们在进行该需求时，同样是先计算最近1日的数据，然后再计算最近7/30日的数据即可。

我们取数的第一步，首先是确定需要从哪些表当中取数。对应该需求，我们可以从DWD层流量域页面访问表当中取数，因为该表当中每一行的数据都有上一页id和当前页的id。我们可以从该表进行聚合即可。

但是，桑基图需要满足以下两条规则：

​ 1.不能从null开始；

​ 2.不能出现环；

为了解决上述两条规则，我们需要进一步想实现该需求的思路。

首先是针对规则1，由于不能从null开始，那么我们通过观察数据，发现当我们通过会话进行排序之后，每下一条数据的当前页id，就是上一条数据的当前页id的下一页id，因此，我们可以将当前页的id作为source，下一行数据的当前页id作为target，此处使用了lead函数：

select
    recent_days,
    page_id,
    lead(page_id,1,null) over(partition by session_id,recent_days) next_page_id
from dwd_traffic_page_view_inc lateral view explode(array(1,7,30)) tmp as recent_days
where dt>=date_add('2020-06-14',-recent_days+1)

而针对规则2，我们则是通过给每次会话当中用户访问的路径页面进行排序，然后在页面id前加上对应的序号，即解决了用户访问路径会出现环的问题：

select
    '2020-05-01' dt,
    recent_days,
    source,
    nvl(target,'null'),
    count(*) path_count
from
(
    select
        recent_days,
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target
    from
    (
        select
            recent_days,
            page_id,
            lead(page_id,1,null) over(partition by session_id,recent_days) next_page_id,
            row_number() over (partition by session_id,recent_days order by view_time) rn
        from dwd_traffic_page_view_inc lateral view explode(array(1,7,30)) tmp as recent_days
        where dt>=date_add('2020-05-01',-recent_days+1)
    )t1
)t2
group by recent_days,source,target;

为了保证数据的不重写以及防止小文件的产生，我们最终数据的装载语句如下所示：

insert overwrite table ads_page_path
select * from ads_page_path
union
select
    '2020-06-14' dt,
    recent_days,
    source,
    nvl(target,'null'),
    count(*) path_count
from
(
    select
        recent_days,
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target
    from
    (
        select
            recent_days,
            page_id,
            lead(page_id,1,null) over(partition by session_id,recent_days) next_page_id,
            row_number() over (partition by session_id,recent_days order by view_time) rn
        from dwd_traffic_page_view_inc lateral view explode(array(1,7,30)) tmp as recent_days
        where dt>=date_add('2020-06-14',-recent_days+1)
    )t1
)t2
group by recent_days,source,target;

2.用户主题需求

用户主题的需求共有五个，分别是用户变动统计、用户留存率、用户新增活跃统计、用户行为漏斗分析以及新增交易用户统计。

2.1用户变动统计

该需求包括两个指标，分别为流失用户数和回流用户数：

统计周期	指标	说明
最近1日	流失用户数	之前活跃过的用户，最近一段时间未活跃，就称为流失用户。此处要求统计7日前（只包含7日前当天）活跃，但最近7日未活跃的用户总数。
最近1日	回流用户数	之前的活跃用户，一段时间未活跃（流失），今日又活跃了，就称为回流用户。此处要求统计回流用户总数。

2.1.1建表语句

DROP TABLE IF EXISTS ads_user_change;
CREATE EXTERNAL TABLE ads_user_change
(
    `dt`               STRING COMMENT '统计日期',
    `user_churn_count` BIGINT COMMENT '流失用户数',
    `user_back_count`  BIGINT COMMENT '回流用户数'
) COMMENT '用户变动统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_user_change/';

2.1.2数据装载

思路分析：

如何求流失用户数，我们的第一想法还是从DWD层入手，我们的DWD层表当中，毫无疑问需要用到的表是用户域用户登录表，该表当中记录着每日用户登录的相关信息。我们根据定义，需要求的是在七天前登录了，而这七天都没有登录的用户。例如一个用户2022-6-12日登录了，而他之后从202