标签：sku 10 create Hive 商品 2021 date id 刷题
需求描述
从订单明细表（order_detail）中统计每天商品1和商品2销量（件数）的差值（商品1销量-商品2销量）
期望结果如下：
|
create_date
|
diff
|
|
2020-10-08
|
-24
|
|
2021-09-27
|
2
|
|
2021-09-30
|
9
|
|
2021-10-01
|
-10
|
|
2021-10-02
|
-5800
|
|
2021-10-03
|
4
|
|
2021-10-04
|
-55
|
|
2021-10-05
|
-30
|
|
2021-10-06
|
-49
|
|
2021-10-07
|
-40
|
需要用到的表：
订单明细表：order_detail
|
order_detail_id(订单明细id)
|
order_id(订单id)
|
sku_id(商品id)
|
create_date(下单日期)
|
price(商品单价)
|
sku_num(商品件数)
|
|
1
|
1
|
1
|
2021-09-30
|
2000.00
|
2
|
|
2
|
1
|
3
|
2021-09-30
|
5000.00
|
5
|
|
22
|
10
|
4
|
2020-10-02
|
6000.00
|
1
|
|
23
|
10
|
5
|
2020-10-02
|
500.00
|
24
|
|
24
|
10
|
6
|
2020-10-02
|
2000.00
|
5
|
参考SQL
select coalesce(t.create_date, t1.create_date) create_date,
nvl(t.sku_num, 0) - nvl(t1.sku_num, 0) diff
from (select create_date, sum(sku_num) sku_num
from order_detail
where sku_id = '1'
group by create_date) t
full join
(select create_date, sum(sku_num) sku_num
from order_detail
where sku_id = '2'
group by create_date) t1 on t.create_date = t1.create_date;
标签：sku,
10,
create,
Hive,
商品,
2021,
date,
id,
刷题
From： https://www.cnblogs.com/wdh01/p/17041055.html