Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE if not EXISTS scores (
  id int,
  class char,
  score float
);

INSERT INTO scores VALUES
(1, 'A', 0.5),
(1, 'B', 0.2),
(1, 'C', 0.1),
(2, 'A', 0.1),
(2, 'B', 0.2),
(3, 'D', 0.01),
(4, 'A', 0.5),
(4, 'B', 0.5);
8 rows affected

select s.*
from (select s.*, 
             sum(score) over (partition by id order by class) / sum(score) over (partition by id) as threshold_hi,
             (sum(score) over (partition by id order by class) - score) / sum(score) over (partition by id) as threshold_lo
      from scores s
     ) s join
     (select i.id, random() as rand
      from (select distinct id from scores) i
     ) i
     on i.id = s.id and i.rand between s.threshold_lo and s.threshold_hi


id
class
score
threshold_hi
threshold_lo


1
C
0.1
1
0.875

2
B
0.2
1
0.33333333333333337

3
D
0.01
1
0

4
B
0.5
1
0.5


select  id, random()
from (select distinct id from scores s) i


id
random


3
0.5844478924053931

4
0.4951404451956698

2
0.029418228468678365

1
0.24607900753223433

id	random
3	0.5844478924053931
4	0.4951404451956698
2	0.029418228468678365
1	0.24607900753223433