从数据库中按权重随机检索项目

Question

假设我有一张记录满满的表，我想从中随机获取记录。然而，我希望该表中的某些行比其他行更频繁地出现（这些行因用户而异）。使用SQL，最好的方法是什么？

我能想到的唯一方法是创建一个临时表，将我想要更常见的行填入其中，然后用来自该表的其他随机选择的行进行填充。是否有更好的方法？

- Paul

Answer 1

我能想到的一种方法是在表格中创建另一列，用于存储权重的累计和，然后通过生成一个介于0和所有权重总和之间的随机数来获取记录，并获取累计和数值小于该随机数的最高行。

例如，如果您有以下四行权重：

+---+--------+------------+
|row| weight | rollingsum |
+---+--------+------------+
| a |      3 |          3 |
| b |      3 |          6 |
| c |      4 |         10 |
| d |      1 |         11 |  
+---+--------+------------+

然后，选择0到11之间（包括0和11）的随机数n，并在0<=n<3时返回行a，在3<=n<6时返回b，以此类推。这里有一些生成滚动总和的链接：http://dev.mysql.com/tech-resources/articles/rolling_sums_in_mysql.html，http://dev.mysql.com/tech-resources/articles/rolling_sums_in_mysql_followup.html。

- Marquis Wang

---

哇，太棒了！谢谢，看起来这是最好的方法。我需要结合 Shiraz 建议的视图，因为我想让每个用户的权重不同（这会有问题吗？），但除此之外，这应该可以很好地工作。 - Paul

Answer 2

我不知道仅使用 SQL 是否能轻松完成此操作。使用 T-SQL 或类似语言，您可以编写循环以复制行，或者可以使用 SQL 生成执行行复制的 指令。

我不知道您的概率模型是什么，但您可以使用这种方法来实现后者。假设有以下表定义：

RowSource
---------
RowID

UserRowProbability
------------------
UserId
RowId
FrequencyMultiplier

你可以编写类似以下的查询（特定于SQL Server）：

SELECT TOP 100 rs.RowId, urp.FrequencyMultiplier
FROM RowSource rs
  LEFT JOIN UserRowProbability urp ON rs.RowId = urp.RowId
ORDER BY ISNULL(urp.FrequencyMultiplier, 1) DESC, NEWID()

这将负责选择一组随机行以及应重复多少次。然后，在您的应用程序逻辑中，您可以执行行复制并对结果进行洗牌。

- Jacob

Answer 3

从3个表users、data和user-data开始。User-data包含每个用户应优先选择哪些行。

然后创建一个基于用户优选数据行的视图。

创建第二个视图，其中包含未优选的数据。

创建第三个视图，它是前两个视图的联合。联合应从优选数据中选择更多行。

最后从第三个视图中随机选择行。

- Shiraz Bhaiji