首届NoCOUG国际SQL挑战赛

newkid

原帖由 wabjtam123 于 2009-9-15 21:39 发表
NEWKID兄，你要参赛，一定能获奖的！！！

如果有热心围观群众奖我一定拿下！

〇〇

防火墙 还是很精密的
挡住了 http://iggyfernandez.wordpress.com/author/iggyfernandez/
但不挡住
http://iggyfernandez.files.wordpress.com/2009/07/simplified-version-of-winning-solution.doc
http://iggyfernandez.files.wordpress.com/2009/08/we-have-a-winner.pdf

purebegger

看到这高深莫测的傅立叶变换，突然想读研去了，跳专业到数学系～

xhhjj

傅立叶变换早忘记了

chen222246lei

原帖由 〇〇 于 2009-9-16 09:27 发表
防火墙 还是很精密的
挡住了 http://iggyfernandez.wordpress.com/author/iggyfernandez/
但不挡住
http://iggyfernandez.files.wordp ... inning-solution.doc
http://iggyfernandez.files.wordp ... e-have-a-winner.pdf

还是看不见呀，怎么用DBMS_XMLGET.GETXMLTYPE运行动态SQL的，干脆把SQL贴出来得了，别让兄弟们自己到处找了。呵呵。

newkid

原帖由 chen222246lei 于 2009-9-16 17:47 发表


还是看不见呀，怎么用DBMS_XMLGET.GETXMLTYPE运行动态SQL的，干脆把SQL贴出来得了，别让兄弟们自己到处找了。呵呵。

我不是在#17贴出来了吗？
他的解法是很巧妙的，根本不用高等数学知识。

〇〇

期待中文版

〇〇

貌似看懂了一点出题者的解法，矩阵相乘，7次=4次*2次*1次
把连接次数按二进制位分解，拼出动态sql
但他说varchar2 2048字符限制，我怎么觉得是4000
还说要用伪CARDINALITY提示避免600错误。。。

newkid

他的解法精华就是把连接次数大大减少，和这位的思路是一样的：
http://www.pythian.com/news/2385/nocoug-sql-challenge-entry

VARCHAR2我也认为是4000.
CARDINALITY提示我去掉了也不会出错，他是在11G下试验的。他的写法在10G不能运行，最后拼接SQL会出错，不知道为什么只能CONNECT两层。

我把它修改了一下就可以在10G下运行了：
VAR N NUMBER;
EXEC :N:=7;

WITH
section1 AS  
(  
SELECT 1 AS section,  
       1 AS clause#,  
       'WITH l0 as(SELECT face_value as f,probability as p FROM die)' AS clause  
  FROM DUAL  
)
,section2 AS  
(  
SELECT 2 AS section,  
       LEVEL AS clause#,  
       ',l' || TO_CHAR (LEVEL) || ' AS(SELECT f,SUM(p) AS p FROM(SELECT t1.f+t2.f AS f, t1.p*t2.p AS p FROM l' || TO_CHAR (LEVEL - 1) || ' t1,l' || TO_CHAR (LEVEL - 1) || ' t2)GROUP BY f)' AS clause  
  FROM DUAL  
CONNECT BY POWER (2, LEVEL) <= :n  
)
,section3 AS  
(  
SELECT 3 AS section,  
       ROW_NUMBER() OVER (ORDER BY LEVEL DESC) AS clause#,  
       DECODE (BITAND (:n, POWER (2, LEVEL - 1)), 0, '', 'SELECT f,sum(p) AS p FROM(SELECT t1.f+t2.f AS f,t1.p*t2.p AS p FROM l' || TO_CHAR (LEVEL - 1) || ' t1,(') AS clause  
  FROM DUAL  
CONNECT BY POWER (2, LEVEL - 1) <= :n  
)
,section4 AS  
(  
SELECT 4 AS section,  
       1 AS clause#,  
       '(SELECT 0 AS f, 1 AS p FROM dual)' AS clause  
  FROM DUAL  
)
,section5 AS  
(  
SELECT 5 AS section,  
       ROW_NUMBER() OVER (ORDER BY LEVEL) AS clause#,  
       DECODE (BITAND (:n, POWER (2, LEVEL - 1)), 0, '', ')t2)GROUP BY f') AS clause  
  FROM DUAL  
CONNECT BY POWER (2, LEVEL - 1) <= :n  
)
,all_sections AS  
(  
SELECT ROW_NUMBER() OVER (ORDER BY section DESC, clause# DESC) AS clause#
      ,COUNT(*) OVER() AS CNT
      ,clause  
  FROM (SELECT section, clause#, clause FROM section1 UNION ALL  
        SELECT section, clause#, clause FROM section2 UNION ALL  
        SELECT section, clause#, clause FROM section3 UNION ALL  
        SELECT section, clause#, clause FROM section4 UNION ALL  
        SELECT section, clause#, clause FROM section5)  
WHERE LENGTH(clause) != 0  
)
,sql_text AS  
(  
SELECT REPLACE(SYS_CONNECT_BY_PATH (clause, '|'),'|') AS sql_text  
  FROM all_sections  
WHERE clause# =1
START WITH clause# = CNT
CONNECT BY clause# = PRIOR clause#-1
)
,xml_string AS  
(  
SELECT DBMS_XMLGEN.getxmltype (sql_text) xml_string  
  FROM sql_text  
)
SELECT sum,  
       probability  
  FROM xml_string,  
       XMLTABLE  
       ('$x/ROWSET/ROW' PASSING xml_string AS "x"  
         COLUMNS  
           sum NUMBER PATH 'F',  
           probability NUMBER PATH 'P'  
       ) x  
ORDER BY sum ;

我修改了两个子查询：ALL_SECTIONS和SQL_TEXT.

〇〇

newkid可以改成niu(牛)kid