How to select random trial with sql? (Bernoulli Trial)
For each parent that exists I would like to select 1 child row randomly. The table structure looks like this:
parent
id
child
parent_id
id
rank
where there are 1..N child records for each parent and rank is unique per parent going from 1 to N.
The output should be like:
parent child rank
--------+-------+-------
1 34 7
2 56 8
...
With each parent producing one child row. (This will serve as a basis for Bernoulli Trials.)
(PostgreSQL has a random() function that开发者_开发百科 gives a number between 0 and 1.)
not familiar at all with postgresql, but perhaps something like this?
SELECT DISTINCT ON (parent_id) parent_id as "parent", id as "child", rank
FROM child c
ORDER BY
parent_id, random()
精彩评论