输入示例:
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}
。
有什么想法吗
7条答案
按热度按时间vlf7wbxs1#
这应该可以达到目的:
子查询
Q
给出以下结果:然后,我们简单地生成一个范围[0,100]内的随机数,并选择等于或超过该数字的第一行(
WHERE
子句)。我们使用公共表表达式(WITH
)来确保随机数只计算一次。SELECT SUM(percent) FROM YOUR_TABLE
允许您在percent
中拥有任何权重-它们不需要严格地是百分比(i.即加起来为100)。*[SQL小提琴]
inn6fuwd2#
(1).0 / p)
由Efraimidis和Spirakis描述的算法。
dgsult0t3#
Branko接受的解决方案是伟大的(谢谢!然而,我想提供一个性能一样好的替代方案(根据我的测试),而且可能更容易可视化。
让我们回顾一下。原来的问题也许可以概括如下:
给定一个id和相对权重的Map,创建一个查询,返回Map中的随机id,但概率与其相对权重成正比。
请注意,重点是相对权重,而不是百分比。正如布兰科在他的回答中指出的那样,使用相对权重对任何事情都有效,包括百分比。
现在,考虑一些测试数据,我们将把它们放在一个临时表中:
请注意,我使用了一个比原来问题中更复杂的例子,因为它 * 不 * 方便地加起来为100,并且 * 相同的权重 *(20)被使用 * 多次 *(对于id 2和3),这是很重要的考虑因素,正如您稍后将看到的那样。
我们要做的第一件事是将权重转换为从0到1的概率,这只不过是一个简单的归一化(weight / sum(weights)):
这将导致以下输出:
当然,上面的查询做的工作比我们的需求严格必要的要多,但我发现以这种方式可视化相对概率很有帮助**,而且它确实使选择id的最后一步变得微不足道:
现在,让我们将所有这些与一个测试结合在一起,该测试确保查询返回的数据具有预期的分布。我们将使用
generate_series()
生成一个随机数百万次:这将产生类似于以下内容的输出:
如你所见,它完美地跟踪了预期的分布。
性能
上面的查询非常高效。即使在我的普通机器上,PostgreSQL运行在WSL 1示例中(太可怕了!),执行速度相对较快:
适配生成测试数据
在为单元/集成测试生成测试数据时,我经常使用上述查询的变体。其想法是生成随机数据,近似于跟踪现实的概率分布。
在这种情况下,我发现计算开始和结束分布一次并将结果存储在表中很有用:
然后,我可以重复使用这些预先计算的概率,这会带来额外的性能和更简单的使用。
我甚至可以将其全部 Package 在一个函数中,我可以在任何时候调用它来获取随机id:
窗口功能框
值得注意的是,上面的技术使用了一个非标准框架
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
的窗口函数。这对于处理某些权重可能重复的事实是必要的,这就是为什么我首先选择具有重复权重的测试数据!mwecs4sa4#
您建议的查询似乎有效;看这个SQLFiddle演示。它创建了错误的分布;见下文。
为了防止PostgreSQL优化子查询,我将它 Package 在一个
VOLATILE
SQL函数中。PostgreSQL没有办法知道你打算让子查询对外部查询的每一行运行一次,所以如果你不强制它volatile,它只会执行一次。另一种可能性-尽管查询规划器可能会在未来优化-是使其看起来像是一个相关的子查询,就像这个使用always-true where子句的技巧,如下所示:http://sqlfiddle.com/#!12/3039b/9猜测(在你更新解释为什么它不工作之前)你的测试方法有问题,或者你在外部查询中使用这个子查询,PostgreSQL注意到它不是一个相关的子查询,并只执行一次,就像这个例子一样。.
**更新:**生成的发行版与您所期望的不一样。这里的问题是你通过对
random()
进行 * 多个样本 * 来扭曲分布;你需要一个 * 单一 * 样本。此查询生成正确的分布(SQLFiddle):
不用说,性能是可怕的。它使用两套嵌套的窗口。我在做的是:
8dtrkrch5#
这里有一些东西供你玩:
本质上是执行左外联接,以便有两列应用between子句。
请注意,只有当您以正确的方式排序您的table时,它才能工作。
vfhzx4xs6#
基于Branko Dimitrijevic的回答,我编写了这个查询,通过使用分层窗口函数(与
ROLLUP
不同)使用percent
的总和可能会更快,也可能不会更快。如果排序并不重要,
SUM(percent) OVER (ROWS UNBOUNDED PRECEDING) AS rank,
可能更好,因为它避免了首先对数据进行排序。我也试了一下魏技师的答案(as described in this paper, apparently),在性能上看起来很有希望,但经过一些测试,分布似乎是关闭的:
nbewdwxp7#
从this paper注意,我们必须计算
random() ^ (-1.0 / p)
(***减去***1)。SQLFiddle示例将为您提供:
完整代码
架构
查询