根据参数在数据库中查找固定数量的 RANDOM 用户的最佳方法是什么？

Question

我正在 Kotlin 中开发 Spring Boot REST API。底层数据库是 Postgresql，我正在使用 Spring Data JPA 进行数据库访问。

我有一个名为“用户”的表，其中有一些用户数据。用户属性之一是“性别”。它可以具有以下两个值之一：MALE 或 FEMALE。

我想在我的应用程序中有一个功能来找到我以前从未见过的特定性别的人的随机数（例如 20）。我的意思是 - 假设我有一个表，我在其中存储我已经看到的用户的 ID。

所以现在，我想做的基本上是从 Users 表中获取 20 个随机用户，其中性别为 MALE 并且 id 不在 [我看到的 ids 列表] 中。

查询的随机性最初让我创建了一个原生查询：

SELECT *  FROM users WHERE gender = :gender ORDER BY random() LIMIT :number

但是，我意识到这可能非常低效，因为该order by random()部分将对整个表格进行排序（如果我选择一种性别，则为表格的一半）。

所以我的第二个想法是处理代码中的随机性。所以我决定调用 db 来计算用户数量（以获取最高的 id），然后生成一些 id 值，范围从 0 到最高，过滤掉我看到的那些，然后从中获取用户数据库 ID：

val numberOfUsersInDatabase = userRepository.count()
    val idsOfUsersVotedForBefore = voteService.findIdsOfUsersVotedFor(requestingUser.id!!)    val excludedIds = idsOfUsersVotedForBefore.plus(requestingUser.id)    val idsToFetch = random.longs(2*amountOfIds, 1L, numberOfUsersInDatabase)
            .boxed()
            .filter { num -> !excludedIds.contains(num) }
            .limit(amountOfIds)
            .collect(toSet())   val randomUsers = userRepository.findUsersByIds(idsToFetch)

但在这种情况下，我无法知道随机选择的用户的性别是什么，所以我不可能在进行数据库调用之前按性别过滤结果。

你能建议如何更好地解决这个问题吗？

Answer 1

我并不完全熟悉 Kotlin 语言，但我会用 Java 写出逻辑，希望它能很好地为您翻译。你的目标是获取 20 个单一性别，但在获取它之前你无法弄清楚它是什么性别。因为我们已经从数据库中获取了 20 行，所以我们可以获取更多作为备用。我们可以使用累积分布来计算我们实际需要的行数，使用这个工具：https://stattrek.com/online-calculator/binomial.aspx假设 50/50 性别细分，概率为 0.5。如果您的性别分布与您的需求不同，您可以调整此项。如果性别细分不是 50/50，您可以为每个性别创建单独的桶提取以获得适当的成功水平。我们希望至少有 20 场成功的比赛。样本大小为 60，我们有99.6% 的概率有 20 次或更多的性别匹配。所以我们可以获取 60 个而不是 20 个，筛选出所选性别的前 20 个。如果我们没有达到 20（0.4% 的机会），则重新绘制另一组 20 来填充我们的组。所以 99% 的时间，60 行提取，在一个坏的情况下，可能是 80 行提取。这消除了在应该适用于超大型数据库的数据库端使用 RAND 的情况。Set<Long> idsToFetch = random.longs(2*amountOfIds, 1L, numberOfUsersInDatabase)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .boxed()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .filter { num -> !excludedIds.contains(num) }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .limit(amountOfIds * 3)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .collect(toSet());List<User> randomUsers = userRepository.findUsersByIds(idsToFetch);List<User> selectedUsers = randomUsers&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .stream()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .filter(e -> e.gender == selectedGender)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .limit(amountOfIds)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .collect(toList());if(selectedUsers.length < amountOfIds) {&nbsp;&nbsp; &nbsp; //redo or single fetch operation}

Answer 2

在你的条件下选择10万个id.内存中大约几MB的数据.just shuffle it.thenselect * from tables in(id1,id2...,id20)

Answer 3

在您的帖子中使用查询的物化视图怎么样。它可以安排在您选择的时间表刷新（使用 cron 作业或 Postgres 提供的其他工具）

Answer 4

不要生成 id，而是生成行索引。然后在一个循环中你可以这样做select top 1 start at :randomBase *from users where gender = :gender