ITPUB SQL大賽第二期(一)
貼一下第二期的第一道題。
第二期題目參考:http://www.itpub.net/thread-1403356-1-1.html
版主newkid點評參考:http://www.itpub.net/thread-1411980-1-1.html
簡單描述一下問題:
問題1(80分):
在一個6*6的棋盤中,放置12個球,每行、每列、每個正負45度的斜線上最多放置2個球,請用一個SQL語句(不可以用PL/SQL或T-SQL匿名塊、過程或函式,也不可以用Java等外部語言)求出不“重複”的擺法的個數(剔除上下對稱、左右對稱、中心對稱,沿中心點旋轉等各種變形)
對於這個問題,其實就是在第一題的基礎上加大了難度。個人認為最困難地方是搞清楚存在多少種變形的方法,這並沒有什麼太好的辦法,不過好在有第一題的基礎,我們可以在第一題結果上,透過手工畫圖的方式來搞清楚各種變形和原圖形的關係。當然後來題目給出了進一步提示,明確了8種等價的變形,這相當於降低了一部分難度。
SQL> with c as
2 (select rownum - 1 c from dual connect by rownum <= 2),
3 lines as
4 (select to_number(c1.c || c2.c || c3.c || c4.c || c5.c || c6.c) line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5, c6.c c6
5 from c c1, c c2, c c3, c c4, c c5, c c6
6 where c1.c + c2.c + c3.c + c4.c + c5.c + c6.c = 2
7 order by 1 desc),
8 result as (select rownum,
9 ltrim(to_char(line1.line, '099999'))
10 || chr(10) || ltrim(to_char(line2.line, '099999'))
11 || chr(10) || ltrim(to_char(line3.line, '099999'))
12 || chr(10) || ltrim(to_char(line4.line, '099999'))
13 || chr(10) || ltrim(to_char(line5.line, '099999'))
14 || chr(10) || ltrim(to_char(line6.line, '099999')) result1,
15 reverse(ltrim(to_char(line1.line, '099999')))
16 || chr(10) || reverse(ltrim(to_char(line2.line, '099999')))
17 || chr(10) || reverse(ltrim(to_char(line3.line, '099999')))
18 || chr(10) || reverse(ltrim(to_char(line4.line, '099999')))
19 || chr(10) || reverse(ltrim(to_char(line5.line, '099999')))
20 || chr(10) || reverse(ltrim(to_char(line6.line, '099999'))) result2,
21 ltrim(to_char(line6.line, '099999'))
22 || chr(10) || ltrim(to_char(line5.line, '099999'))
23 || chr(10) || ltrim(to_char(line4.line, '099999'))
24 || chr(10) || ltrim(to_char(line3.line, '099999'))
25 || chr(10) || ltrim(to_char(line2.line, '099999'))
26 || chr(10) || ltrim(to_char(line1.line, '099999')) result3,
27 reverse(ltrim(to_char(line6.line, '099999')))
28 || chr(10) || reverse(ltrim(to_char(line5.line, '099999')))
29 || chr(10) || reverse(ltrim(to_char(line4.line, '099999')))
30 || chr(10) || reverse(ltrim(to_char(line3.line, '099999')))
31 || chr(10) || reverse(ltrim(to_char(line2.line, '099999')))
32 || chr(10) || reverse(ltrim(to_char(line1.line, '099999'))) result4,
33 line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1
34 || chr(10) || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
35 || chr(10) || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
36 || chr(10) || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
37 || chr(10) || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
38 || chr(10) || line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6 result5,
39 line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1
40 || chr(10) || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
41 || chr(10) || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
42 || chr(10) || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
43 || chr(10) || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
44 || chr(10) || line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6 result6,
45 line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6
46 || chr(10) || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
47 || chr(10) || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
48 || chr(10) || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
49 || chr(10) || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
50 || chr(10) || line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1 result7,
51 line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6
52 || chr(10) || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
53 || chr(10) || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
54 || chr(10) || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
55 || chr(10) || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
56 || chr(10) || line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1 result8
57 from lines line1, lines line2, lines line3, lines line4, lines line5, lines line6
58 where line1.line + line2.line + line3.line + line4.line + line5.line + line6.line = 222222
59 and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line + 100000*line6.line), '012') is null
60 and ltrim(to_char(100000*line1.line + 10000*line2.line + 1000*line3.line + 100*line4.line + 10*line5.line + line6.line), '012') is null
61 )
62 select count(distinct greatest(result1, result2, result3, result4, result5, result6, result7, result8)) cn
63 from result;
CN
----------
155
已用時間: 00: 00: 18.65
最先完成的SQL無非就是第一題的基礎上將5*5變成了6*6,然後根據各種變形的規則生成對應的結果。由於8種變形之間可以相互轉換,因此這8種等價的變形的最大值是同一個圖形,這樣透過COUNT DISTINCT的方式,就獲得了去重的解。
雖然結果得到了,但是這個SQL執行速度較慢,需要進一步的最佳化。
SQL> with c as
2 (select rownum - 1 c from dual connect by rownum <= 2),
3 lines as
4 (select to_number(c1.c || c2.c || c3.c || c4.c || c5.c || c6.c) line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5, c6.c c6
5 from c c1, c c2, c c3, c c4, c c5, c c6
6 where c1.c + c2.c + c3.c + c4.c + c5.c + c6.c = 2
7 ),
8 result as (select /*+ ordered */
9 ltrim(to_char(line1.line, '099999'))
10 || ltrim(to_char(line2.line, '099999'))
11 || ltrim(to_char(line3.line, '099999'))
12 || ltrim(to_char(line4.line, '099999'))
13 || ltrim(to_char(line5.line, '099999'))
14 || ltrim(to_char(line6.line, '099999')) result1,
15 reverse(ltrim(to_char(line1.line, '099999')))
16 || reverse(ltrim(to_char(line2.line, '099999')))
17 || reverse(ltrim(to_char(line3.line, '099999')))
18 || reverse(ltrim(to_char(line4.line, '099999')))
19 || reverse(ltrim(to_char(line5.line, '099999')))
20 || reverse(ltrim(to_char(line6.line, '099999'))) result2,
21 ltrim(to_char(line6.line, '099999'))
22 || ltrim(to_char(line5.line, '099999'))
23 || ltrim(to_char(line4.line, '099999'))
24 || ltrim(to_char(line3.line, '099999'))
25 || ltrim(to_char(line2.line, '099999'))
26 || ltrim(to_char(line1.line, '099999')) result3,
27 reverse(ltrim(to_char(line6.line, '099999')))
28 || reverse(ltrim(to_char(line5.line, '099999')))
29 || reverse(ltrim(to_char(line4.line, '099999')))
30 || reverse(ltrim(to_char(line3.line, '099999')))
31 || reverse(ltrim(to_char(line2.line, '099999')))
32 || reverse(ltrim(to_char(line1.line, '099999'))) result4,
33 line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1
34 || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
35 || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
36 || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
37 || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
38 || line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6 result5,
39 line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1
40 || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
41 || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
42 || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
43 || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
44 || line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6 result6,
45 line1.c6 || line2.c6 || line3.c6 || line4.c6 || line5.c6 || line6.c6
46 || line1.c5 || line2.c5 || line3.c5 || line4.c5 || line5.c5 || line6.c5
47 || line1.c4 || line2.c4 || line3.c4 || line4.c4 || line5.c4 || line6.c4
48 || line1.c3 || line2.c3 || line3.c3 || line4.c3 || line5.c3 || line6.c3
49 || line1.c2 || line2.c2 || line3.c2 || line4.c2 || line5.c2 || line6.c2
50 || line1.c1 || line2.c1 || line3.c1 || line4.c1 || line5.c1 || line6.c1 result7,
51 line6.c6 || line5.c6 || line4.c6 || line3.c6 || line2.c6 || line1.c6
52 || line6.c5 || line5.c5 || line4.c5 || line3.c5 || line2.c5 || line1.c5
53 || line6.c4 || line5.c4 || line4.c4 || line3.c4 || line2.c4 || line1.c4
54 || line6.c3 || line5.c3 || line4.c3 || line3.c3 || line2.c3 || line1.c3
55 || line6.c2 || line5.c2 || line4.c2 || line3.c2 || line2.c2 || line1.c2
56 || line6.c1 || line5.c1 || line4.c1 || line3.c1 || line2.c1 || line1.c1 result8
57 from lines line1, lines line2, lines line3, lines line4, lines line5, lines line6
58 where line1.line + line2.line + line3.line + line4.line + line5.line + line6.line = 222222
59 and ltrim(line1.line + line2.line + line3.line, '012') is null
60 and ltrim(line1.line + line2.line + line3.line + line4.line, '012') is null
61 and ltrim(line1.line + line2.line + line3.line + line4.line + line5.line, '012') is null
62 and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line + 100000*line6.line), '012') is null
63 and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line), '012') is null
64 and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line), '012') is null
65 and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line), '012') is null
66 and ltrim(to_char(100000*line1.line + 10000*line2.line + 1000*line3.line + 100*line4.line + 10*line5.line + line6.line), '012') is null
67 and ltrim(to_char(100*line1.line + 10*line2.line + line3.line), '012') is null
68 and ltrim(to_char(1000*line1.line + 100*line2.line + 10*line3.line + line4.line), '012') is null
69 and ltrim(to_char(10000*line1.line + 1000*line2.line + 100*line3.line + 10*line4.line + line5.line), '012') is null
70 )
71 select count(distinct greatest(result1, result2, result3, result4, result5, result6, result7, result8))
72 from result;
COUNT(DISTINCTGREATEST(RESULT1,RESULT2,RESULT3,RESULT4,RESULT5,RESULT6,RESULT7,RESULT8))
----------------------------------------------------------------------------------------
155
已用時間: 00: 00: 00.64
這裡最佳化的主要思路是,在連線過程中,如果發現當前的組合已經不滿足題目的要求,那麼就儘早過濾掉。比如如果將前三行各個列相加,發現某列的結果已經出現了3,那麼這種變形顯然不可能滿足題目要求,因此就可以被捨棄。所以這裡新增了判斷,當前3行結果的所有變形獲得後,過濾掉列和兩個斜線上出現大於2的情況。同樣在前4行和前5行結果中進行相同的過濾,這使得大量不符合條件的結果沒有參加到後續的連線中,從而極大的提高了效能。
另外之所以新增了/*+ ORDERED */這個提示,是因為在存在6張表連線時,Oracle打亂了連線順序,先從line6開始進行連線,這會導致兩個結果,一是5×5矩陣中正確的排序被打亂,二是前面的最佳化手段將不起作用,因為我們認為連線從line1開始。加上提示後,確保Oracle按照指定的順序進行表連線,從而達到最佳化目的。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-691174/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 我與Itpub
- 大獎等你來拿!HMS Core線上Codelabs挑戰賽第二期開始
- 第二期 OceanBase 技術徵文大賽來襲!快來釋放你的原力!
- ITPUB的採訪稿
- 【Python】備份itpub部落格Python
- 開通ITPub部落格了!
- 恭喜您成功申請ITPUB BLOG
- itpub插入超連結測試
- [SWPUCTF 2021 新生賽]easy_sqlSQL
- 【CSDN競賽第二期】得CSDN機械鍵盤等精美禮品
- 開啟ITPUB部落格專欄技能!
- 轉戰ITpub CSDN ID Dadunl43
- 《安全大咖》第二期:“教書育人”錢林松
- 用一條SQL語句顯示所有可能的比賽組合SQL
- 【比賽覆盤】2024第七屆“傳智杯”全國大學生計算機大賽程式設計挑戰賽(初賽第一場)計算機程式設計
- 積木大賽
- 第一屆天池 PolarDB 資料庫效能大賽資料庫
- http://blog.itpub.net/69994146/viewspace-2865498/HTTPView
- 千帆杯第二期賽題釋出!你的賀歲靈感會是下一個流量密碼嗎密碼
- CUSGA第一屆中國大學生遊戲開發創作大賽複賽名單公佈!遊戲開發
- 給ITPub部落格小編的幾句話
- 第十五屆藍橋杯大賽軟體賽省賽 C/C++ 大學 A 組C++
- GeekPwn雲安全挑戰賽賽前大揭秘!
- 大廠程式設計師凡爾賽的一天程式設計師
- 大賽報名 | AI口罩佩戴檢測大賽期待你的參與!AI
- 2008北京九華山莊itpub年會有感
- NineData獲"IT168 & ITPub 年度創新產品"獎
- 深圳國際金融科技大賽Ultipa嬴圖斬獲“人工智慧與大資料”賽道一等獎。人工智慧大資料
- 比賽結果公佈!第一屆CUSGA中國大學生遊戲開發創作大賽圓滿落幕!遊戲開發
- 程式設計大賽WBS程式設計
- 【大咖直播】Elastic 企業搜尋實戰工作坊(第二期)AST
- 智慧航空AI大賽-阿里雲演算法大賽總結(原始碼分享)AI阿里演算法原始碼
- 第二屆資料安全大賽“數信杯”資料安全大賽 WP
- 第一屆PolarDB資料庫效能大賽Java選手分享資料庫Java
- 【有獎徵文】第一屆 OceanBase 技術徵文大賽來啦!
- 前端週刊第二期前端
- 2024廣東大學生攻防大賽WP
- 2022廣東大學生攻防大賽WP
- 賽程更新| 2022微軟與英特爾黑客鬆大賽火熱報名中,兩大賽道等你Battle!微軟黑客BAT