PostgreSQL按规则采样正负样本：每3行选取的SQL查询问题

核心思路 #

利用窗口函数ROW_NUMBER()对正负样本分别按指定顺序排序并编号，筛选出编号为3的倍数的记录，实现“每第3个采样”的需求。

完整查询语句 #

-- 合并正负样本结果
SELECT image_id, score, 'positive' AS sample_type
FROM (
    -- 正样本：score降序排列，取每第3个
    SELECT 
        image_id,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
    FROM unlabeled_image_predictions
    -- 可选：过滤仅保留接近1的样本，阈值可按需调整
    WHERE score >= 0.7
) pos_samples
WHERE row_num % 3 = 0

UNION ALL

SELECT image_id, score, 'negative' AS sample_type
FROM (
    -- 负样本：score升序排列，取每第3个
    SELECT 
        image_id,
        score,
        ROW_NUMBER() OVER (ORDER BY score ASC) AS row_num
    FROM unlabeled_image_predictions
    -- 可选：过滤仅保留接近0的样本，阈值可按需调整
    WHERE score <= 0.3
) neg_samples
WHERE row_num % 3 = 0;

关键说明 #

• 窗口函数的作用：ROW_NUMBER()会严格按照排序后的顺序为每条记录分配唯一序号，确保采样的顺序性。如果需要将相同score的样本视为同一组（避免因排序波动导致采样偏差），可替换为RANK()或DENSE_RANK()。
• 采样规则调整：若你需要的是“从第1个开始每3个取1个”（即第1、4、7...条），只需将WHERE row_num % 3 = 0改为WHERE row_num % 3 = 1即可。
• 阈值过滤：WHERE score >= 0.7和WHERE score <= 0.3是可选条件，用于排除明显不接近1或0的中间样本，可根据业务需求调整阈值或直接删除该条件。

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