我在SQL里有这张表：

CREATE TABLE mytable 
(
    id     BIGINT,
    color  VARCHAR(10),
    food   VARCHAR(10),
    sport  VARCHAR(12),
    animal VARCHAR(10)
) DISTRIBUTE ON (id);

INSERT INTO mytable (id, color, food, sport, animal) VALUES
( 1, 'red',    'pizza',  'soccer',     'dog'),
( 2, 'blue',   'sushi',  'tennis',     'cat'),
( 3, 'green',  'tacos',  'basketball', 'horse'),
( 4, 'yellow', 'pasta',  'golf',       'rabbit'),
( 5, 'pink',   'pizza',  'swimming',   'fox'),
( 6, 'orange', 'burger', 'soccer',     'bear'),
( 7, 'black',  'ramen',  'hockey',     'wolf'),
( 8, 'purple', 'tacos',  'cricket',    'cat'),
( 9, 'white',  'salad',  'tennis',     'dog'),
(10, 'brown',  'pizza',  'running',    'deer'),
(11, 'cyan',   'curry',  'boxing',     'tiger'),
(12, 'gray',   'sushi',  'soccer',     'horse'),
(13, 'teal',   'burger', 'baseball',   'owl'),
(14, 'maroon', 'pasta',  'golf',       'dog'),
(15, 'navy',   'tacos',  'rugby',      'frog');

我想从这张表中随机选取15行（也就是带放回的抽样）。

最终结果应该大致看起来像这样：

我想到的逻辑——生成对应要被选中的行的随机编号，然后再把它们与原表连接起来：

SELECT
    g.i AS resample_id, 
    t.id AS original_id, 
    t.color, t.food,
    t.sport,
    t.animal
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY NULL) AS i,
           CAST((RANDOM() * 15) AS BIGINT) + 1 AS pick_id
    FROM mytable
    LIMIT 15
) g
JOIN mytable t ON t.id = g.pick_id
ORDER BY g.i;

这会得到空的结果。

有人能帮我看看到底是哪里出错了吗？

解决方案

我会改为使用一个独立的源来生成随机数。这样即使数据集少于15行，也能确保得到15行。

下面的查询做了以下几件事：

• 生成源数据（15行）
• 生成一个数字表（64k行）
• 生成两组各20个随机数
• 选取这些随机抽样的行

CREATE TABLE mytable (
    id     BIGINT,
    color  VARCHAR(10),
    food   VARCHAR(10),
    sport  VARCHAR(12),
    animal VARCHAR(10)
) DISTRIBUTE ON (id);
INSERT INTO mytable (id, color, food, sport, animal) VALUES
( 1, 'red',    'pizza',  'soccer',     'dog'),
( 2, 'blue',   'sushi',  'tennis',     'cat'),
( 3, 'green',  'tacos',  'basketball', 'horse'),
( 4, 'yellow', 'pasta',  'golf',       'rabbit'),
( 5, 'pink',   'pizza',  'swimming',   'fox'),
( 6, 'orange', 'burger', 'soccer',     'bear'),
( 7, 'black',  'ramen',  'hockey',     'wolf'),
( 8, 'purple', 'tacos',  'cricket',    'cat'),
( 9, 'white',  'salad',  'tennis',     'dog'),
(10, 'brown',  'pizza',  'running',    'deer'),
(11, 'cyan',   'curry',  'boxing',     'tiger'),
(12, 'gray',   'sushi',  'soccer',     'horse'),
(13, 'teal',   'burger', 'baseball',   'owl'),
(14, 'maroon', 'pasta',  'golf',       'dog'),
(15, 'navy',   'tacos',  'rugby',      'frog');

CREATE TABLE numbers (
  id BIGINT NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO
  numbers
WITH
  n(d, base, ix, val) AS
(
  VALUES
    (0, 0, 0, 0), 
    (1, 1, 0, 1)

  UNION ALL

  SELECT
    d+1, base*2, ix, base*2 + ix
  FROM
  (
    SELECT
      d, base, ix
    FROM
      n

    UNION ALL

    SELECT
      d, base, base + ix
    FROM
      n
  )
    AS double_up(d, base, ix)
  WHERE
    d BETWEEN 1 AND 15
)
SELECT
  val
FROM
  n
;

WITH
  sampler AS
(
  SELECT
    s.id AS set_id,
    n.id AS sample_id,
    FLOOR(
      RANDOM()
      *
      (SELECT COUNT(*) FROM mytable)
      +
      1
    )
      AS sample_rn
  FROM
    numbers AS s
  CROSS JOIN
    numbers AS n
  WHERE
    s.id < 2
    AND
    n.id < 20
), 
  sorted AS
(
  SELECT
    ROW_NUMBER() OVER () AS rn,
    mytable.*
  FROM
    mytable
)
SELECT
  sampler.set_id,
  sampler.sample_id,
  original.id AS original_id,
  original.color,
  original.food,
  original.sport,
  original.animal
FROM
  sampler
INNER JOIN
  sorted AS original
    ON original.rn = sampler.sample_rn
ORDER BY
  sampler.set_id,
  sampler.sample_id
;

fiddle