需要帮助％工作分配 [英] Need help with % work allocation

问题描述

Hi Team，

Hi Team,

我需要查询帮助才能将消费者的％分配给用户group。

I need a help with the query to get % allocation of consumers to the users within a group.

组内有组，用户和消费者应根据要分配的工作量分配。

There are groups under the group there are users and consumers should be allocated based on the % work to be allocated.

详细信息：

Details:

该组中的组和用户（用户可能处于不同状态，工作分配应该在该组的用户范围内）

Group and Users in that group (an user may be in different and work allocation should be within users of that group)

一个组有用户，它有分配百分比：

A Group has users and it has % of allocation:

进入系统的新消费者：

New consumers coming into the system:

根据该组和用户的％分配给用户。

Those to be allocated to the users based on the % for that group and user.

没有硬编码规则，％必须准确但如果它们或多或少匹配那么它应该没问题。

There is no hard code rule that % has to be accurate but if they are more or less matching then it should be fine.

基于新消费者的
％分配如下：

% allocation based on new consumers be like:

• Group1 User1 = 4％的25％（新消费者）= 1因此应该为他分配1个消费者
• Group1 User2 = 4％的15％ = 0.6并且它应该被分配给1个消费者，因为它更多0.5
• Group1 User3 = 10％4 = 0.4所以它不会消​​费者
• Group1 User4 = 50％ 4 = 2所以它应该得到2个消费者
• Group2 User5 = 0.6 = 1消费者
• Group2 User1 = 1.2 = 1消费者
• Group2 User6 = 1.98 = 2消费者
• Group2 User7 = 2.22 = 2个消费者
• Group3 User2 = 2.2 = 2个消费者+ 1个消费者提醒，因为它是第一个用户或订单而user1可能
• Group3 User1  = 2.2 = 2个消费者 
• Group3 User8  = 2.2 = 2个消费者 
• Group3 User9  = 2.2 = 2个消费者 
• Group3 User10  = 2.2 = 2个消费者 

• Group1 User1 = 25% of 4 (new consumers) = 1 so 1 consumer should be allocated to him
• Group1 User2 = 15% of 4 = 0.6 and it should be allocated with 1 consumer as it is more 0.5
• Group1 User3 = 10% of 4 = 0.4 so it won't the consumer
• Group1 User4 = 50%4 = 2 so it should get 2 consumers
• Group2 User5 = 0.6 = 1 consumer
• Group2 User1 = 1.2 = 1 consumer
• Group2 User6 = 1.98 = 2 consumers
• Group2 User7 = 2.22 = 2 consumers
• Group3 User2 = 2.2 = 2 consumers + 1 consumer for the reminder as it is first user or order and user1 may get
• Group3 User1 = 2.2 = 2 consumers 
• Group3 User8 = 2.2 = 2 consumers 
• Group3 User9 = 2.2 = 2 consumers 
• Group3 User10 = 2.2 = 2 consumers 

查询：

DECLARE @GroupUser TABLE
(
	GroupName VARCHAR(10),
	UserName VARCHAR(10),
	Percentage DECIMAL(4,2)
)
INSERT INTO @GroupUser
SELECT 'Group1','User1',25 UNION ALL
SELECT 'Group1','User2',15 UNION ALL
SELECT 'Group1','User3',10 UNION ALL
SELECT 'Group1','User4',50 UNION ALL
SELECT 'Group2','User5',10 UNION ALL
SELECT 'Group2','User1',20 UNION ALL
SELECT 'Group2','User6',33 UNION ALL
SELECT 'Group2','User7',37 UNION ALL
SELECT 'Group3','User2',20 UNION ALL
SELECT 'Group3','User1',20 UNION ALL
SELECT 'Group3','User8',20 UNION ALL
SELECT 'Group3','User9',20 UNION ALL
SELECT 'Group3','User10',20

SELECT *FROM @GroupUser

DECLARE @NewWork TABLE
(
	GroupName VARCHAR(10),
	ConsumerNumber INT
)
INSERT INTO @NewWork(GroupName,ConsumerNumber)
SELECT 'Group1',23 UNION ALL
SELECT 'Group1',32 UNION ALL
SELECT 'Group1',31 UNION ALL
SELECT 'Group1',55 UNION ALL
SELECT 'Group2',100 UNION ALL
SELECT 'Group2',101 UNION ALL
SELECT 'Group2',102 UNION ALL
SELECT 'Group2',103 UNION ALL
SELECT 'Group2',104 UNION ALL
SELECT 'Group2',105 UNION ALL
SELECT 'Group3',106 UNION ALL
SELECT 'Group3',107 UNION ALL
SELECT 'Group3',108 UNION ALL
SELECT 'Group3',110 UNION ALL
SELECT 'Group3',115 UNION ALL
SELECT 'Group3',130 UNION ALL
SELECT 'Group3',243 UNION ALL
SELECT 'Group3',2 UNION ALL
SELECT 'Group3',44 UNION ALL
SELECT 'Group3',67 UNION ALL
SELECT 'Group3',78

SELECT *FROM @NewWork

DECLARE @FinalExpectedOuput TABLE
(
	GroupName VARCHAR(10),
	ConsumerNumber INT,
	UserName VARCHAR(10)
)
INSERT INTO @FinalExpectedOuput
SELECT 'Group1',23,'User1' UNION ALL
SELECT 'Group1',32,'User2' UNION ALL
SELECT 'Group1',31,'User4' UNION ALL
SELECT 'Group1',55,'User4' UNION ALL
SELECT 'Group2',100,'User5' UNION ALL
SELECT 'Group2',101,'User1' UNION ALL
SELECT 'Group2',102,'User6' UNION ALL
SELECT 'Group2',103,'User6' UNION ALL
SELECT 'Group2',104,'User7' UNION ALL
SELECT 'Group2',105,'User7' UNION ALL
SELECT 'Group3',106,'User2' UNION ALL
SELECT 'Group3',107,'User2' UNION ALL
SELECT 'Group3',108,'User1' UNION ALL
SELECT 'Group3',110,'User1' UNION ALL
SELECT 'Group3',115,'User8' UNION ALL
SELECT 'Group3',130,'User8' UNION ALL
SELECT 'Group3',243,'User9' UNION ALL
SELECT 'Group3',2,'User9' UNION ALL
SELECT 'Group3',44,'User10' UNION ALL
SELECT 'Group3',67,'User10' UNION ALL
SELECT 'Group3',78,'User2'

SELECT *FROM @FinalExpectedOuput

感谢您的输入

问候，

Eshwar。

请不要忘记标记为答案我的帖子解决了你的问题，如果帖子有用，请使用投票。这会对其他用户有所帮助。

Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.

推荐答案

当你谈到新工作时，我有点困惑？在那种情况下，我们不应该考虑目前的工作吗？然而，无论如何，这个问题非常困难。我不确定我的解决方案是否完全符合您的要求，但它似乎与您的样本中的预期
结果相符。

I'm a little confused when you talk about new work? In that case, shouldn't we consider current work? Then again, the problem is quite difficult anyway. I am not sure that my solution below meets your requirements exactly, but it appears to match the expected results in your sample.

有两个CTE，第一个计算累积该组的百分比，从具有最高百分比的用户开始。我还计算PrevPerc，它是前一个用户的值。第二个CTE计算每个组中消费者的数量
并为消费者编号。

There are two CTEs, the first computes the accumulated percentage for the group, starting with the user with highest percentage. I also compute PrevPerc which is the value to to the previous user. The second CTE computes number of consumer in each group and numbers the consumers.

在最后的加入中，我匹配内部。除以计数的rowno落入定义组中用户的间隔。您可能会注意到，我将百分比除以100并将所有值转换为浮点数，以避免出现意外结果，因为小数位数太少。

In the final join, I match on internal. The rowno divided by the count falls into a interval that defines a user in a group. You may note that I divide the percentages by 100 and I cast all values to float to avoid surprises with results having too few decimals.

; WITH GroupAccums AS (
    SELECT GroupName, UserName,
           cast(SUM(Percentage) OVER(PARTITION BY GroupName
                                     ORDER BY Percentage DESC, UserName
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS float) / 100 AS AccumPerc,
           isnull(cast(SUM(Percentage) OVER(PARTITION BY GroupName
                                            ORDER BY Percentage DESC, UserName
                                            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS float) / 100, 0) AS PrevPerc
    FROM   @GroupUser
), ConsumerData AS (
   SELECT GroupName, ConsumerNumber,
          cast(COUNT(*) OVER (PARTITION BY GroupName) AS float) AS GroupCnt,
          cast(row_number() OVER(PARTITION BY GroupName ORDER BY ConsumerNumber) AS float) AS rowno
   FROM   @NewWork
)
SELECT GA.GroupName, CD.ConsumerNumber, GA.UserName
FROM   GroupAccums GA
JOIN   ConsumerData CD ON CD.GroupName = GA.GroupName
                      AND CD.rowno / CD.GroupCnt > GA.PrevPerc 
                      AND CD.rowno / CD.GroupCnt <= GA.AccumPerc
ORDER  BY GA.GroupName, CD.ConsumerNumber    

这篇关于需要帮助％工作分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！