5、数据仓库电商项目（尚硅谷第二版） DWT层

最新推荐文章于 2024-03-07 10:49:41 发布
最新推荐文章于 2024-03-07 10:49:41 发布
阅读量774 收藏 2
点赞数 1
分类专栏： 自己玩的大数据（练习） 文章标签： 数据仓库 hive 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29057619/article/details/110082848
版权

5、数据仓库电商项目 DWT层

上一章节：4、数据仓库电商项目（尚硅谷第二版）DWS层
下一章节：6、数据仓库电商项目 ADS层

本章需求
 

        DWT累积性全量表建模
 
注：按照需求自己要敲一遍，可以百度，但切记不要 重度 粘贴复制。
 
越到后面越难，各种表间关系和聚合，现在没时间仔细写，先把一些文档复制上来，后面慢慢补充~

DWT层的概括

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

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

 
 

DWT设备主题宽表

 
在这里插入图片描述
该表中为DWS层每日活跃设备的累积，找对对应的字段进行导入。
在这里插入图片描述

OLD代表旧的数据，NEW代表新数据，老用户的首次活跃时间是不会变的，只会变老用户的末次活跃时间，如果是今天新增的用户，则今天是为首次活跃时间。

建表语句

drop table if exists dwt_uv_topic;
create external table dwt_uv_topic
(
    `mid_id` string COMMENT '设备唯一标识',
    `user_id` string COMMENT '用户标识',
    `version_code` string COMMENT '程序版本号',
    `version_name` string COMMENT '程序版本名',
    `lang` string COMMENT '系统语言',
    `source` string COMMENT '渠道号',
    `os` string COMMENT '安卓系统版本',
    `area` string COMMENT '区域',
    `model` string COMMENT '手机型号',
    `brand` string COMMENT '手机品牌',
    `sdk_version` string COMMENT 'sdkVersion',
    `gmail` string COMMENT 'gmail',
    `height_width` string COMMENT '屏幕宽高',
    `app_time` string COMMENT '客户端日志产生时的时间',
    `network` string COMMENT '网络模式',
    `lng` string COMMENT '经度',
    `lat` string COMMENT '纬度',
    `login_date_first` string  comment '首次活跃时间',
    `login_date_last` string  comment '末次活跃时间',
    `login_day_count` bigint comment '当日活跃次数',
    `login_count` bigint comment '累积活跃天数'
)
stored as parquet
location '/warehouse/gmall/dwt/dwt_uv_topic'
tblproperties ("parquet.compression"="lzo");

数据装载
今天未登录的老用户：
老用户：old.mid.id is not null
新用户：old.mid.id is null
今天登录的老用户：new.mid id is not null and old.mid.id is not null

hive (gmall)> 
insert overwrite table dwt_uv_topic
select
    nvl(new.mid_id,old.mid_id),
    nvl(new.user_id,old.user_id),
    nvl(new.version_code,old.version_code),
    nvl(new.version_name,old.version_name),
    nvl(new.lang,old.lang),
    nvl(new.source,old.source),
    nvl(new.os,old.os),
    nvl(new.area,old.area),
    nvl(new.model,old.model),
    nvl(new.brand,old.brand),
    nvl(new.sdk_version,old.sdk_version),
    nvl(new.gmail,old.gmail),
    nvl(new.height_width,old.height_width),
    nvl(new.app_time,old.app_time),
    nvl(new.network,old.network),
    nvl(new.lng,old.lng),
    nvl(new.lat,old.lat),
    if(old.mid_id is null,'2020-03-29',old.login_date_first),
    if(new.mid_id is not null,'2020-03-29',old.login_date_last),
    if(new.mid_id is not null, new.login_count,0),
    nvl(old.login_count,0)+if(new.login_count>0,1,0)
from
(
    select
        *
    from dwt_uv_topic
)old
full outer join
(
    select
        *
    from dws_uv_detail_daycount
    where dt='2020-03-29'
)new
on old.mid_id=new.mid_id;

会员主题宽表

建表语句

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");

数据装载

hive (gmall)> 
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");

数据装载

hive (gmall)> 
insert overwrite table 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 outer join 
(
    select 
        sku_id,
        sum(if(dt='2020-03-29', order_count,0 )) order_count,
        sum(if(dt='2020-03-29',order_num ,0 ))  order_num, 
        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_num,0 )) payment_num,
        sum(if(dt='2020-03-29',payment_amount,0 )) payment_amount,
        sum(if(dt='2020-03-29',refund_count,0 )) refund_count,
        sum(if(dt='2020-03-29',refund_num,0 )) refund_num,
        sum(if(dt='2020-03-29',refund_amount,0 )) refund_amount,  
        sum(if(dt='2020-03-29',cart_count,0 )) cart_count,
        sum(if(dt='2020-03-29',cart_num,0 )) cart_num,
        sum(if(dt='2020-03-29',favor_count,0 )) favor_count,
        sum(if(dt='2020-03-29',appraise_good_count,0 )) appraise_good_count,  
        sum(if(dt='2020-03-29',appraise_mid_count,0 ) ) appraise_mid_count ,
        sum(if(dt='2020-03-29',appraise_bad_count,0 )) appraise_bad_count,  
        sum(if(dt='2020-03-29',appraise_default_count,0 )) appraise_default_count,
        sum(order_count) order_count30 ,
        sum(order_num) order_num30,
        sum(order_amount) order_amount30,
        sum(payment_count) payment_count30,
        sum(payment_num) payment_num30,
        sum(payment_amount) payment_amount30,
        sum(refund_count) refund_count30,
        sum(refund_num) refund_num30,
        sum(refund_amount) refund_amount30,
        sum(cart_count) cart_count30,
        sum(cart_num) cart_num30,
        sum(favor_count) favor_count30,
        sum(appraise_good_count) appraise_good_count30,
        sum(appraise_mid_count) appraise_mid_count30,
        sum(appraise_bad_count) appraise_bad_count30,
        sum(appraise_default_count) 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;

优惠券主题宽表（预留）

在这里插入图片描述

建表语句

hive (gmall)> 
drop table if exists dwt_coupon_topic;
create external table dwt_coupon_topic
(
    `coupon_id` string  COMMENT '优惠券ID',
    `get_day_count` bigint COMMENT '当日领用次数',
    `using_day_count` bigint COMMENT '当日使用(下单)次数',
    `used_day_count` bigint COMMENT '当日使用(支付)次数',
    `get_count` bigint COMMENT '累积领用次数',
    `using_count` bigint COMMENT '累积使用(下单)次数',
    `used_count` bigint COMMENT '累积使用(支付)次数'
)COMMENT '购物券主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_coupon_topic/'
tblproperties ("parquet.compression"="lzo");

数据装载

hive (gmall)> 
insert overwrite table dwt_coupon_topic
select
    nvl(new.coupon_id,old.coupon_id),
    nvl(new.get_count,0),
    nvl(new.using_count,0),
    nvl(new.used_count,0),
    nvl(old.get_count,0)+nvl(new.get_count,0),
    nvl(old.using_count,0)+nvl(new.using_count,0),
    nvl(old.used_count,0)+nvl(new.used_count,0)
from
(
    select
        *
    from dwt_coupon_topic
)old
full outer join
(
    select
        coupon_id,
        get_count,
        using_count,
        used_count
    from dws_coupon_use_daycount
    where dt='2020-03-29'
)new
on old.coupon_id=new.coupon_id;

活动主题宽表（预留）

在这里插入图片描述
建表语句

hive (gmall)> 
drop table if exists dwt_activity_topic;
create external table dwt_activity_topic(
    `id` string COMMENT '活动id',
    `activity_name` string  COMMENT '活动名称',
    `order_day_count` bigint COMMENT '当日日下单次数',
    `payment_day_count` bigint COMMENT '当日支付次数',
    `order_count` bigint COMMENT '累积下单次数',
    `payment_count` bigint COMMENT '累积支付次数'
) COMMENT '活动主题宽表'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/dwt/dwt_activity_topic/'
tblproperties ("parquet.compression"="lzo");

数据装载

hive (gmall)> 
insert overwrite table dwt_activity_topic
select
    nvl(new.id,old.id),
    nvl(new.activity_name,old.activity_name),
    nvl(new.order_count,0),
    nvl(new.payment_count,0),
    nvl(old.order_count,0)+nvl(new.order_count,0),
    nvl(old.payment_count,0)+nvl(new.payment_count,0)
from
(
    select
        *
    from dwt_activity_topic
)old
full outer join
(
    select
        id,
        activity_name,
        order_count,
        payment_count
    from dws_activity_info_daycount
    where dt='2020-03-29'
)new
on old.id=new.id;

DWT层数据导入脚本

 vim dws_to_dwt.sh

#!/bin/bash

APP=gmall
hive=/opt/module/hive/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else 
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table ${APP}.dwt_uv_topic
select
    nvl(new.mid_id,old.mid_id),
    nvl(new.user_id,old.user_id),
    nvl(new.version_code,old.version_code),
    nvl(new.version_name,old.version_name),
    nvl(new.lang,old.lang),
    nvl(new.source,old.source),
    nvl(new.os,old.os),
    nvl(new.area,old.area),
    nvl(new.model,old.model),
    nvl(new.brand,old.brand),
    nvl(new.sdk_version,old.sdk_version),
    nvl(new.gmail,old.gmail),
    nvl(new.height_width,old.height_width),
    nvl(new.app_time,old.app_time),
    nvl(new.network,old.network),
    nvl(new.lng,old.lng),
    nvl(new.lat,old.lat),
    nvl(old.login_date_first,'$do_date'),
    if(new.login_count>0,'$do_date',old.login_date_last),
    nvl(new.login_count,0),
    nvl(new.login_count,0)+nvl(old.login_count,0)
from
(
    select
        *
    from ${APP}.dwt_uv_topic
)old
full outer join
(
    select
        *
    from ${APP}.dws_uv_detail_daycount
    where dt='$do_date'
)new
on old.mid_id=new.mid_id;

insert overwrite table ${APP}.dwt_user_topic
select
    nvl(new.user_id,old.user_id),
    if(old.login_date_first is null and new.login_count>0,'$do_date',old.login_date_first),
    if(new.login_count>0,'$do_date',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,'$do_date',old.order_date_first),
    if(new.order_count>0,'$do_date',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,'$do_date',old.payment_date_first),
    if(new.payment_count>0,'$do_date',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
(
    select 
        *
    from ${APP}.dwt_user_topic
)old
full outer join
(
    select
        user_id,
        sum(if(dt='$do_date',login_count,0)) login_count,
        sum(if(dt='$do_date',order_count,0)) order_count,
        sum(if(dt='$do_date',order_amount,0)) order_amount,
        sum(if(dt='$do_date',payment_count,0)) payment_count,
        sum(if(dt='$do_date',payment_amount,0)) payment_amount,
        sum(if(order_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 ${APP}.dws_user_action_daycount
    where dt>=date_add( '$do_date',-30)
    group by user_id
)new
on old.user_id=new.user_id;

with
sku_act as
(
select 
    sku_id,
    sum(if(dt='$do_date', order_count,0 )) order_count,
    sum(if(dt='$do_date',order_num ,0 ))  order_num, 
    sum(if(dt='$do_date',order_amount,0 )) order_amount ,
    sum(if(dt='$do_date',payment_count,0 )) payment_count,
    sum(if(dt='$do_date',payment_num,0 )) payment_num,
    sum(if(dt='$do_date',payment_amount,0 )) payment_amount,
    sum(if(dt='$do_date',refund_count,0 )) refund_count,
    sum(if(dt='$do_date',refund_num,0 )) refund_num,
    sum(if(dt='$do_date',refund_amount,0 )) refund_amount,  
    sum(if(dt='$do_date',cart_count,0 )) cart_count,
    sum(if(dt='$do_date',cart_num,0 )) cart_num,
    sum(if(dt='$do_date',favor_count,0 )) favor_count,
    sum(if(dt='$do_date',appraise_good_count,0 )) appraise_good_count,  
    sum(if(dt='$do_date',appraise_mid_count,0 ) ) appraise_mid_count ,
    sum(if(dt='$do_date',appraise_bad_count,0 )) appraise_bad_count,  
    sum(if(dt='$do_date',appraise_default_count,0 )) appraise_default_count,
    sum( order_count  ) order_count30 ,
    sum( order_num  )  order_num30,
    sum(order_amount ) order_amount30,
    sum(payment_count ) payment_count30,
    sum(payment_num ) payment_num30,
    sum(payment_amount ) payment_amount30,
    sum(refund_count  ) refund_count30,
    sum(refund_num ) refund_num30,
    sum(refund_amount ) refund_amount30,
    sum(cart_count  ) cart_count30,
    sum(cart_num ) cart_num30,
    sum(favor_count ) favor_count30,
    sum(appraise_good_count ) appraise_good_count30,
    sum(appraise_mid_count  ) appraise_mid_count30,
    sum(appraise_bad_count ) appraise_bad_count30,
    sum(appraise_default_count )  appraise_default_count30 
from ${APP}.dws_sku_action_daycount
where dt>=date_add ( '$do_date',-30)
group by sku_id
),
sku_topic
as 
(
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 ${APP}.dwt_sku_topic
)
insert overwrite table ${APP}.dwt_sku_topic
select 
    nvl(sku_act.sku_id,sku_topic.sku_id) ,
    dwd_dim_sku_info.spu_id,
    nvl (sku_act.order_count30,0)      ,
    nvl (sku_act.order_num30,0)   ,
    nvl (sku_act.order_amount30,0)   ,
    nvl(sku_topic.order_count,0)+ nvl (sku_act.order_count,0) ,
    nvl(sku_topic.order_num,0)+ nvl (sku_act.order_num,0)   ,
    nvl(sku_topic.order_amount,0)+ nvl (sku_act.order_amount,0),
    nvl (sku_act.payment_count30,0),
    nvl (sku_act.payment_num30,0),
    nvl (sku_act.payment_amount30,0),
    nvl(sku_topic.payment_count,0)+ nvl (sku_act.payment_count,0) ,
    nvl(sku_topic.payment_num,0)+ nvl (sku_act.payment_count,0)  ,
    nvl(sku_topic.payment_amount,0)+ nvl (sku_act.payment_count,0)  ,
    nvl (refund_count30,0),
    nvl (sku_act.refund_num30,0),
    nvl (sku_act.refund_amount30,0),
    nvl(sku_topic.refund_count,0)+ nvl (sku_act.refund_count,0),
    nvl(sku_topic.refund_num,0)+ nvl (sku_act.refund_num,0),
    nvl(sku_topic.refund_amount,0)+ nvl (sku_act.refund_amount,0),
    nvl(sku_act.cart_count30,0)  ,
    nvl(sku_act.cart_num30,0)  ,
    nvl(sku_topic.cart_count  ,0)+ nvl (sku_act.cart_count,0),
    nvl( sku_topic.cart_num  ,0)+ nvl (sku_act.cart_num,0),
    nvl(sku_act.favor_count30 ,0)  ,
    nvl (sku_topic.favor_count  ,0)+ nvl (sku_act.favor_count,0),
    nvl (sku_act.appraise_good_count30 ,0)  ,
    nvl (sku_act.appraise_mid_count30 ,0)  ,
    nvl (sku_act.appraise_bad_count30 ,0)  ,
    nvl (sku_act.appraise_default_count30 ,0)  ,
    nvl (sku_topic.appraise_good_count  ,0)+ nvl (sku_act.appraise_good_count,0)  ,
    nvl (sku_topic.appraise_mid_count   ,0)+ nvl (sku_act.appraise_mid_count,0) ,
    nvl (sku_topic.appraise_bad_count  ,0)+ nvl (sku_act.appraise_bad_count,0)  ,
    nvl (sku_topic.appraise_default_count  ,0)+ nvl (sku_act.appraise_default_count,0) 
from sku_act
full outer join sku_topic
on sku_act.sku_id =sku_topic.sku_id
left join ${APP}.dwd_dim_sku_info
on sku_act.sku_id = dwd_dim_sku_info.id;

insert overwrite table ${APP}.dwt_coupon_topic
select
    nvl(new.coupon_id,old.coupon_id),
    nvl(new.get_count,0),
    nvl(new.using_count,0),
    nvl(new.used_count,0),
    nvl(old.get_count,0)+nvl(new.get_count,0),
    nvl(old.using_count,0)+nvl(new.using_count,0),
    nvl(old.used_count,0)+nvl(new.used_count,0)
from
(
    select
        *
    from ${APP}.dwt_coupon_topic
)old
full outer join
(
    select
        coupon_id,
        get_count,
        using_count,
        used_count
    from ${APP}.dws_coupon_use_daycount
    where dt='$do_date'
)new
on old.coupon_id=new.coupon_id;

insert overwrite table ${APP}.dwt_activity_topic
select
    nvl(new.id,old.id),
    nvl(new.activity_name,old.activity_name),
    nvl(new.order_count,0),
    nvl(new.payment_count,0),
    nvl(old.order_count,0)+nvl(new.order_count,0),
    nvl(old.payment_count,0)+nvl(new.payment_count,0)
from
(
    select
        *
    from ${APP}.dwt_activity_topic
)old
full outer join
(
    select
        id,
        activity_name,
        order_count,
        payment_count
    from ${APP}.dws_activity_info_daycount
    where dt='$do_date'
)new
on old.id=new.id;
"

$hive -e "$sql"
Jecky不姓码
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
数仓搭建-DWT层
qq_43827516的博客
11-10 412
6.1 设备主题宽表 累积型表：1、找old (在当前层找，也就是当前要插入数据的表) new(当前层的下一层找) 2、full outer join 1）建表语句 hive (gmall)> drop table if exists dwt_uv_topic; create external table dwt_uv_topic ( `mid_id` string comment '设备id', `brand` string comment '手机品牌', `model`
数据仓库-dwt层表的分区规划
hsl971105的博客
10-07 349
行：维度对象的累计汇总行为 列：维度ID+维度模型中与该维度相关的事实表的度量的累积汇总。 分区：截止当日的全量的历史累计 dwt的字段是dws字段的n倍。dws仅计算用户当天的汇总数据，dwt包含首次、末次、最近n天、开始到至今等指标。最近n天的指标每天都有，具有时效性。 用户主题 DROP TABLE IF EXISTS dwt_user_topic; CREATE EXTERNAL TABLE IF NOT EXISTS dwt_user_topic ( `user_id.
数仓搭建DWT层
noob9527的博客
04-26 595
尚硅谷电商数仓DWT层
数仓学习笔记(7)——数仓搭建(DWT层)
qq_64557330的博客
09-11 509
这里的7天前和30天前都指的是那一天的累计行为，而如果要统计最近7天或者30天的累计值，需要使用第一张表(前一天的全量累计行为)+第二张表(最新一天的累积行为)-7/30那一天的累计行为。
数仓搭建 DWT 层
weixin_45417821的博客
11-02 328
文章目录访客主题用户主题商品主题优惠券主题活动主题地区主题DWT层首日数据导入脚本DWT层每日数据导入脚本 访客主题 1）建表语句 DROP TABLE IF EXISTS dwt_visitor_topic; CREATE EXTERNAL TABLE dwt_visitor_topic ( `mid_id` STRING COMMENT '设备id', `brand` STRING COMMENT '手机品牌', `model` STRING COMMENT '手机型号',
尚硅谷大数据项目之电商数仓电商数据仓库系统1
08-04
【尚硅谷大数据项目之电商数仓电商数据仓库系统1】主要涵盖了数据仓库的构建和理论，包括数仓分层、命名规范、范式理论、关系建模与维度建模，以及核心的维度表和事实表。 1. **数仓分层**：数据仓库的分层设计是...
AUTOCAD 中级 高新技术考试 第二章 DWT文件
12-18
标题中的“AUTOCAD 中级 高新技术考试 第二章 DWT文件”表明了我们要讨论的是AutoCAD软件的中级学习内容，特别是在高新技术考试中的第二章节，重点是DWT文件。DWT代表“Drawing Template”，是AutoCAD中的一种模板...
dwt2w.zip_dwt2w_二维变换VC6.0
09-20
二维离散小波变换（2D Discrete Wavelet Transform，简称2D-DWT）是一种在图像处理领域广泛应用的数学工具，它将图像分解为不同频率的成分，以便于分析、压缩和重构。在这个名为“dwt2w.zip_dwt2w_二维变换VC6.0”的...
大数据项目之电商数仓（3电商数据仓库系统）V4.2.2.docx
09-07
《大数据项目之电商数仓（3电商数据仓库系统）V4.2.2》文档主要阐述了电商领域的大数据处理和数据仓库构建的关键概念和技术。本文档的核心内容围绕数仓的分层、理论基础以及数据建模方法展开。 首先，数仓的分层...
二级DWT.zip_DWT图像水印_dwt_matlab 二级DWT_小波_小波变换
07-15
在本压缩包中，"二级DWT.zip_DWT图像水印_dwt_matlab 二级DWT_小波_小波变换"的标题暗示了主要涉及的是小波变换在图像水印技术中的应用，以及使用MATLAB编程语言实现的过程。 首先，我们来理解一下小波变换的基本...
大数据项目之电商数仓（2用户行为数据仓库）.docx
08-03
此数仓项目文档详细介绍从项目需求分析到数据生成模块、数据采集模块、数仓各分层搭建再到业务实现的过程，其中各种环境的配置很细致，很有价值学习一下。
【电商数仓】数仓搭建之数据主题（data warehouse topic-- DWT）层（DWT层之用户、商品主题表格建立，数据导入）
weixin_43923463的博客
10-10 1344
DWT层主要对DWS层的数据进一步进行汇总各列的由来：由DWS层在每个时间跨度上进行扩充，时间跨度的选取取决于具体的需求，这里取c dxx3.这四个维度 2 数据导入 （1）首日导入 （2）每日导入 2020-06-15 昨天的累积数据已经有了，今天的数据在dws的用户主题表，用dws 2020-06-14的数据更新dwt截止到2020-06-14的累积数据。导入思路 二 商品主题 按照商品作为维度，把数据在时间的维度上进行汇总 2 数据导入 （1）首日导入 （2）每日导入
数据仓库——下篇
平平无奇小码农~
11-05 685
文章目录六、DWS层6.1 业务术语6.1.1 用户6.1.2 新增用户6.1.3 活跃用户6.1.4 周（月）活跃用户6.1.5 月活跃率6.1.6 沉默用户6.1.7 版本分布6.1.8 本周回流用户6.1.9 连续n周活跃用户6.1.10 忠诚用户6.1.11 连续活跃用户6.1.12 近期流失用户6.1.13 留存用户应用的6.1.14 用户新鲜度6.1.15 单次使用时长6.1.16 日使用时长6.1.17 启动次数计算标准6.2
数据仓库分层
最新发布
weixin_51450341的博客
03-07 291
注意：数仓分层的方式并不是固定的，可以根据业务场景灵活设计。例如可以在DWD层后插入DWM(数据中间层，可以作为DWD和DWS之间的过渡层，对数据进行进一步的加工和整合)并去除DWT。如果不使用分层架构，那么系统操作的数据都是原始数据，基于这样数据源编写的SQL语句会复杂庞大，且在发生错误的时候不易定位源头。数据集市是一种微型的数据仓库，存储的数据、主题、历史数据都更少一些，常在部门级进行使用。通过数据分层，可以对中间层数据进行规范，能够减少大量的重复计算，增加结果的复用性。解耦真实数据和计算数据。
数据仓库建模
不言尘世
05-30 489
1 ODS 层 层 （1）保持数据原貌不做任何修改，起到备份数据的作用。 （2）数据采用压缩，减少磁盘存储空间（例如：原始数据 100G，可以压缩到 10G 左右） （3）创建分区表，防止后续的全表扫描 2 DWD 层 DWD 层需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型。 维度建模一般按照以下四个步骤： 选择业务过程 → 声明粒度 → 确认维度 → 确认事实 ** （1 ）选择业务过程** 在业务系统中，挑选我们感兴趣的业务线，比如下单业务，支付业务，退款业务，物流 业务，一条业务线对应一
数据仓库之电商数仓-- 3.3、电商数据仓库系统(DWT层)
FunnyPrince_的博客
10-20 671
目录八、数仓搭建-DWT层8.1 访客主题8.2 用户主题8.3 商品主题8.4 优惠券主题8.5 活动主题8.6 地区主题8.7 DWT层首日数据导入脚本8.8 DWT层每日数据导入脚本 八、数仓搭建-DWT层 8.1 访客主题 建表语句 DROP TABLE IF EXISTS dwt_visitor_topic; CREATE EXTERNAL TABLE dwt_visitor_topic ( `mid_id` STRING COMMENT '设备id', `brand` STR
尚硅谷数据仓库实战之1项目需求及架构设计
weixin_42526326的博客
12-01 3775
数仓笔记 数据仓库和数据集市详解：ODS、DW、DWD、DWM、DWS、ADS 尚硅谷数仓实战之1项目需求及架构设计 尚硅谷数仓实战之2数仓分层+维度建模 尚硅谷数仓实战之3数仓搭建
【百度云提取】：JDK1.8API、Jdk1.8安装包、jQueryAPI文档、JavaScriptAPI文档、W3School相关API文档
CodingLJ
12-02 1742
永久有效 链接：https://pan.baidu.com/s/17icyNmNDmEICd0Y7EUjTCQ 提取码：aiu7 复制这段内容后打开百度网盘手机App，操作更方便哦--来自百度网盘超级会员V3的分享 JDK1.8 安装包 链接：https://pan.baidu.com/s/1e9LE0xT99taK4zXtpirfdw 提取码：z1xm 复制这段内容后打开百度网盘手机App，操作更方便哦--来自百度网盘超级会员V3的分享 ...
尚硅谷-离线数仓-笔记
林子茗的博客
07-11 1813
数据仓库是一个为数据分析而设计的企业级数据管理系统。数据仓库可集中、整合多个信息源的大量数据，借助数据仓库的分析能力，企业可从数据中获得宝贵的信息进而改进决策。同时，随着时间的推移，数据仓库中积累的大量历史数据对于数据科学家和业务分析师也是十分宝贵的。数据仓库（ Data Warehouse ），是为企业所有决策制定过程，提供所有系统数据支持的战略集合。通过对数据仓库中数据的分析，可以帮助企业，改进业务流程、控制成本、提高产品质量等。数据仓库，并不是数据的最终目的地，而是为数据最终的目的地做好准备。这些准备
电商数据仓库系统粒度声明及命名规范
尚硅谷大数据项目的电商数据仓库系统是一个用于存储和分析电商数据的系统。该系统的设计遵循了数据仓库的一些核心理论和原则。 在数据仓库设计中，声明粒度是非常重要的一部分，它意味着在事实表中的一行数据代表着...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值