查看每件商品的售价涨幅情况
题目需求
从商品价格变更明细表(sku_price_modify_detail),得到最近一次价格的涨幅情况,并按照涨幅升序排序。
结果如下:
Sku_id(商品id) | Price_change(涨幅) |
---|---|
8 | -200.00 |
9 | -100.00 |
2 | -70.00 |
11 | -16.00 |
12 | -15.00 |
3 | 1.00 |
5 | 10.00 |
10 | 10.00 |
7 | 12.00 |
6 | 12.00 |
1 | 100.00 |
4 | 400.00 |
代码实现
-- 对每个商品按照修改日期倒序排序 并求出差值
select
sku_id,
new_price-lead(new_price,1,0) over (partition by sku_id order by change_date desc) price_change,
rank() over (partition by sku_id order by change_date desc) rk
from
sku_price_modify_detail
-- 最近一次修改的价格
select
sku_id,
price_change
from
(
select
sku_id,
new_price-lead(new_price,1,0) over (partition by sku_id order by change_date desc) price_change,
rank() over (partition by sku_id order by change_date desc) rk
from
sku_price_modify_detail
)t1
where
rk = 1
order by
price_change;