2012-06-15

Pascal matrix

Just read about yet another SQL Challenge pointing to the original request to solve Pascal matrix with ANSI SQL. Well this following solution is maybe braking the rules, but illustrates an Oracle 1Xy wishlist feature. Mixing analytic functions and recursive subquery factoring. The solution is using PostgreSQL 9.1 capabilities.

with recursive n (u) as (
select 1 
union all
select n.u+1 
  from n
 where n.u < 8
), q as (
select n.u v, m.u w 
  from n, n m 
), r (v,w,s,d,e) as (
select v,w, 1::bigint,w::bigint,sum(w)over(order by w)::bigint
  from q
 where v = 1
union all
select q.v,q.w
      ,r.d
      ,r.e
      ,sum(r.e)over(order by r.w)::bigint
  from r 
 inner join q 
    on r.w=q.w and r.v+1=q.v
)
select v,w,s
  from r
;

1;1;1
1;2;1
1;3;1
1;4;1
1;5;1
1;6;1
1;7;1
1;8;1
2;1;1
2;2;2
2;3;3
2;4;4
2;5;5
2;6;6
2;7;7
2;8;8
3;1;1
3;2;3
3;3;6
3;4;10
3;5;15
3;6;21
3;7;28
3;8;36
4;1;1
4;2;4
4;3;10
4;4;20
4;5;35
4;6;56
4;7;84
4;8;120
5;1;1
5;2;5
5;3;15
5;4;35
5;5;70
5;6;126
5;7;210
5;8;330
6;1;1
6;2;6
6;3;21
6;4;56
6;5;126
6;6;252
6;7;462
6;8;792
7;1;1
7;2;7
7;3;28
7;4;84
7;5;210
7;6;462
7;7;924
7;8;1716
8;1;1
8;2;8
8;3;36
8;4;120
8;5;330
8;6;792
8;7;1716
8;8;3432

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.