Hive sql 常见面试题-国庆期间每个品类的商品的收藏量和购买量

1. 题目需求

从订单明细表（order_detail）和收藏信息表（favor_info）统计2021国庆期间，每个商品总收藏量和购买量

结果如下：

2. 需要用到的表

订单明细表：order_detail

收藏信息表：favor_info

3. 查询sql

with

  o1 as (

    select

      sku_id,

      sum(sku_num) as sku_sum

    from

      order_detail

    where

      create_date BETWEEN '2021-10-01' and '2021-10-07'

    group by

      sku_id

  ),

  f1 as (

    select

      sku_id,

      count(1) as favor_cn

    from

      favor_info

    where

      create_date BETWEEN '2021-10-01' and '2021-10-07'

    group by

      sku_id

  )

select

  o1.sku_id,

  nvl(o1.sku_sum,0) as sku_sum,

  nvl(f1.favor_cn,0) as favor_cn

from

  o1

  full OUTER join f1 on o1.sku_id = f1.sku_id