点击关注公众号，干货第一时间送达
一道比较简单的题目，
用到了咱们之前演示过的 "开窗排序"，
还有就是，"偏移函数"，
hive 中的 "偏移函数" 包括 lag()、lead()
作用就是获取当前行的前后行数据，方便计算。
话不多说，一起来看看。
【温馨提示：建表语句及数据导入脚本，已经放在文章末尾】
一、需求场景
题目：
查询商品价格变更明细表sku_price_modify_detail，计算每件商品最近一次价格的涨幅情况，并按照涨幅升序排列。
商品价格变更明细表sku_price_modify_detail：
正确结果：
二、解决方案
演示工具：Hive-3.1.3 + DataGrip2022
从商品价格变更明细表sku_price_modify_detail：
可以直观的看出价格new_price，
随着日期change_date，
有所变化，
涨幅的计算，也就是求出相邻两行new_price的差值。
不过，题目要求的是最近的价格变动。
所以还需要考虑对change_date的筛选，
过滤得到的应该是最新的日期。
有了思路就好操作了。
（1）按照商品sku_id 分组，价格变更日期change_date排序，分别获取每行的下一行数据
select sku_id,
change_date,
new_price,
lead(new_price) over (partition by sku_id order by change_date desc ) as old_price
from sku_price_modify_detail
结果集：
大家可以看到，通过 "lead()" 偏移函数的操作，
可以使得每行数据，都可以获取到其下一行中的 new_price值，
放到 old_price 列。
如果下一行数据不存在，则 old_price 用 null 填充。
lead() 函数知识补充：
LEAD(column [, offset [, default_value]]) OVER ( [ORDER BY clause] )
column 参数：指定要获取其值的列名称。
offset 参数：指定要偏移的行数。
default_value 参数：指定如果无法找到下一行，则使用的默认值。ORDER BY 子句：用于指定窗口中的行顺序。
上面代码小编用了简写，完整的写法是：
lead(new_price,1,null) over (partition by sku_id order by change_date desc ) as old_price
此外，诸位可以注意到有 null 的行，其实不需要，
怎么剔除呢？
看下面。
（2）按照商品sku_id 分组，价格变更日期change_date排序，每行数据排序
select sku_id,
change_date,
new_price,
lead(new_price) over (partition by sku_id order by change_date desc ) as old_price,
row_number() over (partition by sku_id order by change_date desc ) as rk
from sku_price_modify_detail
结果集：
诸位看到没，增加了序号 rk，
接下来直接筛选出 rk=1 的即可。
关于 "row_number()" 函数的使用演示，
前面的文章：
有详细介绍，感兴趣的小伙伴可以点击翻看。
到了这，聪明的小伙伴可以发现前面两个步骤可以简化，
并且直接得出结果。
（3）计算商品价格涨幅
select sku_id,
new_price-old_price as diff
from (
select sku_id,new_price,
lead(new_price) over (partition by sku_id order by change_date desc ) as old_price,
row_number() over (partition by sku_id order by change_date desc) as rk
from sku_price_modify_detail
)t1
where rk=1
order by diff;
结果集：
三、源数据
商品价格变更明细表sku_price_modify_detail：
create table sku_price_modify_detail
(
sku_id string,
new_price decimal(16, 2),
change_date string
)
row format serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
with serdeproperties ('field.delim' = '\t') stored as
inputformat 'org.apache.hadoop.mapred.TextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location 'hdfs://mycluster/user/hive/warehouse/hql_test2.db/sku_price_modify_detail'
tblproperties ('bucketing_version' = '2');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('1', 1900.00, '2021-09-25');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('1', 2000.00, '2021-09-26');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('2', 80.00, '2021-09-29');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('2', 10.00, '2021-09-30');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('3', 4999.00, '2021-09-25');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('3', 5000.00, '2021-09-26');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('4', 5600.00, '2021-09-26');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('4', 6000.00, '2021-09-27');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('5', 490.00, '2021-09-27');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('5', 500.00, '2021-09-28');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('6', 1988.00, '2021-09-30');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('6', 2000.00, '2021-10-01');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('7', 88.00, '2021-09-28');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('7', 100.00, '2021-09-29');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('8', 800.00, '2021-09-28');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('8', 600.00, '2021-09-29');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('9', 1100.00, '2021-09-27');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('9', 1000.00, '2021-09-28');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('10', 90.00, '2021-10-01');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('10', 100.00, '2021-10-02');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('11', 66.00, '2021-10-01');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('11', 50.00, '2021-10-02');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('12', 35.00, '2021-09-28');
INSERT INTO hql_test2.sku_price_modify_detail (sku_id, new_price, change_date) VALUES ('12', 20.00, '2021-09-29');
文章转载自皮皮克克，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
