gpt4 book ai didi

sql - 如何使用另一个表中的随机行更新表的每一行

转载 作者:行者123 更新时间:2023-12-04 20:22:52 27 4
gpt4 key购买 nike

我正在构建我的第一个去标识化脚本,并且遇到了我的方法问题。

我有一张 table dbo.pseudonyms谁的firstname列填充了 200 行数据。这列 200 行中的每一行都有一个值(没有一个为空)。这个表还有一个id列(整数,主键,非空),数字为 1-200。

我想做的是,在一个声明中,重新填充我的整个 USERS表带 firstname从我的 pseudonyms 中为每一行随机选择的数据 table 。

要生成用于采摘的随机数,我正在使用 ABS(Checksum(NewId())) % 200 .每次做SELECT ABS(Checksum(NewId())) % 200我在我正在寻找的范围内得到一个数值就好了,没有间歇性不稳定的行为。

但是,当我在以下语句中使用此公式时:

SELECT pn.firstname 
FROM DeIdentificationData.dbo.pseudonyms pn
WHERE pn.id = ABS(Checksum(NewId())) % 200

我得到非常间歇性的结果。我会说大约 30% 的结果返回一个从表中挑选出来的名字(这是预期的结果),大约 30% 返回一个以上的结果(这令人困惑,没有重复的 id 列值),大约 30% 返回 NULL(即使 firstname 列中没有空行)

我确实为这个特定问题寻找了很长时间,但到目前为止无济于事。我假设这个问题与使用这个公式作为指针有关,但我不知道如何做到这一点。

想法?

最佳答案

为什么您在问题中的查询返回意外结果

您的原始查询选自 Pseudonyms .服务器扫描表的每一行,选择 ID从该行生成一个随机数,将生成的数与 ID 进行比较.

当特定行的生成数字碰巧与 ID 相同时在该行的结果集中返回该行。很可能偶然生成的数字永远不会与 ID 相同。 ,以及生成的数字与 ID 重合几次。

更详细一点:

  • 服务器选择一行 ID=1 .
  • 生成一个随机数,比如 25 .为什么不?一个不错的随机数。
  • 1 = 25 ?否 => 不返回该行。
  • 服务器选择一行 ID=2 .
  • 生成一个随机数,比如 125 .为什么不?一个不错的随机数。
  • 2 = 125 ?否 => 不返回该行。
  • 等等...


  • Here is a complete solution on SQL Fiddle

    样本数据
    DECLARE @VarPseudonyms TABLE (ID int IDENTITY(1,1), PseudonymName varchar(50) NOT NULL);
    DECLARE @VarUsers TABLE (ID int IDENTITY(1,1), UserName varchar(50) NOT NULL);

    INSERT INTO @VarUsers (UserName)
    SELECT TOP(1000)
    'UserName' AS UserName
    FROM sys.all_objects
    ORDER BY sys.all_objects.object_id;

    INSERT INTO @VarPseudonyms (PseudonymName)
    SELECT TOP(200)
    'PseudonymName'+CAST(ROW_NUMBER() OVER(ORDER BY sys.all_objects.object_id) AS varchar) AS PseudonymName
    FROM sys.all_objects
    ORDER BY sys.all_objects.object_id;

    Users有 1000 行相同的 UserName对于每一行。表 Pseudonyms有 200 行不同的 PseudonymNames :
    SELECT * FROM @VarUsers;
    ID UserName
    -- --------
    1 UserName
    2 UserName
    ...
    999 UserName
    1000 UserName

    SELECT * FROM @VarPseudonyms;
    ID PseudonymName
    -- -------------
    1 PseudonymName1
    2 PseudonymName2
    ...
    199 PseudonymName199
    200 PseudonymName200

    第一次尝试

    起初我尝试了一种直接的方法。对于 Users 中的每一行我想从 Pseudonyms 中随机获取一行:
    SELECT
    U.ID
    ,U.UserName
    ,CA.PseudonymName
    FROM
    @VarUsers AS U
    CROSS APPLY
    (
    SELECT TOP(1)
    P.PseudonymName
    FROM @VarPseudonyms AS P
    ORDER BY CRYPT_GEN_RANDOM(4)
    ) AS CA
    ;

    事实证明优化器太聪明了,这产生了一些随机的,但相同的 PseudonymName每个 User ,这不是我所期望的:
    ID   UserName   PseudonymName
    1 UserName PseudonymName181
    2 UserName PseudonymName181
    ...
    999 UserName PseudonymName181
    1000 UserName PseudonymName181

    因此,我稍微调整了这种方法,并为 Users 中的每一行生成了一个随机数。第一的。然后我用生成的数字找到了 Pseudonym有了这个 ID对于 Users 中的每一行使用 CROSS APPLY .
    CTE_Users有一个额外的列,随机数从 1 到 200。在 CTE_Joined 中我们从 Pseudonyms 中选择一行每个 User .
    最后我们 UPDATE原版 Users table 。

    最终解决方案
    WITH
    CTE_Users
    AS
    (
    SELECT
    U.ID
    ,U.UserName
    ,1 + 200 * (CAST(CRYPT_GEN_RANDOM(4) as int) / 4294967295.0 + 0.5) AS rnd
    FROM @VarUsers AS U
    )
    ,CTE_Joined
    AS
    (
    SELECT
    CTE_Users.ID
    ,CTE_Users.UserName
    ,CA.PseudonymName
    FROM
    CTE_Users
    CROSS APPLY
    (
    SELECT P.PseudonymName
    FROM @VarPseudonyms AS P
    WHERE P.ID = CAST(CTE_Users.rnd AS int)
    ) AS CA
    )
    UPDATE CTE_Joined
    SET UserName = PseudonymName;

    结果
    SELECT * FROM @VarUsers;
    ID UserName
    1 PseudonymName41
    2 PseudonymName132
    3 PseudonymName177
    ...
    998 PseudonymName60
    999 PseudonymName141
    1000 PseudonymName157

    SQL Fiddle

    关于sql - 如何使用另一个表中的随机行更新表的每一行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29760225/

    27 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com