查看: 264|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2020-10-6 MODEL查询

[复制链接]
论坛徽章:
527
奥运会纪念徽章:垒球
日期: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:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2020-10-15 23:18 | 显示全部楼层 |阅读模式

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

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

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

原始出处:
https://devgym.oracle.com/

作者:Kim Berg Hansen

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

当我扔两个个六面的骰子,总共有 6 x 6 = 36 种可能的结果(一个骰子面值1至6,另外一个也是1至6, 总共36种可能的组合)。如果我把两个骰子的值加在一起,结果是在2至12之间。

这些结果中有一些仅仅在特定情况下出现,比如12就只能是6和6的结果。另外一些可能是在多种不同情况下出现,比如7可以是1号骰子2, 2号骰子5, 但也可能是1号骰子4, 2号骰子3,还有几种其他组合。

我想要11种可能的结果的列表,以及对于每种结果有多少种组合可以给出这个结果。

哪些选项可以执行不出错并且产生这个所需的输出:

    RESULT     THROWS
---------- ----------
         2          1
         3          2
         4          3
         5          4
         6          5
         7          6
         8          5
         9          4
        10          3
        11          2
        12          1
        

(A)
with dice as (
   select level as value
   from dual
   connect by level <= 6
)
select
   d1.value + d2.value as result
, count(*) as throws
from dice d1
cross join dice d2
group by d1.value + d2.value
order by result;

(B)
select
   val1 + val2 as result
, count(*) as throws
from dual
model
   dimension by (1 as val1, 1 as val2)
   measures (1 as dummy)
   rules (
      dummy[
         for val1 from 1 to 6 increment 1
       , for val2 from 1 to 6 increment 1
      ] = 1
   )
group by val1 + val2
order by result;

(C)
select
   result
, count(*) as throws
from (
   select val1 + val2 as result
   from dual
   model
      dimension by (1 as val1, 1 as val2)
      measures (1 as dummy)
      rules (
         dummy[
            for val1 from 1 to 6 increment 1
          , for val2 from 1 to 6 increment 1
         ] = 1
      )
)
group by result
order by result;

(D)
select
   result
, count(*) as throws
from (
   select val1 + val2 as result
   from dual
   model
      dimension by (null as val1, null as val2)
      measures (null as dummy)
      rules (
         dummy[
            for val1 from 1 to 6 increment 1
          , for val2 from 1 to 6 increment 1
         ] = 1
      )
)
group by result
order by result;

(E)
select
   result
, count(*) as throws
from (
   select val1 + val2 as result
   from dual
   model
      dimension by (cast(null as integer) as val1, cast(null as integer) as val2)
      measures (cast(null as integer) as dummy)
      rules (
         dummy[
            for val1 from 1 to 6 increment 1
          , for val2 from 1 to 6 increment 1
         ] = 1
      )
)
group by result
order by result;

(F)
select
   result
, count(*) as throws
from (
   select val1 + val2 as result
   from dual
   model
      dimension by (cast(null as integer) as val1, cast(null as integer) as val2)
      measures (cast(null as integer) as dummy)
      rules (
         dummy[
            for val1 from 1 to 6 increment 1
          , for val2 from 1 to 6 increment 1
         ] = 1
      )
)
WHERE result is not null
group by result
order by result;

(G)
select
   result
, count(*) as throws
from (
   select val1 + val2 as result
   from dual
   WHERE 1=0
   model
      dimension by (cast(null as integer) as val1, cast(null as integer) as val2)
      measures (cast(null as integer) as dummy)
      rules (
         dummy[
            for val1 from 1 to 6 increment 1
          , for val2 from 1 to 6 increment 1
         ] = 1
      )
)
group by result
order by result;
论坛徽章:
0
发表于 2020-10-16 16:26 | 显示全部楼层
A C F G

使用道具 举报

回复
论坛徽章:
0
发表于 2020-10-16 17:21 | 显示全部楼层
A:采用笛卡儿积,对获取的结果进行分组,统计,正确
B:语法问题,报ORA-00933错误
C:采用model子句,生成1到6两列数据,对两列求和,最后通过分组排序,得到正确结果
D:ORA-01723: 不允许长度为 0 的列,错误
E:将null强转为integer,结果集中包含null,所以错误
F:在E的基础上对结果集,去掉null,正确
G:where 1=0 在制表之前将初始设定的值去掉,在插入rules设定规则的数据,结果正确

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期: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:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2020-10-17 00:57 | 显示全部楼层
答案ACFG, 3楼得奖。

A:
这是经典的SQL方法,创建一个生成6行的行源,将其与自身交叉连接,生成36个组合,并根据加法结果进行分组。

B:
使用MODEL子句生成6x6网格是可以的,但是在SELECT语句的评估顺序中,GROUP BY发生在MODEL子句之前。这里我们试图对MODEL的结果进行GROUP BY,这是不允许的,我们得到了错误:
ORA-00933: SQL command not properly ended.

C:
对前一选项中的错误的解决方法是将MODEL查询放在内联视图中,然后在外层查询中进行GROUP BY。

D:
前一选项将dimension 和 measures 初始化为数字1,这里我们尝试初始化为NULL值。但是初始化不仅决定了值,还决定了数据类型,NULL在没有进一步说明的情况下,默认认为是一个零长度的字符串。对于dimension,这不是一个有效的数据类型,所以我们得到错误:
ORA-01723: zero-length columns are not allowed.

E:
我们可以修正前一选项,方法将NULL值显式CAST为INTEGER数据类型,那么我们就不会再得到错误。但是这个选项又有一个问题:DUAL包含一行,这就在MODEL中创建了一个单元格,这个单元格的DIMENSION和MEASURE值初始化为NULL值。然后RULES子句在6x6网格中生成36个单元格,所以内联视图总共返回37行,我们想要的36行加上VAL1和VAL2都为NULL的一行,所以GROUP BY给了我们这个错误的输出,包括最后一个NULL的结果:

    RESULT THROWS
---------- ----------
         2 1
         3 2
         4 3
         5 4
         6 5
         7 6
         8 5
         9 4
        10 3
        11 2
        12 1
                    1
                    
F:
我们可以通过在外层查询的WHERE子句中过滤掉NULL值来消除前一选项中错误的NULL结果。

G:
但我们也可以通过WHERE 1=0的过滤来修正E选项,确保我们根本没有从DUAL中选择任何行。这意味着当MODEL子句被评估时,根本不会有任何初始单元格--MODEL子句返回的唯一单元格将是由RULES子句创建的36个。

使用道具 举报

回复

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

本版积分规则 发表回复

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