需求

各分類商品購(gòu)物車存量Top10

前提

今天是 2020-06-14

建表語句

CREATE EXTERNAL TABLE ads_sku_cart_num_top10_by_cate
(
    `dt`             STRING COMMENT '統(tǒng)計(jì)日期',
    `category1_id`   STRING COMMENT '一級(jí)分類ID',
    `category1_name` STRING COMMENT '一級(jí)分類名稱',
    `category2_id`   STRING COMMENT '二級(jí)分類ID',
    `category2_name` STRING COMMENT '二級(jí)分類名稱',
    `category3_id`   STRING COMMENT '三級(jí)分類ID',
    `category3_name` STRING COMMENT '三級(jí)分類名稱',
    `sku_id`         STRING COMMENT '商品id',
    `sku_name`       STRING COMMENT '商品名稱',
    `cart_num`       BIGINT COMMENT '購(gòu)物車中商品數(shù)量',
    `rk`             BIGINT COMMENT '排名'
) COMMENT '各分類商品購(gòu)物車存量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,
                -- 計(jì)算當(dāng)前的sku_id在同一個(gè)三級(jí)分類中的排名
                row_number() over (partition by category3_id order by cart_num desc) rk
         from (
                  select sku_id,
                         -- 所有用戶購(gòu)物車中添加的此種商品的數(shù)量累加
                         sum(sku_num) cart_num
-- 一個(gè)用戶購(gòu)物車中的一個(gè)sku是一行
                  from dwd_trade_cart_full
-- 只統(tǒng)計(jì)當(dāng)天用戶購(gòu)物車中的存量信息
                  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;

步驟

只討論 三級(jí)分類

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
        -- 一個(gè)用戶在一天下單的一個(gè)商品是一行
        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. 統(tǒng)計(jì)當(dāng)天用戶購(gòu)物車中的存量信息，根據(jù)某個(gè)商品，統(tǒng)計(jì)其在購(gòu)物車中的數(shù)量
   A表:

select sku_id,
  -- 所有用戶購(gòu)物車中添加的此種商品的數(shù)量累加
  sum(sku_num) cart_num
-- 一個(gè)用戶購(gòu)物車中的一個(gè)sku是一行
from dwd_trade_cart_full
-- 只統(tǒng)計(jì)當(dāng)天用戶購(gòu)物車中的存量信息
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. 左關(guān)聯(lián)，運(yùn)用 開窗函數(shù) 進(jìn)行排序
   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;