0

I am not even sure if this is a possibility so my apologies if I'm barking up the wrong tree; I am ignorant of Postgres's limitations as I am self-taught.

This is hypothetical, but if I had a table like so:

dice_rolls

roll_1 roll_2 sum
1 1 2
1 2 3
1 3 4
1 4 5
1 5 6
1 6 7
2 1 3
2 2 4
2 3 5
2 4 6
2 5 7
2 6 8
3 1 4
3 2 5
3 3 6
3 4 7
3 5 8
3 6 9
4 1 5
4 2 6
4 3 7
4 4 8
4 5 9
4 6 10
5 1 6
5 2 7
5 3 8
5 4 9
5 5 10
5 6 11
6 1 7
6 2 8
6 3 9
6 4 10
6 5 11
6 6 12

we can see that the value '7' for sum appears most frequently. If we plotted the frequency distribution of the sum, it would look something like this:

               x
            x  x  x
         x  x  x  x  x  
      x  x  x  x  x  x  x 
   x  x  x  x  x  x  x  x  x
x  x  x  x  x  x  x  x  x  x  x

2  3  4  5  6  7  8  9  10 11 12

Therefore, we can assume that a truly random selection of this dataset will follow this distribution pattern. What I'm interested in is psuedo-random DISTINCT selection of N rows that attempts to normalize the distribution based on the sum column.

E.g.,

SELECT * FROM dice_rolls ORDER BY RANDOM() LIMIT 5;

is what I have now. This query is most likely to contain rows where the sum is 5, 6, 7, 8, 9. How would I go about using a custom ORDER BY function that would be significantly more likely (or even strictly) to, return rows where the sum has an even chance of being 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, or 12?

1 Answer 1

0

Get a CTE with the different sums, pick one and return a matching row:

WITH sums AS (
   SELECT DISTINCT dice_sum FROM dice_rolls
)
SELECT * FROM dice_rolls
WHERE dice_sum = (SELECT dice_sum FROM sums ORDER BY random() LIMIT 1)
ORDER BY random() LIMIT 1;
1
  • If I were to change the LIMIT 1 in both places to LIMIT 5, will 5 dice_sums come back that are distinct using WHERE IN instead? Or each with an even chance of being selected? No, right? I think maybe I am looking for a more complicated WHERE clause in this case? I'm going to keep researching; I'll accept this answer if I don't find anything else as this does return at least one row, thanks!
    – PoorBob
    Commented Jan 21, 2021 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.