1. 首页
  2. 文章中心
  3. 数仓电商项目

五、数据仓库电商项目——DWT层、可与DWS合并一层

54 阅读 0 评论
我是靠谱客的博主 悦耳大地，最近开发中收集的这篇文章主要介绍五、数据仓库电商项目——DWT层、可与DWS合并一层，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

DWT层 

DWT层将DWS层每日聚合的数据进行积累，DWT层不是分区表，是一个累积型全量表，并且数据来源与DWS层。

累积性全量表：查询要改动的旧数据，查询新增和变化的新数据，新旧关联，以新换旧，导入覆盖。

DWT设备主题宽表

在这里插入图片描述

用户主题宽表

 

drop table if exists dwt_user_topic;
create external table dwt_user_topic
(
    user_id string  comment '用户id',
    login_date_first string  comment '首次登录时间',
    login_date_last string  comment '末次登录时间',
    login_count bigint comment '累积登录天数',
    login_last_30d_count bigint comment '最近30日登录天数',
    order_date_first string  comment '首次下单时间',
    order_date_last string  comment '末次下单时间',
    order_count bigint comment '累积下单次数',
    order_amount decimal(16,2) comment '累积下单金额',
    order_last_30d_count bigint comment '最近30日下单次数',
    order_last_30d_amount bigint comment '最近30日下单金额',
    payment_date_first string  comment '首次支付时间',
    payment_date_last string  comment '末次支付时间',
    payment_count decimal(16,2) comment '累积支付次数',
    payment_amount decimal(16,2) comment '累积支付金额',
    payment_last_30d_count decimal(16,2) comment '最近30日支付次数',
    payment_last_30d_amount decimal(16,2) comment '最近30日支付金额'
 )COMMENT '用户主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_user_topic/'
tblproperties ("parquet.compression"="lzo");



 insert overwrite table dwt_user_topic
select
    nvl(new.user_id,old.user_id),
    if(old.login_date_first is null and new.login_count>0,'2020-03-29',old.login_date_first),
    if(new.login_count>0,'2020-03-29',old.login_date_last),
    nvl(old.login_count,0)+if(new.login_count>0,1,0),
    nvl(new.login_last_30d_count,0),
    if(old.order_date_first is null and new.order_count>0,'2020-03-29',old.order_date_first),
    if(new.order_count>0,'2020-03-29',old.order_date_last),
    nvl(old.order_count,0)+nvl(new.order_count,0),
    nvl(old.order_amount,0)+nvl(new.order_amount,0),
    nvl(new.order_last_30d_count,0),
    nvl(new.order_last_30d_amount,0),
    if(old.payment_date_first is null and new.payment_count>0,'2020-03-29',old.payment_date_first),
    if(new.payment_count>0,'2020-03-29',old.payment_date_last),
    nvl(old.payment_count,0)+nvl(new.payment_count,0),
    nvl(old.payment_amount,0)+nvl(new.payment_amount,0),
    nvl(new.payment_last_30d_count,0),
    nvl(new.payment_last_30d_amount,0)
from
dwt_user_topic old
full outer join
(
    select
        user_id,
        sum(if(dt='2020-03-29',login_count,0)) login_count,
        sum(if(dt='2020-03-29',order_count,0)) order_count,
        sum(if(dt='2020-03-29',order_amount,0)) order_amount,
        sum(if(dt='2020-03-29',payment_count,0)) payment_count,
        sum(if(dt='2020-03-29',payment_amount,0)) payment_amount,
        sum(if(login_count>0,1,0)) login_last_30d_count,
        sum(order_count) order_last_30d_count,
        sum(order_amount) order_last_30d_amount,
        sum(payment_count) payment_last_30d_count,
        sum(payment_amount) payment_last_30d_amount
    from dws_user_action_daycount
    where dt>=date_add( '2020-03-29',-30)
    group by user_id
)new
on old.user_id=new.user_id;

商品主题宽表

hive (gmall)> 
drop table if exists dwt_sku_topic;
create external table dwt_sku_topic
(
    sku_id string comment 'sku_id',`在这里插入代码片`
    spu_id string comment 'spu_id',
    order_last_30d_count bigint comment '最近30日被下单次数',
    order_last_30d_num bigint comment '最近30日被下单件数',
    order_last_30d_amount decimal(16,2)  comment '最近30日被下单金额',
    order_count bigint comment '累积被下单次数',
    order_num bigint comment '累积被下单件数',
    order_amount decimal(16,2) comment '累积被下单金额',
    payment_last_30d_count   bigint  comment '最近30日被支付次数',
    payment_last_30d_num bigint comment '最近30日被支付件数',
    payment_last_30d_amount  decimal(16,2) comment '最近30日被支付金额',
    payment_count   bigint  comment '累积被支付次数',
    payment_num bigint comment '累积被支付件数',
    payment_amount  decimal(16,2) comment '累积被支付金额',
    refund_last_30d_count bigint comment '最近三十日退款次数',
    refund_last_30d_num bigint comment '最近三十日退款件数',
    refund_last_30d_amount decimal(10,2) comment '最近三十日退款金额',
    refund_count bigint comment '累积退款次数',
    refund_num bigint comment '累积退款件数',
    refund_amount decimal(10,2) comment '累积退款金额',
    cart_last_30d_count bigint comment '最近30日被加入购物车次数',
    cart_last_30d_num bigint comment '最近30日被加入购物车件数',
    cart_count bigint comment '累积被加入购物车次数',
    cart_num bigint comment '累积被加入购物车件数',
    favor_last_30d_count bigint comment '最近30日被收藏次数',
    favor_count bigint comment '累积被收藏次数',
    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 '累积默认评价数'
 )COMMENT '商品主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_sku_topic/'
tblproperties ("parquet.compression"="lzo");

INSERT OVERWRITE INTO dwt_sku_topic
SELECT nvl(new.sku_id, old.sku_id), dwd_dim_sku_info.spu_id
	, nvl(new.order_count30, 0)
	, nvl(new.order_num30, 0)
	, nvl(new.order_amount30, 0)
	, nvl(old.order_count, 0) + nvl(new.order_count, 0)
	, nvl(old.order_num, 0) + nvl(new.order_num, 0)
	, nvl(old.order_amount, 0) + nvl(new.order_amount, 0)
	, nvl(new.payment_count30, 0)
	, nvl(new.payment_num30, 0)
	, nvl(new.payment_amount30, 0)
	, nvl(old.payment_count, 0) + nvl(new.payment_count, 0)
	, nvl(old.payment_num, 0) + nvl(new.payment_count, 0)
	, nvl(old.payment_amount, 0) + nvl(new.payment_count, 0)
	, nvl(new.refund_count30, 0)
	, nvl(new.refund_num30, 0)
	, nvl(new.refund_amount30, 0)
	, nvl(old.refund_count, 0) + nvl(new.refund_count, 0)
	, nvl(old.refund_num, 0) + nvl(new.refund_num, 0)
	, nvl(old.refund_amount, 0) + nvl(new.refund_amount, 0)
	, nvl(new.cart_count30, 0)
	, nvl(new.cart_num30, 0)
	, nvl(old.cart_count, 0) + nvl(new.cart_count, 0)
	, nvl(old.cart_num, 0) + nvl(new.cart_num, 0)
	, nvl(new.favor_count30, 0)
	, nvl(old.favor_count, 0) + nvl(new.favor_count, 0)
	, nvl(new.appraise_good_count30, 0)
	, nvl(new.appraise_mid_count30, 0)
	, nvl(new.appraise_bad_count30, 0)
	, nvl(new.appraise_default_count30, 0)
	, nvl(old.appraise_good_count, 0) + nvl(new.appraise_good_count, 0)
	, nvl(old.appraise_mid_count, 0) + nvl(new.appraise_mid_count, 0)
	, nvl(old.appraise_bad_count, 0) + nvl(new.appraise_bad_count, 0)
	, nvl(old.appraise_default_count, 0) + nvl(new.appraise_default_count, 0)
FROM (
	SELECT sku_id, spu_id, order_last_30d_count, order_last_30d_num, order_last_30d_amount
		, order_count, order_num, order_amount, payment_last_30d_count, payment_last_30d_num
		, payment_last_30d_amount, payment_count, payment_num, payment_amount, refund_last_30d_count
		, refund_last_30d_num, refund_last_30d_amount, refund_count, refund_num, refund_amount
		, cart_last_30d_count, cart_last_30d_num, cart_count, cart_num, favor_last_30d_count
		, favor_count, appraise_last_30d_good_count, appraise_last_30d_mid_count, appraise_last_30d_bad_count, appraise_last_30d_default_count
		, appraise_good_count, appraise_mid_count, appraise_bad_count, appraise_default_count
	FROM dwt_sku_topic
) old
	FULL JOIN (
		SELECT sku_id
			, sum(if(dt = '2020-03-29', order_count, 0)) AS order_count
			, sum(if(dt = '2020-03-29', order_num, 0)) AS order_num
			, sum(if(dt = '2020-03-29', order_amount, 0)) AS order_amount
			, sum(if(dt = '2020-03-29', payment_count, 0)) AS payment_count
			, sum(if(dt = '2020-03-29', payment_num, 0)) AS payment_num
			, sum(if(dt = '2020-03-29', payment_amount, 0)) AS payment_amount
			, sum(if(dt = '2020-03-29', refund_count, 0)) AS refund_count
			, sum(if(dt = '2020-03-29', refund_num, 0)) AS refund_num
			, sum(if(dt = '2020-03-29', refund_amount, 0)) AS refund_amount
			, sum(if(dt = '2020-03-29', cart_count, 0)) AS cart_count
			, sum(if(dt = '2020-03-29', cart_num, 0)) AS cart_num
			, sum(if(dt = '2020-03-29', favor_count, 0)) AS favor_count
			, sum(if(dt = '2020-03-29', appraise_good_count, 0)) AS appraise_good_count
			, sum(if(dt = '2020-03-29', appraise_mid_count, 0)) AS appraise_mid_count
			, sum(if(dt = '2020-03-29', appraise_bad_count, 0)) AS appraise_bad_count
			, sum(if(dt = '2020-03-29', appraise_default_count, 0)) AS appraise_default_count
			, sum(order_count) AS order_count30, sum(order_num) AS order_num30
			, sum(order_amount) AS order_amount30, sum(payment_count) AS payment_count30
			, sum(payment_num) AS payment_num30, sum(payment_amount) AS payment_amount30
			, sum(refund_count) AS refund_count30, sum(refund_num) AS refund_num30
			, sum(refund_amount) AS refund_amount30, sum(cart_count) AS cart_count30
			, sum(cart_num) AS cart_num30, sum(favor_count) AS favor_count30
			, sum(appraise_good_count) AS appraise_good_count30, sum(appraise_mid_count) AS appraise_mid_count30
			, sum(appraise_bad_count) AS appraise_bad_count30, sum(appraise_default_count) AS appraise_default_count30
		FROM dws_sku_action_daycount
		WHERE dt >= date_add('2020-03-29', -30)
		GROUP BY sku_id
	) new
	ON new.sku_id = old.sku_id
	LEFT JOIN dwd_dim_sku_info ON new.sku_id = dwd_dim_sku_info.id;

 优惠券主题宽表（预留）

在这里插入图片描述

活动主题宽表（预留） 

在这里插入图片描述

最后

以上就是悦耳大地为你收集整理的五、数据仓库电商项目——DWT层、可与DWS合并一层的全部内容，希望文章能够帮你解决五、数据仓库电商项目——DWT层、可与DWS合并一层所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(36)

相关文章

一种分析HDFS文件变化及小文件分布情况的方法文档编写目的实现方式说明
一种分析HDFS文件变化及小文件分布情况的方法文档编写目的实现方式说明
Hadoop 2.2.0 在centos6.2 64位下的安装--分布式模式
Hadoop 2.2.0 在centos6.2 64位下的安装--分布式模式
6.capacity scheduler1.先决条件2.容量调试器2.1 特性2.2 配置3.设置队列优先级4.刷新队列 
6.capacity scheduler1.先决条件2.容量调试器2.1 特性2.2 配置3.设置队列优先级4.刷新队列 
HDFS 优缺点
HDFS 优缺点
五、数据仓库电商项目——DWT层、可与DWS合并一层
五、数据仓库电商项目——DWT层、可与DWS合并一层
Hadoop-HDFS的特点Hadoop-HDFS的特点
Hadoop-HDFS的特点Hadoop-HDFS的特点
大数据基础知识（二）：了解hadoop原理
大数据基础知识（二）：了解hadoop原理
大数据学习教程SD版第七篇【Hive】1. Hive 简介2. Hive vs RDBMS3. Hive 安装4. Hive JDBC5. Hive Shell6. Hive Conf7. Hive Type8. Hive SQL9. Hive 压缩10. Hive 文件格式
大数据学习教程SD版第七篇【Hive】1. Hive 简介2. Hive vs RDBMS3. Hive 安装4. Hive JDBC5. Hive Shell6. Hive Conf7. Hive Type8. Hive SQL9. Hive 压缩10. Hive 文件格式

评论列表共有 0 条评论

立即
投稿
返回
顶部