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. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.