【电商数仓】数仓搭建之服务数据（data warehouse service-- DWS）层（商品、优惠券、活动主题的建立与数据导入）

最新推荐文章于 2023-07-17 16:19:03 发布
最新推荐文章于 2023-07-17 16:19:03 发布
阅读量271 收藏 1
点赞数
分类专栏： 电商数仓 文章标签： 大数据 sql hadoop spark database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43923463/article/details/127141119
版权

文章目录

一 商品主题

商品主题表所有数据来源：

商品度量值
订单运费/优惠金额/原始金额/最终金额
订单详情√件数/优惠金额/原始金额/最终金额
支付支付金额
加购√件数/金额
收藏√次数
评价√次数
退单√件数/金额
退款√件数/金额
优惠券领用次数

1 建表语句

DROP TABLE IF EXISTS dws_sku_action_daycount;
CREATE EXTERNAL TABLE dws_sku_action_daycount
(
    `sku_id` STRING COMMENT 'sku_id',
    `order_count` BIGINT COMMENT '被下单次数',
    `order_num` BIGINT COMMENT '被下单件数',
    `order_activity_count` BIGINT COMMENT '参与活动被下单次数',
    `order_coupon_count` BIGINT COMMENT '使用优惠券被下单次数',
    `order_activity_reduce_amount` DECIMAL(16,2) 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_num` 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 '被退款金额',
    `cart_count` BIGINT COMMENT '被加入购物车次数',
    `favor_count` BIGINT COMMENT '被收藏次数',
    `appraise_good_count` BIGINT COMMENT '好评数',
    `appraise_mid_count` BIGINT COMMENT '中评数',
    `appraise_bad_count` BIGINT COMMENT '差评数',
    `appraise_default_count` BIGINT COMMENT '默认评价数'
) COMMENT '每日商品行为'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dws/dws_sku_action_daycount/'
TBLPROPERTIES ("orc.compress"="snappy");

2 数据导入

（1）首日导入

with
tmp_order as
(
    select
        date_format(create_time,'yyyy-MM-dd') dt,
        sku_id,
        count(*) order_count,
        sum(sku_num) order_num,
        sum(if(split_activity_amount>0,1,0)) order_activity_count,
        sum(if(split_coupon_amount>0,1,0)) order_coupon_count,
        sum(split_activity_amount) order_activity_reduce_amount,
        sum(split_coupon_amount) order_coupon_reduce_amount,
        sum(original_amount) order_original_amount,
        sum(split_final_amount) order_final_amount
    from dwd_order_detail
    -- 按照dt和商品id进行统计
    group by date_format(create_time,'yyyy-MM-dd'),sku_id
),
-- dwd_order_detail中的order_id存放所有下单的信息，但这些有的被支付，有些没被支付，join dwd_payment_info表区分开这些信息
tmp_pay as
(
    select
        date_format(callback_time,'yyyy-MM-dd') dt,
        sku_id,
        count(*) payment_count,
        sum(sku_num) payment_num,
        sum(split_final_amount) payment_amount
    from dwd_order_detail od
    join
    (
        select
            order_id,
            callback_time
        from dwd_payment_info
        -- 11.59分发起的订单，但没支付就不统计
        where callback_time is not null
    )pi on pi.order_id=od.order_id
    group by date_format(callback_time,'yyyy-MM-dd'),sku_id
),
-- 退单表
tmp_ri as
(
    select
        -- 按照退单创建时间进行分区
        date_format(create_time,'yyyy-MM-dd') dt,
        sku_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'),sku_id
),
-- 退款表中没有payment_num需要join退单表
tmp_rp as
(
    select
        -- 按照退款完成时间进行分区
        date_format(callback_time,'yyyy-MM-dd') dt,
        rp.sku_id,
        count(*) refund_payment_count,
        sum(ri.refund_num) refund_payment_num,
        sum(refund_amount) refund_payment_amount
    from
    (
        select
            order_id,
            sku_id,
            refund_amount,
            callback_time
        from dwd_refund_payment
    )rp
    left join
    (
        select
            order_id,
            sku_id,
            refund_num
        from dwd_order_refund_info
    )ri
    -- 同一单的同一件商品
    on rp.order_id=ri.order_id
    and rp.sku_id=ri.sku_id
    group by date_format(callback_time,'yyyy-MM-dd'),rp.sku_id
),
-- cart_favor的数据出自action_log，不能是cart_info和favor_info
tmp_cf as
(
    select
        dt,
        item sku_id,
        sum(if(action_id='cart_add',1,0)) cart_count,
        sum(if(action_id='favor_add',1,0)) favor_count
    from dwd_action_log
    where action_id in ('cart_add','favor_add')
    group by dt,item
),
-- 评价表
tmp_comment as
(
    select
        date_format(create_time,'yyyy-MM-dd') dt,
        sku_id,
        sum(if(appraise='1201',1,0)) appraise_good_count,
        sum(if(appraise='1202',1,0)) appraise_mid_count,
        sum(if(appraise='1203',1,0)) appraise_bad_count,
        sum(if(appraise='1204',1,0)) appraise_default_count
    from dwd_comment_info
    group by date_format(create_time,'yyyy-MM-dd'),sku_id
)
-- 汇总使用join方式效率太低，可以使用如下方法（union all）
insert overwrite table dws_sku_action_daycount partition(dt)
select
    sku_id,
    sum(order_count),
    sum(order_num),
    sum(order_activity_count),
    sum(order_coupon_count),
    sum(order_activity_reduce_amount),
    sum(order_coupon_reduce_amount),
    sum(order_original_amount),
    sum(order_final_amount),
    sum(payment_count),
    sum(payment_num),
    sum(payment_amount),
    sum(refund_order_count),
    sum(refund_order_num),
    sum(refund_order_amount),
    sum(refund_payment_count),
    sum(refund_payment_num),
    sum(refund_payment_amount),
    sum(cart_count),
    sum(favor_count),
    sum(appraise_good_count),
    sum(appraise_mid_count),
    sum(appraise_bad_count),
    sum(appraise_default_count),
    dt
from
(
    select
        dt,
        sku_id,
        order_count,
        order_num,
        order_activity_count,
        order_coupon_count,
        order_activity_reduce_amount,
        order_coupon_reduce_amount,
        order_original_amount,
        order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_order
    union all
    select
        dt,
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        payment_count,
        payment_num,
        payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_pay
    union all
    select
        dt,
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        refund_order_count,
        refund_order_num,
        refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_ri
    union all
    select
        dt,
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        refund_payment_count,
        refund_payment_num,
        refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_rp
    union all
    select
        dt,
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        cart_count,
        favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_cf
    union all
    select
        dt,
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        appraise_good_count,
        appraise_mid_count,
        appraise_bad_count,
        appraise_default_count
    from tmp_comment
)t1
group by dt,sku_id;

（2）每日导入

与首日导入的区别：不用按照dt进行分区，直接给定dt某一个具体的日期

with
tmp_order as
(
    select
        sku_id,
        count(*) order_count,
        sum(sku_num) order_num,
        sum(if(split_activity_amount>0,1,0)) order_activity_count,
        sum(if(split_coupon_amount>0,1,0)) order_coupon_count,
        sum(split_activity_amount) order_activity_reduce_amount,
        sum(split_coupon_amount) order_coupon_reduce_amount,
        sum(original_amount) order_original_amount,
        sum(split_final_amount) order_final_amount
    from dwd_order_detail
    where dt='2020-06-15'
    group by sku_id
),
tmp_pay as
(
    select
        sku_id,
        count(*) payment_count,
        sum(sku_num) payment_num,
        sum(split_final_amount) payment_amount
    from dwd_order_detail
    where (dt='2020-06-15'
    or dt=date_add('2020-06-15',-1))
    and order_id in
    (
        select order_id from dwd_payment_info where dt='2020-06-15'
    )
    group by sku_id
),
tmp_ri as
(
    select
        sku_id,
        count(*) refund_order_count,
        sum(refund_num) refund_order_num,
        sum(refund_amount) refund_order_amount
    from dwd_order_refund_info
    where dt='2020-06-15'
    group by sku_id
),
tmp_rp as
(
    select
        rp.sku_id,
        count(*) refund_payment_count,
        sum(ri.refund_num) refund_payment_num,
        sum(refund_amount) refund_payment_amount
    from
    (
        select
            order_id,
            sku_id,
            refund_amount
        from dwd_refund_payment
        where dt='2020-06-15'
    )rp
    left join
    (
        select
            order_id,
            sku_id,
            refund_num
        from dwd_order_refund_info
        where dt>=date_add('2020-06-15',-15)
    )ri
    on rp.order_id=ri.order_id
    and rp.sku_id=ri.sku_id
    group by rp.sku_id
),
tmp_cf as
(
    select
        item sku_id,
        sum(if(action_id='cart_add',1,0)) cart_count,
        sum(if(action_id='favor_add',1,0)) favor_count
    from dwd_action_log
    where dt='2020-06-15'
    and action_id in ('cart_add','favor_add')
    group by item
),
tmp_comment as
(
    select
        sku_id,
        sum(if(appraise='1201',1,0)) appraise_good_count,
        sum(if(appraise='1202',1,0)) appraise_mid_count,
        sum(if(appraise='1203',1,0)) appraise_bad_count,
        sum(if(appraise='1204',1,0)) appraise_default_count
    from dwd_comment_info
    where dt='2020-06-15'
    group by sku_id
)
insert overwrite table dws_sku_action_daycount partition(dt='2020-06-15')
select
    sku_id,
    sum(order_count),
    sum(order_num),
    sum(order_activity_count),
    sum(order_coupon_count),
    sum(order_activity_reduce_amount),
    sum(order_coupon_reduce_amount),
    sum(order_original_amount),
    sum(order_final_amount),
    sum(payment_count),
    sum(payment_num),
    sum(payment_amount),
    sum(refund_order_count),
    sum(refund_order_num),
    sum(refund_order_amount),
    sum(refund_payment_count),
    sum(refund_payment_num),
    sum(refund_payment_amount),
    sum(cart_count),
    sum(favor_count),
    sum(appraise_good_count),
    sum(appraise_mid_count),
    sum(appraise_bad_count),
    sum(appraise_default_count)
from
(
    select
        sku_id,
        order_count,
        order_num,
        order_activity_count,
        order_coupon_count,
        order_activity_reduce_amount,
        order_coupon_reduce_amount,
        order_original_amount,
        order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_order
    union all
    select
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        payment_count,
        payment_num,
        payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_pay
    union all
    select
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        refund_order_count,
        refund_order_num,
        refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_ri
    union all
    select
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        refund_payment_count,
        refund_payment_num,
        refund_payment_amount,
        0 cart_count,
        0 favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_rp
    union all
    select
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        cart_count,
        favor_count,
        0 appraise_good_count,
        0 appraise_mid_count,
        0 appraise_bad_count,
        0 appraise_default_count
    from tmp_cf
    union all
    select
        sku_id,
        0 order_count,
        0 order_num,
        0 order_activity_count,
        0 order_coupon_count,
        0 order_activity_reduce_amount,
        0 order_coupon_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        0 payment_num,
        0 payment_amount,
        0 refund_order_count,
        0 refund_order_num,
        0 refund_order_amount,
        0 refund_payment_count,
        0 refund_payment_num,
        0 refund_payment_amount,
        0 cart_count,
        0 favor_count,
        appraise_good_count,
        appraise_mid_count,
        appraise_bad_count,
        appraise_default_count
    from tmp_comment
)t1
group by sku_id;

二 优惠券主题

优惠券表数据来源：

优惠券度量值
订单运费/优惠金额/原始金额/最终金额
订单详情√件数/优惠金额/原始金额/最终金额
支付支付金额
加购件数/金额
收藏次数
评价次数
退单件数/金额
退款件数/金额
优惠券领用√次数

1 建表语句

DROP TABLE IF EXISTS dws_coupon_info_daycount;
CREATE EXTERNAL TABLE dws_coupon_info_daycount(
    `coupon_id` STRING COMMENT '优惠券ID',
    -- 从领用表确定
    `get_count` BIGINT COMMENT '领取次数',
    -- 从订单详情表确定
    `order_count` BIGINT COMMENT '使用(下单)次数', 
    `order_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_reduce_amount` DECIMAL(16,2) COMMENT '使用某券的支付优惠金额',
    `payment_amount` DECIMAL(16,2) COMMENT '使用某券支付的总金额',
    -- 从领用表确定
    `expire_count` BIGINT COMMENT '过期次数'
) COMMENT '每日活动统计'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dws/dws_coupon_info_daycount/'
TBLPROPERTIES ("orc.compress"="snappy");

2 数据导入

（1）首日导入

with
-- 统计优惠券获得、下单、支付、过期的数量
tmp_cu as
(
    select
        coalesce(coupon_get.dt,coupon_using.dt,coupon_used.dt,coupon_exprie.dt) dt,
        coalesce(coupon_get.coupon_id,coupon_using.coupon_id,coupon_used.coupon_id,coupon_exprie.coupon_id) coupon_id,
        nvl(get_count,0) get_count,
        nvl(order_count,0) order_count,
        nvl(payment_count,0) payment_count,
        nvl(expire_count,0) expire_count
    from
    (
        select
            date_format(get_time,'yyyy-MM-dd') dt,
            coupon_id,
            count(*) get_count
        from dwd_coupon_use
        group by date_format(get_time,'yyyy-MM-dd'),coupon_id
    )coupon_get
    full outer join
    (
        select
            date_format(using_time,'yyyy-MM-dd') dt,
            coupon_id,
            count(*) order_count
        from dwd_coupon_use
        where using_time is not null
        group by date_format(using_time,'yyyy-MM-dd'),coupon_id
    )coupon_using
    on coupon_get.dt=coupon_using.dt
    and coupon_get.coupon_id=coupon_using.coupon_id
    full outer join
    (
        select
            date_format(used_time,'yyyy-MM-dd') dt,
            coupon_id,
            count(*) payment_count
        from dwd_coupon_use
        where used_time is not null
        group by date_format(used_time,'yyyy-MM-dd'),coupon_id
    )coupon_used
    on nvl(coupon_get.dt,coupon_using.dt)=coupon_used.dt
    and nvl(coupon_get.coupon_id,coupon_using.coupon_id)=coupon_used.coupon_id
    full outer join
    (
        select
            date_format(expire_time,'yyyy-MM-dd') dt,
            coupon_id,
            count(*) expire_count
        from dwd_coupon_use
        where expire_time is not null
        group by date_format(expire_time,'yyyy-MM-dd'),coupon_id
    )coupon_exprie
    on coalesce(coupon_get.dt,coupon_using.dt,coupon_used.dt)=coupon_exprie.dt
    and coalesce(coupon_get.coupon_id,coupon_using.coupon_id,coupon_used.coupon_id)=coupon_exprie.coupon_id
),
-- 统计下单金额
tmp_order as
(
    select
        date_format(create_time,'yyyy-MM-dd') dt,
        coupon_id,
        sum(split_coupon_amount) order_reduce_amount,
        sum(original_amount) order_original_amount,
        sum(split_final_amount) order_final_amount
    from dwd_order_detail
    where coupon_id is not null
    group by date_format(create_time,'yyyy-MM-dd'),coupon_id
),
-- 统计下单的金额中有多少被支付了
tmp_pay as
(
    select
        date_format(callback_time,'yyyy-MM-dd') dt,
        coupon_id,
        sum(split_coupon_amount) payment_reduce_amount,
        sum(split_final_amount) payment_amount
    from
    (
        select
            order_id,
            coupon_id,
            split_coupon_amount,
            split_final_amount
        from dwd_order_detail
        where coupon_id is not null
    )od
    join
    (
        select
            order_id,
            callback_time
        from dwd_payment_info
    )pi
    on od.order_id=pi.order_id
    group by date_format(callback_time,'yyyy-MM-dd'),coupon_id
)
insert overwrite table dws_coupon_info_daycount partition(dt)
select
    coupon_id,
    sum(get_count),
    sum(order_count),
    sum(order_reduce_amount),
    sum(order_original_amount),
    sum(order_final_amount),
    sum(payment_count),
    sum(payment_reduce_amount),
    sum(payment_amount),
    sum(expire_count),
    dt
from
(
    select
        dt,
        coupon_id,
        get_count,
        order_count,
        0 order_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        payment_count,
        0 payment_reduce_amount,
        0 payment_amount,
        expire_count
    from tmp_cu
    union all
    select
        dt,
        coupon_id,
        0 get_count,
        0 order_count,
        order_reduce_amount,
        order_original_amount,
        order_final_amount,
        0 payment_count,
        0 payment_reduce_amount,
        0 payment_amount,
        0 expire_count
    from tmp_order
    union all
    select
        dt,
        coupon_id,
        0 get_count,
        0 order_count,
        0 order_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        payment_reduce_amount,
        payment_amount,
        0 expire_count
    from tmp_pay
)t1
group by dt,coupon_id;

（2）每日导入

将首日导入中需要分区的dt变成dt=XXXX-XX-XX

with
tmp_cu as
(
    select
        coupon_id,
        sum(if(date_format(get_time,'yyyy-MM-dd')='2020-06-15',1,0)) get_count,
        sum(if(date_format(using_time,'yyyy-MM-dd')='2020-06-15',1,0)) order_count,
        sum(if(date_format(used_time,'yyyy-MM-dd')='2020-06-15',1,0)) payment_count,
        sum(if(date_format(expire_time,'yyyy-MM-dd')='2020-06-15',1,0)) expire_count
    from dwd_coupon_use
    where dt='9999-99-99'
    or dt='2020-06-15'
    group by coupon_id
),
-- 事务型事实表，只处理当天新增的数据，没有9999-99-99分区
tmp_order as
(
    select
        coupon_id,
        sum(split_coupon_amount) order_reduce_amount,
        sum(original_amount) order_original_amount,
        sum(split_final_amount) order_final_amount
    from dwd_order_detail
    where dt='2020-06-15'
    and coupon_id is not null
    group by coupon_id
),
tmp_pay as
(
    select
        coupon_id,
        sum(split_coupon_amount) payment_reduce_amount,
        sum(split_final_amount) payment_amount
    from dwd_order_detail
    where (dt='2020-06-15'
    or dt=date_add('2020-06-15',-1))
    and coupon_id is not null
    and order_id in
    (
        select order_id from dwd_payment_info where dt='2020-06-15'
    )
    group by coupon_id
)
insert overwrite table dws_coupon_info_daycount partition(dt='2020-06-15')
select
    coupon_id,
    sum(get_count),
    sum(order_count),
    sum(order_reduce_amount),
    sum(order_original_amount),
    sum(order_final_amount),
    sum(payment_count),
    sum(payment_reduce_amount),
    sum(payment_amount),
    sum(expire_count)
from
(
    select
        coupon_id,
        get_count,
        order_count,
        0 order_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        payment_count,
        0 payment_reduce_amount,
        0 payment_amount,
        expire_count
    from tmp_cu
    union all
    select
        coupon_id,
        0 get_count,
        0 order_count,
        order_reduce_amount,
        order_original_amount,
        order_final_amount,
        0 payment_count,
        0 payment_reduce_amount,
        0 payment_amount,
        0 expire_count
    from tmp_order
    union all
    select
        coupon_id,
        0 get_count,
        0 order_count,
        0 order_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        0 payment_count,
        payment_reduce_amount,
        payment_amount,
        0 expire_count
    from tmp_pay
)t1
group by coupon_id;

三 活动主题

活动主题表数据来源

活动度量值
订单运费/优惠金额/原始金额/最终金额
订单详情√件数/优惠金额/原始金额/最终金额
支付支付金额
加购件数/金额
收藏次数
评价次数
退单件数/金额
退款件数/金额
优惠券领用次数

1 建表语句

DROP TABLE IF EXISTS dws_activity_info_daycount;
CREATE EXTERNAL TABLE dws_activity_info_daycount(
    `activity_rule_id` STRING COMMENT '活动规则ID',
    `activity_id` STRING COMMENT '活动ID',
    -- 数据来源：order_detail
    `order_count` BIGINT COMMENT '参与某活动某规则下单次数',--注意：针对的是某个活动的某个具体规则
    `order_reduce_amount` DECIMAL(16,2) COMMENT '参与某活动某规则下单减免金额',
    `order_original_amount` DECIMAL(16,2) COMMENT '参与某活动某规则下单原始金额',--只统计参与活动的订单明细,未参与活动的订单明细不统计。
    `order_final_amount` DECIMAL(16,2) COMMENT '参与某活动某规则下单最终金额',
    -- 数据来源：order_detail join payment_info
    `payment_count` BIGINT COMMENT '参与某活动某规则支付次数',
    `payment_reduce_amount` DECIMAL(16,2) COMMENT '参与某活动某规则支付减免金额',
    `payment_amount` DECIMAL(16,2) COMMENT '参与某活动某规则支付金额'
) COMMENT '每日活动统计'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dws/dws_activity_info_daycount/'
TBLPROPERTIES ("orc.compress"="snappy");

2 数据导入

（1）首日导入

with
tmp_order as
(
    select
        date_format(create_time,'yyyy-MM-dd') dt,
        activity_rule_id,
        activity_id,
        count(*) order_count,
        sum(split_activity_amount) order_reduce_amount,
        sum(original_amount) order_original_amount,
        sum(split_final_amount) order_final_amount
    from dwd_order_detail
    where activity_id is not null
    group by date_format(create_time,'yyyy-MM-dd'),activity_rule_id,activity_id
),
tmp_pay as
(
    select
        date_format(callback_time,'yyyy-MM-dd') dt,
        activity_rule_id,
        activity_id,
        count(*) payment_count,
        sum(split_activity_amount) payment_reduce_amount,
        sum(split_final_amount) payment_amount
    from
    (
        select
            activity_rule_id,
            activity_id,
            order_id,
            split_activity_amount,
            split_final_amount
        from dwd_order_detail
        where activity_id is not null
    )od
    join
    (
        select
            order_id,
            callback_time
        from dwd_payment_info
    )pi
    on od.order_id=pi.order_id
    group by date_format(callback_time,'yyyy-MM-dd'),activity_rule_id,activity_id
)
insert overwrite table dws_activity_info_daycount partition(dt)
select
    activity_rule_id,
    activity_id,
    sum(order_count),
    sum(order_reduce_amount),
    sum(order_original_amount),
    sum(order_final_amount),
    sum(payment_count),
    sum(payment_reduce_amount),
    sum(payment_amount),
    dt
from
(
    select
        dt,
        activity_rule_id,
        activity_id,
        order_count,
        order_reduce_amount,
        order_original_amount,
        order_final_amount,
        0 payment_count,
        0 payment_reduce_amount,
        0 payment_amount
    from tmp_order
    union all
    select
        dt,
        activity_rule_id,
        activity_id,
        0 order_count,
        0 order_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        payment_count,
        payment_reduce_amount,
        payment_amount
    from tmp_pay
)t1
group by dt,activity_rule_id,activity_id;

（2）每日导入

with
tmp_order as
(
    select
        activity_rule_id,
        activity_id,
        count(*) order_count,
        sum(split_activity_amount) order_reduce_amount,
        sum(original_amount) order_original_amount,
        sum(split_final_amount) order_final_amount
    from dwd_order_detail
    where dt='2020-06-15'
    and activity_id is not null
    group by activity_rule_id,activity_id
),
tmp_pay as
(
    select
        activity_rule_id,
        activity_id,
        count(*) payment_count,
        sum(split_activity_amount) payment_reduce_amount,
        sum(split_final_amount) payment_amount
    from dwd_order_detail
    where (dt='2020-06-15'
    or dt=date_add('2020-06-15',-1))
    and activity_id is not null
    and order_id in
    (
        select order_id from dwd_payment_info where dt='2020-06-15'
    )
    group by activity_rule_id,activity_id
)
insert overwrite table dws_activity_info_daycount partition(dt='2020-06-15')
select
    activity_rule_id,
    activity_id,
    sum(order_count),
    sum(order_reduce_amount),
    sum(order_original_amount),
    sum(order_final_amount),
    sum(payment_count),
    sum(payment_reduce_amount),
    sum(payment_amount)
from
(
    select
        activity_rule_id,
        activity_id,
        order_count,
        order_reduce_amount,
        order_original_amount,
        order_final_amount,
        0 payment_count,
        0 payment_reduce_amount,
        0 payment_amount
    from tmp_order
    union all
    select
        activity_rule_id,
        activity_id,
        0 order_count,
        0 order_reduce_amount,
        0 order_original_amount,
        0 order_final_amount,
        payment_count,
        payment_reduce_amount,
        payment_amount
    from tmp_pay
)t1
group by activity_rule_id,activity_id;
OneTenTwo76
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
【电商数仓】数仓搭建之服务数据（data warehouse service-- DWS）层（DWS层概述、几个系统函数和用户主题的建立与数据导入）
weixin_43923463的博客
10-07 921
DWD层：将数据重新建模，以粒度最细的方式将所有的**明细**数据放入DWD层。在一个公司中，会计或者财务会关心明细，将明细总结形成一张资产负债表交给老板。 数仓最终形成的表格不是明细层，数据量十分大，可读性差。 最终呈现的数据是汇总过的数据，所以在DWS层需要将数据以感兴趣的角度进行汇总，如下sql，为分组汇总 按照维度进行汇总，六个维度表（商品、优惠券、活动、地区、时间、用户）。 所有能够汇总的事实按照用户维度进行汇总，就得到了一张用户主题汇总表，所有能够汇总的事实如下表所示：
【电商数仓】数仓搭建之服务数据（data warehouse service-- DWS）层（DWS层之地区、访客主题，时间维度，首日与每日导入脚本）
weixin_43923463的博客
10-09 255
在/home/hzy/bin目录下创建脚本dwd_to_dws_init.sh。在/home/hzy/bin目录下创建脚本dwd_to_dws.sh。综上，时间维度没有进行汇总。时间维度没有进行汇总，因为。
快速入门数据仓库（Data WareHouse）
丧心病狂Loli控的博客
11-13 554
在很久很久之前，异世界里生活着许许多多的种族，有人类、有精灵、有兽人，还有哥布林、魔王…   这个异世界的神想要统一的管理这些种族，于是神打造了多个象征权力的戒指，分发给每个种族的首领——这个戒指可以帮助他们更好的管理和控制各自的种族。   而神拥有一个独一无二、至高无上的戒指，从而控制每个种族的领袖，进而控制整个异世界。   这个异世界的每个生命就是一条数据（Data），每个种族首领拥有的戒指就是数据库（Data Base），而那个最独一无二的戒指就是数据仓库（Data W.
数据仓库 DataWarehouse 学习
py_tamir的博客
04-05 2038
目录 1、定义 2、特点 3、元数据 4、建模方法 5、星型模型与雪花模型 6、数据库与数据仓库区别 1、定义 出于分析性报告和决策支持目的而创建，提供所有类型数据支持的战略集合。 2、特点 数据面向主题 数据是集成的 数据是不可更新的 数据是随时间变化的 3、元数据 存储方式一：数据集 存储方式二：元数据库，一般使用MySQL 4、建模方法 范式建模 维度建模 实...
【电商数仓】数仓搭建之数据应用（application data stpre -- ADS）层（数仓血缘图，ADS层之优惠券、活动、商品（一、二）、订单主题（一、二））
weixin_43923463的博客
10-13 662
品牌复购率是指一段时间内重复购买某品牌的人数与购买过该品牌的人数的比值。重复购买即购买次数大于等于2，购买过即购买次数大于1。该指标为商品综合统计，包含每个spu被下单总次数和被下单总金额。要求统计最近30日发布的所有优惠券的领用情况和补贴率。补贴率：总优惠金额与使用优惠券的订单的原价金额的比值。补贴率：总优惠金额与参与活动的订单原价金额的比值。统计最近30日发布的所有活动的参与情况和补贴率。该需求包含订单总金额和下单总人数。统计最近30天的各品牌复购率。该需求包含订单总数。
大数据项目之电商数仓（2用户行为数据仓库）.docx
11-18
* DWS 层（Data Warehouse Summary）：是指汇总数据的存储层，用于存储电商平台上的汇总数据。 * ADS 层（Application Data Store）：是指应用数据的存储层，用于存储电商平台上的应用数据。 数仓搭建环境准备 电商...
数仓建模-数据仓库（Data Warehouse）中对数据进行组织、设计和建模
最新发布
06-02
数仓建模是指在数据仓库（Data Warehouse）中对数据进行组织、设计和建模的过程。这个过程旨在创建一个能够支持数据分析和业务决策的结构化数据环境。下面是数仓建模的一般步骤： 1. **需求分析：** 首先，了解业务...
企业级电商离线数仓项目-2022
07-22
1991年，Bill Inmon出版了他的第一本关于数据仓库的书《Building the Data Warehouse》，标志着数据仓库概念的确立。数据仓库的基本原理、技术架构以及分析系统的主要原则都已确定，数据仓库初具雏形。 2. 数据仓库...
数据仓库维度建模实践-体系搭建-网易01.pdf
10-30
大师著作的《The Data Warehouse Toolkit-The Complete Guide to Dimensional Modeling》是数仓工程领域最流行的维度建模经典。 在具体建模之前，需要规划好我们的数据体系架构，划定边界，搭建统一规范的体系架构...
基于hadoop生态搭建的电商数据仓库，整体功能架构包含数据采集、数仓搭建、数据导出、数据可视化等.zip
10-01
在压缩包文件"mall-data-warehouse-master"中，可能包含了实现以上功能的相关代码和配置文件，这通常涵盖Hadoop集群的配置、数据模型设计、ETL脚本、查询语句、以及可视化报表的模板或接口。通过深入研究这些文件，...
【电商数仓】数仓搭建之数据主题（data warehouse topic-- DWT）层（DWT层之访客、优惠券、活动、地区主题）
weixin_43923463的博客
10-11 627
【代码】【电商数仓】数仓搭建之数据主题（data warehouse topic-- DWT）层（DWT层之访客、优惠券、活动、地区主题）
6.4 离线数仓—ADS层活动及优惠券主题需求的实现
yunweijie_cn的博客
03-05 279
ADS层设计开发前言一、最近30天发布的活动的补贴率1.需求说明和分析2.建表语句二、使用步骤1.需求说明和分析2.读入数据总结 前言 前面完成了ADS层交易主题相关需求的设计和开发，下面进行ADS层活动及优惠券主题的设计和开发。 一、最近30天发布的活动的补贴率 1.需求说明和分析 1）需求说明 统计粒度 指标 说明 活动 补贴率 参与促销活动的订单明细活动减免金额总和/原始金额总和 2）字段分析 根据需求可知，该表要包含以下字段：统计日期、活动ID、与活动相关的维度（至少有活动名称
GaussDB(DWS)介绍
热门推荐
Forlogenの解忧杂货铺
01-06 2万+
数据仓库服务（Data Warehouse Service，GaussDB（DWS））是一种基于公有云基础架构和平台的在线数据处理数据库，提供即开即用、可扩展且完全托管的分析型数据库服务。 1. 简介 GaussDB（DWS）是基于华为融合数据仓库GaussDB产品的云原生服务，兼容标准ANSI SQL 99和SQL 2003，同时兼容PsotgreSQL/Oracle数据库生态，为各行各业提供PB级大数据分析解决方案。 其中： ● DWS：基于MPP架构的数据库，提供PB级数据负载能力、百TB级数.
数据仓库十大主题；TeraData金融数据模型
Jackie 的博客
04-03 1万+
　　Teradata天睿公司（纽交所代码：TDC），是美国前十大上市软件公司之一。经过逾30 年的发展，Teradata天睿公司已经成为全球最大的专注于大数据分析、数据仓库和整合营销管理解决方案的供应商。其提出一种先进的FS-LDM模型(Financial Services LogcialData Model) --企业级数据模型，包括金融机构业务数据，囊括了银行约80%的业务数据，并把预定义的...
数据仓库——分层原理
weixin_46010244的博客
07-17 5997
一、数仓建模的意义，为什么要对数据仓库分层？ 只有用数据模型将数据有序的组织和存储起来之后，大数据才能得到高性能、低成本、高效率、高质量的使用。 1、清晰数据结构：每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。数据关系条理化：源系统间存在复杂的数据关系，比如客户信息同时存在于核心系统、信贷系统、理财系统、资金系统，取数时该如何决策呢？数据仓库会对相同主题的数据进行统一建模，把复杂的数据关系梳理成条理清晰的数据模型，使用时就可避免上述问题了。 2、数据血缘
【数仓系列】数仓分层的意义价值及如何设计数据分层
helloxiaozhe的博客
09-17 7604
文章目录 一、前言 二、数仓建模 三、数仓分层 四、数仓的基本特征 五、数据仓库用途 六、数仓分层的好处 七、如何分层 一、前言 现在说数仓，更多的会和数据平台或者基础架构搭上，已经融合到整个基础设施的搭建上。这里呢，我们不说Hadoop各种组件之间的配合，我们就简单说下数仓分层的意义价值和该如何设计分层。 二、数仓建模 说到数仓建模，就得提下经典的2套理论： 范式建模 Inmon提出的集线器的自上而下（EDW-DM）的数据仓库架构。 维度建模 Kimbal...
【电商数仓】数仓搭建之数据主题（data warehouse topic-- DWT）层（DWT层之用户、商品主题表格建立，数据导入）
weixin_43923463的博客
10-10 1332
DWT层主要对DWS层的数据进一步进行汇总各列的由来：由DWS层在每个时间跨度上进行扩充，时间跨度的选取取决于具体的需求，这里取c dxx3.这四个维度 2 数据导入 （1）首日导入 （2）每日导入 2020-06-15 昨天的累积数据已经有了，今天的数据在dws的用户主题表，用dws 2020-06-14的数据更新dwt截止到2020-06-14的累积数据。导入思路 二 商品主题 按照商品作为维度，把数据在时间的维度上进行汇总 2 数据导入 （1）首日导入 （2）每日导入
优惠券数据库设计
竹林幽深
12-26 2143
https://blog.51cto.com/u_15776740/5916257?articleABtest=1
数仓(三)：分层设计 ODS-DWD-DWS-ADS
.
11-27 2万+
一、数仓建模的意义，为什么要对数据仓库分层？ 只有数据模型将数据有序的组织和存储起来之后，大数据才能得到高性能、低成本、高效率、高质量的使用。 1、清晰数据结构：每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。 数据关系条理化：源系统间存在复杂的数据关系，比如客户信息同时存在于核心系统、信贷系统、理财系统、资金系统，取数时该如何决策呢？数据仓库会对相同主题的数据进行统一建模，把复杂的数据关系梳理成条理清晰的数据模型，使用时就可避免上述问题了。 2、数据血缘追...
电商数仓实践：用户行为数据仓库搭建与环境配置
这个项目首先介绍了数仓分层的概念，解释了为什么要进行数据分层，以及各个层次如ODS(Operational Data Store)、DWD(Detailed Data Warehouse)、DWS(Dimensional Data Warehouse)和ADS(Approved Data Service)的作用...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

OneTenTwo76

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值