Hive sql 常见面试题-每个商品销售首年的年份、销售数量和销售金额

1. 题目需求：

从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

期望结果如下：

2. 需要用到的表：

订单明细表：order_detail

3. 查询结果

select
  sku_id,
  year,
  sum(`if` (now_year = year, sku_num, 0)) order_num,
  sum(`if` (now_year = year, sku_num * price, 0)) order_amount
from
  (
    select
      sku_id,
      year (create_date) now_year,
      sku_num,
      price,
      first_value (year (create_date)) over (
        partition by
          sku_id
        order by
          create_date
      ) year
    from
      order_detail
  ) t
group by
  sku_id,
  year;