ITPUB论坛-中国最专业的IT技术社区

 
 注册
热搜:
查看: 678|回复: 5

[每日一题] PL/SQL Challenge 每日一题:2017-11-30 NTH_VALUE函数

[复制链接]
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
跳转到指定楼层
1#
发表于 2017-12-5 05:00 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
/forum.php?m ... eid&typeid=1808

原始出处:


作者: Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我们的绝密部门有这张表,保存着一系列的代码和对应的值:

create table qz_values (
   seq   integer primary key
, code  varchar2(10)
, value integer
)
/

insert into qz_values values (1, 'SPADE', 10)
/
insert into qz_values values (2, 'HEART', 13)
/
insert into qz_values values (3, 'SPADE', 16)
/
insert into qz_values values (4, 'SPADE', 19)
/
insert into qz_values values (5, 'HEART', 20)
/
insert into qz_values values (6, 'HEART', 22)
/
insert into qz_values values (7, 'SPADE', 24)
/
insert into qz_values values (8, 'HEART', 35)
/
insert into qz_values values (9, 'SPADE', 42)
/
commit
/

由于某些深不可测的原因,他们要求按照SEQ的顺序列出一个数据列表,其中包括一列以显示他们所谓的“倒数第二个黑桃”。 它被定义为最后一个黑桃(SPADE)所在行(不包括当前行)之前的SPADE行,所以从当前行开始,我们应该“倒退”,直到遇到第二个 CODE = SPADE的行,并从该行获得VALUE。

我写了这个未完成的查询来给他们所需的数据列表:

select seq, code, value
     , ##REPLACE##
          as second_last_spade
  from qz_values
order by seq
/

哪些选项包含了一个列别名为SECOND_LAST_SPADE的表达式,可用来取代##REPLACE## 使得查询返回这个所需的输出:

       SEQ CODE            VALUE SECOND_LAST_SPADE
---------- ---------- ---------- -----------------
         1 SPADE              10
         2 HEART              13
         3 SPADE              16
         4 SPADE              19                10
         5 HEART              20                16
         6 HEART              22                16
         7 SPADE              24                16
         8 HEART              35                19
         9 SPADE              42                19

(A)
       nth_value(
          case code when 'SPADE' then value end
        , 2
       ) from LAST ignore nulls over (
          order by seq
          rows between unbounded preceding and current row
       )

(B)
       nth_value(
          case code when 'SPADE' then value end
        , 2
       ) from LAST ignore nulls over (
          order by seq
          rows between unbounded preceding and 1 preceding
       )

(C)
      nth_value(
          case code when 'SPADE' then value end
        , 2
       ) from FIRST ignore nulls over (
          order by seq DESC
          rows between unbounded preceding and 1 preceding
       )

(D)
       nth_value(
          case code when 'SPADE' then value end
        , 2
       ) from FIRST ignore nulls over (
          order by seq DESC
          rows between 1 following and unbounded following
       )

(E)
       nth_value(
          case code when 'SPADE' then value end
        , 2
       ) from FIRST ignore nulls over (
          order by seq DESC
          rows between current row and unbounded following
       )

(F)
       lag(
          case code when 'SPADE' then value end
        , 2
       ) ignore nulls over (
          order by seq
       )

(G)
       (
          select max(spade2.value) keep (
                    dense_rank last order by spade2.seq
                 )
            from qz_values spade1
            join qz_values spade2
                  on spade2.code = spade1.code
                 and spade2.seq  < spade1.seq
           where spade1.code = 'SPADE'
             and spade1.seq  < qz_values.seq
       )
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
2#
 楼主| 发表于 2017-12-6 05:57 | 只看该作者
顶下。

使用道具 举报

回复
认证徽章
论坛徽章:
5
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29鲜花蛋
日期:2011-12-20 16:06:45娜美
日期:2017-12-15 13:56:33技术图书徽章
日期:2017-11-17 09:13:02妮可·罗宾
日期:2017-11-17 10:43:23
3#
发表于 2017-12-6 10:51 | 只看该作者
(A) 錯,包含了當前行 SPADE
(B) 正常,1 preceding 沒有包含當前行 SPADE
(C) 錯, 成了SEQ逆序的開始取從大到小的第二個SPADE
(D) 正確
(E) 錯,包含了當前行 SPADE
(F) 正確

使用道具 举报

回复
论坛徽章:
18
秀才
日期:2016-04-29 15:11:10秀才
日期:2017-04-05 13:27:59秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2017-01-20 11:06:21秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31
4#
发表于 2017-12-6 13:43 | 只看该作者
我选BDEF
A、包含当前行,错误。
BD、正确,排序从后往前与倒序从前往后都可以。
C、查询窗口倒序从第一行开始,不正确
E、lag函数实现,正确。
F、其他方式实现,正确。
E、

使用道具 举报

回复
论坛徽章:
480
榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09举人
日期:2015-11-23 10:04:09
5#
 楼主| 发表于 2017-12-7 04:38 | 只看该作者
答案BDFG, 本期无人得奖。

A: 我们正确地使用了2作为偏移量以及FROM LAST (因为我们是升序排序的),但是因为窗口截至CURRENT ROW, 当我们的当前行是SPADE, 这一行本身会被计算为偏移量1, 所以在那些SPADE的数据上,结果错误地给了我们“上一个黑桃”而不是“倒数第二个黑桃”。在HEART数据上并无区别,所以在那些行上面结果是正确的。

总而言之我们得到这个错误输出:

       SEQ CODE            VALUE SECOND_LAST_SPADE
---------- ---------- ---------- -----------------
         1 SPADE              10
         2 HEART              13
         3 SPADE              16                10
         4 SPADE              19                16
         5 HEART              20                16
         6 HEART              22                16
         7 SPADE              24                19
         8 HEART              35                19
         9 SPADE              42                24
B: 将窗口的终点修改成1 PRECEDING 就解决了前一选项的问题,给了我们正确的输出。
C: 当我们以降序排序,使用了PRECEDING的窗口实际上变成了SEQ值更高的窗口,于是FROM FIRST是从最高的SEQ值算起。

此处我们得到这个错误输出:

       SEQ CODE            VALUE SECOND_LAST_SPADE
---------- ---------- ---------- -----------------
         1 SPADE              10                24
         2 HEART              13                24
         3 SPADE              16                24
         4 SPADE              19                24
         5 HEART              20                24
         6 HEART              22                24
         7 SPADE              24
         8 HEART              35
         9 SPADE              42

D:
如果使用降序排序,我们就可以用FOLLOWING窗口以及FROM FIRST来得到和B一样的正确结果。这个代码理解起来可能比较不清晰,但它实际上和B定义了同样的窗口。

这里有个小问题,分析函数使用了和查询不一样的ORDER BY。因此这个选项执行了两个排序操作,而B选项只需要一个排序。

但是你弄懂了可以将排序和窗口反过来,从LAST变成FIRST来得到同样结果,这是一件好事。假设查询本身必须以SEQ DESC排序,你就可以将分析函数写成同样的ORDER BY, 从而获益于单次排序。

E:
正如D是B的“反序”版本得到了同样的结果,这个选项是A的“反序”版本,得到的是同样错误的结果。在窗口中包含CURRENT ROW 对于SPADE行会得到错误结果。这和A的输出是完全一样的。

F:(推荐)
除了 NTH_VALUE, 11.2版本还增强了LAG函数,支持IGNORE NULLS。在这个特例中,LAG是一种类似的方法可以得到相同的结果。

如果你需要用ORDER BY SEQ DESC, 你可以简单地将LAG 转换成 LEAD.

G:(不推荐)
不用分析函数也是可以的,但是代价是多次访问同样的数据。

使用道具 举报

回复
论坛徽章:
18
秀才
日期:2016-04-29 15:11:10秀才
日期:2017-04-05 13:27:59秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:23:10秀才
日期:2017-04-05 13:22:59秀才
日期:2017-03-02 10:35:32秀才
日期:2017-01-20 11:06:21秀才
日期:2017-01-20 11:00:36秀才
日期:2017-01-20 11:04:31
6#
发表于 2017-12-7 09:09 | 只看该作者
答案太多了,顺序都看错了。
来自苹果客户端来自客户端

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 |
  | | |
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表