查看: 503|回复: 5

[每日一题] PL/SQL Challenge 每日一题:2020-4-14 PRESENTV

[复制链接]
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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-5-21 21:33 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者:Kim Berg Hansen

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

我有一张表存储每件商品的年销售额:

create table qz_sales (
   item     varchar2(10) not null
, year     integer      not null
, qty      integer      not null
, primary key (item, year)
);

insert into qz_sales values ('Keyboard', 2018, 38);
insert into qz_sales values ('Keyboard', 2019, 42);
insert into qz_sales values ('Monitor' , 2018, 11);
insert into qz_sales values ('Headset' , 2019,  0);

commit;

我希望根据这些规则来查看每一件商品的销售量,包括2020年的估计销售量:

对于一个给定的商品,2020年的估计销售量为2019年的销售量加10。
如果该商品没有登记2019年的销售量(甚至不是0,而是非存在的行),那么2020年的估计销售量应该是5。

哪些选项中包含了一个实现这些规则的查询,可以执行不出错,并产生这个期望的输出:

ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2019          0
Headset          2020         10
Keyboard         2018         38
Keyboard         2019         42
Keyboard         2020         52
Monitor          2018         11
Monitor          2020          5


(A)
select item, year, qty
from qz_sales
model
   partition by (item)
   dimension by (year)
   measures (qty)
   rules (
      qty[2020] = NVL2(qty[2019], qty[2019] + 10, 5)
   )
order by item, year;

(B)
select item, year, qty
from qz_sales
model
   partition by (item)
   dimension by (year)
   measures (qty)
   IGNORE NAV
   rules (
      qty[2020] = NVL2(qty[2019], qty[2019] + 10, 5)
   )
order by item, year;

(C)
select item, year, qty
from qz_sales
model
   partition by (item)
   dimension by (year)
   measures (qty)
   rules (
      qty[2020] = PRESENTV(qty[2019], qty[2019] + 10, 5)
   )
order by item, year;

(D)
select item, year, qty
from qz_sales
model
   partition by (item)
   dimension by (year)
   measures (qty)
   IGNORE NAV
   rules (
      qty[2020] = PRESENTV(qty[2019], qty[2019] + 10, 5)
   )
order by item, year;

(E)
select item, year, qty
from qz_sales
model
   dimension by (item, year)
   measures (qty)
   rules (
      qty[any, 2020] = PRESENTV(
                          qty[cv(), 2019]
                        , qty[cv(), 2019] + 10
                        , 5
                       )
   )
order by item, year;

(F)
select item, year, qty
from qz_sales
model
   dimension by (item, year)
   measures (qty)
   rules UPSERT ALL (
      qty[any, 2020] = PRESENTV(
                          qty[cv(), 2019]
                        , qty[cv(), 2019] + 10
                        , 5
                       )
   )
order by item, year;

(G)
select item, year, qty
from qz_sales
UNION ALL
select item, 2020 as year, NVL2(qty, qty + 10, 5) as qty
from qz_sales
where year = 2019
order by item, year;

(H)
select item, year, qty
from qz_sales
UNION ALL
select i.item, 2020 as year, NVL2(s.qty, s.qty + 10, 5) as qty
from (
   select distinct item
   from qz_sales
) i
left outer join qz_sales s
   on s.item = i.item
   and s.year = 2019
order by item, year;

论坛徽章:
16
山治
日期:2017-01-11 16:13:26技术图书徽章
日期:2017-08-23 14:17:00技术图书徽章
日期:2017-08-23 14:17:00秀才
日期: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
发表于 2020-5-22 09:24 | 显示全部楼层
答案:ACDFH

A):通过model子句和nvl2函数实现,正确

B):同A相比增加了 IGNORE NAV,空值或缺失数字值时返回0
   Monitor 2019年的QTY被当做0处理,而非空值,与题目意思不符,错误

C):同A相比将nvl2替换成了presentv,正确
   如果cell引用的记录在model子句执行之前就存在,那么presentv(cell, expr1, expr2)返回表达式expr1。如果这条记录不存在,则返回表达式expr2
   
D):同C相比增加了IGNORE NAV,正确
   同为model子句的分支,我理解的presentv执行顺序在IGNORE NAV之前,输出结果正确

E):同A相比,调整了partition by、dimension by子句
   默认是返回更新的数据行,insert的不会显示,2020年预估的数据缺失,错误

F):同E相比,增加了UPSERT ALL,修复了E的问题,正确

G):通过union all来实现,当2019为null的时候,2020年预估的数据缺失,错误

H):通过left join,修复了G的问题,正确


看了这个题目,partition by、dimension by两个子句,我又有点迷糊了,抽时间再学习下

使用道具 举报

回复
论坛徽章:
534
生肖徽章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
发表于 2020-5-22 10:08 | 显示全部楼层

答案:ACDFH
A: model规则子句中使用 NVL2,qty[2019] 不为NULL,则qty[2019] + 10,否则返回5
B: IGNORE NAV 对迷失的单元格,数字型视为0,字符型视为空字符串,所以qty[2020] =10
C: PRESENTV函数只能使用在MODEL语句中,且使用在规则的右手边,测试单元格,存在返回qty[2019] + 10,不存在返回 5
D: MODEL语句有IGNORE NAV,规则内部有PRESENTV函数,应该是PRESENTV函数优先级更高
E: 这次ITEM,和 year 都被视为了维度,item 维度使用了any,会计算所有ITEM的组合,但ITEM属于符号引用,应该加子句 UPSERT ALL
F: rules 后面关于更新,有3个子句 UPDATE,UPSERT和 UPSERT ALL
   UPDATE,规则引用的单元格不存在,不插入单元格
   UPSERT,规则左边引用的单元格不存在,在创建单元格,而且仅适用于位置引用,符号引用还不行
   UPSERT ALL 对于item 维度使用了any,符号引用不存在的单元格,会计算所有ITEM的组合,创建并插入数组
   纠正了E
G: 一般的UNION ALL写法,基于2019预测2020,但union all 的后半部分,如果对于的item 2019的数据不存在,结果就不会包含对应的预测。
H: 还是G的思路,但distinct item,再 left outer join,对于的item 2019的数据不存在,ITEM会存在,对应的2020预测就不会丢失。纠正了G

使用道具 举报

回复
论坛徽章:
16
山治
日期:2017-01-11 16:13:26技术图书徽章
日期:2017-08-23 14:17:00技术图书徽章
日期:2017-08-23 14:17:00秀才
日期: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
发表于 2020-5-22 10:22 | 显示全部楼层
solomon_007 发表于 2020-5-22 10:08
答案:ACDFHA: model规则子句中使用 NVL2,qty[2019] 不为NULL,则qty[2019] + 10,否则返回5B: IGNORE NAV ...

根据E选项反推,rules后面默认是 rules update
我把A选项也改成rules update,结果2020年预期的没有输出

****>select item, year, qty
  2  from qz_sales
  3  model
  4     partition by (item)
  5     dimension by (year)
  6     measures (qty)
  7     rules update(
  8        qty[2020] = NVL2(qty[2019], qty[2019] + 10, 5)
  9     )
10  order by item, year;

ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2019          0
Keyboard         2018         38
Keyboard         2019         42
Monitor          2018         11

把A选项改为rules upsert all,这个时候输出是正常的
****>select item, year, qty
  2  from qz_sales
  3  model
  4     partition by (item)
  5     dimension by (year)
  6     measures (qty)
  7     rules upsert all(
  8        qty[2020] = NVL2(qty[2019], qty[2019] + 10, 5)
  9     )
10  order by item, year;

ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2019          0
Headset          2020         10
Keyboard         2018         38
Keyboard         2019         42
Keyboard         2020         52
Monitor          2018         11
Monitor          2020          5


印象中,model语句控制输出,还有return语句
把A选项改成 model return updated rows,输出的是公式里面update的行,这个好理解
****>select item, year, qty
  2  from qz_sales
  3  model return updated rows
  4     partition by (item)
  5     dimension by (year)
  6     measures (qty)
  7     rules (
  8        qty[2020] = NVL2(qty[2019], qty[2019] + 10, 5)
  9     )
10  order by item, year;

ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2020         10
Keyboard         2020         52
Monitor          2020          5



E选项输出的没有2020年预期的,调整为F,rules upsert all就会改变

还是说rules 后面的 UPDATE/UPDATE/UPSERT ALL  并不是默认的
会根据partition by、dimension by的调整发生变化?


这个地方有点懵,猫哥帮忙看下

使用道具 举报

回复
论坛徽章:
16
山治
日期:2017-01-11 16:13:26技术图书徽章
日期:2017-08-23 14:17:00技术图书徽章
日期:2017-08-23 14:17:00秀才
日期: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
发表于 2020-5-22 10:43 | 显示全部楼层
刚看了官网:UPSERT is the default.

看了猫哥的解释,A默认是upsert是没问题的,E因为是符号引用,需要使用 upsert all

这样都解释通了,我理解错了

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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-5-23 09:48 | 显示全部楼层
答案ACDFH, 2楼得奖。

A:
如果NVL2的第一个参数不是NULL,就返回第二个参数,否则返回第三个参数。对于Monitor,单元格QTY[2019]不存在,所以引用返回NULL,从而使NVL2返回期望的输出。

B:
但如果我们使用了IGNORE NAV,就意味着当我们引用不存在或为NULL的单元格时,会返回一个默认值,而对于数字来说,默认值是0。这意味着Monitor的QTY[2019]将返回0而不是NULL,这就给了我们这样一个错误的输出,最后一行中的QTY 10而不是5:

ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2019          0
Headset          2020         10
Keyboard         2018         38
Keyboard         2019         42
Keyboard         2020         52
Monitor          2018         11
Monitor          2020         10
C:
PRESENTV类似于NVL2,只是它不检查第一个参数的值是否为NULL,而是第一个参数需要是一个单元格。如果这个单元格在MODEL子句评估开始时就存在(无论值是多少),则返回第二个参数,否则返回第三个参数。这正是我们想要的。

D:
由于PRESENTV与值是否为NULL无关,所以它不受IGNORE NAV的使用影响。无论是否有IGNORE NAV,它都可以工作。

E:
这里我们将item作为一个维度使用,而不是分区键。这意味着RULES子句中的等号左边必须是QTY[ANY, 2020],而不是前面选项的QTY[2020]。RULES子句可以是UPDATE、UPSERT或UPSERT ALL——因为我们没有指定任何东西,所以默认为UPSERT。UPSERT的表现只有通过使用字面量的位置记号法才能创建新的单元格——如果我们在这里使用ANY,那么就不会创建新的单元格。因此,我们会得到这样一个错误的输出,它只包含原始行:


ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2019          0
Keyboard         2018         38
Keyboard         2019         42
Monitor          2018         11

F:
但是指定UPSERT ALL就能解决了前一选项的问题。UPSERT ALL指定了我们希望在使用ANY或其他存在式谓词时,新的单元格也会被创建。所以这个方法很好用。

G:
乍看之下,通过查询2019年数据生成2020年数据,这看起来不错,但问题是Monitor没有2019年数据。所以我们得到的是这样的错误输出,即Monitor缺少2020年数据:

ITEM             YEAR        QTY
---------- ---------- ----------
Headset          2019          0
Headset          2020         10
Keyboard         2018         38
Keyboard         2019         42
Keyboard         2020         52
Monitor          2018         11

H:
为了确保我们为所有商品生成2020年数据,我们可以在一个内联视图中得到所有不同的商品,并将它们与2019年的销售记录进行左外连接。这样,我们就可以为包括Monitor在内的所有商品生成2020年数据,并得到正确的输出。


使用道具 举报

回复

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

本版积分规则 发表回复

DTCC2020中国数据库技术大会 限时8.5折

【架构革新 高效可控】2020年9月21日~23日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


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