踏踏实实练习HSQ--day02

select sku_id,order_amount,year,order_num
from
(select sku_id,sum(price*sku_num) as order_amount
,substring(create_date,1,4) as year,
 sum(sku_num) as order_num,
row_number() over(partition by sku_id order by substring(create_date,1,4)) as rn
from order_detail
group by sku_id,substring(create_date,1,4))t1
where rn=1

hive> 
select sku_id,
       year(create_date),
       sum(sku_num),
       sum(price*sku_num)
from (
         select order_id,
                sku_id,
                price,
                sku_num,
                create_date,
                rank() over (partition by sku_id order by year(create_date)) rk
         from order_detail
     ) t1
where rk = 1
group by sku_id,year(create_date);