数仓（十八）数仓建模以及分层总结(ODS、DIM、DWD、DWS、DWT、ADS层)

nvl(new_end_date,old_end_date),
nvl(new_end_date,old_end_date) dt

 cast(date_add('2021-05-02',-1) as string),
 cast(date_add('2021-05-02',-1) as string) dt

1、DWD层设计要点

DWD层是对用户的日志行为进行解析，以及对业务数据采用维度模型的方式重新建模（维度退化），这两部分内容需要分别处理。

2、DWD层埋点日志log处理

前端埋点的日志信息，已经写到ODS层ods_log表了，传入的参数是一个String类型字符串即一条日志信息一个String类型字符串。

login_count` BIGINT COMMENT '登录次数'，
`cart_count` BIGINT COMMENT '加入购物车次数',
`favor_count` BIGINT COMMENT '收藏次数',
`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 '订单总金额',
`payment_count` BIGINT COMMENT '支付次数',
`payment_amount` DECIMAL(16,2) COMMENT '支付金额',
`refund_order_count` BIGINT COMMENT '退单次数',
`refund_order_num` BIGINT COMMENT '退单件数'，
`refund_order_amount` DECIMAL(16,2) COMMENT '退单金额',
`refund_payment_count` BIGINT COMMENT '退款次数',
`refund_payment_num` BIGINT COMMENT '退款件数',
`refund_payment_amount` DECIMAL(16,2) COMMENT '退款金额',
`coupon_get_count` BIGINT COMMENT '优惠券领取次数',
`coupon_using_count` BIGINT COMMENT '优惠券使用(下单)次数',
`coupon_used_count` BIGINT COMMENT '优惠券使用(支付)次数',
`appraise_good_count` BIGINT COMMENT '好评数'，
`appraise_mid_count` BIGINT COMMENT '中评数',
`appraise_bad_count` BIGINT COMMENT '差评数',
`appraise_default_count` BIGINT COMMENT '默认评价数'，
`order_detail_stats` array<struct<sku_id:string,sku_num:bigint,order_count:bigint,activity_reduce_amount:decimal(16,2),coupon_reduce_amount:decimal(16,2),original_amount:decimal(16,2),final_amount:decimal(16,2)>> COMMENT '下单明细统计'

elect
  date_format(callback_time,'yyyy-MM-dd') dt,
  user_id,
  count(*) payment_count,
  sum(payment_amount) payment_amount
from dwd_payment_info
group by date_format(callback_time,'yyyy-MM-dd'),user_id

select
    date_format(create_time,'yyyy-MM-dd') dt,
    user_id,
    count(*) refund_order_count,
    sum(refund_num) refund_order_num,
    sum(refund_amount) refund_order_amount
from dwd_order_refund_info
group by date_format(create_time,'yyyy-MM-dd'),user_id

         coupon_using_count 

        '优惠券使用(下单)次数',

         coupon_used_count 

        '优惠券使用(支付)次数',

insert overwrite table dws_user_action_daycount partition(dt)
select
    coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id,tmp_od.user_id),
    nvl(login_count,0),
    nvl(cart_count,0),
    nvl(favor_count,0),
    XXXX,

insert overwrite table dws_user_action_daycount partition(dt='2020-06-15')
select
    coalesce(tmp_login.user_id,tmp_cf.user_id,tmp_order.user_id,tmp_pay.user_id,tmp_ri.user_id,tmp_rp.user_id,tmp_comment.user_id,tmp_coupon.user_id,tmp_od.user_id),
    nvl(login_count,0),
    nvl(cart_count,0),
    nvl(favor_count,0),
    nvl(order_count,0),

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

其实这两层建模完全可以构建放到一层里面做加工和处理。但是为了更好的复用，我们分为两层。

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

  `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 '累积登录天数',

• 首次、末次登录、购买、支付、退款等；

• 最近1日、7日、30日各个主题；

• 累积登录、次数、支付、退款等；

ADS层数据是专门给业务使用的数据层，这层是面向业务定制的应用数据层。

• 这一层是提供为数据产品使用的结果数据、指标等。

• 在这里，主要是提供给数据产品和数据分析使用的数据，一般会存放在 ES、MySQL等系统中供线上系统使用，也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用。如报表数据，或者说那种大宽表。

DROP TABLE IF EXISTS ads_user_total;
CREATE EXTERNAL TABLE `ads_user_total` 
(
  `dt` STRING COMMENT '统计日期',
  `recent_days` BIGINT COMMENT '最近天数,0:累积值,1:最近1天,7:最近7天,30:最近30天',
  `new_user_count` BIGINT COMMENT '新注册用户数',
  `new_order_user_count` BIGINT COMMENT '新增下单用户数',
  `order_final_amount` DECIMAL(16,2) COMMENT '下单总金额',
  `order_user_count` BIGINT COMMENT '下单用户数',
  `no_order_user_count` BIGINT COMMENT '未下单用户数(具体指活跃用户中未下单用户)'
) COMMENT '用户统计'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_total/';

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

DROP TABLE IF EXISTS ads_user_action;
CREATE EXTERNAL TABLE `ads_user_action` (
  `dt` STRING COMMENT '统计日期',
  `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
  `home_count` BIGINT COMMENT '浏览首页人数',
  `good_detail_count` BIGINT COMMENT '浏览商品详情页人数',
  `cart_count` BIGINT COMMENT '加入购物车人数',
  `order_count` BIGINT COMMENT '下单人数',
  `payment_count` BIGINT COMMENT '支付人数'
) COMMENT '漏斗分析'
ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_action/';

DROP TABLE IF EXISTS ads_user_retention;
CREATE EXTERNAL TABLE ads_user_retention (
  `dt` STRING COMMENT '统计日期'，
  `create_date` STRING COMMENT '用户新增日期',
  `retention_day` BIGINT COMMENT '截至当前日期留存天数',
  `retention_count` BIGINT COMMENT '留存用户数量',
  `new_user_count` BIGINT COMMENT '新增用户数量',
  `retention_rate` DECIMAL(16,2) COMMENT '留存率'
) COMMENT '用户留存率'
ROW FORMAT DELIMITED  FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_retention/';