时间戳

首页 > 解决方案 > 用户分配订单

sql - 用户分配订单

问题描述

我有订单和用户。我为每个用户的订单平均分配。添加新用户时，我需要重新分配。并且有必要考虑算法中已完成和限制之间的差异。如果用户对第一个分布进行了限制，则他们不会将其包含在新分布中，并将该列暴露给来自该列的限制值。

    declare @orderCount int
    set @orderCount = 50 --Orders for Distribution

    --Result table
    declare @t table (
    users char(3),
    limit int,
    Made int
    )

    --Add users
    insert into @t (users, limit, Made) values
    ('us1',0,0),
    ('us2',0,0),
    ('us3',0,0)


    --Table for the Distribution algorithm
    declare @c table (
        users char(3),
        limit int,
        Made int,
        Cnt int,
        Rn int
    )

    --Count users and row_number 
    insert into @c
        select
            *,
            COUNT(*) OVER () as Cnt,
            ROW_NUMBER() OVER (ORDER BY users) as Rn
        from
            @t

    --var for check new distribution>made
    DECLARE @check int = 0

    --Distribution
    update t
    set @check = (@orderCount/Cnt) + CASE WHEN @orderCount % Cnt >= Rn THEN 1 ELSE 0 END,
             limit=
                CASE WHEN
                    (@check >= t.Made)
                THEN
                    @check                  
                ELSE
                    t.Made
                END
            FROM @t t
                INNER JOIN @c cn ON cn.users=t.users  

    --Test data
    UPDATE @t SET Made=12 WHERE users='us1'
    UPDATE @t SET Made=10 WHERE users='us2'
    UPDATE @t SET Made=5 WHERE users='us3'

    --Check result
    SELECT * FROM @t

    --add to distribution new users
    INSERT INTO @t  (users, limit, Made) values ('us4',0,0)
    INSERT INTO @t  (users, limit, Made) values ('us5',0,0)

    --Clear table
    DELETE FROM @c

    --Check new data
    insert into @c
        select
            *,
            COUNT(*) OVER () as Cnt,
            ROW_NUMBER() OVER (ORDER BY users) as Rn
        from
            @t

    --Distribution
    update t
    set @check = (@orderCount/Cnt) + CASE WHEN @orderCount % Cnt >= Rn THEN 1 ELSE 0 END,
             limit=
                CASE WHEN
                    (@check >= t.Made)
                THEN
                    @check                  
                ELSE
                    t.Made
                END
            FROM @t t
                INNER JOIN @c cn ON cn.users=t.users            

    --Check result
    SELECT * FROM @t

在此处输入图像描述

它为用户划分订单数量。1 个用户的 50/3 ~16 个订单。如果我添加 2 个新用户，那么 50/5 ~ 10。但是 1 个用户执行 12 个订单。还有 12 个新的分销订单 (10)。然后他过去进入第 12 列限制。而 50-12=38。然后 38/4 ~ 9 订单其余用户。

标签： sqlsql-server

解决方案

SELECT x.users, 
        x.work,
        x.Made,
        sum(CASE WHEN Made>NewLimit  THEN  Made ELSE 0 END) OVER() as Dif,
        count(CASE WHEN Made<NewLimit  THEN NewLimit END) OVER() as Cnt,
        CASE WHEN Made>NewLimit THEN 0 ELSE 1 END as IsUsed,
        (CASE WHEN Made<NewLimit THEN
        ROW_NUMBER() OVER (PARTITION BY x.work, (case when Made<NewLimit then 1 else 0 end) ORDER BY work) ELSE 0 END) as Rn
FROM (
SELECT t.users ,(@orderCount/Cnt) + CASE WHEN @orderCount % Cnt >= Rn THEN 1 ELSE 0 END as NewLimit,
         t.Made,
         t.work     
    FROM @t t
        INNER JOIN @c cn ON cn.users=t.users
        ) x

我考虑旧分布更多的数量。我正在为新发行版设置 isUsed 标志。我考虑数字。我从所有订单中扣除新莱姆石习惯的数量，然后将收到的数量除以适合新分配的数量。

推荐阅读