sql - 从具有加权行概率的 PostgreSQL 表中选择随机行

Question

示例输入:

SELECT * FROM test; id | percent   ----+----------  1 | 50   2 | 35     3 | 15   (3 rows)

你会如何编写这样的查询，平均 50% 的时间我可以获得 id=1 的行，35% 的时间 id=2 的行，15% 的时间 id=3 的行？

我尝试了类似 SELECT id FROM test ORDER BY p * random() DESC LIMIT 1 的方法，但它给出了错误的结果。运行 10,000 次后，我得到如下分布:{1=6293, 2=3302, 3=405}，但我预计分布接近:{1=5000, 2=3500 , 3=1500}.

有什么想法吗？

Answer 1

这应该可以解决问题:

WITH CTE AS (
    SELECT random() * (SELECT SUM(percent) FROM YOUR_TABLE) R
)
SELECT *
FROM (
    SELECT id, SUM(percent) OVER (ORDER BY id) S, R
    FROM YOUR_TABLE CROSS JOIN CTE
) Q
WHERE S >= R
ORDER BY id
LIMIT 1;

子查询 Q 给出以下结果:

1  50
2  85
3  100

然后我们简单地生成一个 [0, 100) 范围内的随机数，并选择等于或超过该数字的第一行(WHERE 子句)。我们使用公用表表达式(WITH)来确保随机数只计算一次。

顺便说一句，SELECT SUM(percent) FROM YOUR_TABLE 允许您在 percent 中设置任何权重 - 它们不一定必须是百分比(即加起来为 100)。

[SQL Fiddle]