每个商品销售首年的年份、销售数量和销售金额
题目需求
从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。
期望结果如下：
sku_id （商品id） |
year （销售首年年份） |
order_num （首年销量） |
order_amount （首年销售金额） |
1 |
2021 |
51 |
102000.00 |
2 |
2021 |
302 |
3020.00 |
3 |
2021 |
36 |
180000.00 |
4 |
2021 |
53 |
318000.00 |
5 |
2021 |
242 |
121000.00 |
6 |
2021 |
32 |
64000.00 |
7 |
2021 |
252 |
25200.00 |
8 |
2021 |
253 |
151800.00 |
9 |
2021 |
194 |
194000.00 |
10 |
2021 |
299 |
29900.00 |
11 |
2021 |
320 |
16000.00 |
12 |
2021 |
349 |
6980.00 |
我做的：
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);
