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

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

[每日一题] PL/SQL Challenge 每日一题:2017-7-20 12c新功能:Lateral 内联视图

[复制链接]
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-7-25 05:05 | 显示全部楼层 |阅读模式

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

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

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

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

作者:        Kim Berg Hansen

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

注:本题的执行环境为12c或更高版本

我有这些博客和博客文章的表:

create table qz_blogs (
   blog_id  integer  primary key
, name     varchar2(20)
)
/

create table qz_posts (
   blog_id  integer  references qz_blogs
, posted   date
, title    varchar2(20)
)
/

insert into qz_blogs values (10, 'Cajun Cooking')
/
insert into qz_blogs values (20, 'Homemade Knitwear')
/
insert into qz_blogs values (30, 'DIY Furniture')
/

insert into qz_posts values (10, date '2017-07-01', 'Mixing Spices')
/
insert into qz_posts values (10, date '2017-07-03', 'Jambalaya Hot')
/
insert into qz_posts values (30, date '2017-07-05', 'Oval OakTable')
/

commit
/

我想要一张博客的列表,上面显示每个博客的最近一篇文章。这个清单必须以最后发表的时间的降序排列,所以有最新文章的博客排在列表的最前面。

哪些选项包含了一个查询能够产生这样一个清单:

   BLOG_ID NAME                 LAST_POST  LAST_TITLE
---------- -------------------- ---------- --------------------
        30 DIY Furniture        2017-07-05 Oval OakTable
        10 Cajun Cooking        2017-07-03 Jambalaya Hot
        20 Homemade Knitwear
        
(A)
select b.blog_id
     , b.name
     , (select max(p.posted)
          from qz_posts p
         where p.blog_id = b.blog_id
       ) as last_post
     , (select max(p.title) keep (dense_rank last order by p.posted)
          from qz_posts p
         where p.blog_id = b.blog_id
       ) as last_title
  from qz_blogs b
order by last_post desc nulls last
/

(B)
select blog_id, name, posted as last_post, title as last_title
  from (
   select b.blog_id
        , b.name
        , p.posted
        , p.title
        , row_number() over (
             partition by b.blog_id order by p.posted desc
          ) as rn
     from qz_blogs b
     left outer join qz_posts p
         on p.blog_id = b.blog_id
  )
where rn = 1
order by last_post desc nulls last
/

(C)
select b.blog_id
     , b.name
     , last_p.posted as last_post
     , last_p.title as last_title
  from qz_blogs b
  left outer join lateral (
      select p.posted
           , p.title
        from qz_posts p
       where p.blog_id = b.blog_id
       order by p.posted desc
       fetch first 1 row only
  ) last_p
      on 1 = 1
order by last_post desc nulls last
/


(D)
select b.blog_id
     , b.name
     , last_p.posted as last_post
     , last_p.title as last_title
  from qz_blogs b
  left outer join (
   select blog_id, posted, title
     from (
      select p.blog_id
           , p.posted
           , p.title
           , row_number() over (
                partition by p.blog_id order by p.posted desc
             ) as rn
        from qz_posts p
     )
    where rn = 1
  ) last_p
      on last_p.blog_id = b.blog_id
order by last_post desc nulls last
/

(E)
select b.blog_id
     , b.name
     , last_p.last_post
     , last_p.last_title
  from qz_blogs b
  left outer join (
      select p.blog_id
           , max(p.posted) as last_post
           , max(p.title) keep (
                dense_rank last order by p.posted
             ) as last_title
        from qz_posts p
       group by p.blog_id
  ) last_p
      on last_p.blog_id = b.blog_id
order by last_post desc nulls last
/

论坛徽章:
13
SQL大赛参与纪念
日期:2011-04-13 12:08:17秀才
日期:2017-08-11 15:37:56秀才
日期:2017-08-11 15:37:56秀才
日期:2017-08-11 15:37:56秀才
日期:2017-08-11 15:37:59秀才
日期:2017-08-11 15:37:59秀才
日期:2017-08-11 15:37:59秀才
日期:2017-08-11 15:37:592013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:09:19
发表于 2017-7-25 08:58 | 显示全部楼层
ABCDE
A 用keep取得了同一个blog_id中,最大posted的最大title。
B 用row_number()对同blog_id中的posted进行倒序排号(m),并在where中用m=1取得了第一条,即对同blog_id中的posted的最大值
C lateral使得右侧括号里可以用主表b的列。所以where中可以写b.blog_id,然后倒序取第一行。。。
D row_number(),和B差不多,检索多了一层。
E A的改良版
nulls last把排序时null放在最后。

使用道具 举报

回复
论坛徽章:
13
秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2017-7-25 10:22 | 显示全部楼层
答案 ABCDE
A 通过max 和keep函数来处理
B 通过row_number 排序,取值第一条
C 12C新功能lateral内嵌视图
D 同B
E 同A

这种需求,业务上常见,我一般都是row_number处理。

后面测试下不同写法的性能问题。

使用道具 举报

回复
论坛徽章:
459
探花
日期:2015-08-18 09:50:16秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-7-26 04:09 | 显示全部楼层
答案ABCDE, 2楼得奖。

A: 用两个子查询来为每个博客获取最后一篇文章的发表日期和标题,这将能很好地工作,但代价是两次访问了qz_posts表。
B: 执行到qz_posts的外连接给了我们所需的数据,但是对于那些有多篇文章的博客就会产生太多的数据行。我们可以使用ROW_NUMBER分析函数来过滤掉不需要的行,只保留最后一篇博客文章。
C: 在12.1,你可以执行一个LATERAL连接,在查询qz_posts表的内联视图的WHERE子句中使用来自qz_blogs表的列,使得FETCH FIRST被应用于每个博客。在这个例子中的ON子句在功能上并不是必需的,但要求有这个ON子句,所以我们可以用一个永远为真的子句。
D: 以前我们没有LATERAL 和 FETCH FIRST子句,我们可以外连接到一个内联视图,它仅仅有每个博客所必需的“最后一篇”的那些行。我们可以用类似B选项的ROW_NUMBER调用来达到此目的。
E: 我们也可以用类似A选项的GROUP BY 和 KEEP 来产生内联视图,但是这次不用两次访问表。

使用道具 举报

回复

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

本版积分规则

SACC2017购票7.8折优惠进行时

2017中国系统架构师大会(SACC2017)将于10月19-21日在北京新云南皇冠假日酒店震撼来袭。今年,大会以“云智未来”为主题,云集国内外顶级专家,围绕云计算、人工智能、大数据、移动互联网、产业应用等热点领域展开技术探讨与交流。本届大会共设置2大主会场,18个技术专场;邀请来自互联网、金融、制造业、电商等多个领域,100余位技术专家及行业领袖来分享他们的经验;并将吸引4000+人次的系统运维、架构师及IT决策人士参会,为他们提供最具价值的交流平台。
----------------------------------------
优惠时间:2017年8月30日前

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