使用T-SQL按自定义百分比将Azure SQL中80万条记录分配至分组

针对你这个80万行items表的分组需求，我设计了一个基于T-SQL的解决方案，完全兼容Azure SQL和SQL Server 2016，能高效处理大表数据，还能灵活支持最多5个自定义百分比的分配（总和必须为100%）。

核心思路 #

1. 用**表值参数(TVP)**接收用户传入的分组百分比，既灵活又能保证参数结构规范；
2. 先验证参数合法性（总和100%、最多5个分组、每个百分比>0）；
3. 计算每个分组对应的行数区间（解决百分比乘总记录数的小数问题，自动调整余数）；
4. 给每条记录生成随机行号，根据行号落在的区间分配对应分组。

具体实现代码 #

首先创建表值参数，用来传递分组信息：

CREATE TYPE dbo.PercentageGroup AS TABLE (
    GroupId INT PRIMARY KEY,
    Percentage DECIMAL(5,2) NOT NULL CHECK (Percentage > 0)
);

然后创建存储过程封装所有逻辑：

CREATE PROCEDURE dbo.AssignItemsToGroups
    @Groups dbo.PercentageGroup READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 参数合法性验证
    DECLARE @TotalPercentage DECIMAL(5,2) = (SELECT SUM(Percentage) FROM @Groups);
    DECLARE @GroupCount INT = (SELECT COUNT(*) FROM @Groups);

    IF @TotalPercentage <> 100.00
    BEGIN
        THROW 50001, '所有分组百分比的总和必须等于100%', 1;
    END

    IF @GroupCount > 5
    BEGIN
        THROW 50002, '最多支持5个分组', 1;
    END

    -- 2. 计算总记录数，用COUNT_BIG避免大表INT溢出
    DECLARE @TotalRows BIGINT = (SELECT COUNT_BIG(*) FROM items);

    -- 3. 计算每个分组的行数阈值（处理四舍五入误差）
    WITH GroupThresholds AS (
        SELECT
            GroupId,
            Percentage,
            SUM(Percentage) OVER (ORDER BY GroupId) AS CumulativePercentage,
            ROUND(@TotalRows * SUM(Percentage) OVER (ORDER BY GroupId) / 100, 0) AS CumulativeRowCount
        FROM @Groups
    ),
    AdjustedThresholds AS (
        SELECT
            GroupId,
            Percentage,
            CumulativeRowCount,
            LAG(CumulativeRowCount, 1, 0) OVER (ORDER BY GroupId) AS PreviousCumulativeRowCount,
            CumulativeRowCount - LAG(CumulativeRowCount, 1, 0) OVER (ORDER BY GroupId) AS TargetRowCount
        FROM GroupThresholds
    ),
    FinalThresholds AS (
        SELECT
            GroupId,
            PreviousCumulativeRowCount,
            -- 最后一个分组自动调整，确保总行数匹配
            CASE WHEN GroupId = (SELECT MAX(GroupId) FROM @Groups) THEN @TotalRows ELSE CumulativeRowCount END AS CumulativeRowCount
        FROM AdjustedThresholds
    )
    -- 4. 给记录分配分组
    SELECT
        i.*,
        ft.GroupId AS [Group]
    FROM (
        -- 用NEWID()随机打乱记录顺序，保证分组随机性
        SELECT *, ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum
        FROM items
    ) i
    JOIN FinalThresholds ft
        ON i.RowNum > ft.PreviousCumulativeRowCount
        AND i.RowNum <= ft.CumulativeRowCount
    ORDER BY ft.GroupId, i.RowNum;
END;

调用示例 #

你可以根据需要传入不同的百分比组合：

示例1：20%, 20%, 30%, 30%

DECLARE @Groups1 dbo.PercentageGroup;
INSERT INTO @Groups1 (GroupId, Percentage)
VALUES (1,20.00), (2,20.00), (3,30.00), (4,30.00);

EXEC dbo.AssignItemsToGroups @Groups = @Groups1;

示例2：12%, 12%, 12%, 12%, 52%

DECLARE @Groups2 dbo.PercentageGroup;
INSERT INTO @Groups2 (GroupId, Percentage)
VALUES (1,12.00), (2,12.00), (3,12.00), (4,12.00), (5,52.00);

EXEC dbo.AssignItemsToGroups @Groups = @Groups2;

示例3：30%, 30%, 40%

DECLARE @Groups3 dbo.PercentageGroup;
INSERT INTO @Groups3 (GroupId, Percentage)
VALUES (1,30.00), (2,30.00), (3,40.00);

EXEC dbo.AssignItemsToGroups @Groups = @Groups3;

示例4：100%（全部分到一个组）

DECLARE @Groups4 dbo.PercentageGroup;
INSERT INTO @Groups4 (GroupId, Percentage)
VALUES (1,100.00);

EXEC dbo.AssignItemsToGroups @Groups = @Groups4;

关键细节说明 #

• 随机性保证：用ORDER BY NEWID()为每条记录生成唯一随机行号，确保分组是随机分配的；如果不需要随机，只需把ORDER BY NEWID()改成你需要的排序字段（比如ORDER BY CreateTime）即可。
• 大表适配：用COUNT_BIG()统计总记录数，避免80万行数据超出INT类型的范围；存储过程的执行计划会被缓存，多次调用性能更优。
• 余数处理：最后一个分组自动调整行数，解决百分比乘总记录数后的四舍五入误差，确保所有记录都被分配，且总行数完全匹配。
• 参数安全：表值参数自带约束（Percentage > 0），加上存储过程里的总和、数量验证，避免非法输入导致的错误。

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