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

 找回密码
 注册
查看: 782|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2018-6-21 TOP-N 查询

[复制链接]
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
跳转到指定楼层
1#
发表于 2018-6-26 04:25 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

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

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

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

原始出处:
http://www.plsqlchallenge.com/

作者:Kim Berg Hansen  

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

在成功举行了会议之后,我们在这张表保存了对每场会议的评分:

create table qz_sessions (
   id       integer primary key
, track    varchar2(10)
, title    varchar2(30)
, score    number
)
/

insert into qz_sessions values (1, 'Dev', 'Pattern Matching'  , 4.8)
/
insert into qz_sessions values (2, 'Dev', 'Analytic Functions', 4.6)
/
insert into qz_sessions values (3, 'Dev', 'WITH PL/SQL Clause', 4.8)
/
insert into qz_sessions values (4, 'DBA', 'Clustering Factor' , 4.7)
/
insert into qz_sessions values (5, 'DBA', 'ASSM in the Wild'  , 4.5)
/
insert into qz_sessions values (6, 'DBA', 'Hot Segments'      , 4.6)
/
commit
/

对于每种记录(track)我们想要为该种记录下得分最高的会议颁发最佳演讲者奖项。如果有多于一人获得同样的最高分,则获得该分数的全部得奖。

哪些选项包含的查询会从每种记录中找出得分最高的会议,得到这个所需的输出:

        ID TRACK      TITLE                               SCORE
---------- ---------- ------------------------------ ----------
         4 DBA        Clustering Factor                     4.7
         1 Dev        Pattern Matching                      4.8
         3 Dev        WITH PL/SQL Clause                    4.8

(A)
select id, track, title, score
  from (
   select id, track, title, score
        , RANK() over (
             partition by track order by score desc
          ) rnk
     from qz_sessions
       )
where rnk = 1
order by track, title
/

(B)
select id, track, title, score
  from (
   select id, track, title, score
        , DENSE_RANK() over (
             partition by track order by score desc
          ) rnk
     from qz_sessions
       )
where rnk = 1
order by track, title
/

(C)
select id, track, title, score
  from (
   select id, track, title, score
        , ROW_NUMBER() over (
             partition by track order by score desc
          ) rnk
     from qz_sessions
       )
where rnk = 1
order by track, title
/

(D)
select id, track, title, score
  from qz_sessions
where (track, score) IN (
   select track, max(score)
     from qz_sessions
    group by track
       )
order by track, title
/

(E)
select id, track, title, score
  from qz_sessions
where (track, score) = (
   select track, max(score)
     from qz_sessions
    group by track
       )
order by track, title
/

(F)
select id, track, title, score
  from qz_sessions
where score = (
   select max(score)
     from qz_sessions q2
    where q2.track = qz_sessions.track
       )
order by track, title
/
论坛徽章:
2
秀才
日期:2018-05-22 16:17:26秀才
日期:2018-05-22 16:17:40
2#
发表于 2018-6-26 09:09 | 只看该作者
答案:ABDF
A:先进行排名再获取排名第一的记录,正确
B:先进行排名再获取排名第一的记录,与A不同的是RANK()与DENSE_RANK()的不同,其实这不影响答案,它们的区别是重复会不会跳号
C:ROW_NUMBER()相当于计数,错误
D和E的区别是IN和等号,因子查询返回多行,所以D对,E错
F:先根据track获取最大得分,再求出获取排名第一的记录,正确

使用道具 举报

回复
论坛徽章:
293
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
3#
发表于 2018-6-26 09:13 | 只看该作者
答案: ABDF

A: 分析函数RANK 按TRACK分区,score 倒排 取RANK=1的
B: DENSE_RANK 排序值连续,功能与RANK一样
C: ROW_NUMBER 相同的分区和值,结果序号不一样
D: 先聚合找到每个TRACK的最大值,再IN查找
E: 应该用IN,因为子查询有多行
F: 与D相同,写法不一样

使用道具 举报

回复
论坛徽章:
496
紫蜘蛛
日期:2007-09-26 17:05:56奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:53
4#
 楼主| 发表于 2018-6-27 04:26 | 只看该作者
答案ABDF, 2楼得奖。

A: (推荐)
利用按照track分区的分析函数RANK,我们可以得到我们所需的数据,所以对于每种记录(track),按照分数降序的排名被指定。RANK的设计方式使得相同分数的数据行会得到相同的排名值,然后下一个排名值会“跳过”打成平手的记录数。(这和奥运会是一样的——如果有两枚金牌,那么就没有银牌,只有铜牌)

B: (推荐)
DENSE_RANK函数同样会为打成平手的行赋予相同的排名值,就和RANK一样。区别在于RANK会在平局出现时“跳过”随后的行(如上面所述),但是DENSE_RANK不会跳过。(在DENSE_RANK中,如果有两枚金牌,下一个仍然还是银牌)但是既然我们只查询排名第一的行,在这个特定用例中,不管用RANK还是DENSE_RANK都无关紧要,因为排名第一的都是一样结果。

C: 但是ROW_NUMBER函数不会为平局的数据赋予相同的排名值。ROW_NUMBER会赋予连续的唯一值,碰到平局的话,哪一行得到哪个值就是不确定的。所以我们得到了这个错误的输出(或者另外一种输出显示"WITH PL/SQL Clause" 而不是"Pattern Matching" ,取决于优化器选择了哪个访问计划):

        ID TRACK      TITLE                               SCORE
---------- ---------- ------------------------------ ----------
         4 DBA        Clustering Factor                     4.7
         1 Dev        Pattern Matching                      4.8

D:(不推荐)
这是在8.1.7之前,另外一种传统的方法来查找每种记录的最高分,然后再找出具有这些(track,highscore)的会议记录。问题在于,谁能必须多次访问表——利用前面选项的分析函数你只需访问一次表。
E: 相比起前一选项,此处我们执行了"=" 而不是"IN"操作,当我们在一个track中有多个最高分就出错了,因为 "=" 要求子查询返回的数据不多于一行。所以这个选项会报错:
ORA-01427: single-row subquery returns more than one row.
F:(不推荐)
D选项用了一个非相关的子查询,在一个GROUP BY中获得所有track和最高分的组合,然后用IN操作符来查询表。另一方法是使用"=", 假如我们把它作为相关子查询而不用GROUP BY,而是让自查询找到该种类的最高分并且返回一行,所以我们不会得到前一选项的错误。

使用道具 举报

回复

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

本版积分规则

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