如何编写SQL查询实现特定规则的正负样本抽样？

核心逻辑 #

• 正样本：按score降序排序后，每3条取第3条，直到凑够10000条
• 负样本：按score升序排序（对应降序结果的末尾部分），同样每3条取第3条，凑够10000条

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

正样本查询 #

SELECT id, score
FROM (
    SELECT 
        id, 
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
    FROM your_table_name
) ranked_data
WHERE row_num % 3 = 0
LIMIT 10000;

负样本查询 #

SELECT id, score
FROM (
    SELECT 
        id, 
        score,
        ROW_NUMBER() OVER (ORDER BY score ASC) AS row_num
    FROM your_table_name
) ranked_data
WHERE row_num % 3 = 0
LIMIT 10000;

不支持窗口函数的老版本MySQL #

正样本查询 #

SELECT id, score
FROM (
    SELECT 
        id, 
        score,
        @row_counter := @row_counter + 1 AS row_num
    FROM your_table_name, (SELECT @row_counter := 0) init_var
    ORDER BY score DESC
) ranked_data
WHERE row_num % 3 = 0
LIMIT 10000;

负样本查询 #

SELECT id, score
FROM (
    SELECT 
        id, 
        score,
        @row_counter := @row_counter + 1 AS row_num
    FROM your_table_name, (SELECT @row_counter := 0) init_var
    ORDER BY score ASC
) ranked_data
WHERE row_num % 3 = 0
LIMIT 10000;

注意事项 #

• 把your_table_name替换成你实际使用的表名
• row_num % 3 = 0的逻辑正好匹配“从第3条开始，每3条抽取一条”的要求
• 负样本用升序排序，相当于从降序结果的末尾反向按规则抽取，完全符合需求

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