master

分支 (1)

管理

管理

master

gmalll
/
ADS-2.sql

--近30日各个品牌的复购率
    --重复购买人数占购买人数的比例
    --数据来源
        --dws_trade_user_sku_order_nd
DROP TABLE IF EXISTS ads_repeat_purchase_by_tm;
CREATE EXTERNAL TABLE ads_repeat_purchase_by_tm
(
    `dt`                  STRING COMMENT '统计日期',
    `recent_days`       BIGINT COMMENT '最近天数,30:最近30天',
    `tm_id`              STRING COMMENT '品牌ID',
    `tm_name`            STRING COMMENT '品牌名称',
    `order_repeat_rate` DECIMAL(16, 2) COMMENT '复购率'
) COMMENT '最近30日各品牌复购率统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_repeat_purchase_by_tm/';
insert overwrite table ads_repeat_purchase_by_tm
select *from ads_repeat_purchase_by_tm
union
select
    "2022-06-08",
    30,
    tm_id,
    tm_name,
    sum(`if`(order_count>1,1,0))/count(user_id)*100
from (
    select
        user_id,
        tm_id,
        tm_name,
        sum(order_count_30d)order_count
    from dws_trade_user_sku_order_30d
    group by user_id, tm_id, tm_name
     )t
group by tm_id, tm_name ;

--各品牌下单统计
DROP TABLE IF EXISTS ads_order_stats_by_tm;
CREATE EXTERNAL TABLE ads_order_stats_by_tm
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `tm_id`                   STRING COMMENT '品牌ID',
    `tm_name`                 STRING COMMENT '品牌名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品牌商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_tm/';
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
select
    *
from(

select
    "2022-06-08",
    1,
    tm_id,
    tm_name,
    sum(order_count_1d),
    count(distinct user_id)
from dws_trade_user_sku_order_1d
where dt="2022-06-08"
group by tm_id, tm_name
union all
--最近7日
select
    "2022-06-08",
    7,
    tm_id,
    tm_name,
    sum(order_count_7d),
    count(distinct user_id)
from dws_trade_user_sku_order_30d
where dt="2022-06-08"
and order_count_7d>0
group by tm_id, tm_name
union all
--最近30日
select
    "2022-06-08",
    30,
    tm_id,
    tm_name,
    sum(order_count_30d),
    count(distinct user_id)
from dws_trade_user_sku_order_30d
where dt="2022-06-08"
and order_count_30d>0
group by tm_id, tm_name
    )t;

--融合
insert overwrite table ads_order_stats_by_tm
select * from ads_order_stats_by_tm
union
select
    *
from(
select
    "2022-06-08",
    1,
    tm_id,
    tm_name,
    sum(order_count_1d),
    count(distinct user_id)
from dws_trade_user_sku_order_1d
where dt="2022-06-08"
group by tm_id, tm_name
union all
select
    "2022-06-08",
    day,
    tm_id,
    tm_name,
    sum(order_count),
    count(distinct user_id)
from (
    select
        "2022-06-08",
        day,
        tm_id,
        tm_name,
        user_id,
        `if`(day=7,order_count_7d,order_count_30d)order_count
    from dws_trade_user_sku_order_30d lateral view explode(array(7,30))tmp as day
    where dt="2022-06-08"
     )t where order_count>0
group by day, tm_id, tm_name )tm;
--各品类商品下单统计
DROP TABLE IF EXISTS ads_order_stats_by_cate;
CREATE EXTERNAL TABLE ads_order_stats_by_cate
(
    `dt`                      STRING COMMENT '统计日期',
    `recent_days`             BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `category1_id`            STRING COMMENT '一级品类ID',
    `category1_name`          STRING COMMENT '一级品类名称',
    `category2_id`            STRING COMMENT '二级品类ID',
    `category2_name`          STRING COMMENT '二级品类名称',
    `category3_id`            STRING COMMENT '三级品类ID',
    `category3_name`          STRING COMMENT '三级品类名称',
    `order_count`             BIGINT COMMENT '下单数',
    `order_user_count`        BIGINT COMMENT '下单人数'
) COMMENT '各品类商品下单统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_stats_by_cate/';

insert overwrite table ads_order_stats_by_cate
select * from ads_order_stats_by_cate
union
    select
        *
    from(
    select
        "2022-06-08",
        1,
        `category1_id`  ,
        `category1_name`,
        `category2_id`  ,
        `category2_name`,
        `category3_id`  ,
        `category3_name`,
        sum(order_count_1d),
        count(distinct user_id)
    from dws_trade_user_sku_order_1d
    where dt="2022-06-08"
    group by `category1_id`, `category1_name`, `category2_id`, `category2_name`, `category3_id`, `category3_name`
union all
    select
        "2022-06-08",
        day,
        `category1_id`  ,
        `category1_name`,
        `category2_id`  ,
        `category2_name`,
        `category3_id`  ,
        `category3_name`,
        sum(order_count),
        count(distinct user_id)
    from (
        select
            "2022-06-08",
            day,
            `category1_id`  ,
            `category1_name`,
            `category2_id`  ,
            `category2_name`,
            `category3_id`  ,
            `category3_name`,
            user_id,
            `if`(day=7,order_count_7d,order_count_30d)order_count
        from dws_trade_user_sku_order_30d lateral view explode(array(7,30))tmp as day
        where dt="2022-06-08"
         )t where order_count>0
    group by day, `category1_id`, `category1_name`, `category2_id`, `category2_name`, `category3_id`, `category3_name` )tm;

--各个品类商品购物车存量top3
    --各品类商品
    --购物车存量
        --周期快照事实表
    --1.（省份+商品）销量统计出来
    --2.相同省份分组
    --3.将组内的统计结果数据进行排序
    --4.将组内排序的结果取前N条
DROP TABLE IF EXISTS ads_sku_cart_num_top3_by_cate;
CREATE EXTERNAL TABLE ads_sku_cart_num_top3_by_cate
(
    `dt`             STRING COMMENT '统计日期',
    `category1_id`   STRING COMMENT '一级品类ID',
    `category1_name` STRING COMMENT '一级品类名称',
    `category2_id`   STRING COMMENT '二级品类ID',
    `category2_name` STRING COMMENT '二级品类名称',
    `category3_id`   STRING COMMENT '三级品类ID',
    `category3_name` STRING COMMENT '三级品类名称',
    `sku_id`         STRING COMMENT 'SKU_ID',
    `sku_name`       STRING COMMENT 'SKU名称',
    `cart_num`       BIGINT COMMENT '购物车中商品数量',
    `rk`             BIGINT COMMENT '排名'
) COMMENT '各品类商品购物车存量Top3'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_sku_cart_num_top3_by_cate/';
insert overwrite table ads_sku_cart_num_top3_by_cate
select * from ads_sku_cart_num_top3_by_cate
union
select
            "2022-06-08",
            `category1_id`  ,
            `category1_name`,
            `category2_id`  ,
            `category2_name`,
            `category3_id`  ,
            `category3_name`,
            `sku_id`        ,
            `sku_name`      ,
            `cart_num`      ,
            rk
from (
    select
             `category1_id`  ,
            `category1_name`,
            `category2_id`  ,
            `category2_name`,
            `category3_id`  ,
            `category3_name`,
            `sku_id`        ,
            `sku_name`      ,
            `cart_num`      ,
            row_number() over (partition by category1_id ,category2_id,category3_id order by cart_num desc ) rk
    from(
        select
            `category1_id`  ,
            `category1_name`,
            `category2_id`  ,
            `category2_name`,
            `category3_id`  ,
            `category3_name`,
            `sku_id`        ,
            `sku_name`      ,
            `cart_num`
        from (
            select
                sku_id,
                sum(sku_num) cart_num
            from dwd_trade_cart_full
            where dt="2022-06-08"
            group by sku_id
        )cart
        left join (select
                        id,
                        sku_name,
                        category1_id,
                        category1_name,
                        category2_id,
                        category2_name,
                        category3_id,
                        category3_name
                   from dim_sku_full
                   where dt="2022-06-08"
        )sku on cart.sku_id=sku.id
    )t
    group by
            `category1_id`  ,
            `category1_name`,
            `category2_id`  ,
            `category2_name`,
            `category3_id`  ,
            `category3_name`,
            `sku_id`        ,
            `sku_name`      ,
            `cart_num`
)tmp
where rk<=3;

--各品牌商品收藏次数Top3
DROP TABLE IF EXISTS ads_sku_favor_count_top3_by_tm;
CREATE EXTERNAL TABLE ads_sku_favor_count_top3_by_tm
(
    `dt`          STRING COMMENT '统计日期',
    `tm_id`       STRING COMMENT '品牌ID',
    `tm_name`     STRING COMMENT '品牌名称',
    `sku_id`      STRING COMMENT 'SKU_ID',
    `sku_name`    STRING COMMENT 'SKU名称',
    `favor_count` BIGINT COMMENT '被收藏次数',
    `rk`          BIGINT COMMENT '排名'
) COMMENT '各品牌商品收藏次数Top3'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_sku_favor_count_top3_by_tm/';
insert overwrite table ads_sku_favor_count_top3_by_tm
select * from ads_sku_favor_count_top3_by_tm
union
select
    "2022-06-08",
    tm_id,
    tm_name,
    sku_id,
    sku_name,
    favor_add_count_1d,
    rk
from (
    select
    tm_id,
    tm_name,
    sku_id,
    sku_name,
    favor_add_count_1d,
    rank() over (partition by tm_id order by favor_add_count_1d desc )rk
    from dws_interaction_sku_favor_add_1d
    where dt="2022-06-08"
     )td
where rk<=3;

--下单到支付时间间隔平均值
DROP TABLE IF EXISTS ads_order_to_pay_interval_avg;
CREATE EXTERNAL TABLE ads_order_to_pay_interval_avg
(
    `dt`                        STRING COMMENT '统计日期',
    `order_to_pay_interval_avg` BIGINT COMMENT '下单到支付时间间隔平均值,单位为秒'
) COMMENT '下单到支付时间间隔平均值统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_to_pay_interval_avg/';
--用户年龄平均值：avg
--时间间隔平均值avg（datediff（支付时间，下单时间））
--累计快照事实表
--to_unix_timestamp把时间转为时间戳（秒）
insert overwrite table ads_order_to_pay_interval_avg
select * from ads_order_to_pay_interval_avg
union
select
    "2022-06-08",
    avg(to_unix_timestamp(payment_time)-to_unix_timestamp(order_time))
from dwd_trade_trade_flow_acc
where (dt="2022-06-08"or dt="9999-12-31") and payment_date_id="2022-06-08";
--各个省份交易统计
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_total_amount` DECIMAL(16, 2) COMMENT '订单金额'
) COMMENT '各省份交易统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_order_by_province/';
insert overwrite table ads_order_by_province
select *
from ads_order_by_province
union
    select
        "2022-06-08"            ,
        1                ,
    `province_id`       ,
    `province_name`     ,
    `area_code`         ,
    `iso_code`          ,
    iso_3166_2 `iso_code_3166_2`   ,
    `order_count_1d`       ,
    `order_total_amount_1d`
    from gmall.dws_trade_province_order_1d
    where dt="2022-06-08"
union all
    select
        "2022-06-08"            ,
        7                ,
    `province_id`       ,
    `province_name`     ,
    `area_code`         ,
    `iso_code`          ,
    iso_3166_2 `iso_code_3166_2`   ,
    `order_count_7d`       ,
    `order_total_amount_7d`
    from gmall.dws_trade_province_order_nd
    where dt="2022-06-08"
union all
    select
        "2022-06-08"            ,
        30                ,
    `province_id`       ,
    `province_name`     ,
    `area_code`         ,
    `iso_code`          ,
    iso_3166_2 `iso_code_3166_2`   ,
    `order_count_30d`       ,
    `order_total_amount_30d`
    from gmall.dws_trade_province_order_nd
    where dt="2022-06-08";

insert overwrite table ads_order_by_province
select *
from ads_order_by_province
union
select *
from (
    select
        "2022-06-08"            ,
            1                ,
        `province_id`       ,
        `province_name`     ,
        `area_code`         ,
        `iso_code`          ,
        iso_3166_2 `iso_code_3166_2`   ,
        `order_count_1d`       ,
        `order_total_amount_1d`
        from gmall.dws_trade_province_order_1d
        where dt="2022-06-08"
    union
    select
        "2022-06-08"            ,
        day                ,
        `province_id`       ,
        `province_name`     ,
        `area_code`         ,
        `iso_code`          ,
        iso_3166_2 `iso_code_3166_2`   ,
        if(day=7,order_count_7d,order_count_30d)      ,
        if(day=7,order_total_amount_7d,order_total_amount_30d)
        from gmall.dws_trade_province_order_nd lateral view explode(array(7,30)) tmp as day
        where dt="2022-06-08"
     )tmp;

--优惠券使用统计
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 '优惠券名称',
    `used_count`      BIGINT COMMENT '使用次数',
    `used_user_count` BIGINT COMMENT '使用人数'
) COMMENT '优惠券使用统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_coupon_stats/';

insert overwrite table ads_coupon_stats
select * from ads_coupon_stats
union
select
    '2022-06-08' dt,
    coupon_id,
    coupon_name,
    cast(sum(used_count_1d) as bigint),
    cast(count(*) as bigint)
from dws_tool_user_coupon_coupon_used_1d
where dt='2022-06-08'
group by coupon_id,coupon_name;