R结合SQL执行随机抽样默认是否为有放回抽样？如何实现有放回抽样

你当前使用的ORDER BY RANDOM() LIMIT 30写法执行的是无放回抽样。
该逻辑的本质是将符合WHERE条件的所有记录随机打乱顺序后，从头截取前30条返回，单条记录最多只会被选中1次。如果指定的LIMIT数值大于分组内的总记录数（比如从50条setosa中抽60条），该语句最多只能返回分组内实际存在的50条记录，无法满足抽样量要求，也能佐证这是无放回逻辑。

SQLite没有内置的有放回抽样语法，可通过以下两种方式实现需求，优先推荐纯服务端执行的方案，性能更高：

• 方案1：纯SQL服务端实现（推荐）
  实现逻辑：先给目标分组内的所有记录分配唯一行号，再生成指定数量的、可重复的随机行号（范围为1到分组总记录数），最后通过关联匹配取出对应行号的记录，因为随机行号可重复，单条记录可被多次抽取，完全符合有放回抽样规则。
  完整代码如下：

# 抽取30条setosa有放回样本
setosa_sample <- dbGetQuery(con, "
WITH setosa_pool AS (
  SELECT *, ROW_NUMBER() OVER () AS row_id FROM iris WHERE Species = 'setosa'
),
random_row_ids AS (
  SELECT ABS(RANDOM()) % (SELECT COUNT(*) FROM setosa_pool) + 1 AS target_rid
  FROM generate_series(1, 30)
)
SELECT pool.* FROM setosa_pool pool
INNER JOIN random_row_ids rids ON pool.row_id = rids.target_rid
")

# 抽取30条virginica有放回样本
virginica_sample <- dbGetQuery(con, "
WITH virginica_pool AS (
  SELECT *, ROW_NUMBER() OVER () AS row_id FROM iris WHERE Species = 'virginica'
),
random_row_ids AS (
  SELECT ABS(RANDOM()) % (SELECT COUNT(*) FROM virginica_pool) + 1 AS target_rid
  FROM generate_series(1, 30)
)
SELECT pool.* FROM virginica_pool pool
INNER JOIN random_row_ids rids ON pool.row_id = rids.target_rid
")

# 合并最终抽样结果
final_sample <- rbind(setosa_sample, virginica_sample)

该方案不受抽样量限制，哪怕需要抽取的样本量大于分组内原始记录数（比如从50条setosa中抽100条）也能正常返回结果。

• 方案2：R侧生成随机ID后查询
  逻辑更简单，先查询分组总记录数，在R侧生成有放回的随机行号，再拼接SQL到库中匹配对应记录：

# 先查询两个目标分组的总记录数
group_info <- dbGetQuery(con, "
SELECT Species, COUNT(*) AS total_cnt FROM iris
WHERE Species IN ('setosa', 'virginica')
GROUP BY Species
")

# 生成两组有放回随机行号
setosa_rids <- sample(
  x = 1:group_info$total_cnt[group_info$Species == "setosa"],
  size = 30,
  replace = TRUE
)
virginica_rids <- sample(
  x = 1:group_info$total_cnt[group_info$Species == "virginica"],
  size = 30,
  replace = TRUE
)

# 注意：这里不能直接用IN匹配，否则重复ID只会返回1次，需要用JOIN关联保留重复行
setosa_sample <- dbGetQuery(con, paste0("
WITH setosa_pool AS (
  SELECT *, ROW_NUMBER() OVER () AS row_id FROM iris WHERE Species = 'setosa'
),
id_list AS (
  SELECT ", paste(setosa_rids, collapse = " AS rid UNION ALL SELECT "), " AS rid
)
SELECT pool.* FROM setosa_pool pool
INNER JOIN id_list ON pool.row_id = id_list.rid
"))

virginica_sample <- dbGetQuery(con, paste0("
WITH virginica_pool AS (
  SELECT *, ROW_NUMBER() OVER () AS row_id FROM iris WHERE Species = 'virginica'
),
id_list AS (
  SELECT ", paste(virginica_rids, collapse = " AS rid UNION ALL SELECT "), " AS rid
)
SELECT pool.* FROM virginica_pool pool
INNER JOIN id_list ON pool.row_id = id_list.rid
"))

final_sample <- rbind(setosa_sample, virginica_sample)

踩坑提示：如果直接用WHERE rid IN (...)的写法匹配随机行号，重复出现的行号只会返回1条对应记录，会导致抽样结果不符合有放回要求，必须通过关联逐行匹配ID列表的方式保留重复抽取的记录。

内容的提问来源于stack exchange，提问作者stats_noob