1. 筛选去年总销量小于100的商品
1.1 题目需求
从订单明细表(order_detail)中筛选出去年(2021年)总销量小于100的商品及其销量,同时不考虑上架时间少于一个月的商品。假设今天的日期是2022-01-10。
期望结果如下:
sku_id | name | order_num |
---|---|---|
1 | xiaomi 10 | 51 |
3 | apple 12 | 36 |
4 | xiaomi 13 | 53 |
6 | 洗碗机 | 32 |
1.2 代码实现
hive>
select t1.sku_id,name,order_num
from (select sku_id,sum(sku_num) order_numfrom order_detailwhere year(create_date) = '2021'and sku_id in (select sku_idfrom sku_infowhere datediff('2022-01-10', from_date) > 30)group by sku_idhaving sum(sku_num) < 100