sql for Statistics

As part of my sql tutorial which is still very much in progress, I thought I'd do a quick post on using sql with statistics. I am covering two topics today, which are elementary concepts in statistics but forms a compliment my tutorials very well.

Permutations

Consider a set of 4 objects. Now, suppose that we want to fill three positions with objects selected from the 4. We need to consider the order of the items in each set as well, then the number of possible ordered arrangements is 24 and they are

a b c	b a c	c a b	Dab
a b d	b a d	c a d	d a c
a c b	b c a	c b a	d b c
a c d	b c d	c b d	d b a
a d c	b d a	c d b	d c a
a d b	b d c	c d a	d c b

The number of possible ordered arrangements can be computed as follows:

Since there are 3 positions and 4 objects, the first position can be filled in 4 different ways. Once the first position is filled the remaining 2 positions can be filled from the remaining 3 objects. Thus, the second position can be filled in 3 ways. The third position can be filled in 2 ways. Then the total number of ways 3 positions can be filled out of 4 objects is given by

(4)(3)(2) = 24.

In HANA, we can write the following sql to achieve the same result set:

do begin

t_data = 

    select 'a' as id

    from dummy

    union all 

    select  'b' as id

    from dummy

    union all 

    select  'c' as id

    from dummy

    union all 

    select  'd' as id

    from dummy

    ;

    

select o.id, t.id, r.id, f.id

from :t_data as o

cross join :t_data as t

cross join :t_data as r

cross join :t_data as f

where o.id != t.id

and o.id != r.id

and o.id != f.id

and t.id != r.id

and t.id != f.id

and r.id != f.id

;

end;

  ;ID;ID;ID;ID

1 ;a ;b ;c ;d 

2 ;a ;b ;d ;c 

3 ;a ;c ;b ;d 

4 ;a ;c ;d ;b 

5 ;a ;d ;b ;c 

6 ;a ;d ;c ;b 

7 ;b ;a ;c ;d 

8 ;b ;a ;d ;c 

9 ;b ;c ;a ;d 

10;b ;c ;d ;a 

11;b ;d ;a ;c 

12;b ;d ;c ;a 

13;c ;a ;b ;d 

14;c ;a ;d ;b 

15;c ;b ;a ;d 

16;c ;b ;d ;a 

17;c ;d ;a ;b 

18;c ;d ;b ;a 

19;d ;a ;b ;c 

20;d ;a ;c ;b 

21;d ;b ;a ;c 

22;d ;b ;c ;a 

23;d ;c ;a ;b 

24;d ;c ;b ;a 

Combinations

In permutation, order is important. But in many problems, the order of selection is not important and interest centers only on the set of r objects. Let c denote the number of subsets of size r that can be selected from n different objects. 

How many committees of two chemists and one physicist can be formed from 4 chemists and 3 physicists?

do begin

t_phys =

    select 'P1' as id

    from dummy

    union all

    select  'P2' as id

    from dummy

    union all

    select  'P3' as id

    from dummy

    ;

t_chem =

    select 'C1' as id

    from dummy

    union all

    select  'C2' as id

    from dummy

    union all

    select  'C3' as id

    from dummy

    union all

    select  'C4' as id

    from dummy

    ;

  

select p.id, ca.id, cb.id

from :t_phys as p

cross join :t_chem as ca

cross join :t_chem as cb

where ca.id != cb.id

  and cb.id > ca.id

order by p.id, ca.id, cb.id

  ;

 ;ID;ID;ID

1 ;P1;C1;C2

2 ;P1;C1;C3

3 ;P1;C1;C4

4 ;P1;C2;C3

5 ;P1;C2;C4

6 ;P1;C3;C4

7 ;P2;C1;C2

8 ;P2;C1;C3

9 ;P2;C1;C4

10;P2;C2;C3

11;P2;C2;C4

12;P2;C3;C4

13;P3;C1;C2

14;P3;C1;C3

15;P3;C1;C4

16;P3;C2;C3

17;P3;C2;C4

18;P3;C3;C4