需求-各品牌商品交易统计

建表语句

CREATE EXTERNAL TABLE ads_trade_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 '订单人数',
    `order_refund_count`      BIGINT COMMENT '退单数',
    `order_refund_user_count` BIGINT COMMENT '退单人数'
) COMMENT '各品牌商品交易统计'

前提

今天为 2020-06-14

完整sql

with
    order_info as (

        select
              1 recent_days,tm_id,tm_name,
               sum(order_count_1d) order_count,
               count(distinct user_id) order_user_count
        -- 一个用户在一天下单的一个商品是一行
        from dws_trade_user_sku_order_1d
        where dt='2020-06-14'
        group by tm_id,tm_name
        union all

               select
                        recent_days,tm_id,tm_name,
                       sum(if(recent_days = 7,order_count_7d,order_count_30d)) order_count,
                      -- 判断这个人在最近7天或30天是否下过单
                       count(distinct `if`(if(recent_days = 7,order_count_7d,order_count_30d) >0 ,user_id,null) ) order_user_count
                -- 一个用户在一天下单的一个商品是一行
               -- 一个人最近30天下单了，但是最新7天没有下单
                from dws_trade_user_sku_order_nd
                lateral view explode(`array`( 7, 30)) tmp as recent_days
                where dt='2020-06-14'
                group by recent_days,tm_id,tm_name

        -- union all 就是上下拼接
        -- union : 在union all的基础上，再 group by 上下拼接的所有列


    ),
     refund_info as (

        select
              1 recent_days,tm_id,tm_name,
               sum(order_refund_count_1d) order_refund_count,
               count(distinct user_id) order_refund_user_count
        -- 一个用户在一天下单的一个商品是一行
        from dws_trade_user_sku_order_refund_1d
        where dt='2020-06-14'
        group by tm_id,tm_name
        union all
               select
                        recent_days,tm_id,tm_name,
                       sum(if(recent_days = 7,order_refund_count_7d,order_refund_count_30d)) order_refund_count,
                      -- 判断这个人在最近7天或30天是否下过单
                       count(distinct `if`(if(recent_days = 7,order_refund_count_7d,order_refund_count_30d) >0 ,user_id,null) ) order_refund_user_count
                -- 一个用户在一天下单的一个商品是一行
               -- 一个人最近30天下单了，但是最新7天没有下单
                from dws_trade_user_sku_order_refund_nd
                lateral view explode(`array`( 7, 30)) tmp as recent_days
                where dt='2020-06-14'
                group by recent_days,tm_id,tm_name



    )
insert overwrite table ads_trade_stats_by_tm
select * from ads_trade_stats_by_tm
union
select
      '2020-06-14' dt,
       nvl(order_info.recent_days,refund_info.recent_days) recent_days,
       nvl(order_info.tm_id, refund_info.tm_id) tm_id,
       nvl(order_info.tm_name, refund_info.tm_name) tm_name,
       nvl(order_count,0) ,
       nvl(order_user_count,0) ,
       nvl(order_refund_count,0) ,
       nvl(order_refund_user_count,0)
--
from
     -- 最近30天所有下单的品牌
     order_info
         full join
         -- 所有退单的品牌
         refund_info
on order_info.recent_days = refund_info.recent_days and order_info.tm_id= refund_info.tm_id;

思考步骤

订单 与 退单 思路相同 ，只简述 订单步骤

1. 算 1日 订单数 与 订单人数

  select
              1 recent_days,tm_id,tm_name,
               sum(order_count_1d) order_count,
               count(distinct user_id) order_user_count
        -- 一个用户在一天下单的一个商品是一行
        from dws_trade_user_sku_order_1d
        where dt='2020-06-14'
        group by tm_id,tm_name

2. 计算 7 30 日
   （1）表 dws_trade_user_sku_order_nd

（2）将上表的数据 变成两份

（3）根据 recent_days,tm_id,tm_name 聚合
（4）选择有效数据

A => if(recent_days = 7,order_count_7d,order_count_30d)

（4）判断是否 订单数>0 ,有效则记录用户id，无效则null
用户1 订单1 匹克 篮球鞋
用户1 订单2 匹克 拖鞋

计为 两条记录

B => `if`( A >0 ,user_id,null)

（5）因为 dws_trade_user_sku_order_nd 的粒度为 sku
防止 有人 在 同一品牌 不同商品 下单
计算下单人数时：需要对订单 去重

  count(distinct B )