数仓（十六）从0到1简单搭建加载数仓DWT层

畅谈Fintech 2022-01-06

2374

上一节我们讲解了数仓DWS层的搭建、解析、加载。DWS层是计算当日各维度对象当日汇总统计数据。DWS层针对不同的主题，对各个事实表的计算聚合值，对不同的各个主题的加工和使用。

这节我们讲解数仓DWT层的搭建、解析和加载。其实这两层建模完全可以构建放到一层里面做加工和处理。但是为了更好的复用，我们分为两层。

一、DWT层概念回顾

在第四章我们对DWT层做了概述，现在我们再来回顾一下。

DWT(Data Warehouse Topic)

以分析的主题对象为建模驱动，基于上层的应用和产品的指标需求，构建主题对象的全量宽表。简单描述就是按照维度来决定分析者的角度，如：某用户从注册登录后下了多少订单、近30天、60天、90天的订单数据、支付数据、退款数据、评论数据、收藏数据等等。

二、DWT层思路分析

这层涉及的主题和DWS层一样包括：访客主题、用户主题、商品主题、优惠券主题、活动主题、地区主题等。只不过DWS层的粒度是对当日用户汇总信息，而DWT层是对截止到当日、或者近7日、近30日等的汇总信息。

这里我们拿用户主题这个来举列：

DWS层：用户主题层是记录某一个用户在某一天的汇总行为。

DWT层：用户主题层是记录某一个用户截止在当日的汇总行为。

1、统计字段

和DWS层一样，首先我们看一下关于用户主题表，我们需要统计或者说加工的指标字段。一般情况下DWT层指标是DWS层的好几倍。这里我们先列举登录维度的指标字段。一共是10个，而DWS层登录维度的指标只有1个。

  `login_date_first` STRING COMMENT '首次活跃日期',
  `login_date_last` STRING COMMENT '末次活跃日期',
  `login_date_1d_count` STRING COMMENT '最近1日登录次数',
  `login_last_1d_day_count` BIGINT COMMENT '最近1日登录天数',
  `login_last_7d_count` BIGINT COMMENT '最近7日登录次数',
  `login_last_7d_day_count` BIGINT COMMENT '最近7日登录天数',
  `login_last_30d_count` BIGINT COMMENT '最近30日登录次数',
  `login_last_30d_day_count` BIGINT COMMENT '最近30日登录天数',
  `login_count` BIGINT COMMENT '累积登录次数',
  `login_day_count` BIGINT COMMENT '累积登录天数',

2、首日计算加载思路

首日加载的思路是：

如下图：DWS层2021年5月1日的数据、5月2日、5月3日的各个维度的对象的汇总统计数据，需要全部汇集到DWT层2021年5月3日的数据里面。

3、每日计算加载策略

每日加载的思路是：

如下图：DWT层2021年5月4日的数据里面有两部分：一个是DWT层5月3日的数据，一部分是DWS层2021年5月4日的当日的汇总数据，这两部分需要全部汇集到DWT层2021年5月4日的数据里面。

三、DWT层用户主题指标解析

用户主题的指标分为几大类：登录类指标、订单类指标、退单类指标、支付指标、优惠券指标、购物车、评论、收藏等等。

我们主要关注主题的

首次、末次登录、购买、支付、退款等；
最近1日、7日、30日各个主题；
累积登录、次数、支付、退款等；

1、登录类指标

`login_date_first` STRING COMMENT '首次活跃日期',
`login_date_last` STRING COMMENT '末次活跃日期',
`login_date_1d_count` STRING COMMENT '最近1日登录次数',
`login_last_1d_day_count` BIGINT COMMENT '最近1日登录天数',
`login_last_7d_count` BIGINT COMMENT '最近7日登录次数',
`login_last_7d_day_count` BIGINT COMMENT '最近7日登录天数',
`login_last_30d_count` BIGINT COMMENT '最近30日登录次数',
`login_last_30d_day_count` BIGINT COMMENT '最近30日登录天数',
`login_count` BIGINT COMMENT '累积登录次数',
`login_day_count` BIGINT COMMENT '累积登录天数'

SQL实现

2021-05-01
select
    id,
    login_date_first,--以用户的创建日期作为首次登录日期
    nvl(login_date_last,date_add('2021-05-01',-1)),--若有历史登录记录，则根据历史记录获取末次登录日期，否则统一指定一个日期
    nvl(login_last_1d_count,0),
    nvl(login_last_1d_day_count,0),
    nvl(login_last_7d_count,0),
    nvl(login_last_7d_day_count,0),
    nvl(login_last_30d_count,0),
    nvl(login_last_30d_day_count,0),
    nvl(login_count,0),
    nvl(login_day_count,0),
from
(
    select
        id,
        date_format(create_time,'yyyy-MM-dd') login_date_first
    from dim_user_info
    where dt='9999-99-99'
)t1
left join
(
    select
        user_id user_id,
        max(dt) login_date_last,
        sum(if(dt='2021-05-01',login_count,0)) login_last_1d_count,
        sum(if(dt='2021-05-01' and login_count>0,1,0)) login_last_1d_day_count,
        sum(if(dt>=date_add('2021-05-01',-6),login_count,0)) login_last_7d_count,
        sum(if(dt>=date_add('2021-05-01',-6) and login_count>0,1,0)) login_last_7d_day_count,
        sum(if(dt>=date_add('2021-05-01',-29),login_count,0)) login_last_30d_count,
        sum(if(dt>=date_add('2021-05-01',-29) and login_count>0,1,0)) login_last_30d_day_count,
        sum(login_count) login_count,

其他主题实现的SQL请参考上述主题，自行思考处理。

2、订单类指标

    `order_date_first` STRING COMMENT '首次下单时间',
    `order_date_last` STRING COMMENT '末次下单时间',
    `order_last_1d_count` BIGINT COMMENT '最近1日下单次数',
    `order_activity_last_1d_count` BIGINT COMMENT '最近1日订单参与活动次数',
    `order_activity_reduce_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日订单减免金额(活动)',
    `order_coupon_last_1d_count` BIGINT COMMENT '最近1日下单用券次数',
    `order_coupon_reduce_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日订单减免金额(优惠券)',
    `order_last_1d_original_amount` DECIMAL(16,2) COMMENT '最近1日原始下单金额',
    `order_last_1d_final_amount` DECIMAL(16,2) COMMENT '最近1日最终下单金额',
    `order_last_7d_count` BIGINT COMMENT '最近7日下单次数',
    `order_activity_last_7d_count` BIGINT COMMENT '最近7日订单参与活动次数',
    `order_activity_reduce_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日订单减免金额(活动)',
    `order_coupon_last_7d_count` BIGINT COMMENT '最近7日下单用券次数',
    `order_coupon_reduce_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日订单减免金额(优惠券)',
    `order_last_7d_original_amount` DECIMAL(16,2) COMMENT '最近7日原始下单金额',
    `order_last_7d_final_amount` DECIMAL(16,2) COMMENT '最近7日最终下单金额',
    `order_last_30d_count` BIGINT COMMENT '最近30日下单次数',
    `order_activity_last_30d_count` BIGINT COMMENT '最近30日订单参与活动次数',
    `order_activity_reduce_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日订单减免金额(活动)',
    `order_coupon_last_30d_count` BIGINT COMMENT '最近30日下单用券次数',
    `order_coupon_reduce_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日订单减免金额(优惠券)',
    `order_last_30d_original_amount` DECIMAL(16,2) COMMENT '最近30日原始下单金额',
    `order_last_30d_final_amount` DECIMAL(16,2) COMMENT '最近30日最终下单金额',
    `order_count` BIGINT COMMENT '累积下单次数',
    `order_activity_count` BIGINT COMMENT '累积订单参与活动次数',
    `order_activity_reduce_amount` DECIMAL(16,2) COMMENT '累积订单减免金额(活动)',
    `order_coupon_count` BIGINT COMMENT '累积下单用券次数',
    `order_coupon_reduce_amount` DECIMAL(16,2) COMMENT '累积订单减免金额(优惠券)',
    `order_original_amount` DECIMAL(16,2) COMMENT '累积原始下单金额',
    `order_final_amount` DECIMAL(16,2) COMMENT '累积最终下单金额'

3、支付类指标

        `payment_date_first` STRING COMMENT '首次支付时间',
`payment_date_last` STRING COMMENT '末次支付时间',
`payment_last_1d_count` BIGINT COMMENT '最近1日支付次数',
`payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日支付金额',
`payment_last_7d_count` BIGINT COMMENT '最近7日支付次数',
`payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日支付金额',
`payment_last_30d_count` BIGINT COMMENT '最近30日支付次数',
`payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日支付金额',
`payment_count` BIGINT COMMENT '累积支付次数',
`payment_amount` DECIMAL(16,2) COMMENT '累积支付金额',

4、退单类指标

`refund_order_last_1d_count` BIGINT COMMENT '最近1日退单次数',
`refund_order_last_1d_num` BIGINT COMMENT '最近1日退单件数',
`refund_order_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日退单金额',
`refund_order_last_7d_count` BIGINT COMMENT '最近7日退单次数',
`refund_order_last_7d_num` BIGINT COMMENT '最近7日退单件数',
`refund_order_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日退单金额',
`refund_order_last_30d_count` BIGINT COMMENT '最近30日退单次数',
`refund_order_last_30d_num` BIGINT COMMENT '最近30日退单件数',
`refund_order_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日退单金额',
`refund_order_count` BIGINT COMMENT '累积退单次数',
`refund_order_num` BIGINT COMMENT '累积退单件数',
`refund_order_amount` DECIMAL(16,2) COMMENT '累积退单金额',
`refund_payment_last_1d_count` BIGINT COMMENT '最近1日退款次数',
`refund_payment_last_1d_num` BIGINT COMMENT '最近1日退款件数',
`refund_payment_last_1d_amount` DECIMAL(16,2) COMMENT '最近1日退款金额',
`refund_payment_last_7d_count` BIGINT COMMENT '最近7日退款次数',
`refund_payment_last_7d_num` BIGINT COMMENT '最近7日退款件数',
`refund_payment_last_7d_amount` DECIMAL(16,2) COMMENT '最近7日退款金额',
`refund_payment_last_30d_count` BIGINT COMMENT '最近30日退款次数',
`refund_payment_last_30d_num` BIGINT COMMENT '最近30日退款件数',
`refund_payment_last_30d_amount` DECIMAL(16,2) COMMENT '最近30日退款金额',
`refund_payment_count` BIGINT COMMENT '累积退款次数',
`refund_payment_num` BIGINT COMMENT '累积退款件数',
`refund_payment_amount` DECIMAL(16,2) COMMENT '累积退款金额',

5、购物车类指标

`cart_last_1d_count` BIGINT COMMENT '最近1日加入购物车次数',
`cart_last_7d_count` BIGINT COMMENT '最近7日加入购物车次数',
`cart_last_30d_count` BIGINT COMMENT '最近30日加入购物车次数',
`cart_count` BIGINT COMMENT '累积加入购物车次数',

6、收藏夹类指标

`favor_last_1d_count` BIGINT COMMENT '最近1日收藏次数',
`favor_last_7d_count` BIGINT COMMENT '最近7日收藏次数',
`favor_last_30d_count` BIGINT COMMENT '最近30日收藏次数',
`favor_count` BIGINT COMMENT '累积收藏次数',

7、优惠券类指标

`coupon_last_1d_get_count` BIGINT COMMENT '最近1日领券次数',
`coupon_last_1d_using_count` BIGINT COMMENT '最近1日用券(下单)次数',
`coupon_last_1d_used_count` BIGINT COMMENT '最近1日用券(支付)次数',
`coupon_last_7d_get_count` BIGINT COMMENT '最近7日领券次数',
`coupon_last_7d_using_count` BIGINT COMMENT '最近7日用券(下单)次数',
`coupon_last_7d_used_count` BIGINT COMMENT '最近7日用券(支付)次数',
`coupon_last_30d_get_count` BIGINT COMMENT '最近30日领券次数',
`coupon_last_30d_using_count` BIGINT COMMENT '最近30日用券(下单)次数',
`coupon_last_30d_used_count` BIGINT COMMENT '最近30日用券(支付)次数',
`coupon_get_count` BIGINT COMMENT '累积领券次数',
`coupon_using_count` BIGINT COMMENT '累积用券(下单)次数',
`coupon_used_count` BIGINT COMMENT '累积用券(支付)次数',

8、评论类指标

`appraise_last_1d_good_count` BIGINT COMMENT '最近1日好评次数',
`appraise_last_1d_mid_count` BIGINT COMMENT '最近1日中评次数',
`appraise_last_1d_bad_count` BIGINT COMMENT '最近1日差评次数',
`appraise_last_1d_default_count` BIGINT COMMENT '最近1日默认评价次数',
`appraise_last_7d_good_count` BIGINT COMMENT '最近7日好评次数',
`appraise_last_7d_mid_count` BIGINT COMMENT '最近7日中评次数',
`appraise_last_7d_bad_count` BIGINT COMMENT '最近7日差评次数',
`appraise_last_7d_default_count` BIGINT COMMENT '最近7日默认评价次数',
`appraise_last_30d_good_count` BIGINT COMMENT '最近30日好评次数',
`appraise_last_30d_mid_count` BIGINT COMMENT '最近30日中评次数',
`appraise_last_30d_bad_count` BIGINT COMMENT '最近30日差评次数',
`appraise_last_30d_default_count` BIGINT COMMENT '最近30日默认评价次数',
`appraise_good_count` BIGINT COMMENT '累积好评次数',
`appraise_mid_count` BIGINT COMMENT '累积中评次数',
`appraise_bad_count` BIGINT COMMENT '累积差评次数',
`appraise_default_count` BIGINT COMMENT '累积默认评价次数'

请读者参考后完成以下主题：

访客主题、商品主题、优惠券主题、活动主题、地区主题等，每日装载和首装载的SQL。

总结：

数仓DWT层的概念、作用；
DWT层的宽表聚合值是各个事实表各个阶段的的聚合值，怎么样加工和计算？

数据库

文章转载自畅谈Fintech，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。