Hive sql 常见面试题-查询销售件数高于品类平均数的商品

1. 题目需求

从订单明细表（order_detail）中查询累积销售件数高于其所属品类平均数的商品

期望结果如下：

2. 需要用到的表：

商品信息表：sku_info

订单明细表：order_detail

3. 查询sql

select
  sku_id,
  name,
  sum_num,
  cate_avg_num
from
  (
    select
      category_id,
      sku_id,
      name,
      sum_num,
      cast(
        avg(sum_num) over (
          partition by
            category_id
        ) as int
      ) cate_avg_num
    from
      (
        select
          category_id,
          name,
          od.sku_id,
          sum(sku_num) sum_num
        from
          order_detail od
          join sku_info si on od.sku_id = si.sku_id
        group by
          si.category_id,
          od.sku_id,
          name
      ) t1
  ) t2
where
  sum_num > cate_avg_num