需求
各分類商品購物車存量Top10
前提
今天是 2020-06-14
建表語句
CREATE EXTERNAL TABLE ads_sku_cart_num_top10_by_cate
(
`dt` STRING COMMENT '統(tǒng)計日期',
`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 '購物車中商品數(shù)量',
`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,
-- 計算當(dāng)前的sku_id在同一個三級分類中的排名
row_number() over (partition by category3_id order by cart_num desc) rk
from (
select sku_id,
-- 所有用戶購物車中添加的此種商品的數(shù)量累加
sum(sku_num) cart_num
-- 一個用戶購物車中的一個sku是一行
from dwd_trade_cart_full
-- 只統(tǒng)計當(dāng)天用戶購物車中的存量信息
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;
步驟
只討論 三級分類
- 查詢一天
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
步驟
- 統(tǒng)計當(dāng)天用戶購物車中的存量信息蘑拯，根據(jù)某個商品兜粘，統(tǒng)計其在購物車中的數(shù)量
A表:
select sku_id,
-- 所有用戶購物車中添加的此種商品的數(shù)量累加
sum(sku_num) cart_num
-- 一個用戶購物車中的一個sku是一行
from dwd_trade_cart_full
-- 只統(tǒng)計當(dāng)天用戶購物車中的存量信息
where dt = '2020-06-14'
group by sku_id
- 獲取商品 品類信息
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'
- 左關(guān)聯(lián)孔轴，運用 開窗函數(shù) 進行排序
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
- 從 C表中 取到 前10
select
...
rk -- 排名
from C
where rk <= 10;
