hive 实现行轮值
需求
现需要返回每本小说的的iD,书名、评分，以及下一个比当前小说高和低的评分值。如果没有找到更高或更低的评分值，你希望结果集可以“折回”（第一个 score 的前一行是最后一个score；反之，最后一个 score 的下一行即是第一个 scoe）。你希望返回如下所示的结果集。
|
id
|
name
|
score
|
forward_score
|
rewind_score
|
|
1004
|
孔雀翎
|
79.29
|
80.66
|
97.66
|
|
1007
|
侠客行
|
80.66
|
81
|
79.29
|
|
1009
|
绝代双骄
|
81
|
82.9
|
80.66
|
|
1010
|
碧血剑
|
82.9
|
85.75
|
81
|
|
1006
|
萍踪侠影
|
85.75
|
86.31
|
82.9
|
|
1005
|
流星蝴蝶剑
|
86.31
|
88.95
|
85.75
|
|
1012
|
武林外传
|
88.95
|
91.69
|
86.31
|
|
1001
|
笑傲镜湖
|
91.69
|
92.88
|
88.95
|
|
1011
|
射雕英雄传
|
92.88
|
95.21
|
91.69
|
|
1002
|
倚天屠龙记
|
95.21
|
96.32
|
92.88
|
|
1003
|
神雕侠侣
|
96.32
|
97.66
|
95.21
|
|
1008
|
小李飞刀
|
97.66
|
79.29
|
96.32
|
需求其实就是寻找比当前小说评分低及评分高的前后的评分值，如果遇到最高的评分或最低的评分则进行折回
数据准备
create table movies ( id int, muvie_name varchar(25), score double ) stored as orc tblproperties ("orc.compress" = "snappy"); insert into movies values (1001, "笑傲镜湖", 91.69); insert into movies values (1002, "倚天屠龙记", 95.21); insert into movies values (1003, "神雕侠侣", 96.32); insert into movies values (1004, "孔雀翎", 79.29); insert into movies values (1005, "流星蝴蝶剑", 86.31); insert into movies values (1006, "萍踪侠影", 85.75); insert into movies values (1007, "侠客行", 80.66); insert into movies values (1008, "小李飞刀",97.66); insert into movies values (1009, "绝代双骄",81.0); insert into movies values (1010, "碧血剑",82.90); insert into movies values (1011, "射雕英雄传",92.88); insert into movies values (1012, "武林外传",88.95); select * from movies;
|
id
|
name
|
score
|
|
1001
|
笑傲镜湖
|
91.69
|
|
1002
|
倚天屠龙记
|
95.21
|
|
1011
|
射雕英雄传
|
92.88
|
|
1012
|
武林外传
|
88.95
|
|
1003
|
神雕侠侣
|
96.32
|
|
1004
|
孔雀翎
|
79.29
|
|
1005
|
流星蝴蝶剑
|
86.31
|
|
1006
|
萍踪侠影
|
85.75
|
|
1007
|
侠客行
|
80.66
|
|
1008
|
小李飞刀
|
97.66
|
|
1009
|
绝代双骄
|
81
|
|
1010
|
碧血剑
|
82.9
|
需求分析
窗口函数 lag over和 lead over将分别返回当前行的上一行和下一行记录。“上一行”或“下一行”取决于 over子句里的 order by 子句。我们首先按照 score 排序数据集，并提取出了当前行的上一行和下一行。
select *, lead(score, 1) over (order by score) forward_score, lag(score, 1) over (order by score) remind_score from movies;
|
id
|
name
|
score
|
forward_score
|
rewind_score
|
|
1004
|
孔雀翎
|
79.29
|
80.66
|
|
|
1007
|
侠客行
|
80.66
|
81
|
79.29
|
|
1009
|
绝代双骄
|
81
|
82.9
|
80.66
|
|
1010
|
碧血剑
|
82.9
|
85.75
|
81
|
|
1006
|
萍踪侠影
|
85.75
|
86.31
|
82.9
|
|
1005
|
流星蝴蝶剑
|
86.31
|
88.95
|
85.75
|
|
1012
|
武林外传
|
88.95
|
91.69
|
86.31
|
|
1001
|
笑傲镜湖
|
91.69
|
92.88
|
88.95
|
|
1011
|
射雕英雄传
|
92.88
|
95.21
|
91.69
|
|
1002
|
倚天屠龙记
|
95.21
|
96.32
|
92.88
|
|
1003
|
神雕侠侣
|
96.32
|
97.66
|
95.21
|
|
1008
|
小李飞刀
|
97.66
|
96.32
|
注意，孔雀翎 的 remind_score 是 null，而 小李飞刀 的 forword_score 也是 null；这是因为两部小说的 score 分别是最低值和最高值。“问题”部分提到，forward_score或 rewind_score若出现 null值，则应该“折回”。这就意味着，对于最大的 score，forworf_score值应为 表中最小的 score；而对于最小的 score，rewind 值应为最大的 score。没有指定分区（即 over子句后面跟一对空括号）的窗口函数 min over和 max over 将分别返回最大和最小的 score。结果集如下所示。
select *, nvl(lead(score,1) over (order by score) ,min(score) over ()) forward_score, nvl(lag(score,1) over (order by score),max(score) over ()) remind_score from movies;
|
id
|
name
|
score
|
forward_score
|
rewind_score
|
|
1004
|
孔雀翎
|
79.29
|
80.66
|
97.66
|
|
1007
|
侠客行
|
80.66
|
81
|
79.29
|
|
1009
|
绝代双骄
|
81
|
82.9
|
80.66
|
|
1010
|
碧血剑
|
82.9
|
85.75
|
81
|
|
1006
|
萍踪侠影
|
85.75
|
86.31
|
82.9
|
|
1005
|
流星蝴蝶剑
|
86.31
|
88.95
|
85.75
|
|
1012
|
武林外传
|
88.95
|
91.69
|
86.31
|
|
1001
|
笑傲镜湖
|
91.69
|
92.88
|
88.95
|
|
1011
|
射雕英雄传
|
92.88
|
95.21
|
91.69
|
|
1002
|
倚天屠龙记
|
95.21
|
96.32
|
92.88
|
|
1003
|
神雕侠侣
|
96.32
|
97.66
|
95.21
|
|
1008
|
小李飞刀
|
97.66
|
79.29
|
96.32
|
