查看: 428|回復: 2

[每日一題] PL/SQL Challenge 每日一題:2020-6-23 LAST_VALUE

[復制鏈接]
論壇徽章:
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
跳轉到指定樓層
1#
發表于 2020-7-10 02:21 | 只看該作者 回帖獎勵 |倒序瀏覽 |閱讀模式
最先答對且答案未經編輯的puber將獲得紀念章一枚(答案不可編輯但可發新貼補充或糾正),其他會員如果提供有價值的分析、討論也可獲得紀念章一枚。

每兩周的優勝者可獲得itpub獎勵的技術圖書一本。

以往舊題索引:
http://www.3490925.live/forum.php?m ... eid&typeid=1808

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

作者:Kim Berg Hansen

運行環境:SQLPLUS, SERVEROUTPUT已打開, 最低版本要求:11.2
注:本題給出答案時候要求給予簡要說明才能得到獎品

我有一張表,里面保存著一次比賽的結果:

create table qz_results (
   name        varchar2(10) not null
, technical   integer      not null
, artistic    integer      not null
);

insert into qz_results values ('McDougal',  9,  9);
insert into qz_results values ('Svensson', 10,  7);
insert into qz_results values ('Corbier' , 10,  8);
insert into qz_results values ('Nakamura',  8,  8);

commit;

參賽者獲得兩個分數,每個分數在1到10之間,一個是技術分(technical),一個是藝術分(artistic)。勝者為技術分高者--如果出現平局,則藝術分高者為勝。假設組合(TECHNICAL, ARTISTIC)是唯一的,所以唯一的贏家將被確定地找到。

這條規則意味著可能會有參賽者的藝術分數比冠軍大。我希望看到一個按字母順序排列的參賽者列表,其中包括他們的分數,以及一列ART_DIFF,顯示他們自己的ARTISTIC分數和獲勝者(本例中為Corbier)的ARTISTIC分數之間的差異。

為此我寫了這個未完成的查詢:


select
   name
, technical
, artistic
, artistic -
   ##REPLACE##
   as art_diff
from qz_results
order by name;

哪些選項包含一個表達式來檢索獲勝者的ARTISTIC分數,它可以替換##REPLACE##,使查詢能夠執行不出錯,并始終返回這個所需的輸出(無論它運行的頻率有多高,也不管優化器可能做出什么選擇):

NAME        TECHNICAL   ARTISTIC   ART_DIFF
---------- ---------- ---------- ----------
Corbier            10          8          0
McDougal            9          9          1
Nakamura            8          8          0
Svensson           10          7         -1

(A)
   last_value(artistic) over (
      order by technical
      rows between unbounded preceding and unbounded following
   )

(B)
   last_value(artistic) over (
      order by technical, artistic
      rows between unbounded preceding and unbounded following
   )

(C)
   last_value(artistic) over (
      order by technical
      range between unbounded preceding and unbounded following
   )

(D)
   last_value(artistic) over (
      order by technical, artistic
      range between unbounded preceding and unbounded following
   )

(E)
   first_value(artistic) over (
      order by technical desc
      rows between unbounded preceding and unbounded following
   )

(F)
   first_value(artistic) over (
      order by technical desc, artistic desc
      rows between unbounded preceding and unbounded following
   )

(G)   
   first_value(artistic) over (
      order by technical desc
      range between unbounded preceding and unbounded following
   )
   
(H)
   first_value(artistic) over (
      order by technical desc, artistic desc
      range between unbounded preceding and unbounded following
   )  

論壇徽章:
535
生肖徽章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#
發表于 2020-7-10 13:30 | 只看該作者

答案 BDFH
A: 只按技術排序,這里有兩個并列的10分,ARTISTIC卻一個為8,一個為7,這里ARTISTIC取了7   
B: 按技術,藝術分升序排序,取第一名的artistic,再在SQL做減,正解
C: 這里ARTISTIC取了8,但對應的RANGE選項G,卻取了 7,
   答案雖是對的,但感覺依賴于數據集,方法不對。
D: 開窗為全部行集,rows between 應該和  range between 等效
E: ARTISTIC取了7
F: 與B對應,反序排序,取first_value
G: ARTISTIC取了7
H: 與D對應,反序排序,取first_value

使用道具 舉報

回復
論壇徽章:
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
3#
 樓主| 發表于 2020-7-11 11:00 | 只看該作者
答案BCDFH, 本期無人得獎。注意C選項里面提到了LAST_VALUE的一個比較不為人知的特性。

A:
這里我們只按技術分數排序,所以哪一行成為最后一行是不確定的。LAST_VALUE將從Svensson或Corbier中選取ARTISTIC值,這取決于優化器選取的任何一種訪問方法。我們可能會得到正確的輸出,但考慮到插入順序,我們很可能會得到錯誤的輸出,其中ART_DIFF是用Svensson的分數而不是Corbier的分數計算的:

NAME        TECHNICAL   ARTISTIC   ART_DIFF
---------- ---------- ---------- ----------
Corbier            10          8          1
McDougal            9          9          2
Nakamura            8          8          1
Svensson           10          7          0


B:
我們可以通過在ORDER BY中加入ARTISTIC來確保它成為確定性的(對于這些數據而言),這樣無論優化器選擇什么樣的訪問計劃,我們都能得到正確的輸出。

C:
當我們在LAST_VALUE中使用RANGE BETWEEN時,即使ORDER BY不是確定性的,輸出也會變成確定性的。當ORDER BY中存在平局,而我們使用range時,那么LAST_VALUE將為那些具有最后TECHNICAL值的行選擇ARTISTIC的最大值。因此,這就會給我們提供了始終正確的輸出。

D:
在ORDER BY中加入ARTISTIC,就像選項B一樣,使ORDER BY成為確定性的(對于這些數據而言),然后不管我們使用ROWS還是RANGE BETWEEN,選項B和本選項都會得到同樣正確的結果。

E:
將ORDER BY切換為DESC,并使用FIRST_VALUE并不能改變ORDER BY不是確定性的事實,這個選項和A選項存在同樣的問題,結果要么是正確的輸出,要么是和選項A一樣的錯誤輸出。

F:
修正前一個選項方法可以和選項B修正選項A的問題完全一樣,我們只需要在TECHNICAL和ARTISTIC上同時使用DESC,就可以讓FIRST_VALUE給出我們想要的結果。

G:
使用RANGE和不確定的ORDER BY使得FIRST_VALUE調用類似于選項C的規則,只是對于FIRST_VALUE來說,它為那些具有第一個(按降序)TECHNICAL值的行選擇ARTISTIC的最小值。實質上,這就和我們使用ORDER BY TECHNICAL DESC, ARTISTIC ASC一樣。因此,我們得到了與選項A中相同的錯誤輸出,但與選項A和E不同的是,這個選項始終產生相同的錯誤輸出。

H:
使用適當的確定性(對于這些數據)ORDER BY再次使我們使用ROWS還是RANGE變得不重要----這個選項和選擇F都是有效的,就像選項B和選項D都是有效的。
   

使用道具 舉報

回復

您需要登錄后才可以回帖 登錄 | 注冊

本版積分規則 發表回復

TOP技術積分榜 社區積分榜 徽章 團隊 統計 知識索引樹 積分競拍 文本模式 幫助
  ITPUB首頁 | ITPUB論壇 | 數據庫技術 | 企業信息化 | 開發技術 | 微軟技術 | 軟件工程與項目管理 | IBM技術園地 | 行業縱向討論 | IT招聘 | IT文檔
  ChinaUnix | ChinaUnix博客 | ChinaUnix論壇
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓優訊信息技術有限公司版權所有 聯系我們 
京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號:11010802021510 廣播電視節目制作經營許可證:編號(京)字第1149號
  
快速回復 返回頂部 返回列表
30岁的男人干啥赚钱快赚钱多 股票行情今天大盘是多少 赛车pk10app 股票涨跌的秘密 内蒙古十一选五开奖结 4887香港铁算资料开奖小说 股票投资特点 安徽快三基本走势图 江西多乐彩11选五走势图 黑龙江快乐十分遗漏三个号 黑龙江快乐十分走势图一定牛