Forum Stats

  • 3,767,924 Users
  • 2,252,731 Discussions
  • 7,874,381 Comments

Discussions

Pascal's triangle or binomial coefficients

13»

Comments

  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219 Bronze Badge
  • 94799
    94799 Member Posts: 2,208
    LOL. Is this better?
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> VARIABLE r NUMBER;
    SQL> EXEC :r := 10;
    
    PL/SQL procedure successfully completed.
    
    SQL> SELECT LPAD (' ', 5 * (:r - an) / 2) ||
      2           REPLACE (SYS_CONNECT_BY_PATH (
      3              LPAD (r, 5), '/'), '/') pascals_triangle
      4  FROM  (SELECT a.n an, b.n bn,
      5                product (GREATEST (b.n, 1)) OVER (
      6                   PARTITION BY a.n) /
      7                product (GREATEST (b.n, 1)) OVER (
      8                   PARTITION BY a.n ORDER BY b.n) /
      9                product (GREATEST (a.n - b.n, 1)) OVER (
     10                   PARTITION BY a.n ORDER BY b.n DESC) r
     11         FROM   TABLE (many (0, :r)) a,
     12                TABLE (many (0, a.n)) b)
     13  WHERE  CONNECT_BY_ISLEAF = 1
     14  START WITH bn = 0
     15  CONNECT BY an = PRIOR an AND bn = PRIOR bn + 1;
    
    PASCALS_TRIANGLE
    --------------------------------------------------------------------------------
                                 1
                              1    1
                            1    2    1
                         1    3    3    1
                       1    4    6    4    1
                    1    5   10   10    5    1
                  1    6   15   20   15    6    1
               1    7   21   35   35   21    7    1
             1    8   28   56   70   56   28    8    1
          1    9   36   84  126  126   84   36    9    1
        1   10   45  120  210  252  210  120   45   10    1
    
    11 rows selected.
    
    SQL> 
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Splendid !!

    Nicolas.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    SQL> col PASCALS_TRIANGLE for a80
    SQL>
    SQL> with Renban as (
    2 select RowNum-1 as Val
    3 from dual connect by Level <= 15),
    4 WorkView as (
    5 select Ra.Val as n,Rb.Val as r
    6 from Renban Ra,Renban Rb
    7 where Ra.Val >= Rb.Val),
    8 RowList as (
    9 select n,r,
    10 case when r=0 then 1
    11 else (select round(exp(sum(Ln(a.N-b.R+1)))) from WorkView b
    12 where b.N=a.N
    13 and b.R > 0
    14 and b.R<=a.R) end as NPR,
    15 case when r=0 then 1
    16 else (select round(exp(sum(Ln(b.R)))) from WorkView b
    17 where b.N=a.N
    18 and b.R > 0
    19 and b.R<=a.R) end as "R!"
    20 from WorkView a),
    21 PascalTriangle as (
    22 select N,SubStr(sys_connect_by_path(Val,' '),2) as Pascal
    23 from (select N,R,NPR / "R!" as Val from RowList)
    24 where Connect_by_IsLeaf = 1
    25 start with R=0
    26 connect by prior N=N
    27 and prior R=R-1)
    28 select Lpad(' ',(MaxLength-Length(Pascal))/2-1) || Pascal as PASCALS_TRIANGLE
    29 from PascalTriangle,(select max(Length(Pascal)) as MaxLength from PascalTriangle);

    PASCALS_TRIANGLE
    --------------------------------------------------------------------------------
    1
    1 1
    1 2 1
    1 3 3 1
    1 4 6 4 1
    1 5 10 10 5 1
    1 6 15 20 15 6 1
    1 7 21 35 35 21 7 1
    1 8 28 56 70 56 28 8 1
    1 9 36 84 126 126 84 36 9 1
    1 10 45 120 210 252 210 120 45 10 1
    1 11 55 165 330 462 462 330 165 55 11 1
    1 12 66 220 495 792 924 792 495 220 66 12 1
    1 13 78 286 715 1287 1716 1716 1287 715 286 78 13 1
    1 14 91 364 1001 2002 3003 3432 3003 2002 1001 364 91 14 1
  • 146850
    146850 Member Posts: 116
    Thanks, Vadim Tropashko.
    I'm reading your book, SQL Design Patterns.

    Query Your Dream & Future at
    http://www.soqool.com
  • 572471
    572471 Member Posts: 984 Green Ribbon
    iterative power of model:
    SQL> var p number;
    SQL> exec :p:=10;
    
    PL/SQL procedure successfully completed
    p
    ---------
    10
    
    SQL> 
    SQL> select n from (select 1 n from dual)
      2   model
      3    dimension by (0 dim)
      4    measures (n, n n2)
      5     rules iterate(1000) until (iteration_number+1>=:p)
      6      (n2[ANY]=n[CV()],
      7       n[for dim from 1 to iteration_number increment 1] = nvl(n2[CV()]+n2[CV()-1],1))
      8  /
    
             N
    ----------
             1
             9
            36
            84
           126
           126
            84
            36
             9
             1
    
    10 rows selected
    p
    ---------
    10
    
    SQL> 
  • Alessandro Rossi
    Alessandro Rossi Member Posts: 1,057 Bronze Badge
    edited May 28, 2007 11:00AM
    Here it is.

    Some needed stuff.
    create or replace type string_table is table of varchar2(4000)
    /

    CREATE OR REPLACE
    FUNCTION tab2string ( tab in string_table, delimitatore in varchar2 default ',' ) return varchar2 as
    out_string varchar2(32767);
    begin
    if ( cardinality(tab) > 0 ) then
    for i in tab.first .. tab.last loop
    if ( i != tab.first ) then
    out_string := out_string||delimitatore;
    else
    out_string := '';
    end if;
    out_string := out_string || tab(i);
    end loop;
    return out_string;
    else
    return null;
    end if;
    end;
    /

    create or replace function fact(
    n in integer
    ) return integer
    as
    out_val number;
    begin
    out_val := 1;
    for i in 1 .. n loop
    out_val := out_val * i;
    end loop;
    return out_val;
    end;
    /
    and some output ( n = 6 )
    Processing ...
    select fact(:n)/(fact(rownum-1)*fact(:n-rownum+1)) val
    from dual
    connect by rownum-1 <= :n

    Query finished, retrieving results...
    VAL
    --------------------------------------
    1
    6
    15
    20
    15
    6
    1

    7 row(s) retrieved


    Processing ...
    select tab2string( cast (
    multiset (
    select fact(a.row_no)/(fact(level-1)*fact(a.row_no-level+1)) val
    from dual
    connect by level-1 <= a.row_no
    )
    as string_table
    ),' '
    ) as pascal_triangle
    from (
    select rownum-1 as row_no
    from dual
    connect by level-1 <= :n
    ) a

    Query finished, retrieving results...
    PASCAL_TRIANGLE
    --------------------------------------------------------------------------------
    1
    1 1
    1 2 1
    1 3 3 1
    1 4 6 4 1
    1 5 10 10 5 1
    1 6 15 20 15 6 1

    7 row(s) retrieved
    Bye Alessandro
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown

    And of course a XML solution:
    michaels>  COLUMN pascal format a30
    
    michaels>  VAR pascal number
    
    michaels>  EXEC :pascal := 7
    
    michaels>  SELECT RTRIM(x.COLUMN_VALUE.EXTRACT('r/text()'),',') pascal
      FROM XMLTable('declare function local:fact($i)
                     {
                       if($i > 0) then 
                         number($i * local:fact($i - 1))
                       else (1)
                     };
    
                     for $n in 0 to .
                     return <r> {for $k in 0 to $n 
                                  return concat(local:fact($n) div (local:fact($k) * local:fact($n - $k)),",")
                                }
                            </r>' PASSING XMLTYPE('<p>'||:pascal||'</p>')) x
    
    PASCAL                        
    ------------------------------
    1                             
    1, 1                          
    1, 2, 1                       
    1, 3, 3, 1                    
    1, 4, 6, 4, 1                 
    1, 5, 10, 10, 5, 1            
    1, 6, 15, 20, 15, 6, 1        
    1, 7, 21, 35, 35, 21, 7, 1    
    
    
    8 rows selected.
  • 486393
    486393 Member Posts: 487
    exec :l_numrows := 5;

    with data as
    (
    select ver,hor,decode(num,0,to_number(null),num) numn
    from (select level hor from dual connect by level < :l_numrows+2)
    , (select level ver from dual connect by level < :l_numrows+1)
    model
    dimension by (hor,ver)
    measures (cast (null as number(10)) num)
    rules
    (
    num[1,any] = 1
    , num[2,1] = 1
    , num[hor>1,ver>1] = nvl(num[cv(hor)-1,cv(ver)-1],0) + nvl(num[cv(hor),cv(ver)-1],0)
    )
    )
    select ver,totalrow,sumrow
    from
    (
    select *
    from data
    model
    partition by (ver)
    dimension by (hor)
    measures (numn
    ,cast(null as varchar2(25)) totalrow
    ,count(*) over (partition by ver) counter
    ,cast(0 as number(5)) sumrow)
    rules
    (
    totalrow[any] order by hor = totalrow[cv(hor)-1] ||' '||numn[cv(hor)]
    , sumrow[any] order by hor = nvl(sumrow[cv(hor)-1],0) + nvl(numn[cv(hor)],0)
    )
    )
    where hor=counter
    order by ver,totalrow;

    VER TOTALROW SUMROW
    ---------- ------------------------- ----------
    1 1 1 2
    2 1 2 1 4
    3 1 3 3 1 8
    4 1 4 6 4 1 16
    5 1 5 10 10 5 1 32
This discussion has been closed.