SQL Developer中按Lot各抽取10%数据的实现方案咨询（仅SELECT权限）

针对每个Lot抽取10%随机记录的需求，基于你的现有SQL，提供两种无需创建表/视图的查询实现方案：

方式一：近似随机抽样（高效简洁） #

利用DBMS_RANDOM.VALUE()生成0-1区间的随机数，对每个Lot筛选随机数≤0.1的记录，实现近似10%的抽样，性能更优，适合大数据量场景：

SELECT *
FROM (
    SELECT  
        DISTINCT(LPEL_PRO_PROPREF)....,
        ROW_NUMBER() OVER (Partition by "Value" Order By "Value" desc) as "row_num",
        lpel_fat_code as "Lot",
        DBMS_RANDOM.VALUE() as "Random_Val" -- 生成0到1之间的随机数
    FROM table1
    JOIN table2 on table1.value = table2.value -- 修正原SQL表名拼写错误
    JOIN table3 on table2.value = table3.value
    WHERE lpel_fat_code LIKE '%LOTS%' -- 修正原SQL引号闭合错误
) t
WHERE Random_Val <= 0.1

方式二：精确抽取10%记录 #

若需要严格抽取每个Lot的10%记录（可选择向上/向下取整），通过窗口函数计算每个Lot的总记录数，再筛选组内排名在前10%的记录：

SELECT *
FROM (
    SELECT  
        DISTINCT(LPEL_PRO_PROPREF)....,
        ROW_NUMBER() OVER (Partition by "Value" Order By "Value" desc) as "row_num",
        lpel_fat_code as "Lot",
        -- 按Lot随机排序，生成组内排名
        ROW_NUMBER() OVER (PARTITION BY lpel_fat_code ORDER BY DBMS_RANDOM.VALUE) as Lot_Row_Num,
        -- 计算每个Lot的总记录数
        COUNT(*) OVER (PARTITION BY lpel_fat_code) as Lot_Total_Count
    FROM table1
    JOIN table2 on table1.value = table2.value
    JOIN table3 on table2.value = table3.value
    WHERE lpel_fat_code LIKE '%LOTS%'
) t
WHERE Lot_Row_Num <= CEIL(Lot_Total_Count * 0.1) -- CEIL实现向上取整，若需向下取整用FLOOR

额外优化建议 #

如果仅针对LOTS1到LOTS6这6个特定Lot抽样，可将WHERE子句改为明确匹配，提升查询效率：

WHERE lpel_fat_code IN ('LOTS1', 'LOTS2', 'LOTS3', 'LOTS4', 'LOTS5', 'LOTS6')

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