snowflake-cloud-data-platform - 雪花 : How do I update a column with values taken at random from another table?

Question

我已经为此苦苦挣扎了一段时间。假设我有这两个表:

CREATE TEMPORARY TABLE tmp_target AS (
  SELECT * FROM VALUES
    ('John', 43, 'm', 17363)
  , ('Mark', 21, 'm', 16354)
  , ('Jean', 25, 'f', 74615)
  , ('Sara', 63, 'f', 26531)
  , ('Alyx', 32, 'f', 42365)
  AS target (name, age, gender, zip)
);

和

CREATE TEMPORARY TABLE tmp_source AS (
  SELECT * FROM VALUES
    ('Cory', 42, 'm', 15156)
  , ('Fred', 51, 'm', 71451)
  , ('Mimi', 22, 'f', 45624)
  , ('Matt', 61, 'm', 12734)
  , ('Olga', 19, 'f', 52462)
  , ('Cleo', 29, 'f', 23352)
  , ('Simm', 31, 'm', 62445)
  , ('Mona', 37, 'f', 23261)
  , ('Feng', 44, 'f', 64335)
  , ('King', 57, 'm', 12225)
  AS source (name, age, gender, zip)
);

我想更新 tmp_target 表，方法是从 tmp_source 表中随机取 5 行作为我感兴趣的列。例如，也许我想用 tmp_source 中的 5 个随机名字替换所有名字，或者我想替换名字 和 年龄。

我的第一次尝试是这样的:

UPDATE tmp_target t SET t.name = s.name FROM tmp_source s;

但是，当我检查目标表时，我注意到有相当多的名称是重复的，通常是成对的。同样，Snowflake 为我提供了更新的行数:5 以及 更新的多连接行数:5。我相信这是由于正在发生的事情的不确定性，可能如 Snowflake documentation on updates 中所述。更不用说我有一种挥之不去的感觉，如果表有很多记录，这在某种程度上是非常低效的。

然后我尝试从源表中随机抓取 5 行:

UPDATE tmp_target t SET t.name = cte.name
FROM (
  WITH upd AS (SELECT name FROM tmp_source SAMPLE ROW (5 ROWS))
  SELECT name FROM upd
) AS cte;

但我似乎遇到了完全相同的问题，无论是在我检查目标表时，还是在报告的多连接行数中。我想知道我是否可以以某种方式使用行编号，但是虽然我可以在子查询中生成行号，但我不知道如何在外部查询的 SET 部分中执行此操作。

我想补充一点，这两个表都没有任何可以使用的标识符或索引，我正在寻找不需要任何标识符或索引的解决方案。

如果有人能提供尽可能干净整洁的解决方案或想法，并考虑到效率(想象一个 100K 行的目标表和一个 10M 行的源表)，我将非常感激。谢谢!

Answer 1

我喜欢已经提供的两个答案，但让我给你一个简单的答案来解决简单的问题:

UPDATE tmp_target t 
SET t.name = (
        select array_agg(s.name) possible_names
        from tmp_source s
    )[uniform(0, 9, random())]
;

此解决方案的秘诀在于构建一个可能值数组，并为每个更新的行随机选择一个值。

---

更新:现在有了一个 JavaScript UDF，它可以帮助我们从源中选择每个名字一次

create or replace function incremental_thing()
returns float 
language javascript
as
$$
if (typeof(inc) === "undefined") inc = 0;
return inc++;
$$
;

UPDATE tmp_target t 
SET t.name = (
        select array_agg(s.name) within group (order by random())
        from tmp_source s
    )[incremental_thing()::integer]
;

请注意，JS UDF 每次调用时都会返回一个增量值，这有助于我从排序数组中选择下一个值以用于更新。

由于值在 JS UDF 中递增，只要只涉及一个 JS 环境，这就可以工作。为了单节点处理，避免并行选择XS仓库测试。