In a previous article (Bringing APEX and PL/SQL to D&D) I used a package with formulas of predetermined distributions for rolling multiple dice. In this article I will show how I came up with those formulas.
In each case of rolling multiple 20-sided dice, I didn’t know before hand what the results would be; but I assumed there would be patterns I could write as expressions. If I was wrong about emerging patterns, or if the patterns were too complex, then hard-coded arrays for each of the results 1 to 20 would be my “Plan B”. I’ll examine each of the possible ways in order of increasing complexity of using more dice.
Rolling 1 die
This is the simplest case. I roll 1d20 (that is, one die with 20 faces numbered 1-20.) There is only one way to roll a 1, only one way to roll a 2, only one way to roll a 3, and so on, up to 20 which only has one way to roll it. Thus there are exactly 20 possible combinations of rolling that one die. That’s pretty simple. I just total up the 20 results and then divide by 20 to get the mean result of rolling the one die. SQL wasn’t necessary for this. With only one roll, there is, obviously, only one way to get each result.
Rolling 2 dice
Rolling with Advantage in D&D means you get two chances at your roll. So you roll twice and keep the better of the two results. I don’t need or want a random number generator for this. Instead I use SQL to generate all possible combinations. Many times with SQL you try to avoid Cartesian joins, but that is exactly what we need for this. A simple recursive query will generate the results 1-20 of rolling a single die.
SELECT LEVEL n FROM DUAL CONNECT BY LEVEL <= 20
Then, using a cross join to combine every roll of that die with every roll of another die creates the list of results we need, and using GREATEST we can pick the better result from each pair of possible die rolls.
SQL> WITH r AS 2 ( SELECT LEVEL n 3 FROM DUAL 4 CONNECT BY LEVEL <= 20) 5 SELECT x, y, GREATEST(x, y) result 6 FROM ( 7 SELECT x.n x, y.n y 8 FROM r x CROSS JOIN r y 9 ORDER BY x, y 10 ) 11* ORDER BY result, x, y; X Y RESULT ____ ____ _________ 1 1 1 1 2 2 2 1 2 2 2 2 1 3 3 2 3 3 3 1 3 3 2 3 3 3 3 1 4 4 . . . 20 18 20 20 19 20 20 20 20
So you can see there is only one way to get a result of 1 - we must roll a 1 on both dice. To get a 2 we have three ways: a 1 on the first die and a 2 on the second, a 2 on the first die and a 1 on the second, or rolling 2 on both. As the result values grow, the number of combinations that can produce each result also increase. Counting the number of combinations for each will show us the pattern.
SQL> WITH
2 r
3 AS
4 ( SELECT LEVEL n
5 FROM DUAL
6 CONNECT BY LEVEL <= 20)
7 SELECT result, COUNT(*) cnt
8 FROM (
9 SELECT x, y, GREATEST(x, y) result
10 FROM (
11 SELECT x.n x, y.n y
12 FROM r x CROSS JOIN r y
13 ORDER BY x, y
14 )
15 )
16 GROUP BY result
17* ORDER BY result;
RESULT CNT
_________ ______
1 1
2 3
3 5
4 7
5 9
6 11
7 13
8 15
9 17
10 19
11 21
12 23
13 25
14 27
15 29
16 31
17 33
18 35
19 37
20 39
The number of possible combinations grows by two each time, starting with one. This pattern can be calculated as (2n-1) where "n" is the final result.
Rolling with Disadvantage is the same process as above except you must use the lower of the two die rolls. This produces the same pattern but in reverse. Using LEAST instead of GREATEST to select the lower result.
SQL> WITH
2 r
3 AS
4 ( SELECT LEVEL n
5 FROM DUAL
6 CONNECT BY LEVEL <= 20)
7 SELECT result, COUNT(*) cnt
8 FROM (
9 SELECT x, y, LEAST(x, y) result
10 FROM (
11 SELECT x.n x, y.n y
12 FROM r x CROSS JOIN r y
13 ORDER BY x, y
14 )
15 )
16 GROUP BY result
17* ORDER BY result;
RESULT CNT
_________ ______
1 39
2 37
3 35
4 33
5 31
6 29
7 27
8 25
9 23
10 21
11 19
12 17
13 15
14 13
15 11
16 9
17 7
18 5
19 3
20 1
This descending pattern of combinations can be represented as (41-2n).
The last way my chart would use 2 dice is to roll with the Lucky feature. Lucky is like a weaker version of Advantage. You only get to use the second die if the first die is a 1. So, the only way to get a final result of 1 is to roll a 1 on both the first and the second die. For all other results, you have 21 ways to get each of them. 20 where you roll the first die and get a 2 or higher and then you ignore the second die. Plus 1 more from rolling a 1 on the first die and then using the second die. We can use SQL to confirm this. Instead of LEAST or GREATEST, I use a CASE statement to use the reroll for a 1 on the first, but keep the first die for all other rolls. Then, as before, simply count how many times each result shows up from the 400 possible pairs.
SQL> WITH
2 r
3 AS
4 ( SELECT LEVEL n
5 FROM DUAL
6 CONNECT BY LEVEL <= 20)
7 SELECT result, COUNT(*) cnt
8 FROM (
9 SELECT x, y,
10 CASE WHEN x = 1 THEN y
11 ELSE x
12 END result
13 FROM (
14 SELECT x.n x, y.n y
15 FROM r x CROSS JOIN r y
16 ORDER BY x, y
17 )
18 )
19 GROUP BY result
20* ORDER BY result;
RESULT CNT
_________ ______
1 1
2 21
3 21
4 21
5 21
6 21
7 21
8 21
9 21
10 21
11 21
12 21
13 21
14 21
15 21
16 21
17 21
18 21
19 21
20 21
Rolling 3 dice
To roll three dice means we have the Lucky feature as well as rolling with either Advantage or Disadvantage for a total of 8000 possible combinations. These features have special rules for their interaction. Lucky lets you reroll a 1, but if you are rolling more than one die, you can only apply Lucky to one of them. So, in the unlikely event you roll 1 on both of your first two dice, you're still going to be stuck with one of them after the Lucky reroll. I handle that logic similarly to how Lucky with two dice, I use CASE statements. If the first die (x) is 1 then use the Lucky die. If the second die (y) is 1 then use the Lucky die for it, but only if the first die was not also a 1.
CASE WHEN x = 1 THEN lucky ELSE x END final_x,
CASE WHEN y = 1 AND x != 1 THEN lucky ELSE y END final_y
After the three die rolls are resolved with or without Lucky as needed, then for Advantage, return the GREATEST of the two final_x and final_y values and then, as with all of the others, count the number of combinations that will produce each result.
SQL> WITH
2 r
3 AS
4 ( SELECT LEVEL n
5 FROM DUAL
6 CONNECT BY LEVEL <= 20)
7 SELECT GREATEST(final_x, final_y) result, COUNT(*)
8 FROM (SELECT x,
9 y,
10 lucky,
11 CASE WHEN x = 1 THEN lucky ELSE x END final_x,
12 CASE WHEN y = 1 AND x != 1 THEN lucky ELSE y END final_y
13 FROM (
14 SELECT x.n x, y.n y, z.n lucky
15 FROM r x
16 CROSS JOIN r y
17 CROSS JOIN r z
18 ))
19 GROUP BY GREATEST(final_x, final_y)
20* ORDER BY result;
RESULT COUNT(*)
_________ ___________
1 1
2 25
3 69
4 113
5 157
6 201
7 245
8 289
9 333
10 377
11 421
12 465
13 509
14 553
15 597
16 641
17 685
18 729
19 773
20 817
This pattern is a little tricky. Rolling a 1 is a special condition. There is only one way to do that, each of three dice must be a 1. For all other results, 2 through 20, they grow with the pattern (44n-63). So, for 2 there are 44*2-63 = 88-63=25 combinations. For 3 there are 44*3-63=132-63=69 combinations, and so on.
When Disadvantage is combined with Lucky we calculate it the same way as above except taking the LEAST of the final_x and final_y values. This produces the most odd of all distributions with 1 again being a special case. It is the second least-common result due to Lucky even with Disadvantage forcing you to pick the lowest rolls.
SQL> WITH
2 r
3 AS
4 ( SELECT LEVEL n
5 FROM DUAL
6 CONNECT BY LEVEL <= 20)
7 SELECT LEAST(final_x, final_y) result, COUNT(*)
8 FROM (SELECT x,
9 y,
10 lucky,
11 CASE WHEN x = 1 THEN lucky ELSE x END final_x,
12 CASE WHEN y = 1 AND x != 1 THEN lucky ELSE y END final_y
13 FROM (
14 SELECT x.n x, y.n y, z.n lucky
15 FROM r x
16 CROSS JOIN r y
17 CROSS JOIN r z
18 ))
19 GROUP BY LEAST(final_x, final_y)
20* ORDER BY result;
RESULT COUNT(*)
_________ ___________
1 58
2 814
3 770
4 726
5 682
6 638
7 594
8 550
9 506
10 462
11 418
12 374
13 330
14 286
15 242
16 198
17 154
18 110
19 66
20 22
Here again, we'll handle 1 as a special case with a count of 58 possible combinations. For 2 through 20, we see they follow a pattern of (902-44n) combinations for each result. So, to roll a 2, there are 902-44*2 = 902-88 = 814 possible ways to get that result. With a 20 having 902-44*20= 902-880 = 22 possible combinations. That is 20 combinations where both of the first two dice are 20 and thus don't use the Lucky die. Plus 2 more combinations where one of the first two is a 20 and the other is a 1 with the Lucky die being a 20 replacing the 1.
With that, all 6 possible combinations of rolling possibilities are now covered. This hopefully makes understanding the package in the previous article easier to understand; as well as showing a framework for how to do an analysis of other discrete distributions.
I hope you found this helpful and interesting. Questions and comments, as always, are welcome.