SQL按分组取指定数量随机记录的写法及大表优化咨询

• 字段名、CTE名称拼写不匹配：
  • CTE main 中仅查询了main_id/user_id/email/grouping4个字段，后续UNION子查询中引用了未定义的WORKSPACE_ID/MEMBER_COUNT_GROUPINGS/MEMBER_COUNT字段
  • 筛选条件中字段名混用Grouping和Groupings，和表结构字段名不一致
  • 定义的CTE名为final，最终查询时引用了不存在的final_final对象
• 性能逻辑完全不适配亿级数据量：order by random()会触发全表数据排序，数亿条数据下会耗尽临时存储/内存，查询基本不可能跑完
• 无去重逻辑：未对USER_ID做唯一性校验，最终结果可能出现重复用户，不符合需求

-- 注意：亿级数据量不要直接用这个版本，仅做语法修正参考
WITH sample_a AS (
    SELECT Main_ID, USER_ID, email, Groupings
    FROM your_table
    WHERE Groupings = 'A'
    ORDER BY RANDOM()
    LIMIT 5000
),
sample_b AS (
    SELECT Main_ID, USER_ID, email, Groupings
    FROM your_table
    WHERE Groupings = 'B'
    ORDER BY RANDOM()
    LIMIT 5000
),
sample_c AS (
    SELECT Main_ID, USER_ID, email, Groupings
    FROM your_table
    WHERE Groupings = 'C'
    ORDER BY RANDOM()
    LIMIT 5000
)
SELECT Main_ID, USER_ID, email, Groupings
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY Groupings) as rn
    FROM (
        SELECT * FROM sample_a
        UNION ALL
        SELECT * FROM sample_b
        UNION ALL
        SELECT * FROM sample_c
    ) t
) res
WHERE rn = 1;

• 替换全表随机排序为块级采样：所有主流数据库都支持表采样语法（PostgreSQL用TABLESAMPLE SYSTEM(百分比)、MySQL 8.0+用TABLESAMPLE()、Spark/Hive用TABLESAMPLE(行数 ROWS)），先按分组采样出每个组2~3万条数据（远大于需要的5000条，保证采样随机性），再在小结果集内做随机排序取数，避免扫描全表。以PostgreSQL为例的采样写法：

  SELECT Main_ID, USER_ID, email, Groupings
  FROM your_table TABLESAMPLE SYSTEM(0.1) -- 按0.1%比例块采样，根据单组数据总量调整比例
  WHERE Groupings = 'A'
  ORDER BY RANDOM()
  LIMIT 5000
  

• 建覆盖索引避免回表：给表建(Groupings, USER_ID)联合覆盖索引，查询时直接从索引树获取需要的字段，不需要回表访问主数据，扫描速度可提升10倍以上。
• 去掉无意义CTE：原写法中main CTE未做任何数据处理，直接透传全表数据，会增加查询优化器的解析开销，直接查询原表即可。
• 去重逻辑前置：如果已知同一USER_ID会对应多条记录/跨多个分组，先按USER_ID聚合去重后再做采样，减少后续排序计算的数据量。

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