Oracle中如何从comp_eval_hdr取100条随机行关联多表

你需要的是先从符合日期条件的comp_eval_hdr里随机抽100条，再关联其他表——这种方式能避免先全量关联再抽样导致的效率浪费或结果偏差，针对Oracle环境给你分场景整理了具体实现：

场景1：忽略comp_eval_dtl表 #

如果不需要comp_eval_dtl的数据，直接用下面的语句即可：

SELECT *
FROM (
    -- 先筛选符合日期范围的comp_eval_hdr，随机排序后取前100条
    SELECT *
    FROM comp_eval_hdr
    WHERE START_DATE BETWEEN TO_DATE('01-JAN-16', 'DD-MON-YY') AND TO_DATE('12-DEC-17', 'DD-MON-YY')
    ORDER BY DBMS_RANDOM.RANDOM()
    FETCH FIRST 100 ROWS ONLY
) hdr
-- 关联需要的其他表
JOIN comp_eval_pi_xref xref ON hdr.COMP_EVAL_ID = xref.COMP_EVAL_ID
JOIN core_pi pi ON pi.PI_ID = xref.PI_ID
WHERE pi.PROGRAM_CODE = 'PS';

场景2：保留comp_eval_dtl表关联 #

如果还是需要关联comp_eval_dtl，只需要在关联链里加入这张表：

SELECT *
FROM (
    SELECT *
    FROM comp_eval_hdr
    WHERE START_DATE BETWEEN TO_DATE('01-JAN-16', 'DD-MON-YY') AND TO_DATE('12-DEC-17', 'DD-MON-YY')
    ORDER BY DBMS_RANDOM.RANDOM()
    FETCH FIRST 100 ROWS ONLY
) hdr
JOIN comp_eval_dtl dtl ON hdr.COMP_EVAL_ID = dtl.COMP_EVAL_ID
JOIN comp_eval_pi_xref xref ON hdr.COMP_EVAL_ID = xref.COMP_EVAL_ID
JOIN core_pi pi ON pi.PI_ID = xref.PI_ID
WHERE pi.PROGRAM_CODE = 'PS';

适配Oracle 12c之前的版本 #

如果你的Oracle版本低于12c（不支持FETCH FIRST语法），可以用ROWNUM替代实现：

SELECT *
FROM (
    SELECT hdr.*, ROWNUM rn
    FROM (
        SELECT *
        FROM comp_eval_hdr
        WHERE START_DATE BETWEEN TO_DATE('01-JAN-16', 'DD-MON-YY') AND TO_DATE('12-DEC-17', 'DD-MON-YY')
        ORDER BY DBMS_RANDOM.RANDOM()
    ) hdr
    WHERE ROWNUM <= 100
) hdr
JOIN comp_eval_pi_xref xref ON hdr.COMP_EVAL_ID = xref.COMP_EVAL_ID
JOIN core_pi pi ON pi.PI_ID = xref.PI_ID
WHERE pi.PROGRAM_CODE = 'PS';

额外提示 #

• 用DBMS_RANDOM.RANDOM()排序能保证每次执行都得到不同的随机100条数据；
• 如果comp_eval_hdr数据量极大，想要更高效的抽样，可以试试SAMPLE(0.1)（0.1是抽样百分比，根据数据量调整），但这种方式是基于数据块的抽样，无法严格保证恰好100条，适合不需要精确数量的场景。

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