5.4 离线数仓—DWS层交易域、用户域汇总表设计实现

自学大数据的菜鸡

已于 2022-03-07 20:32:34 修改

阅读量757

点赞数

CC 4.0 BY-SA版权

分类专栏：离线数仓文章标签：数据仓库 hive sql

于 2022-03-02 14:07:26 首次发布

本文链接：https://blog.youkuaiyun.com/yunweijie_cn/article/details/123227269

本文详细介绍了如何设计和实现离线数据仓库DWS层中的交易域和用户域汇总表。内容包括交易域用户粒度订单历史至今汇总表、支付历史至今汇总表及用户域用户粒度登录历史至今汇总表的建表语句、首日及每日数据装载方法，重点讨论了全外连接和并集操作在数据装载中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

前面完成了所有1d和nd表的设计和开发，下面进行td表的设计和开发。

一、交易域用户粒度订单历史至今汇总表

1.建表语句

DROP TABLE IF EXISTS dws_trade_user_order_td;
CREATE EXTERNAL TABLE dws_trade_user_order_td
(
    `user_id`                   STRING COMMENT '用户id',
    `order_date_first`          STRING COMMENT '首次下单日期',
    `order_date_last`           STRING COMMENT '末次下单日期',
    `order_count_td`            BIGINT COMMENT '下单次数',
    `order_num_td`              BIGINT COMMENT '购买商品件数',
    `original_amount_td`        DECIMAL(16, 2) COMMENT '原始金额',
    `activity_reduce_amount_td` DECIMAL(16, 2) COMMENT '活动优惠金额',
    `coupon_reduce_amount_td`   DECIMAL(16, 2) COMMENT '优惠券优惠金额',
    `total_amount_td`           DECIMAL(16, 2) COMMENT '最终金额'
) COMMENT '交易域用户粒度订单历史至今汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_user_order_td'
    TBLPROPERTIES ('orc.compress' = 'snappy');

2.首日数据装载

数据装载分为首日和每日，首日的时候用的是1d表，每日的时候用的是前一天的td表和当天对应的1d表
首日数据装载语句：

insert overwrite table dws_trade_user_order_td partition(dt='2020-06-14')
select
    user_id,
    min(dt) login_date_first,
    max(dt) login_date_last,
    sum(order_count_1d) order_count,
    sum(order_num_1d) order_num,
    sum(order_original_amount_1d) original_amount,
    sum(activity_reduce_amount_1d) activity_reduce_amount,
    sum(coupon_reduce_amount_1d) coupon_reduce_amount,
    sum(order_total_amount_1d) total_amount
from dws_trade_user_order_1d
group by user_id;

3.每日数据装载

方式一：
可以跟首日的思路一样，只不过要读取所有的分区，常此以后，效率不高

insert overwrite table dws_trade_user_order_td partition(dt='2020-06-15')
select
    user_id,
    min(dt) login_date_first,
    max(dt) login_date_last,
    sum(order_count_1d) order_count,