SQL query for generating a full list of Probability outcomes-CodePudding

Let's say that I have a table that looks something like this:

CREATE TABLE student
(
    name CHAR NOT NULL,
    Pwin numeric(5,2) NOT NULL 
)

INSERT INTO student 
VALUES ('A', 0.5), 
       ('B', 0.7), 
       ('C', 0.6)

This represents 3 students who are competing in separate events. Each of them has a probability of winning their event (Pwin). Those events are independent to each other. The probability of them losing the event is simply = 1 - Pwin.

There are 8 possible outcomes

Awin  Bwin  Cwin
Awin  Bwin  Close
Awin  Blose Cwin
Awin  Blose Close
Alose Bwin  Cwin
Alose Bwin  Close
Alose Blose Cwin
Alose Blose Close

I want to write a query that will output these probabilities. So for the example above, the output should look like

0.5 0.7 0.6
0.5 0.7 0.4
0.5 0.3 0.6
0.5 0.3 0.4
0.5 0.7 0.6
0.5 0.7 0.4
0.5 0.3 0.6
0.5 0.3 0.4

CodePudding user response：

For this sample data you can get all the possible combinations with as many joins as the number of different names in the table:

WITH cte AS (
  SELECT name, Pwin p FROM student
  UNION ALL
  SELECT name, 1 - Pwin FROM student
)
SELECT c1.p col1, c2.p col2, c3.p col3
FROM cte c1 
INNER JOIN cte c2 ON c2.name > c1.name
INNER JOIN cte c3 ON c3.name > c2.name;

See the demo.

Of course this does not scale well, performance wise, for more names.