需求

各分类商品购物车存量Top10

前提

今天是 2020-06-14

建表语句

CREATE EXTERNAL TABLE ads_sku_cart_num_top10_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 '商品id',
    `sku_name`       STRING COMMENT '商品名称',
    `cart_num`       BIGINT COMMENT '购物车中商品数量',
    `rk`             BIGINT COMMENT '排名'
) COMMENT '各分类商品购物车存量Top10'

完整sql

insert overwrite table ads_sku_cart_num_top10_by_cate
select * from ads_sku_cart_num_top10_by_cate
union
select
        '2020-06-14' dt,
       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,
                -- 计算当前的sku_id在同一个三级分类中的排名
                row_number() over (partition by category3_id order by cart_num desc) rk
         from (
                  select sku_id,
                         -- 所有用户购物车中添加的此种商品的数量累加
                         sum(sku_num) cart_num
-- 一个用户购物车中的一个sku是一行
                  from dwd_trade_cart_full
-- 只统计当天用户购物车中的存量信息
                  where dt = '2020-06-14'
                  group by sku_id
              ) t1
                  left join
              (
                  select id,
                         category1_id,
                         category1_name,
                         category2_id,
                         category2_name,
                         category3_id,
                         category3_name,
                         sku_name
                  from dim_sku_full
                  where dt = '2020-06-14'
              ) t2
              on t1.sku_id = t2.id
     ) t3
where rk <= 10;

步骤

只讨论 三级分类

1. 查询一天

 select
              1 recent_days,category1_id, category1_name, category2_id,category2_name, category3_id,category3_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 category1_id, category1_name, category2_id,category2_name, category3_id,category3_name

步骤

1. 统计当天用户购物车中的存量信息，根据某个商品，统计其在购物车中的数量
   A表:

select sku_id,
  -- 所有用户购物车中添加的此种商品的数量累加
  sum(sku_num) cart_num
-- 一个用户购物车中的一个sku是一行
from dwd_trade_cart_full
-- 只统计当天用户购物车中的存量信息
where dt = '2020-06-14'
group by sku_id

2. 获取商品 品类信息
   B表:

select id,
           category1_id,
           category1_name,
           category2_id,
           category2_name,
           category3_id,
           category3_name,
           sku_name
from dim_sku_full
where dt = '2020-06-14'

3. 左关联，运用 开窗函数 进行排序
   C表：

select
...
row_number() over (partition by category3_id order by cart_num desc) rk   ---- 排名
from A
left join B 
on  A.sku_id = B.id

4. 从 C表中 取到 前10

select
       ...
       rk -- 排名
from   C
where rk <= 10;