需求

　　现需要返回每本小说的的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