每个商品销售首年的年份、销售数量和销售金额
题目需求
从订单明细表(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,
year(create_date) year,
sum(sku_num) order_num,
sum(sku_num*price) order_amount
from
(
select
sku_id,
create_date,
sku_num,
price,
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);