Oracle SQL实现Bootstrap有放回抽样时连接丢失行问题求助

问题出在你用dbms_random.value生成行号的方式——numbered_rows中的ROW_NUMBER() OVER (PARTITION BY KPI_KEY ORDER BY dbms_random.value)会在每次子查询被调用时重新计算随机排序，导致行号rn不固定；同时random_numbers生成的随机数是独立的，两者的随机逻辑没有绑定，Oracle的查询优化可能会重复执行CTE子查询，最终出现连接后丢失行的情况。

修正方案 #

先给原始数据生成固定的行号（Bootstrap抽样只需要基于固定行号做有放回抽取，无需随机排序行号），然后生成随机抽样的行号列表，再关联固定行号的原始数据。同时用物化提示强制Oracle将行号结果物化，避免重复计算。

修正后的代码示例

WITH basis as (
     SELECT 'A' AS KPI_KEY, 2 AS KPI_VALUE FROM dual
    union all
    SELECT 'A' AS KPI_KEY, 5 AS KPI_VALUE FROM dual
    union all
    SELECT 'A' AS KPI_KEY, 3 AS KPI_VALUE FROM dual
), group_counts AS (
  SELECT KPI_KEY, COUNT(*) as total_count
  FROM basis
  GROUP BY KPI_KEY
), numbered_rows AS (
  /*+ MATERIALIZE */
  SELECT KPI_KEY, KPI_VALUE, 
         ROW_NUMBER() OVER (PARTITION BY KPI_KEY ORDER BY NULL) AS rn -- 用固定排序生成行号
  FROM basis
), random_numbers AS (
  SELECT gc.KPI_KEY, 
         CEIL(DBMS_RANDOM.VALUE(0, gc.total_count)) AS rand_rn
  FROM group_counts gc
  CONNECT BY LEVEL <= gc.total_count 
          AND PRIOR gc.KPI_KEY = gc.KPI_KEY 
          AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
)
SELECT rn.KPI_KEY, nr.KPI_VALUE, nr.rn
FROM random_numbers rn
LEFT JOIN numbered_rows nr 
    ON rn.KPI_KEY = nr.KPI_KEY 
    AND rn.rand_rn = nr.rn;

如果需要抽样前打乱原始数据顺序

可以先将打乱后的结果物化，再生成固定行号：

WITH basis as (
     SELECT 'A' AS KPI_KEY, 2 AS KPI_VALUE FROM dual
    union all
    SELECT 'A' AS KPI_KEY, 5 AS KPI_VALUE FROM dual
    union all
    SELECT 'A' AS KPI_KEY, 3 AS KPI_VALUE FROM dual
), shuffled_basis AS (
  /*+ MATERIALIZE */
  SELECT KPI_KEY, KPI_VALUE
  FROM basis
  ORDER BY DBMS_RANDOM.VALUE
), group_counts AS (
  SELECT KPI_KEY, COUNT(*) as total_count
  FROM shuffled_basis
  GROUP BY KPI_KEY
), numbered_rows AS (
  SELECT KPI_KEY, KPI_VALUE, 
         ROW_NUMBER() OVER (PARTITION BY KPI_KEY ORDER BY NULL) AS rn
  FROM shuffled_basis
), random_numbers AS (
  SELECT gc.KPI_KEY, 
         CEIL(DBMS_RANDOM.VALUE(0, gc.total_count)) AS rand_rn
  FROM group_counts gc
  CONNECT BY LEVEL <= gc.total_count 
          AND PRIOR gc.KPI_KEY = gc.KPI_KEY 
          AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
)
SELECT rn.KPI_KEY, nr.KPI_VALUE, nr.rn
FROM random_numbers rn
LEFT JOIN numbered_rows nr 
    ON rn.KPI_KEY = nr.KPI_KEY 
    AND rn.rand_rn = nr.rn;

关键要点 #

• 用/*+ MATERIALIZE */提示强制Oracle将CTE结果物化，避免重复执行时重新计算随机值
• 给原始数据分配固定行号（基于稳定的排序，比如ORDER BY NULL或原始数据的唯一键），确保关联时行号不变化
• 随机抽样的行号基于固定的总行数生成，和固定行号关联即可实现有放回抽样

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