SQL Server：如何从Tickets表高效选取5条不同userId的随机记录

这个需求很典型——既要让工单多的用户有更高的中奖概率，又要保证每个选中的记录对应唯一用户，还要在百万级数据下跑得够快。下面是几个经过实践验证的方案，从实时计算到预维护统计表，覆盖不同场景：

方案一：实时采样去重（无需额外表，适合数据更新不频繁的场景） #

思路 #

把每个工单看作用户的“投票券”，先随机采样一批工单，然后去重得到唯一用户，再从每个用户的工单里随机选一张。这种方法天然符合“工单越多，选中概率越高”的需求，因为工单多的用户有更多机会被采样到。

高性能SQL实现 #

DECLARE @sampleSize INT = 15; -- 采样15条，大概率能凑够5个唯一用户（可根据实际数据分布调整）

-- 1. 高效随机采样工单（避免全表排序）
WITH RandomTicketSample AS (
    SELECT TOP (@sampleSize) ticketId, userId
    FROM Tickets
    -- 用CHECKSUM+NEWID实现高效随机过滤，比ORDER BY NEWID()快得多
    WHERE ABS(CHECKSUM(NEWID(), ticketId)) % 10000 < @sampleSize * 6 -- 调整比例保证采样量
),
-- 2. 去重得到唯一用户
UniqueSelectedUsers AS (
    SELECT DISTINCT userId
    FROM RandomTicketSample
)
-- 3. 从每个用户中随机选一张工单
SELECT TOP 5 t.*
FROM UniqueSelectedUsers uu
CROSS APPLY (
    SELECT TOP 1 *
    FROM Tickets
    WHERE userId = uu.userId
    ORDER BY NEWID() -- 这里用NEWID()随机选，若有userId索引则速度很快
) t;

性能优化点 #

• 给Tickets表创建非聚集索引：CREATE NONCLUSTERED INDEX IX_Tickets_UserId ON Tickets(userId) INCLUDE(ticketId, [其他需要的字段]); 这样无论是采样还是随机选用户工单，都能快速定位数据。
• 调整@sampleSize和过滤比例：如果用户工单分布不均匀（比如少数用户占大量工单），可以适当增大@sampleSize，确保一次采样就能得到足够的唯一用户。

方案二：预维护用户权重表（适合数据更新频繁、追求极致性能的场景） #

思路 #

提前维护一个记录每个用户工单数量的统计表，这样就不用每次查询都扫描百万级的Tickets表。然后基于用户的工单数量（权重）来随机选用户，最后再取对应工单。

步骤1：创建并维护用户权重表 #

-- 创建用户工单统计表
CREATE TABLE UserTicketCounts (
    userId INT PRIMARY KEY,
    ticketCount INT NOT NULL,
    lastUpdated DATETIME NOT NULL DEFAULT GETDATE()
);

-- 初始化数据
INSERT INTO UserTicketCounts (userId, ticketCount)
SELECT userId, COUNT(*)
FROM Tickets
GROUP BY userId;

-- 创建触发器实时维护统计数据
CREATE TRIGGER tr_Tickets_UpdateWeight
ON Tickets
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 处理插入/更新的用户
    MERGE INTO UserTicketCounts utc
    USING (
        SELECT userId, COUNT(*) AS changeCount
        FROM inserted
        GROUP BY userId
    ) i ON utc.userId = i.userId
    WHEN MATCHED THEN
        UPDATE SET utc.ticketCount = utc.ticketCount + i.changeCount, utc.lastUpdated = GETDATE()
    WHEN NOT MATCHED THEN
        INSERT (userId, ticketCount) VALUES (i.userId, i.changeCount);

    -- 处理删除的用户
    MERGE INTO UserTicketCounts utc
    USING (
        SELECT userId, COUNT(*) AS changeCount
        FROM deleted
        GROUP BY userId
    ) d ON utc.userId = d.userId
    WHEN MATCHED THEN
        UPDATE SET utc.ticketCount = utc.ticketCount - d.changeCount, utc.lastUpdated = GETDATE();

    -- 删除工单数量为0的用户
    DELETE FROM UserTicketCounts WHERE ticketCount <= 0;
END;

步骤2：加权随机选用户并获取工单 #

DECLARE @targetCount INT = 5;
DECLARE @totalWeight INT = (SELECT SUM(ticketCount) FROM UserTicketCounts);

-- 生成随机数，每个数对应权重范围内的用户
WITH RandomWeightNumbers AS (
    SELECT TOP (@targetCount * 2) -- 生成双倍数量，避免重复
           FLOOR(RAND(CHECKSUM(NEWID())) * @totalWeight) + 1 AS RandWeight
    FROM master..spt_values
    WHERE type = 'P'
),
-- 计算用户的权重区间
UserWeightRanges AS (
    SELECT userId, ticketCount,
           SUM(ticketCount) OVER (ORDER BY userId) AS EndWeight,
           SUM(ticketCount) OVER (ORDER BY userId) - ticketCount + 1 AS StartWeight
    FROM UserTicketCounts
),
-- 匹配随机数到用户，去重得到唯一用户
SelectedUsers AS (
    SELECT DISTINCT uw.userId
    FROM RandomWeightNumbers rwn
    JOIN UserWeightRanges uw ON rwn.RandWeight BETWEEN uw.StartWeight AND uw.EndWeight
)
-- 从每个用户中随机选一张工单
SELECT TOP (@targetCount) t.*
FROM SelectedUsers su
CROSS APPLY (
    SELECT TOP 1 *
    FROM Tickets
    WHERE userId = su.userId
    ORDER BY NEWID()
) t;

性能优化点 #

• 给UserTicketCounts表创建索引：CREATE NONCLUSTERED INDEX IX_UserTicketCounts_TicketCount ON UserTicketCounts(ticketCount); 加速权重区间的计算。
• 调整随机数生成数量：生成@targetCount * 2个随机数，确保即使有重复用户，也能快速凑够目标数量。

方案对比 #

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