离线数仓(九)

9 数仓搭建-ADS

9.1 建表说明

  ADS层不涉及建模,建表根据具体需求而定

9.2 访客主题

9.2.1 访客统计

  1.建表语句

drop table if exists ads_visit_stats;
create external table ads_visit_stats(
    `dt` string comment '统计日期',
    `is_new` string comment '新老标识,1:新,0:老',
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `channel` string comment '渠道',
    `uv_count` bigint comment '日活(访问人数)',
    `duration_sec` bigint comment '页面停留总时长',
    `avg_duration_sec` bigint comment '一次会话,页面停留平均时长,单位为描述',
    `page_count` bigint comment '页面总浏览数',
    `avg_page_count` bigint comment '一次会话,页面平均浏览数',
    `sv_count` bigint comment '会话次数',
    `bounce_count` bigint comment '跳出数',
    `bounce_rate` decimal(16,2) comment '跳出率'
) comment '访客统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_visit_stats/';

  2.数据装载

-- 指定spark生成的文件,自动进行合并
set hive.merge.sparkfiles=true;
-- 256000000 默认合并后的文件,不超过256M,如果超过256M,自动滚动,产生新的文件 128M
set hive.merge.size.per.task;
--为了解决小文件问题,在写入ads层时,先把历史数据查询出来和今天的指标合并,之后覆盖写入到ads层表目录,所有的数据,只有一个文件
insert overwrite table ads_visit_stats
select *
from ads_visit_stats
union all
--求不同的is_new,channal,recent_days 分组下,所有session的指标
select '2021-06-08' dt,             --统计日期
       is_new,                      --新老标识,1:新,0:老
       recent_days,                 --最近天数,1:最近1天,7:最近7天,30:最近30天
       channel,                     --渠道
       -- 人数(设备数) 不是 人次,人数应该按照mid_id进行去重统计
       count(distinct split(session_id,'-')[0]) uv_count,   --日活(访问人数)
       sum(duration_sec_per_session) duration_sec,          --页面停留总时长
       avg(duration_sec_per_session) avg_duration_sec,      --一次会话,页面停留平均时长,单位为描述
       sum(page_count_per_session) page_count,              --页面总浏览数
       avg(page_count_per_session) avg_page_count,          --一次会话,页面平均浏览数
       count(*) sv_count,                                   --会话次数
       sum(`if`(page_count_per_session = 1,1,0))  bounce_count,     --跳出数:指仅有一次访问行为的会话的数量
       cast(sum(`if`(page_count_per_session = 1,1,0)) / count(*) * 100 as DECIMAL(16,2)) bounce_rate    --跳出率:跳出数 / 会话总数  * 100
from
(
    --求不同的is_new,channal,recent_days 分组下,每个session 页面停留总时长 和  page_count
    select is_new,                                          --是否首次启动
           channel,                                         --渠道
           recent_days,                                     --最近天数
           session_id,                                      --sessionId
           count(*) page_count_per_session,                 --页面总浏览数
           sum(during_time)  duration_sec_per_session       --页面停留总时长
    from
    (
        -- 求出每次会话的session_id
        -- 统计日期指统计的数据的日期
        select  '2021-06-08' dt,
                is_new,             --是否首次启动
                channel,            --渠道
                mid_id,             --设备id
                during_time,        --持续时间
                last_page_id,       --上页类型
                page_id,            --页面id
                ts,                 --时间
                recent_days,        --最近天数
                concat(mid_id,'-',last_value(`if`(last_page_id is null ,ts,null) ,true) over (partition by mid_id order by ts )) session_id  --sessionId
        from dwd_page_log
        --侧写
        lateral view explode(`array`(1,7,30)) tmp as recent_days
        -- 要求最近7天和30天的统计指标,要从dwd事实中取数据的范围,应该在最近30天
        where dt > date_sub('2021-06-08',30)
        --过滤
        and dt > date_sub('2021-06-08',recent_days)
    ) t1
-- 按照is_new,channal,recent_days分组
group by is_new,channel,recent_days,session_id )t2
group by is_new,channel,recent_days;

9.2.2 路径分析

  1.建表语句

drop table if exists ads_page_path;
create external table ads_page_path(
    `dt` string comment '统计日期',
    --分组的字段
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    --开窗
    `source` string comment '跳转起始页面ID',
    `target` string comment '跳转终到页面ID',
    `path_count` bigint comment '跳转次数'
) comment '页面浏览路径'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_page_path/'

  2.数据装载

insert overwrite table ads_page_path
select * from ads_page_path
union all
select  '2021-06-08' dt,
        --求每个session中,从A页面跳转到B页面跳转了多少次
        recent_days,
        source,
        target,
        count(*)  path_count
from (
    -- 由于产生了闭环,因此需要给一次session访问的所有的页面路径,根据访问的顺序添加id,避免产生闭环
     select recent_days,
            concat(source, '-', rn)     source,
            concat(target, '-', rn + 1) target
     from (
              --求每个session中,根据页面的访问顺序,生成source和target
              select recent_days,                                                                    --最近天数
                     last_page_id,                                                                   --上页类型
                     page_id,                                                                        --页面id
                     page_id                                                                 source, --跳转起始页面ID
                     lead(page_id, 1) over (partition by session_id,recent_days order by ts) target, --跳转终到页面ID
                     mid_id,                                                                         --设备id
                     session_id,
                     row_number() over (partition by session_id,recent_days order by ts)     rn
              from (
                       --先求出了每个页面访问记录的session_id
                       select recent_days,  --最近天数
                              last_page_id, --上页类型
                              page_id,      --页面id
                              mid_id,       --设备id
                              ts,           --时间
                              concat(mid_id, '-', last_value(`if`(last_page_id is null, ts, null), true)
                                                             over (partition by mid_id order by ts)) session_id
                       from dwd_page_log
                                --侧写
                                lateral view explode(`array`(1, 7, 30)) tmp as recent_days
                            --全局过滤
                       where dt > date_sub('2021-06-08', 30)
                         --侧写后过滤
                         and dt > date_sub('2021-06-08', recent_days)
                   ) tmp
          ) tmp1
    )tmp2
group by recent_days,source,target;

9.3 用户主题

9.3.1 用户统计

  1.建表语句

drop table if exists ads_user_totalt;
create external table ads_user_totalt(
    `dt` string comment '统计日期',
    `recent_days` bigint comment '最近天数,0:累积值,1:最近1天,7:最近7天,30:最近30天',
    `new_user_count` bigint comment '新注册用户数',
    `new_order_user_count` bigint comment '新增下单用户数',
    `order_final_amount` decimal(16,2) comment '下单总金额',
    `order_user_count` bigint comment '下单用户数',
    `no_order_user_count` bigint comment '未下单用户数(具体指活跃用户中未下单用户)'
) comment '用户统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_total/';

  2.数据装载

insert overwrite table ads_user_totalt
select *
from ads_user_totalt
union all
select '2021-06-08' dt,
       recent_days,
       sum(`if`(login_date_first > date_sub('2021-06-08',x),1,0)) new_user_count,               --新注册用户数
       sum(`if`(order_date_first > date_sub('2021-06-08',x),1,0)) new_order_user_count,         --新增下单用户数
       sum(order_final_amount) order_final_amount,                                              --下单总金额
       sum(`if`(order_final_amount > 0,1,0)) order_user_count,                                  --下单用户数
       sum(`if`(login_date_last > date_sub('2021-06-08',x) and order_final_amount = 0, 1,0)) no_order_user_count    --只登陆不下单的用户数
from
(
    select recent_days,
           user_id,
           login_date_last,     --末次活跃日期
           login_date_first,    --首次活跃日期
           order_date_first,    --首次下单时间
           case recent_days
                when 0 then order_final_amount
                when 1 then order_last_1d_final_amount
                when 7 then order_last_7d_final_amount
                when 30 then order_last_30d_final_amount
            end order_final_amount,                         --累计最终下单金额
           `if`(recent_days = 0, 65555, recent_days) x      --动态变化:如果是0,表示累计数,否则就是recent_days
    from dwt_user_topic
    lateral view explode(`array`(0,1,7,30)) tmp as recent_days
    where dt = '2021-06-08'
) t1
group by recent_days;

9.3.2 用户变动统计

  1.建表语句

drop table if exists ads_user_change;
create external table ads_user_change(
    `dt` string comment '统计日期',
    `user_churn_count` bigint comment '流失用户数',  --最近7天未活跃的用户=login_date_last <= date_sub(统计日期, 7),也就是说最后一次登录是在7天以前
    `user_back_count` bigint comment '回流用户数'   --本周未活跃的老用户 = 本周活跃的老用户 与 上周活跃的老用户 之间的差集
) comment '用户变动统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_change/';

  2.数据装载

insert overwrite table ads_user_change
select * from ads_user_change
union all
select '2021-06-08' dt,
       user_churn_count,
       user_back_count
from
(
    select count(*) user_back_count     --回流用户数
    from
    (
        --求出本周活跃的老用户
        select user_id
        from dwt_user_topic
        where dt = '2021-06-08'
        --本周活跃= 最后一次活跃时间 > 7天前的时间
        and login_date_last >= date_sub(next_day('2021-06-08','Monday'),7)
        --老用户:第一次活跃时间 < 7天前,也就是说7天前活跃过
        and login_date_first < date_sub(next_day('2021-06-08','Monday'),7)
    ) t1
    left join
    (
        --求上周活跃的用户,注意无法从dwt层求出,dwt层的数据是每日更新,历史状态是无法保留的, 只能从dws取上周时间范围的数据
        select user_id
        from dws_user_action_daycount
        where dt between date_sub(next_day('2021-06-08','Monday'),14) and date_sub(next_day('2021-06-08','Monday'),8)
        --分组去重
        group by user_id
    ) t2 on t1.user_id = t2.user_id
where t2.user_id is null ) t3
join
(
    --求流失用户:每一条数据代表一个流失的用户
    select count(*) user_churn_count
    from dwt_user_topic
    where dt = '2021-06-08' and login_date_last <= date_sub('2021-06-08',7)     --最后一次活跃时间小于7天之前
) t4;

9.3.3 用户行为漏斗分析

  1.建表语句

drop table if exists ads_user_action;
create external table ads_user_action(
    `dt` string comment '统计日期',
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `home_count` bigint comment '浏览首页人数',
    `good_detail_count` bigint comment '浏览商品详情页人数',
    `cart_count` bigint comment '加入购物车人数',
--dwt_user_topic `order_count` bigint comment
'下单人数', `payment_count` bigint comment '支付人数' ) comment '漏斗分析' row format delimited fields terminated by '\t' location '/warehouse/gmall/ads/ads_user_action/';

  2.数据装载

insert overwrite table ads_user_action
select *
from ads_user_action
union all
select '2021-06-08' dt,
       t5.recent_days,
       home_count,
       good_detail,
       cart_count,
       order_count,
       payment_count
from
(
    select recent_days,
           sum(`if`(page_id = 'home',1,0)) home_count,             --浏览首页人数
           sum(`if`(page_id = 'good_detail',1,0)) good_detail      --浏览商品详情页人数
    from
    (
        select recent_days,
               user_id,         --用户id
               page_id          --页面id
        from dwd_page_log
        --侧写
        lateral view explode(`array`(1,7,30)) tmp as recent_days
        -- 截至到6-8日,最近30天的页面访问记录,全局过滤
        where dt > date_sub('2021-06-08',30)
        --侧写后的过滤
        and dt > date_sub('2021-06-08',recent_days)
        --只要 首页和商品详情页的浏览记录
        and page_id in ('home','good_detail')
        --要求必须有User的信息
        and user_id is not null
        --在1,7,30中,每个user访问每个页面(home,good_detail)多少次
        group by recent_days,user_id,page_id
    ) t4
    group by recent_days
) t5
join
(
    -- 如果将dwt层的数据,复制3份,直接侧写,无需过滤,
    -- 如果将dwd层的数据复制3份,侧写后需要过滤
    select recent_days,
           sum(`if`(cart_count > 0,1,0)) cart_count,        --加入购物车人数
           sum(`if`(order_count > 0,1,0)) order_count,      --下单人数
           sum(`if`(payment_count > 0,1,0)) payment_count   --支付人数
    from
    (
        select  recent_days,
                case recent_days
                    when 1 then cart_last_1d_count
                    when 7 then cart_last_7d_count
                    when 30 then cart_last_30d_count
                end cart_count,      --加入购物车人数
                case recent_days
                    when 1 then order_last_1d_count
                    when 7 then order_last_7d_count
                    when 30 then order_last_30d_count
                end order_count,     --下单人数
                case recent_days
                    when 1 then payment_last_1d_count
                    when 7 then payment_last_7d_count
                    when 30 then payment_last_30d_count
                end payment_count     --支付人数
        from dwt_user_topic
        lateral view explode(`array`(1,7,30)) tmp as recent_days
        where dt = '2021-06-08'
    ) t1
    group by recent_days
) t6 on t5.recent_days = t6.recent_days;

9.3.4 用户留存率

  1.建表语句

drop table if exists ads_user_retention;
create external table ads_user_retention(
    `dt` string comment '统计日期',
    `create_date` string comment '用户新增日期',       --用户新增的日期 + 留存天数 = 留存N天用户活跃的日期
    `retention_day` bigint comment '截至当前日期留存天数',
    `retention_count` bigint comment '留存用户数量',    --某天新增的新用户,在距离新增日期N天后,依然活跃,则称为N天的留存用户
    `new_user_count` bigint comment '新增用户数量',
    `retention_rate` decimal(16,2) comment '留存率'    --N留存用户 / N天新增用户
) comment '用户留存率'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_retention/';

  2.数据装载

    1)1日留存

insert overwrite table ads_user_retention
select *
from ads_user_retention
union all
select '2021-06-08' dt,
date_sub('2021-06-08',1) create_date, --用户新增日期
1 retention_day, --截至当前日期留存天数
-- 留存用户数量:6-7日新增的这批人,6-8日依旧访问的有多少人
sum(`if`(login_date_first = date_sub('2021-06-08',1) and login_date_last = '2021-06-08', 1,0)) retention_count,
--新增用户数量:6-7日新增多少人
sum(`if`(login_date_first = date_sub('2021-06-08',1) ,1,0)) new_user_count,
--留存率 = 1日留存用户数量 / 1日新增用户数量
cast(sum(`if`(login_date_first = date_sub('2021-06-08',1) and login_date_last = '2021-06-08', 1,0)) /
sum(`if`(login_date_first = date_sub('2021-06-08',1) ,1,0)) * 100 as decimal(16,2)) retention_rate
from dwt_user_topic
-- 取最新的全体用户的信息
where dt = '2021-06-08';

    2)7日留存

insert overwrite table ads_user_retention
select *
from ads_user_retention
union all
-- 求最近7日留存
select  '2021-06-08'  dt,
        login_date_first create_date,                                   --用户新增日期
        datediff('2021-06-08',login_date_first) retention_day,          --截至当前日期留存天数
        sum(`if`(login_date_last='2021-06-08',1,0)) retention_count,    --留存用户数量:7天之内活跃,现在依然活跃
        count(*) new_user_count,                                        --新增用户数量
        --留存率
        cast( sum(`if`(login_date_last='2021-06-08',1,0)) / count(*) * 100 as decimal(16,2)) retention_rate
from dwt_user_topic
-- 取最新的全体用户的信息
where dt ='2021-06-08'
-- 从全部用户中过滤出  2021-06-01 ~ 2021-06-07 日新增的用户
and login_date_first >= date_sub('2021-06-08',7) and login_date_first < '2021-06-08'
--按照用户新增的日期分组,求每个新增日期,新增了多少人
group by login_date_first;

9.4 商品主题

9.4.1 商品统计

  1.建表语句

drop table if exists ads_order_spu_stats;
create external table ads_order_spu_stats(
    `dt` string comment '统计日期',
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `spu_id` string comment '商品ID',
    `spu_name` string comment '商品名称',
    `tm_id` string comment '品牌ID',
    `tm_name` string comment '品牌名称',
    `category3_id` string comment '三级品类ID',
    `category3_name` string comment '三级品类名称',
    `category2_id` string comment '二级品类ID',
    `category2_name` string comment '二级品类名称',
    `category1_id` string comment '一级品类ID',
    `category1_name` string comment '一级品类名称',
    `order_count` bigint comment '订单数',
    `order_amount` decimal(16,2) comment '订单金额'
) comment '商品销售统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_order_spu_stats/';

  2.数据装载

insert overwrite table ads_order_spu_stats
select *
from ads_order_spu_stats
union all
select '2021-06-08' dt,
       recent_days,
       spu_id,
       spu_name,
       tm_id,
       tm_name,
       category3_id,
       category3_name,
       category2_id,
       category2_name,
       category1_id,
       category1_name,
       sum(order_count) order_count,
       sum(order_amount) order_amount
from
(
    -- 取要统计的指标
    select  sku_id,
            recent_days,
            -- 要sum(x),x随着recent_days进行变化,因此需要判断recent_days到底是几
            case recent_days
                when 1 then order_last_1d_count
                when 7 then order_last_7d_count
                when 30 then order_last_30d_count
            end order_count,        --订单数
            case recent_days
                when 1 then order_last_1d_final_amount
                when 7 then order_last_7d_final_amount
                when 30 then order_last_30d_final_amount
            end order_amount        --订单金额
    from dwt_sku_topic
    --侧写
    lateral view explode(`array`(1,7,30)) tmp as recent_days
    where dt = '2021-06-08'
) t1
left join
(
    -- 查询需要的维度信息
    select id,              --skuId
           spu_id,          --商品ID
           tm_id,           --品牌ID
           tm_name,         --品牌名称
           category3_id,    --三级品类ID
           category3_name,  --三级品类名称
           category2_id,    --二级品类ID
           category2_name,  --二级品类名称
           category1_id,    --一级品类ID
           category1_name,  --一级品类名称
           spu_name         --商品名称
    from dim_sku_info
    where dt = '2021-06-08'
) t2 on t1.sku_id = t2.id
--spu_id和其他的维度字段是1对1的关系,所以可以直接附加在group by sku_id后
group by   recent_days,
           spu_id,
           spu_name,
           tm_id,
           tm_name,
           category3_id,
           category3_name,
           category2_id,
           category2_name,
           category1_id,
           category1_name;

9.4.2 品牌复购率

  1.建表语句

drop table if exists ads_repeat_purchase;
create external table ads_repeat_purchase(
    `dt` string comment '统计日期',
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id` string comment '品牌ID',
    `tm_name` string comment '品牌名称',
    `order_repeat_rate` decimal(16,2) comment '复购率'
) comment '品牌复购率'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_repeat_purchase/';

  2.数据装载

insert overwrite table ads_repeat_purchase
select * from ads_repeat_purchase
union all
select  '2021-06-08' dt,
        recent_days,
        tm_id,
        tm_name,
        -- 根据每个user购买过的单数,判断是购买过多次的人还是购买过的人
        -- 求购买过多次的人数
        cast(sum(if(order_times > 1 ,1,0 )) / count(*) * 100 as decimal(16,2)) order_repeat_rate
from
(
    select  recent_days,user_id,tm_id,tm_name,
            -- 购买过多少单,下单过多少次
            --关联商品维度表,将sku_id切换为tm_id
            count(distinct  order_id) order_times
    from
    (
        --哪些用户购买了什么商品的所有的记录
        select user_id,
               sku_id,
               order_id,
               recent_days
        from dwd_order_detail
        lateral view explode(`array`(1,7,30)) tmp as recent_days
        -- 要取6-8号及之前的数据,至少要取30天的数据,全局过滤
        where dt > date_sub('2021-06-08',30) and dt > date_sub('2021-06-08',recent_days)
    ) t1
    left join
    (
        --求维度信息
        select id,
               tm_id,
               tm_name
        from dim_sku_info
        where dt = '2021-06-08'
    ) t2 on t1.sku_id = t2.id
--由于要统计一个人购买过1次(下单次数,单)还是多次,所以先求每个人购买每个品牌的次数
group by recent_days,user_id,tm_id,tm_name) t3
group by recent_days,tm_id,tm_name;

9.5 订单主题

9.5.1 订单统计

  1.建表语句

drop table if exists ads_order_total;
create external table ads_order_total(
    `dt` string comment '统计日期',
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `order_count` bigint comment '订单数',
    `order_amount` decimal(16,2) comment '订单金额',
    `order_user_count` bigint comment '下单人数'
) comment '订单统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_order_total/';

  2.数据装载

insert overwrite table ads_order_total
select *
from ads_order_total
union all
select '2021-06-08' dt,
       recent_days,
       sum(order_count) order_count,
       sum(order_amount) order_amount,
       sum(`if`(order_amount > 0, 1,0)) order_user_count
from
(
    select user_id,
           recent_days,
           -- 要sum(x),x随着recent_days进行变化,因此需要判断recent_days到底是几
           case recent_days
                when 1 then order_last_1d_count
                when 7 then order_last_7d_count
                when 30 then order_last_30d_count
            end order_count,        --订单数
           case recent_days
                when 1 then order_last_1d_final_amount
                when 7 then order_last_7d_final_amount
                when 30 then order_last_30d_final_amount
            end order_amount        --订单金额
    from dwt_user_topic
    lateral view explode(`array`(1,7,30)) tmp as recent_days
    --保存的是截至到6-8日,累积的所有用户的行为记录
    where dt = '2021-06-08'
) t1
group by recent_days;

9.5.2 各地区订单统计

  1.建表语句

drop table if exists ads_order_by_province;
create external table ads_order_by_province(
    `dt` string comment '统计日期',
    `recent_days` bigint comment '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `province_id` string comment '省份id',
    `province_name` string comment '省份名称',
    `area_code` string comment '地区编码',
    `iso_code` string comment '国际标准地区编码',
    `iso_code_3166_2` string comment '国际标准地区编码',
    `order_count` bigint comment '订单数',
    `order_amount` decimal(16,2) comment '订单金额'
) comment '各地区订单统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_order_by_province/';

  2.数据装载

insert overwrite table ads_order_by_province
select *
from ads_order_by_province
union all
select '2021-06-08' dt,
       recent_days,
       province_id,
       province_name,
       area_code,
       t2.iso_code iso_code,
       t2.iso_3166_2 iso_code_3166_2,
       order_count,
       order_amount
from
(
    --按照省份id和recent_days分组
    select province_id,
           recent_days,
           sum(order_count) order_count,    --订单数
           sum(order_amount) order_amount   --订单金额
    from
    (
        --按地区id、recent_days分组,求订单数和订单金额
        select province_id,
               recent_days,
               -- 要sum(x),x随着recent_days进行变化,因此需要判断recent_days到底是几
               case recent_days
                    when 1 then order_last_1d_count
                    when 7 then order_last_7d_count
                    when 30 then order_last_30d_count
                end order_count,        --订单数
               case recent_days
                    when 1 then order_last_1d_final_amount
                    when 7 then order_last_7d_final_amount
                    when 30 then order_last_30d_final_amount
                end order_amount       --订单金额
        from dwt_area_topic
        lateral view explode(`array`(1,7,30)) tmp as recent_days
        where dt = '2021-06-08'
    ) tmp
    group by recent_days, province_id
) t1
join
(
    --求维度信息
    select id,
           province_name,
           area_code,
           iso_3166_2,
           iso_code
    from dim_base_province
) t2 on t1.province_id = t2.id;

9.6 优惠券主题

9.6.1 优惠券统计

  1.建表语句

drop table if exists ads_coupon_stats;
create external table ads_coupon_stats(
    `dt` string comment '统计日期',
    `coupon_id` string comment '优惠券ID',
    `coupon_name` string comment '优惠券名称',
    `start_date` string comment '发布日期',
    `rule_name` string comment '优惠规则',
    `get_count` bigint comment '领取次数',
    `order_count` bigint comment '使用(下单)次数',
    `expire_count` bigint comment '过期次数',
    `order_original_amount` decimal(16,2) comment '使用优惠券订单原始金额',
    `order_final_amount` decimal(16,2) comment '使用优惠券订单最终金额',
    `reduce_amount` decimal(16,2) comment '优惠金额',
    `reduce_rate` decimal(16,2) comment '补贴率',
    `spu_comment` string comment '商品占比,按订单数统计,要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%',
    `tm_comment` string comment '品牌占比,按订单数统计,要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%'
) comment '商品销售统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_coupon_stats/';

  2.创建UDAF函数,命名为top3,详细信息:https://www.cnblogs.com/LzMingYueShanPao/p/14938683.html

    说明:spu_comment  和 tm_comment 字段使用SQL实现较为复杂,所以采用自定义UDAF函数实现

  3.数据装载

insert overwrite table ads_coupon_stats
select * from ads_coupon_stats
union  all
select '2021-06-08' dt,
       t1.coupon_id,
       coupon_name,
       start_date,
       rule_name,
       nvl(get_count,0),
       nvl(order_count,0),
       nvl(expire_count,0),
       nvl(order_original_amount,0.0),
       nvl(order_final_amount,0.0),
       nvl(reduce_amount,0.0),
       nvl(reduce_rate,0),
       spu_comment,
       tm_comment
from
(
    select id coupon_id,        --优惠券ID
           coupon_name,         --优惠券名称
           date_format(start_time,'yyyy-MM-dd') start_date, --发布日期
           -- 需要根据coupon_type,将优惠券的优惠规则拼接
           case coupon_type
                when '3201' then  concat('满' , condition_amount ,'减' , benefit_amount ,'元')
                when '3202' then  concat('满' , condition_num ,'打' , benefit_discount ,'折')
                when '3203' then  concat('立减' , condition_amount ,'元' )
            end rule_name       --优惠规则
    from dim_coupon_info
    where dt = '2021-06-08'
    --在30天内有效的优惠券
    and date_format(nvl(expire_time,'9999-99-99'),'yyyy-MM-dd') > date_sub('2021-06-08',30)
) t1
left join
(
    select coupon_id,
           get_count,               --领取次数
           order_count,             --使用(下单)次数
           expire_count,            --过期次数
           order_original_amount,   --使用优惠券订单原始金额
           order_final_amount,      --使用优惠券订单最终金额
           payment_reduce_amount reduce_amount,     --优惠金额
           cast(payment_reduce_amount / order_original_amount * 100 as decimal(16,2)) reduce_rate   --补贴率
    from dwt_coupon_topic
    where dt = '2021-06-08'
) t2 on t1.coupon_id = t2.coupon_id
left join
(
    select coupon_id,
           top3(tm_name)  tm_comment,
           top3(spu_name) spu_comment
    from
     (
         -- 先求使用不同种类优惠券的下单购买的订单的品牌信息
         select sku_id,
                coupon_id
         from dwd_order_detail
              --取最近30天的下单信息,全局过滤
         where dt > date_sub('2021-06-08', 30)
           -- 只求使用了优惠券的订单购买详请
           and coupon_id is not null
     ) t3
    left join
    (
        select id,          --sku_id
               tm_name,     --品牌名称
               spu_name     --spu名称
        from dim_sku_info
        where dt = '2021-06-08'
    ) t4 on t3.sku_id = t4.id
    group by coupon_id
) t5
on t1.coupon_id = t5.coupon_id;

9.7 活动主题

9.7.1 活动统计

  1.建表语句

drop table if exists ads_activity_stats;
create external table ads_activity_stats(
    `dt` string comment '统计日期',
    `activity_id` string comment '活动ID',
    `activity_name` string comment '活动名称',
    `start_date` string comment '活动开始日期',
    `order_count` bigint comment '参与活动订单数',
    `order_original_amount` decimal(16,2) comment '参与活动订单原始金额',
    `order_final_amount` decimal(16,2) comment '参与活动订单最终金额',
    `reduce_amount` decimal(16,2) comment '优惠金额',
    `reduce_rate` decimal(16,2) comment '补贴率',
    `rule_comment` string comment '订单规则占比,按订单数统计,要求结果形式如下:满100减10:19.5%,满200减30:80.5%',
    `spu_comment` string comment '商品占比,按订单数统计,要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%',
    `tm_comment` string comment '品牌占比,按订单数统计,要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%'
) comment '商品销售统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_activity_stats/';

  2.数据装载

insert overwrite table ads_activity_stats
select * from ads_activity_stats
union  all
select '2021-06-08' dt,
       t1.activity_id,                      --活动ID
       activity_name,                       --活动名称
       start_date,                          --活动开始日期
       nvl(order_count,0),                 --参与活动订单数
       nvl(order_original_amount,0.0),     --参与活动订单原始金额
       nvl(order_final_amount,0.0),        --参与活动订单最终金额
       nvl(reduce_amount,0.0),             --优惠金额
       nvl(reduce_rate,0),                 --补贴率
       rule_comment,    --订单规则占比,按订单数统计,要求结果形式如下:满100减10:19.5%,满200减30:80.5%
       spu_comment,     --商品占比,按订单数统计,要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%
       tm_comment       --品牌占比,按订单数统计,要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%
from
(
    --统计最新30天,所有在有效期范围 的优惠券的维度信息
    select activity_id,                                             --活动ID
           max(activity_name) activity_name,                        --活动名称
           min(date_format(start_time,'yyyy-MM-dd')) start_date     --活动开始日期
     -- 一个活动规则是1条,在聚合时,依然要按照活动规则聚合,之后再关联时,使用活动关联
    from dim_activity_rule_info
    where dt = '2021-06-08'
    --在30天内有效的活动
    --and date_format(nvl(end_time,'9999-99-99'),'yyyy-MM-dd') > date_sub('2021-06-08',30)
    group by activity_id
) t1
left join
(
    select activity_id,
           sum(order_count) order_count,                        --参与活动订单数
           sum(order_original_amount) order_original_amount,    --参与活动订单原始金额
           sum(order_final_amount) order_final_amount,          --参与活动订单最终金额
           sum(payment_reduce_amount) reduce_amount,            --优惠金额
           --nvl(cast(sum(payment_reduce_amount) / sum(order_original_amount) * 100 as decimal(16,2)),0) reduce_rate     --补贴率
           case sum(payment_reduce_amount)
               when 0 then 0.0
               else nvl(cast(sum(payment_reduce_amount) / sum(payment_reduce_amount) * 100 as decimal(16,2)),0.0)
            end reduce_rate     --补贴率
    from dwt_activity_topic
    where dt = '2021-06-08'
    group by activity_id
) t2 on t1.activity_id = t2.activity_id
left join
(
    select activity_id,
           top3(rule_name) rule_comment,    --订单规则占比,按订单数统计,要求结果形式如下:满100减10:19.5%,满200减30:80.5%
           top3(tm_name) tm_comment,        --品牌占比,按订单数统计,要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%
           top3(spu_name) spu_comment       --商品占比,按订单数统计,要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%
    from
    (
        -- 先求使用不同种类优惠券的下单购买的订单的品牌信息
        select sku_id,activity_rule_id,activity_id
        from dwd_order_detail
        --取最近30天的下单信息,全局过滤
        where dt > date_sub('2021-06-08',30)
        and activity_rule_id is not null
    ) t3
    left join
    (
        select id,
               tm_name,     --品牌名称
               spu_name     --spu名称
        from dim_sku_info
        where dt = '2021-06-08'
    ) t4 on t3.sku_id = t4.id
    left join
    (
        select activity_rule_id,
               case activity_type
                    when '3101' then  concat('满' , condition_amount ,'减' , benefit_amount ,'元')
                    when '3102' then  concat('满' , condition_num ,'打' , benefit_discount ,'折')
                    when '3103' then  concat('立减' , condition_amount ,'元' )
               end rule_name    --规则名称
        from dim_activity_rule_info
        where dt = '2021-06-08'
    ) t5 on t3.activity_rule_id = t5.activity_rule_id
    group by t3.activity_id
) t6 on t1.activity_id = t6.activity_id;

9.9 ADS业务数据导入脚本

  1)编写脚本

    (1)在/home/atguigu/bin目录下创建脚本dwt_to_ads.sh

vim dwt_to_ads.sh
#!/bin/bash

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

ads_visit_stats="
-- 指定spark生成的文件,自动进行合并
set hive.merge.sparkfiles=true;
-- 256000000 默认合并后的文件,不超过256M,如果超过256M,自动滚动,产生新的文件 128M
set hive.merge.size.per.task;
--为了解决小文件问题,在写入ads层时,先把历史数据查询出来和今天的指标合并,之后覆盖写入到ads层表目录,所有的数据,只有一个文件
insert overwrite table ${APP}.ads_visit_stats
select *
from ${APP}.ads_visit_stats
union all
--求不同的is_new,channal,recent_days 分组下,所有session的指标
select '$do_date' dt,             --统计日期
       is_new,                      --新老标识,1:新,0:老
       recent_days,                 --最近天数,1:最近1天,7:最近7天,30:最近30天
       channel,                     --渠道
       -- 人数(设备数) 不是 人次,人数应该按照mid_id进行去重统计
       count(distinct split(session_id,'-')[0]) uv_count,   --日活(访问人数)
       sum(duration_sec_per_session) duration_sec,          --页面停留总时长
       avg(duration_sec_per_session) avg_duration_sec,      --一次会话,页面停留平均时长,单位为描述
       sum(page_count_per_session) page_count,              --页面总浏览数
       avg(page_count_per_session) avg_page_count,          --一次会话,页面平均浏览数
       count(*) sv_count,                                   --会话次数
       sum(if(page_count_per_session = 1,1,0))  bounce_count,     --跳出数:指仅有一次访问行为的会话的数量
       cast(sum(if(page_count_per_session = 1,1,0)) / count(*) * 100 as DECIMAL(16,2)) bounce_rate    --跳出率:跳出数 / 会话总数  * 100
from
(
    --求不同的is_new,channal,recent_days 分组下,每个session 页面停留总时长 和  page_count
    select is_new,                                          --是否首次启动
           channel,                                         --渠道
           recent_days,                                     --最近天数
           session_id,                                      --sessionId
           count(*) page_count_per_session,                 --页面总浏览数
           sum(during_time)  duration_sec_per_session       --页面停留总时长
    from
    (
        -- 求出每次会话的session_id
        -- 统计日期指统计的数据的日期
        select  '$do_date' dt,
                is_new,             --是否首次启动
                channel,            --渠道
                mid_id,             --设备id
                during_time,        --持续时间
                last_page_id,       --上页类型
                page_id,            --页面id
                ts,                 --时间
                recent_days,        --最近天数
                concat(mid_id,'-',last_value(if(last_page_id is null ,ts,null) ,true) over (partition by mid_id order by ts )) session_id  --sessionId
        from ${APP}.dwd_page_log
        --侧写
        lateral view explode(array(1,7,30)) tmp as recent_days
        -- 要求最近7天和30天的统计指标,要从dwd事实中取数据的范围,应该在最近30天
        where dt > date_sub('$do_date',30)
        --过滤
        and dt > date_sub('$do_date',recent_days)
    ) t1
-- 按照is_new,channal,recent_days分组
group by is_new,channel,recent_days,session_id )t2
group by is_new,channel,recent_days;
"

ads_page_path="
insert overwrite table ${APP}.ads_page_path
select * from ${APP}.ads_page_path
union all
select  '$do_date' dt,
        --求每个session中,从A页面跳转到B页面跳转了多少次
        recent_days,
        source,
        target,
        count(*)  path_count
from (
    -- 由于产生了闭环,因此需要给一次session访问的所有的页面路径,根据访问的顺序添加id,避免产生闭环
     select recent_days,
            concat(source, '-', rn)     source,
            concat(target, '-', rn + 1) target
     from (
              --求每个session中,根据页面的访问顺序,生成source和target
              select recent_days,                                                                    --最近天数
                     last_page_id,                                                                   --上页类型
                     page_id,                                                                        --页面id
                     page_id                                                                 source, --跳转起始页面ID
                     lead(page_id, 1) over (partition by session_id,recent_days order by ts) target, --跳转终到页面ID
                     mid_id,                                                                         --设备id
                     session_id,
                     row_number() over (partition by session_id,recent_days order by ts)     rn
              from (
                       --先求出了每个页面访问记录的session_id
                       select recent_days,  --最近天数
                              last_page_id, --上页类型
                              page_id,      --页面id
                              mid_id,       --设备id
                              ts,           --时间
                              concat(mid_id, '-', last_value(if(last_page_id is null, ts, null), true)
                                                             over (partition by mid_id order by ts)) session_id
                       from ${APP}.dwd_page_log
                                --侧写
                                lateral view explode(array(1, 7, 30)) tmp as recent_days
                            --全局过滤
                       where dt > date_sub('$do_date', 30)
                         --侧写后过滤
                         and dt > date_sub('$do_date', recent_days)
                   ) tmp
          ) tmp1
    )tmp2
group by recent_days,source,target;
"

ads_user_totalt="
insert overwrite table ${APP}.ads_user_totalt
select *
from ${APP}.ads_user_totalt
union all
select '$do_date' dt,
       recent_days,
       sum(if(login_date_first > date_sub('$do_date',x),1,0)) new_user_count,               --新注册用户数
       sum(if(order_date_first > date_sub('$do_date',x),1,0)) new_order_user_count,         --新增下单用户数
       sum(order_final_amount) order_final_amount,                                              --下单总金额
       sum(if(order_final_amount > 0,1,0)) order_user_count,                                  --下单用户数
       sum(if(login_date_last > date_sub('$do_date',x) and order_final_amount = 0, 1,0)) no_order_user_count    --只登陆不下单的用户数
from
(
    select recent_days,
           user_id,
           login_date_last,     --末次活跃日期
           login_date_first,    --首次活跃日期
           order_date_first,    --首次下单时间
           case recent_days
                when 0 then order_final_amount
                when 1 then order_last_1d_final_amount
                when 7 then order_last_7d_final_amount
                when 30 then order_last_30d_final_amount
            end order_final_amount,                         --累计最终下单金额
           if(recent_days = 0, 65555, recent_days) x      --动态变化:如果是0,表示累计数,否则就是recent_days
    from ${APP}.dwt_user_topic
    lateral view explode(array(0,1,7,30)) tmp as recent_days
    where dt = '$do_date'
) t1
group by recent_days;
"

ads_user_change="
insert overwrite table ${APP}.ads_user_change
select * from ${APP}.ads_user_change
union all
select '$do_date' dt,
       user_churn_count,
       user_back_count
from
(
    select count(*) user_back_count     --回流用户数
    from
    (
        --求出本周活跃的老用户
        select user_id
        from ${APP}.dwt_user_topic
        where dt = '$do_date'
        --本周活跃= 最后一次活跃时间 > 7天前的时间
        and login_date_last >= date_sub(next_day('$do_date','Monday'),7)
        --老用户:第一次活跃时间 < 7天前,也就是说7天前活跃过
        and login_date_first < date_sub(next_day('$do_date','Monday'),7)
    ) t1
    left join
    (
        --求上周活跃的用户,注意无法从dwt层求出,dwt层的数据是每日更新,历史状态是无法保留的, 只能从dws取上周时间范围的数据
        select user_id
        from ${APP}.dws_user_action_daycount
        where dt between date_sub(next_day('$do_date','Monday'),14) and date_sub(next_day('$do_date','Monday'),8)
        --分组去重
        group by user_id
    ) t2 on t1.user_id = t2.user_id
where t2.user_id is null ) t3
join
(
    --求流失用户:每一条数据代表一个流失的用户
    select count(*) user_churn_count
    from ${APP}.dwt_user_topic
    where dt = '$do_date' and login_date_last <= date_sub('$do_date',7)     --最后一次活跃时间小于7天之前
) t4;
"

ads_user_action="
insert overwrite table ${APP}.ads_user_action
select *
from ${APP}.ads_user_action
union all
select '$do_date' dt,
       t5.recent_days,
       home_count,
       good_detail,
       cart_count,
       order_count,
       payment_count
from
(
    select recent_days,
           sum(if(page_id = 'home',1,0)) home_count,             --浏览首页人数
           sum(if(page_id = 'good_detail',1,0)) good_detail      --浏览商品详情页人数
    from
    (
        select recent_days,
               user_id,         --用户id
               page_id          --页面id
        from ${APP}.dwd_page_log
        --侧写
        lateral view explode(array(1,7,30)) tmp as recent_days
        -- 截至到6-8日,最近30天的页面访问记录,全局过滤
        where dt > date_sub('$do_date',30)
        --侧写后的过滤
        and dt > date_sub('$do_date',recent_days)
        --只要 首页和商品详情页的浏览记录
        and page_id in ('home','good_detail')
        --要求必须有User的信息
        and user_id is not null
        --在1,7,30中,每个user访问每个页面(home,good_detail)多少次
        group by recent_days,user_id,page_id
    ) t4
    group by recent_days
) t5
join
(
    -- 如果将dwt层的数据,复制3份,直接侧写,无需过滤,
    -- 如果将dwd层的数据复制3份,侧写后需要过滤
    select recent_days,
           sum(if(cart_count > 0,1,0)) cart_count,        --加入购物车人数
           sum(if(order_count > 0,1,0)) order_count,      --下单人数
           sum(if(payment_count > 0,1,0)) payment_count   --支付人数
    from
    (
        select  recent_days,
                case recent_days
                    when 1 then cart_last_1d_count
                    when 7 then cart_last_7d_count
                    when 30 then cart_last_30d_count
                end cart_count,      --加入购物车人数
                case recent_days
                    when 1 then order_last_1d_count
                    when 7 then order_last_7d_count
                    when 30 then order_last_30d_count
                end order_count,     --下单人数
                case recent_days
                    when 1 then payment_last_1d_count
                    when 7 then payment_last_7d_count
                    when 30 then payment_last_30d_count
                end payment_count     --支付人数
        from ${APP}.dwt_user_topic
        lateral view explode(array(1,7,30)) tmp as recent_days
        where dt = '$do_date'
    ) t1
    group by recent_days
) t6 on t5.recent_days = t6.recent_days;
"

ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
select *
from ${APP}.ads_user_retention
union all
-- 求最近7日留存
select  '$do_date'  dt,
        login_date_first create_date,                                   --用户新增日期
        datediff('$do_date',login_date_first) retention_day,          --截至当前日期留存天数
        sum(if(login_date_last='$do_date',1,0)) retention_count,    --留存用户数量:7天之内活跃,现在依然活跃
        count(*) new_user_count,                                        --新增用户数量
        --留存率
        cast( sum(if(login_date_last='$do_date',1,0)) / count(*) * 100 as decimal(16,2)) retention_rate
from ${APP}.dwt_user_topic
-- 取最新的全体用户的信息
where dt ='$do_date'
-- 从全部用户中过滤出  2021-06-01 ~ 2021-06-07 日新增的用户
and login_date_first >= date_sub('$do_date',7) and login_date_first < '$do_date'
--按照用户新增的日期分组,求每个新增日期,新增了多少人
group by login_date_first;
"

ads_order_spu_stats="
insert overwrite table ${APP}.ads_order_spu_stats
select *
from ${APP}.ads_order_spu_stats
union all
select '$do_date' dt,
       recent_days,
       spu_id,
       spu_name,
       tm_id,
       tm_name,
       category3_id,
       category3_name,
       category2_id,
       category2_name,
       category1_id,
       category1_name,
       sum(order_count) order_count,
       sum(order_amount) order_amount
from
(
    -- 取要统计的指标
    select  sku_id,
            recent_days,
            -- 要sum(x),x随着recent_days进行变化,因此需要判断recent_days到底是几
            case recent_days
                when 1 then order_last_1d_count
                when 7 then order_last_7d_count
                when 30 then order_last_30d_count
            end order_count,        --订单数
            case recent_days
                when 1 then order_last_1d_final_amount
                when 7 then order_last_7d_final_amount
                when 30 then order_last_30d_final_amount
            end order_amount        --订单金额
    from ${APP}.dwt_sku_topic
    --侧写
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt = '$do_date'
) t1
left join
(
    -- 查询需要的维度信息
    select id,              --skuId
           spu_id,          --商品ID
           tm_id,           --品牌ID
           tm_name,         --品牌名称
           category3_id,    --三级品类ID
           category3_name,  --三级品类名称
           category2_id,    --二级品类ID
           category2_name,  --二级品类名称
           category1_id,    --一级品类ID
           category1_name,  --一级品类名称
           spu_name         --商品名称
    from ${APP}.dim_sku_info
    where dt = '$do_date'
) t2 on t1.sku_id = t2.id
--spu_id和其他的维度字段是1对1的关系,所以可以直接附加在group by sku_id后
group by   recent_days,
           spu_id,
           spu_name,
           tm_id,
           tm_name,
           category3_id,
           category3_name,
           category2_id,
           category2_name,
           category1_id,
           category1_name;
"

ads_repeat_purchase="
insert overwrite table ${APP}.ads_repeat_purchase
select * from ${APP}.ads_repeat_purchase
union all
select  '$do_date' dt,
        recent_days,
        tm_id,
        tm_name,
        -- 根据每个user购买过的单数,判断是购买过多次的人还是购买过的人
        -- 求购买过多次的人数
        cast(sum(if(order_times > 1 ,1,0 )) / count(*) * 100 as decimal(16,2)) order_repeat_rate
from
(
    select  recent_days,user_id,tm_id,tm_name,
            -- 购买过多少单,下单过多少次
            --关联商品维度表,将sku_id切换为tm_id
            count(distinct  order_id) order_times
    from
    (
        --哪些用户购买了什么商品的所有的记录
        select user_id,
               sku_id,
               order_id,
               recent_days
        from ${APP}.dwd_order_detail
        lateral view explode(array(1,7,30)) tmp as recent_days
        -- 要取6-8号及之前的数据,至少要取30天的数据,全局过滤
        where dt > date_sub('$do_date',30) and dt > date_sub('$do_date',recent_days)
    ) t1
    left join
    (
        --求维度信息
        select id,
               tm_id,
               tm_name
        from ${APP}.dim_sku_info
        where dt = '$do_date'
    ) t2 on t1.sku_id = t2.id
--由于要统计一个人购买过1次(下单次数,单)还是多次,所以先求每个人购买每个品牌的次数
group by recent_days,user_id,tm_id,tm_name) t3
group by recent_days,tm_id,tm_name;
"

ads_order_total="
insert overwrite table ${APP}.ads_order_total
select *
from ${APP}.ads_order_total
union all
select '$do_date' dt,
       recent_days,
       sum(order_count) order_count,
       sum(order_amount) order_amount,
       sum(if(order_amount > 0, 1,0)) order_user_count
from
(
    select user_id,
           recent_days,
           -- 要sum(x),x随着recent_days进行变化,因此需要判断recent_days到底是几
           case recent_days
                when 1 then order_last_1d_count
                when 7 then order_last_7d_count
                when 30 then order_last_30d_count
            end order_count,        --订单数
           case recent_days
                when 1 then order_last_1d_final_amount
                when 7 then order_last_7d_final_amount
                when 30 then order_last_30d_final_amount
            end order_amount        --订单金额
    from ${APP}.dwt_user_topic
    lateral view explode(array(1,7,30)) tmp as recent_days
    --保存的是截至到6-8日,累积的所有用户的行为记录
    where dt = '$do_date'
) t1
group by recent_days;
"

ads_order_by_province="
insert overwrite table ${APP}.ads_order_by_province
select *
from ${APP}.ads_order_by_province
union all
select '$do_date' dt,
       recent_days,
       province_id,
       province_name,
       area_code,
       t2.iso_code iso_code,
       t2.iso_3166_2 iso_code_3166_2,
       order_count,
       order_amount
from
(
    --按照省份id和recent_days分组
    select province_id,
           recent_days,
           sum(order_count) order_count,    --订单数
           sum(order_amount) order_amount   --订单金额
    from
    (
        --按地区id、recent_days分组,求订单数和订单金额
        select province_id,
               recent_days,
               -- 要sum(x),x随着recent_days进行变化,因此需要判断recent_days到底是几
               case recent_days
                    when 1 then order_last_1d_count
                    when 7 then order_last_7d_count
                    when 30 then order_last_30d_count
                end order_count,        --订单数
               case recent_days
                    when 1 then order_last_1d_final_amount
                    when 7 then order_last_7d_final_amount
                    when 30 then order_last_30d_final_amount
                end order_amount       --订单金额
        from ${APP}.dwt_area_topic
        lateral view explode(array(1,7,30)) tmp as recent_days
        where dt = '$do_date'
    ) tmp
    group by recent_days, province_id
) t1
join
(
    --求维度信息
    select id,
           province_name,
           area_code,
           iso_3166_2,
           iso_code
    from ${APP}.dim_base_province
) t2 on t1.province_id = t2.id;
"

ads_coupon_stats="
insert overwrite table ${APP}.ads_coupon_stats
select * from ${APP}.ads_coupon_stats
union  all
select '$do_date' dt,
       t1.coupon_id,
       coupon_name,
       start_date,
       rule_name,
       nvl(get_count,0),
       nvl(order_count,0),
       nvl(expire_count,0),
       nvl(order_original_amount,0.0),
       nvl(order_final_amount,0.0),
       nvl(reduce_amount,0.0),
       nvl(reduce_rate,0),
       spu_comment,
       tm_comment
from
(
    select id coupon_id,        --优惠券ID
           coupon_name,         --优惠券名称
           date_format(start_time,'yyyy-MM-dd') start_date, --发布日期
           -- 需要根据coupon_type,将优惠券的优惠规则拼接
           case coupon_type
                when '3201' then  concat('满' , condition_amount ,'减' , benefit_amount ,'元')
                when '3202' then  concat('满' , condition_num ,'打' , benefit_discount ,'折')
                when '3203' then  concat('立减' , condition_amount ,'元' )
            end rule_name       --优惠规则
    from ${APP}.dim_coupon_info
    where dt = '$do_date'
    --在30天内有效的优惠券
    and date_format(nvl(expire_time,'9999-99-99'),'yyyy-MM-dd') > date_sub('$do_date',30)
) t1
left join
(
    select coupon_id,
           get_count,               --领取次数
           order_count,             --使用(下单)次数
           expire_count,            --过期次数
           order_original_amount,   --使用优惠券订单原始金额
           order_final_amount,      --使用优惠券订单最终金额
           payment_reduce_amount reduce_amount,     --优惠金额
           cast(payment_reduce_amount / order_original_amount * 100 as decimal(16,2)) reduce_rate   --补贴率
    from ${APP}.dwt_coupon_topic
    where dt = '$do_date'
) t2 on t1.coupon_id = t2.coupon_id
left join
(
    select coupon_id,
           ${APP}.top3(tm_name)  tm_comment,
           ${APP}.top3(spu_name) spu_comment
    from
     (
         -- 先求使用不同种类优惠券的下单购买的订单的品牌信息
         select sku_id,
                coupon_id
         from ${APP}.dwd_order_detail
              --取最近30天的下单信息,全局过滤
         where dt > date_sub('$do_date', 30)
           -- 只求使用了优惠券的订单购买详请
           and coupon_id is not null
     ) t3
    left join
    (
        select id,          --sku_id
               tm_name,     --品牌名称
               spu_name     --spu名称
        from ${APP}.dim_sku_info
        where dt = '$do_date'
    ) t4 on t3.sku_id = t4.id
    group by coupon_id
) t5
on t1.coupon_id = t5.coupon_id;
"

ads_activity_stats="
insert overwrite table ${APP}.ads_activity_stats
select * from ${APP}.ads_activity_stats
union  all
select '$do_date' dt,
       t1.activity_id,                      --活动ID
       activity_name,                       --活动名称
       start_date,                          --活动开始日期
       nvl(order_count,0),                 --参与活动订单数
       nvl(order_original_amount,0.0),     --参与活动订单原始金额
       nvl(order_final_amount,0.0),        --参与活动订单最终金额
       nvl(reduce_amount,0.0),             --优惠金额
       nvl(reduce_rate,0),                 --补贴率
       rule_comment,    --订单规则占比,按订单数统计,要求结果形式如下:满100减10:19.5%,满200减30:80.5%
       spu_comment,     --商品占比,按订单数统计,要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%
       tm_comment       --品牌占比,按订单数统计,要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%
from
(
    --统计最新30天,所有在有效期范围 的优惠券的维度信息
    select activity_id,                                             --活动ID
           max(activity_name) activity_name,                        --活动名称
           min(date_format(start_time,'yyyy-MM-dd')) start_date     --活动开始日期
     -- 一个活动规则是1条,在聚合时,依然要按照活动规则聚合,之后再关联时,使用活动关联
    from ${APP}.dim_activity_rule_info
    where dt = '$do_date'
    --在30天内有效的活动
    --and date_format(nvl(end_time,'9999-99-99'),'yyyy-MM-dd') > date_sub('$do_date',30)
    group by activity_id
) t1
left join
(
    select activity_id,
           sum(order_count) order_count,                        --参与活动订单数
           sum(order_original_amount) order_original_amount,    --参与活动订单原始金额
           sum(order_final_amount) order_final_amount,          --参与活动订单最终金额
           sum(payment_reduce_amount) reduce_amount,            --优惠金额
           --nvl(cast(sum(payment_reduce_amount) / sum(order_original_amount) * 100 as decimal(16,2)),0) reduce_rate     --补贴率
           case sum(payment_reduce_amount)
               when 0 then 0.0
               else nvl(cast(sum(payment_reduce_amount) / sum(payment_reduce_amount) * 100 as decimal(16,2)),0.0)
            end reduce_rate     --补贴率
    from ${APP}.dwt_activity_topic
    where dt = '$do_date'
    group by activity_id
) t2 on t1.activity_id = t2.activity_id
left join
(
    select activity_id,
           ${APP}.top3(rule_name) rule_comment,    --订单规则占比,按订单数统计,要求结果形式如下:满100减10:19.5%,满200减30:80.5%
           ${APP}.top3(tm_name) tm_comment,        --品牌占比,按订单数统计,要求结果形式如下:Apple:30%,Xiaomi:20%,Huawei:30%,其他:20%
           ${APP}.top3(spu_name) spu_comment       --商品占比,按订单数统计,要求结果形式如下:iPhone X:30%,xiaomi 10:20%,iPhone 12:30%,其他:20%
    from
    (
        -- 先求使用不同种类优惠券的下单购买的订单的品牌信息
        select sku_id,activity_rule_id,activity_id
        from ${APP}.dwd_order_detail
        --取最近30天的下单信息,全局过滤
        where dt > date_sub('$do_date',30)
        and activity_rule_id is not null
    ) t3
    left join
    (
        select id,
               tm_name,     --品牌名称
               spu_name     --spu名称
        from ${APP}.dim_sku_info
        where dt = '$do_date'
    ) t4 on t3.sku_id = t4.id
    left join
    (
        select activity_rule_id,
               case activity_type
                    when '3101' then  concat('满' , condition_amount ,'减' , benefit_amount ,'元')
                    when '3102' then  concat('满' , condition_num ,'打' , benefit_discount ,'折')
                    when '3103' then  concat('立减' , condition_amount ,'元' )
               end rule_name    --规则名称
        from ${APP}.dim_activity_rule_info
        where dt = '$do_date'
    ) t5 on t3.activity_rule_id = t5.activity_rule_id
    group by t3.activity_id
) t6 on t1.activity_id = t6.activity_id;
"

case $1 in
    "ads_visit_stats" )
        hive -e "$ads_visit_stats"
    ;;
    "ads_page_path" )
        hive -e "$ads_page_path"
    ;;
    "ads_user_totalt" )
        hive -e "$ads_user_totalt"
    ;;
    "ads_user_change" )
        hive -e "$ads_user_change"
    ;;
    "ads_user_action" )
        hive -e "$ads_user_action"
    ;;
    "ads_user_retention" )
        hive -e "$ads_user_retention"
    ;;
    "ads_order_spu_stats" )
        hive -e "$ads_order_spu_stats"
    ;;
    "ads_repeat_purchase" )
        hive -e "$ads_repeat_purchase"
    ;;
    "ads_order_total" )
        hive -e "$ads_order_total"
    ;;
    "ads_order_by_province" )
        hive -e "$ads_order_by_province"
    ;;
    "ads_coupon_stats" )
        hive -e "$ads_coupon_stats"
    ;;
    "ads_activity_stats" )
        hive -e "$ads_activity_stats"
    ;;
    "all" )
        hive -e "$ads_visit_stats$ads_page_path$ads_user_totalt$ads_user_change$ads_user_action$ads_user_retention$ads_order_spu_stats$ads_repeat_purchase$ads_order_total$ads_order_by_province$ads_coupon_stats$ads_activity_stats"
    ;;
esac

    (2)增加脚本执行权限

chmod 777 dwt_to_ads.sh

  2)脚本使用

    (1)执行脚本

dwt_to_ads.sh all 2021-06-08          

    (2)查看数据是否导入

原文地址:https://www.cnblogs.com/LzMingYueShanPao/p/14928869.html