如何在SQL中按EmailType权重分布选记录，且保证UserId唯一

针对你的需求，我们可以通过两种主流方法实现：加权随机抽样（简单高效）和贪心目标分配（更贴近理想分布），以下是具体实现：

方法一：加权随机抽样（基础版） #

该方法为每个用户的可用EmailType分配与目标权重成比例的优先级，再结合随机数选择，既保证每个用户仅选一条，又能让整体分布尽可能贴近目标。

实现代码 #

WITH UserEmailWeighted AS (
    SELECT 
        mt.UserId,
        mt.EmailType,
        mt.Column1,
        mt.Column2,
        -- 计算当前用户该EmailType的归一化权重（适配用户仅有的类型）
        ed.WeightageRequired / SUM(ed.WeightageRequired) OVER (PARTITION BY mt.UserId) AS NormWeight
    FROM MainTable mt
    INNER JOIN EmailDistribution ed 
        ON mt.EmailType = ed.EmailType
),
RankedSelections AS (
    SELECT 
        *,
        -- 按用户分组，优先选权重高的类型，随机值打破平局
        ROW_NUMBER() OVER (
            PARTITION BY UserId 
            ORDER BY NormWeight DESC, NEWID()
        ) AS SelectionRank
    FROM UserEmailWeighted
)
SELECT UserId, EmailType, Column1, Column2 /* 替换为你的其他列 */
FROM RankedSelections
WHERE SelectionRank = 1;

逻辑说明 #

1. 关联MainTable和EmailDistribution，计算每个用户的每个EmailType的归一化权重：因为用户可能没有所有类型，所以将用户拥有的类型的目标权重总和归一为1，确保每个用户的可选类型权重比例符合目标。
2. 对每个用户的记录按归一化权重降序排序，并用NEWID()生成的随机值处理权重相同的情况，最后取每个用户的第一条记录。

优缺点 #

• ✅ 代码简洁，性能优异，适合5w行级别的数据量
• ✅ 无需复杂逻辑，自动适配用户的可用类型
• ❌ 分布接近目标但并非严格对齐，适合对精度要求不是极高的场景

方法二：贪心目标分配（进阶版） #

如果需要更严格贴近目标分布，可以采用贪心算法：先按目标数量分配优先级高的EmailType，再处理剩余用户的随机选择。

实现代码 #

-- 1. 初始化变量：总用户数、目标分配数
DECLARE @TotalUsers INT = (SELECT COUNT(DISTINCT UserId) FROM MainTable);

WITH TargetAllocations AS (
    SELECT 
        EmailType,
        ROUND(@TotalUsers * WeightageRequired, 0) AS TargetCount
    FROM EmailDistribution
),
-- 2. 为每个用户标记可用的EmailType，并按目标权重排序
UserEmailPriority AS (
    SELECT 
        mt.UserId,
        mt.EmailType,
        mt.Column1,
        mt.Column2,
        ed.WeightageRequired,
        -- 按用户分组，目标权重高的类型排前面
        ROW_NUMBER() OVER (
            PARTITION BY mt.UserId 
            ORDER BY ed.WeightageRequired DESC, NEWID()
        ) AS PriorityRank
    FROM MainTable mt
    INNER JOIN EmailDistribution ed 
        ON mt.EmailType = ed.EmailType
),
-- 3. 优先分配目标数量的用户（每个用户仅分配一次）
AllocatedUsers AS (
    SELECT 
        UserId,
        EmailType,
        Column1,
        Column2,
        ROW_NUMBER() OVER (PARTITION BY EmailType ORDER BY NEWID()) AS AllocRank
    FROM UserEmailPriority
    WHERE PriorityRank = 1 -- 先选每个用户权重最高的类型
),
-- 4. 筛选出已达到目标数量的EmailType用户
TargetMetUsers AS (
    SELECT au.*
    FROM AllocatedUsers au
    INNER JOIN TargetAllocations ta 
        ON au.EmailType = ta.EmailType
    WHERE au.AllocRank <= ta.TargetCount
),
-- 5. 处理未被分配的用户
UnassignedUsers AS (
    SELECT DISTINCT UserId 
    FROM MainTable
    WHERE UserId NOT IN (SELECT UserId FROM TargetMetUsers)
),
UnassignedSelections AS (
    SELECT 
        mt.UserId,
        mt.EmailType,
        mt.Column1,
        mt.Column2,
        ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY NEWID()) AS RandRank
    FROM MainTable mt
    INNER JOIN UnassignedUsers uu 
        ON mt.UserId = uu.UserId
)
-- 6. 合并结果
SELECT UserId, EmailType, Column1, Column2 FROM TargetMetUsers
UNION ALL
SELECT UserId, EmailType, Column1, Column2 FROM UnassignedSelections WHERE RandRank = 1;

逻辑说明 #

1. 计算每个EmailType需要选中的用户数量（总用户数 × 目标权重）。
2. 为每个用户的可用类型按目标权重排序，优先选择权重最高的类型。
3. 对每个EmailType，抽取不超过目标数量的用户（每个用户仅被选中一次）。
4. 对未被分配的用户，随机选择一条记录补充。

优缺点 #

• ✅ 分布更贴近目标权重，适合对精度要求高的场景
• ❌ 代码复杂度更高，性能略低于加权随机法
• ❌ 需要处理边界情况（如某EmailType的可用用户数不足目标数量）

验证分布效果 #

执行完选择后，可以用以下SQL验证实际分布与目标权重的差距：

DECLARE @TotalUsers INT = (SELECT COUNT(DISTINCT UserId) FROM MainTable);

SELECT 
    fs.EmailType,
    COUNT(*) AS SelectedCount,
    ROUND(COUNT(*) * 1.0 / @TotalUsers, 4) AS ActualWeight,
    ed.WeightageRequired AS TargetWeight,
    ROUND(ABS(COUNT(*) * 1.0 / @TotalUsers - ed.WeightageRequired), 4) AS WeightDiff
FROM FinalSelection fs -- 替换为你的结果表名或CTE
INNER JOIN EmailDistribution ed 
    ON fs.EmailType = ed.EmailType
GROUP BY fs.EmailType, ed.WeightageRequired
ORDER BY WeightDiff DESC;

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