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

 找回密码
 注册
查看: 961|回复: 4

[每日一题] PL/SQL Challenge 每日一题:2018-6-14 PIVOT操作符

[复制链接]
论坛徽章:
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-19 03:38 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

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

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

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

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

作者: Kim Berg Hansen

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

我有一张表,保存着我们即将举行的会议:

create table qz_sessions (
   title          varchar2(20)
, room           varchar2(20)
, session_from   integer
, session_to     integer
)
/

insert into qz_sessions values ('SmartDB in the Cloud', 'Rome', 1, 1)
/
insert into qz_sessions values ('Cloudy Development'  , 'Rome', 2, 2)
/
insert into qz_sessions values ('SQL Dojo Initiation' , 'Rome', 4, 4)
/
insert into qz_sessions values ('Explain Plan Basics' , 'Riga', 2, 3)
/
insert into qz_sessions values ('Index, Index, Index' , 'Riga', 4, 4)
/
insert into qz_sessions values ('MLE Usage Workshop'  , 'Reno', 2, 4)
/
commit
/

我们有三个房间和四个时间段举行会议。有些会议只用一个时间段,所以SESSION_TO和SESSION_FROM一样。其他会话用了多个时间段,所以SESSION_TO大于SESSION_FROM,指示着会议对时段编号的“扩展”。

我想要一张会议安排表,每个时间段一行,每个房间(房间名 Rome, Riga 和 Reno)一列。

哪些选项包含的查询可以执行不出错并且返回这个输出:

   Session Rome                 Riga                 Reno
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud
         2 Cloudy Development   Explain Plan Basics  MLE Usage Workshop
         3                      {continued}          {continued}
         4 SQL Dojo Initiation  Index, Index, Index  {continued}

(A)
select session_from as "Session"
     , case session_from
          when session_to then "Rome"
                          else '{continued}'
       end as "Rome"
     , case session_from
          when session_to then "Riga"
                          else '{continued}'
       end as "Riga"
     , case session_from
          when session_to then "Reno"
                          else '{continued}'
       end as "Reno"
  from qz_sessions
pivot (
   max(title)
   for room in (
      'Rome' as "Rome"
    , 'Riga' as "Riga"
    , 'Reno' as "Reno"
   )
)
order by "Session"
/

(B)
select *
  from (
   select level as "Session" from dual connect by level <= 4
       ) h
  join qz_sessions s
         on h."Session" between s.session_from and s.session_to
pivot (
   max(case h."Session"
          when s.session_from then s.title
                              else '{continued}'
       end)
   for room in (
      'Rome' as "Rome"
    , 'Riga' as "Riga"
    , 'Reno' as "Reno"
   )
)
order by "Session"
/

(C)
select *
  from (
   select h."Session"
        , s.room
        , case h."Session"
             when s.session_from then s.title
                                 else '{continued}'
          end as title
     from (
      select level as "Session" from dual connect by level <= 4
          ) h
     join qz_sessions s
            on h."Session" between s.session_from and s.session_to
  )
pivot (
   max(title)
   for room in (
      'Rome' as "Rome"
    , 'Riga' as "Riga"
    , 'Reno' as "Reno"
   )
)
order by "Session"
/

(D)
select *
  from (
   select s.session_from + h.lvl - 1 as "Session"
        , s.room
        , case h.lvl
             when 1 then s.title
                    else '{continued}'
          end as title
     from qz_sessions s
    cross join lateral (
      select level as lvl from dual
      connect by level <= s.session_to - s.session_from + 1
          ) h
  )
pivot (
   max(title)
   for room in (
      'Rome' as "Rome"
    , 'Riga' as "Riga"
    , 'Reno' as "Reno"
   )
)
order by "Session"
/

(E)
select h."Session"
     , case rome.session_from
          when h."Session" then rome.title
          when null        then null
                           else '{continued}'
       end as "Rome"
     , case riga.session_from
          when h."Session" then riga.title
          when null        then null
                           else '{continued}'
       end as "Riga"
     , case reno.session_from
          when h."Session" then reno.title
          when null        then null
                           else '{continued}'
       end as "Reno"
  from (
   select level as "Session" from dual connect by level <= 4
       ) h
  left outer join qz_sessions rome
         on h."Session" between rome.session_from and rome.session_to
        and rome.room = 'Rome'
  left outer join qz_sessions riga
         on h."Session" between riga.session_from and riga.session_to
        and riga.room = 'Riga'
  left outer join qz_sessions reno
         on h."Session" between reno.session_from and reno.session_to
        and reno.room = 'Reno'
order by "Session"
;

(F)
select h."Session"
     , decode(rome.session_from
        , h."Session", rome.title  
        , null       , null
                     , '{continued}'
       ) as "Rome"
     , decode(riga.session_from
        , h."Session", riga.title  
        , null       , null
                     , '{continued}'
       ) as "Riga"
     , decode(reno.session_from
        , h."Session", reno.title  
        , null       , null
                     , '{continued}'
       ) as "Reno"
  from (
   select level as "Session" from dual connect by level <= 4
       ) h
  left outer join qz_sessions rome
         on h."Session" between rome.session_from and rome.session_to
        and rome.room = 'Rome'
  left outer join qz_sessions riga
         on h."Session" between riga.session_from and riga.session_to
        and riga.room = 'Riga'
  left outer join qz_sessions reno
         on h."Session" between reno.session_from and reno.session_to
        and reno.room = 'Reno'
order by "Session"
;

论坛徽章:
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
2#
发表于 2018-6-19 13:24 | 只看该作者
答案:CDF
本题三个小要点,第一跨时区数据要拉平,第二用PIVOT转换,第三,重复的用‘{continued}’替换
A: 数据没拉平,只用了session_from考虑问题
B: pivot 的前面要用子查询
C: 正确
D: 利用lateral关键字与内联视图JOIN
E: 1,2步对了,第3不,NULL值也被‘{continued}’替换了
F: 纠正了E

SQL>  select n as "Session",
  2                  case when lag(rome) over(order by n) = rome
  3                       then '{continued}'
  4                       else rome
  5                  end rome,
  6                  case when lag(rigo) over(order by n) = rigo
  7                       then '{continued}'
  8                       else rigo
  9                  end rigo,
10                  case when lag(reno) over(order by n) = reno
11                       then '{continued}'
12                       else reno
13                  end reno
14             from (
15                  select a.n,
16                         t.title,
17                         t.room
18                    from qz_sessions t,
19                        (select level n from dual connect by level <=4) a
20                   where t.session_from <= a.n
21                     and a.n<=t.session_to
22                  ) b
23            pivot (
24                    max(title) for room in ('Rome' as rome,
25                                            'Riga' as rigo,
26                                            'Reno' as reno)
27                  )
28            order by n;
   Session ROME                 RIGO                 RENO
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud                     
         2 Cloudy Development   Explain Plan Basics  MLE Usage Workshop
         3                      {continued}          {continued}
         4 SQL Dojo Initiation  Index, Index, Index  {continued}

使用道具 举报

回复
论坛徽章:
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
3#
 楼主| 发表于 2018-6-20 03:55 | 只看该作者
答案CDF, 2楼得奖。

A: 既然我们直接对表进行行转列(缺省使用了所有的列)并且仅仅在PIVOT子句中使用了TITLE 和 ROOM,它就会在SESSION_FROM, SESSION_TO 上执行隐式的聚合操作。于是对于这两个列的五种在表中出现的组合,每个组合得到一行,给了我们这个错误输出,session 2出现太多次,而session 3根本就没有:

   Session Rome                 Riga                 Reno
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud
         2 Cloudy Development
         2 {continued}          {continued}          {continued}
         2 {continued}          {continued}          {continued}
         4 SQL Dojo Initiation  Index, Index, Index

B: 此处我们用了DUAL来创建所有四个session编号,并且连接到标,看看对于每个session 编号有在表中有哪些会议。这是没问题的,但是我们随后在PIVOT中用了列名"Session", SESSION_FROM, TITLE 和 ROOM, 导致它隐式地按照SESSION_TO聚合,所以输出中已经没有"Session"列,于是ORDER BY报错:
ORA-00904: "Session": invalid identifier.

如果我们将 ORDER BY "Session" 改成 ORDER BY 1,我们就可以看到它是如何错误地按照 SESSION_TO聚合:

SESSION_TO Rome                 Riga                 Reno
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud
         2 Cloudy Development
         3                      {continued}
         4 SQL Dojo Initiation  Index, Index, Index  {continued}

C: 如果我们将前一选项中的表连接逻辑和TITLE的CASE结构拿出来放到内联视图中,结果就是这个内联视图具备了列名"Session", ROOM 和 TITLE, 我们可以在PIVOT子句中用ROOM 和 TITLE,导致在"Session"的隐式聚合操作,给了我们所需要的输出。

D: 关于行转列,这个选项和前一选项的效果完全相同。我们只是将内联视图的内容修改,不是简单连接到一个session编号1到4的集合,而是用了LATERAL来将每个会议连接到一个大小确切的session集合。这是一个更加强壮的方案,因为我们不用将一天中的会议数量写成硬编码。

E: 如果不用PIVOT,我们也可以生成四行session编号并且执行三个左外连接,每个房间执行一次。我们得到了正确的行数,但是对于"Rome", "Riga" 和 "Reno" 列的CASE结构出了问题,这种简单CASE是不可以执行WHEN NULL的,这个条件永远不为真。于是我们得到这个错误输出,那些应该为空的时间点错误地被显示为 "{continued}":

   Session Rome                 Riga                 Reno
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud {continued}          {continued}
         2 Cloudy Development   Explain Plan Basics  MLE Usage Workshop
         3 {continued}          {continued}          {continued}
         4 SQL Dojo Initiation  Index, Index, Index  {continued}

F: 我们可以把简单CASE改成搜索型CASE来修复前一选项的错误,但是我们也可以用DECODE达到同样的目的,这是极少数的将NULL视为和NULL相等的函数之一。这样的话我们就能得到所需的结果。

使用道具 举报

回复
求职 : 数据库开发
论坛徽章:
23
秀才
日期:2017-08-11 15:38:46秀才
日期:2018-01-02 15:17:54秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22秀才
日期:2018-01-02 15:18:22技术图书徽章
日期:2018-01-02 15:18:30秀才
日期:2018-03-01 10:05:18秀才
日期:2018-05-22 15:21:20
4#
发表于 2018-6-20 10:52 | 只看该作者
SQL> SELECT *
  2    FROM (SELECT CASE
  3                     WHEN title = lag(title) over(PARTITION BY room ORDER BY
  4                               session_from + LEVEL - 1) THEN
  5                      ' {continued}'
  6                     ELSE
  7                      title
  8                 END title,
  9                 room,
10                 session_from + LEVEL - 1 "SESSION"
11            FROM qz_sessions
12          CONNECT BY PRIOR ROWID = ROWID
13                 AND PRIOR dbms_random.value() IS NOT NULL
14                 AND LEVEL <= session_to - session_from + 1)
15  pivot(MAX(title)
16     FOR room IN('Rome' AS "Rome", 'Riga' AS "Riga", 'Reno' AS "Reno"))
17   ORDER BY 1;
   SESSION Rome                 Riga                 Reno
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud                     
         2 Cloudy Development   Explain Plan Basics  MLE Usage Workshop
         3                       {continued}          {continued}
         4 SQL Dojo Initiation  Index, Index, Index   {continued}

使用道具 举报

回复
论坛徽章:
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
5#
发表于 2018-6-20 12:10 | 只看该作者
楼上不错,先处理重复,后PIVOT也可以

SQL> select n as "Session",
  2         rome,
  3         rigo,
  4         reno
  5     from (
  6          select a.n,
  7                 case when lag(title) over(partition by room order by n) = title
  8                      then '{continued}'
  9                      else title
10                 end title,
11                 t.room
12            from qz_sessions t,
13                 (select level n from dual connect by level <=4) a
14           where a.n between t.session_from and t.session_to
15          ) b
16    pivot (
17            max(b.title) for room in ('Rome' as rome,
18                                      'Riga' as rigo,
19                                      'Reno' as reno)
20          )
21    order by n;
   Session ROME                 RIGO                 RENO
---------- -------------------- -------------------- --------------------
         1 SmartDB in the Cloud                     
         2 Cloudy Development   Explain Plan Basics  MLE Usage Workshop
         3                      {continued}          {continued}
         4 SQL Dojo Initiation  Index, Index, Index  {continued}

使用道具 举报

回复

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

本版积分规则

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