如何基于键值从数据库随机取行？按州抽10个随机客户的高效方法

问题背景 #

现有customers表结构及数据如下：

需求是从每个州中抽取10个随机客户。目前尝试过用UNION ALL逐个州查询的方式：

(SELECT * FROM customers WHERE state = 'Alabama' LIMIT 10)
UNION ALL
(SELECT * FROM customers WHERE state = 'Alaska' LIMIT 10)
UNION ALL
(SELECT * FROM customers WHERE state = 'Arizona' LIMIT 10)
UNION ALL
(SELECT * FROM customers WHERE state = 'Arkansas' LIMIT 10)
...

但这种方法要执行数十次查询，效率极低；如果先导出全表再做后续处理，数据传输和处理成本又很高，需要更优的解决方案。

高效解决方案 #

方法一：窗口函数法（支持MySQL 8.0+、PostgreSQL、SQL Server等） #

用ROW_NUMBER()窗口函数按州分组，随机排序后取每组前10条：

SELECT state, customer
FROM (
    SELECT 
        state, 
        customer,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY RAND()) AS rn
    FROM customers
) t
WHERE rn <= 10;

• 细节说明：
  • PARTITION BY state负责按州分组
  • ORDER BY RAND()让每组内数据随机打乱
  • 不同数据库随机函数有差异：PostgreSQL用RANDOM()，SQL Server用NEWID()

方法二：变量模拟法（适用于MySQL 5.x等无窗口函数的版本） #

用变量跟踪分组状态，实现分组随机取数：

SELECT state, customer
FROM (
    SELECT 
        state, 
        customer,
        @rn := IF(@current_state = state, @rn + 1, 1) AS rn,
        @current_state := state
    FROM customers
    ORDER BY state, RAND()
) t
WHERE rn <= 10;

• 细节说明：通过@current_state记录当前分组的州，@rn为每组内的行计数，先按州排序再随机打乱，最后筛选前10条。

性能优化提示 #

• 给state字段建立索引，能大幅提升分组、排序的效率
• 若表数据量极大，可考虑对state做分区处理，进一步降低查询耗时

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