Pascal's triangle or binomial coefficients

Laurent Schneider · Nov 15, 2005 8:36AM

strike !

94799 · Nov 15, 2005 8:38AM

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 · Nov 15, 2005 9:12AM

Splendid !!

Nicolas.

Aketi Jyuuzou · Dec 17, 2006 10:39PM

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 · May 28, 2007 7:45AM

Thanks, Vadim Tropashko.
I'm reading your book, SQL Design Patterns.

Query Your Dream & Future at
http://www.soqool.com

572471 · May 28, 2007 8:52AM

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 · May 28, 2007 10:58AM

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 · May 28, 2007 1:17PM

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 · May 3, 2008 5:06PM

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

Forum Stats

Quick Links

Categories

Pascal's triangle or binomial coefficients

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Pascal's triangle or binomial coefficients

Comments