需求
各分类商品购物车存量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;
步骤
只讨论 三级分类
- 查询一天
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
步骤
- 统计当天用户购物车中的存量信息，根据某个商品，统计其在购物车中的数量
A表:
select sku_id,
-- 所有用户购物车中添加的此种商品的数量累加
sum(sku_num) cart_num
-- 一个用户购物车中的一个sku是一行
from dwd_trade_cart_full
-- 只统计当天用户购物车中的存量信息
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'
- 左关联，运用 开窗函数 进行排序
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;
