postgreSQL Combinations

Question

I'm trying to figure out how to find all possible combinations (using SQL) for the following situation:

I have 100 ping pong balls in a bowl (id = 1...100)
Each ball is one of 4 colors (color = red, green, blue, yellow)

I want to pick 5 balls (without replacement) as follows.

1 red ball
2 green balls
3 blue balls
2 yellow balls
1 ball that is green, blue, or yellow

How can I determine all possible combinations using SQL as efficiently as possible?

Below is the best I could come up with, but I don't want order to matter (combinations) and I want no replacement:

SELECT pick1.id, pick2.id, pick3.id, pick4.id, pick5.id, pick6.id, pick7.id, pick8.id, pick9.id
FROM bowl AS pick1, bowl AS pick2, bowl AS pick3, bowl AS pick4, bowl AS pick5, bowl AS pick6,
bowl AS pick7, bowl AS pick8, bowl AS pick9
WHERE
pick1.color = "red" AND
pick2.color = "green" AND
pick3.color = "green" AND
pick4.color = "blue" AND
pick5.color = "blue" AND
pick6.color = "blue" AND
pick7.color = "yellow" AND
pick8.color = "yellow" AND
(pick9.color = "green" OR
pick9.color = "blue" OR
pick9.color = "yellow")

It looks like you've created a Cartesian product which gives you all combinations. For sql standard you would use CROSS JOIN to join your tables. What arent you seeing that you would like to? And what does "no replacement" mean? — crthompson
– crthompson, Commented Nov 11, 2014 at 16:30
@paqogomez . . . "No replacement" means that the same ball cannot be chosen twice. "With replacement" means the same ball can be chosen twice. If you are choosing two balls with no replacement, there are 100*99 different combinations. If you are choosing two balls with replacement, there are 100*100 different combinations. — Gordon Linoff
– Gordon Linoff, Commented Nov 11, 2014 at 16:41
Didn't you missed the number of each color at startup ? 25 each one ? — Houari
– Houari, Commented Nov 11, 2014 at 20:47
I think I'd do this with arrays - for each colour, build a set of arrays with each combination of that colour. Then take the cartesian product of all the arrays of different combinations within a colour to get the final product, and unnest if desired. — Craig Ringer
– Craig Ringer, Commented Nov 12, 2014 at 2:11

Community · Accepted Answer · 2017-05-23 12:01:54Z

I haven't tried this in an actual postgresql server but here is an idea.

First I would codify the colors in integers:

0 = red
1 = green
2 = blue
3 = yellow

Now, for example, I want to draw 3 balls: 1 red, 1 green, and 1 green or yellow. The corresponding color codes, after sorting, will be used as a filter in the where clause of the final SQL statement:

[0, 1, 1]
[0, 1, 3]

Then the not in (...) basically ensures that there are no repeating ids, and the sorted array of colors is limited to the set that we specified above.

CREATE EXTENSION intarray;

select p1.id, p2.id, p3.id
from bowl as p1
cross join bowl as p2
cross join bowl as p3
where 
    p2.id not in (p1.id)
and p3.id not in (p1.id, p2.id)
and sort(int[p1.color, p2.color, p3.color]) in (
    int[0,1,1], 
    int[0,1,3]
)

The intarray extension is needed for the sort() function.

A variation not involving array[] nor the intarray extension is also possible as long as you list out all desired combinations of colors in the IN (..) predicate. See link.

Collectives™ on Stack Overflow

postgreSQL Combinations

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related