7.2 DWS 层（以天为单位，汇总统计）

7.2 DWS 层（以天为单位，汇总统计）

DWS 的 字段来源： 维度外键，每个事实表的度量值的聚合值。

聚合单位：以天为单位进行聚合汇总。

分区规划：按天分区，各维度对象当日的汇总统计。

数据装载：

? 首日：按天分区，各维度对象历史数据的汇总统计。

? 每日：按天分区，各维度对象当日的汇总统计。

7.2.1 访客主题

7.2.2 用户主题

1）建表语句：

DROP TABLE IF EXISTS dws_user_action_daycount;
CREATE EXTERNAL TABLE dws_user_action_daycount
(
    `user_id` STRING COMMENT ‘用户id‘,
    `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 ‘退单金额‘,
    `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 ‘下单明细统计‘
) COMMENT ‘每日用户行为‘
PARTITIONED BY (`dt` STRING)
STORED AS PARQUET
LOCATION ‘/warehouse/gmall/dws/dws_user_action_daycount/‘
TBLPROPERTIES ("parquet.compression"="lzo");

2. 数据导入：

首日导入：

? 思路：

（1）`login_count` BIGINT COMMENT ‘登录次数‘
	dwd_start_log 只有移动端有，所以统计不是很准确。
数据来源：dwd_page_log
获取条件：last_page 为null，user_id为非null

(2)`cart_count` BIGINT COMMENT ‘加入购物车次数‘,
数据来源：dwd_action_log
获取条件：user_id 为非空，action_id为 cart_add，通过 user_id,dt 分组

(3)`favor_count` BIGINT COMMENT ‘收藏次数‘,
数据来源：dwd_action_log
获取条件：user_id 为非空，action_id为 favor_add，通过 user_id,dt 分组

(4)`order_count` BIGINT COMMENT ‘下单次数‘,
数据来源：dwd_order_info
获取条件：通过user_id 分组，和create_time的年月日分组

(5)`order_activity_count` BIGINT COMMENT ‘订单参与活动次数‘,
数据来源：dwd_order_info
获取条件：通过user_id和create_time的年月日分组，活动减免金额大于0

(6)`order_activity_reduce_amount` DECIMAL(16,2) COMMENT ‘订单减免金额(活动)‘,
 数据来源：dwd_order_info
获取条件：通过user_id和create_time的年月日分组，直接求和

(7)`order_coupon_count` BIGINT COMMENT ‘订单用券次数‘,
 数据来源：dwd_order_info
获取条件：通过user_id和create_time的年月日分组，直接求和

(8)`order_coupon_reduce_amount` DECIMAL(16,2) COMMENT ‘订单减免金额(优惠券)‘,
(9)`order_original_amount` DECIMAL(16,2)  COMMENT ‘订单单原始金额‘,
(10)`order_final_amount` DECIMAL(16,2) COMMENT ‘订单总金额‘,
数据来源：dwd_order_info
获取条件：通过user_id和create_time的年月日分组，直接求和

(11)`payment_count` BIGINT COMMENT ‘支付次数‘,
(12)`payment_amount` DECIMAL(16,2) COMMENT ‘支付金额‘,
数据来源：dwd_payment_info
获取条件：根据user_id ，date_format(callback_time,‘yyyy-MM-dd‘)分组

(13)`refund_order_count` BIGINT COMMENT ‘退单次数‘,
`refund_order_num` BIGINT COMMENT ‘退单件数‘,
`refund_order_amount` DECIMAL(16,2) COMMENT ‘退单金额‘,
数据来源：dwd_order_refound_info,dwd_refound_payment
获取条件：根据user_id ，date_format(create_time,‘yyyy-MM-dd‘)分组
获取退单件数需要通过两个数据join
(14)`coupon_get_count` BIGINT COMMENT ‘优惠券领取次数‘,
`coupon_using_count` BIGINT COMMENT ‘优惠券使用(下单)次数‘,
`coupon_used_count` BIGINT COMMENT ‘优惠券使用(支付)次数‘,
数据来源：
	dwd_coupon_use
获取条件：
	user_id 每个的使用时间分组。
注意：此字段需要三个表全外联，和第三个表全外联时要用nvl判断 临时表的字段为非空
`appraise_good_count` BIGINT COMMENT ‘好评数‘,
`appraise_mid_count` BIGINT COMMENT ‘中评数‘,
`appraise_bad_count` BIGINT COMMENT ‘差评数‘,
`appraise_default_count` BIGINT COMMENT ‘默认评价数‘,
数据来源：
	dwd_comment_info
获取条件：
`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 ‘下单明细统计‘

?

7.2 DWS 层（以天为单位，汇总统计）

原文：https://www.cnblogs.com/tensortensor/p/14468732.html