国庆期间每个品类的商品的收藏量和购买量
题目需求
从订单明细表(order_detail)和收藏信息表(favor_info)统计2021国庆期间,每个商品总收藏量和购买量
结果如下:
Sku_id | Sku_sum(购买量) | Favor_cn(收藏量) |
---|---|---|
1 | 38 | 1 |
10 | 205 | 2 |
11 | 225 | 2 |
12 | 20556 | 0 |
2 | 6018 | 1 |
3 | 30 | 0 |
4 | 44 | 2 |
5 | 209 | 1 |
6 | 26 | 1 |
7 | 180 | 1 |
8 | 148 | 0 |
9 | 182 | 1 |
代码实现
select
t1.sku_id,
t1.sku_sum,
nvl(t2.favor_cn, 0) favor_cn
from
(
select
sku_id,
sum(sku_num) sku_sum
from
order_detail
where
create_date>='2021-10-01' and create_date<='2021-10-07'
group by
sku_id
)t1
join
(
select
sku_id,
count(*) favor_cn
from
favor_info
where
create_date>='2021-10-01' and create_date<='2021-10-07'
group by
sku_id
)t2
on
t1.sku_id=t2.sku_id