Hive 刷题——统计每日商品1和商品2销量的差值

需求描述

从订单明细表（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;