sql - SQL Server 中的随机选择
问题描述
我有两组,对于第一组中的每个值,我想应用第二组中的一些随机值。我选择的方法使用第一个选择和第二个交叉应用。一个简化的 MWE 如下:
DROP TABLE IF EXISTS #S;
CREATE TABLE #S (c CHAR(1));
INSERT INTO #S VALUES ('A'), ('B');
DROP TABLE IF EXISTS #T;
WITH idGen(id) AS (
SELECT 1
UNION ALL
SELECT id + 1 FROM idGen WHERE id < 1000
)
SELECT id INTO #T FROM idGen OPTION(MAXRECURSION 0);
DROP TABLE IF EXISTS #R;
SELECT c, id INTO #R FROM #S
CROSS APPLY (
SELECT id, ROW_NUMBER() OVER (
/*
-- this gives 100% overlap
PARTITION BY c
ORDER BY RAND(CHECKSUM(NEWID()))
*/
-- this gives the expected ~10% overlap
ORDER BY RAND(CHECKSUM(NEWID()) + CHECKSUM(c))
) AS R
FROM #T
) t
WHERE t.R <= 100;
SELECT COUNT(*) AS PercentOverlap -- ~10%
FROM #R rA JOIN #R rB
ON rB.id = rA.id AND rB.c = 'B'
WHERE rA.c = 'A';
虽然此解决方案有效,但我想知道为什么更改为(注释)分区方法不起作用?此外,使用此解决方案是否有任何警告,因为添加两个校验和感觉有点脏?
在实际问题中,第一个集合中还有一个计数,其中包含要从第二个集合中选择的随机值的数量,它替换了上面示例中的静态 100。但是,使用固定的 100 可以很容易地验证预期的重叠。
解决方案
RAND()
function 是 SQL Server 中的运行时常量。这意味着通常对查询进行一次评估。当您将值传递给RAND
该值时,该值将用作起始种子。
您需要检查执行计划,您将看到优化器将函数评估放在何处。在没有产生预期结果的情况下,最有可能优化器过于积极地优化它并将所有“随机性”移到循环之外。
NEWID()
此外,包装intoCHECKSUM()
和 into也没有意义RAND()
。简单NEWID()
就够了。或者,更好的是,设计用于产生随机数的函数,例如CRYPT_GEN_RANDOM()
您的查询的任何一个版本看起来都有点奇怪。我会这样写:
SELECT c, id INTO #R
FROM #S
CROSS APPLY
(
SELECT TOP(100) -- or #S.SomeField instead of 100
id
FROM #T
ORDER BY CRYPT_GEN_RANDOM(4) -- generate 4 random bytes, usually it is enough
) AS t
;
这为#T
中的每一行提供 100 个随机行#S
。
实际上,上面的查询并不好。优化器再次看到内部查询(内部CROSS APPLY
)不依赖于外部查询并将其优化掉。最终结果是随机行只被选择一次。
我们需要一些东西来让优化器从#S
. 一种方法是这样的:
SELECT c, id INTO #R
FROM #S
CROSS APPLY
(
SELECT TOP(100) -- or #S.SomeField instead of 100
id
FROM #T
ORDER BY CRYPT_GEN_RANDOM(4) + CHECKSUM(c)
) AS t
;
内部查询中的某些内容以引用外部查询中的行。如果你 putTOP(#S.SomeField)
而不是 constant TOP(100)
,则+ CHECKSUM(c)
不需要。
这是第一个变体的计划。您可以看到它#T
被扫描了一次(读取了 1000 行)。
这是第二个变体的计划。您可以看到它#T
被扫描了两次(读取了 2000 行)。
推荐阅读
- javascript - Chrome 扩展 google oauth 2 问题
- r - 在 r 中相交比较向量
- sql - SQL Server - 插入和更新规范(外键关系)
- vba - excel vba将文件上传到sharepoint
- python - 为什么是“\xfc\xa9\xf1\xd2Mb\x80?” == 'ü©ñÒMb\x80?' 在 Python 中是真的吗?
- php - Volley 无法与服务器连接
- c - 用A、B、C三点坐标定义一个框
- javascript - setAttribute 未更改值
- spring - Spring HAL 消费者返回空资源
- javascript - 如何从树中删除项目