PostgreSQL中按id分组抽取最多50条样本数据的实现方法

最优通用方案（支持窗口函数的数据库：MySQL 8.0+/PostgreSQL/SQL Server/Oracle等） #

利用窗口函数ROW_NUMBER()结合随机排序，无需提前获取ID列表，一次查询即可完成需求：

SELECT id, `value 1`, `value 2`, `value 3`
FROM (
    SELECT 
        *,
        -- 不同数据库的随机排序函数略有差异，按需替换
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY RANDOM()) AS row_num
    FROM schema.table
) AS ranked_data
WHERE row_num <= 50;

不同数据库的随机函数替换说明：

• MySQL：将RANDOM()替换为RAND()
• SQL Server：将RANDOM()替换为NEWID()（或CHECKSUM(NEWID())，性能更优）
• Oracle：将RANDOM()替换为DBMS_RANDOM.VALUE()

老版本MySQL（低于8.0，无窗口函数）兼容方案 #

如果使用不支持窗口函数的老版本MySQL，可以通过用户变量实现分组编号：

SELECT id, `value 1`, `value 2`, `value 3`
FROM (
    SELECT 
        *,
        @row_count := IF(@current_id = id, @row_count + 1, 1) AS row_num,
        @current_id := id
    FROM schema.table,
         (SELECT @current_id := NULL, @row_count := 0) AS init_vars
    ORDER BY id, RAND() -- 先按ID分组，再随机排序
) AS ranked_data
WHERE row_num <= 50;

方案优势对比 #

相比你之前尝试的UNION ALL遍历ID的方法，上述方案：

• 无需提前获取完整ID列表，避免额外查询和拼接SQL的繁琐操作
• 单查询完成逻辑，性能更高效，尤其适用于数据量较大的场景
• 天然支持随机抽取样本，满足需求中的随机性要求

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