均匀分布的相关记录

Question

我有一个有超过100000个邮箱和用户具有权限的表。

+---------+---------+
| Mailbox | Trustee |
+---------+---------+
| smb1    | mbx1    |
| smb2    | mbx1    |
| smb2    | mbx2    |
| smb2    | mbx3    |
| smb3    | mbx4    |
| smb3    | mbx5    |
| mbx1    | mbx6    |
| mbx7    | mbx4    |
| smb4    | mbx8    |
| smb4    | mbx9    |
| mbx8    | mbx10   |
+---------+---------+

需要在邮箱列中对托管和邮箱进行分组。例如，mbx1、mbx2和mbx 3是通过对smb2的访问进行关联的，因此它们在桶1.mbx中进入桶1，这意味着smb1也进入了桶1，因为mbx 1在这方面是托管的。然后再往下看，因为mbx6和mbx1有关系，所以它也进入了第1桶，希望其他的都有意义。所以请注意，受托人可以访问smb (共享邮箱)或mbx (邮箱)

我选择的表只有邮箱和受托人，我想写到下面的临时表中。

+---------+---------+--------+
| Mailbox | Trustee | Bucket |
+---------+---------+--------+
| smb1    | mbx1    |      1 |
| smb2    | mbx1    |      1 |
| smb2    | mbx2    |      1 |
| smb2    | mbx3    |      1 |
| smb3    | mbx4    |      2 |
| smb3    | mbx5    |      2 |
| mbx1    | mbx6    |      1 |
| mbx7    | mbx4    |      2 |
| smb4    | mbx8    |      3 |
| smb4    | mbx9    |      3 |
| mbx8    | mbx10   |      3 |
+---------+---------+--------+

然后，我想把桶数放在一起，组成均匀的组。我的想法是，我可以说最大计数100，所以创建一组桶，计数在100个左右的用户。

+---------+---------+-------+
| Groups  | Buckets | Count |
+---------+---------+-------+
|       1 |       1 |     5 |
|       2 |     2,3 |     6 |
+---------+---------+-------+

编辑:我已经走了这么远，我可以通过一个邮箱，让所有的受托人，然后其他邮箱，受托人可以访问。

DECLARE @int int = 1;
WITH Buckets_CTE
    (Trustee)
AS (
    SELECT DISTINCT Trustee
    FROM EXOPerms
        WHERE Mailbox = 'smb1'
)
SELECT DISTINCT Mailbox,Trustee
    FROM EXOPerms
    Where Trustee IN (
    SELECT DISTINCT Trustee
    FROM Buckets_CTE)
    ORDER BY Trustee

上面的声明Int现在是多余的，只是为了看看我是否可以实现桶特性。

Answer 1

下面是一个while循环解决方案。它只需遍历每一行并更新桶。

添加ID，用于逐行遍历数据。

若要检查邮箱/受信者是否存在于另一行中，请检查是否存在i.Mailbox in (m.Mailbox, m.Trustee)：

from @mailbox i
     inner join @mailbox m  
     on  i.ID   <> m.ID   -- don't compare the same row
     and (
            i.Mailbox   in (m.Mailbox, m.Trustee) 
         or i.Trustee   in (m.Mailbox, m.Trustee) 
         )

请注意，当更新桶时，它与当前桶进行比较，并且只接受较低的值。这是为了解决以下情况，在这种情况下，以前的行之间的关系直到后面的行才知道。

ID  MailBox  Trustee 
1   a        b      
2   c        d
3   e        f
4   c        f

ID 1，2，3是在进程顺序分配单独的桶。只有当进程ID 4时，它才将ID 2和3链接在一起。

完全查询

declare @mailbox table
(
    ID      int        identity,
    Mailbox varchar(5),
    Trustee varchar(5),
    Bucket  int
)

insert into @mailbox (Mailbox, Trustee) values
( 'smb1',    'mbx1' ),
( 'smb2',    'mbx1' ),
( 'smb2',    'mbx2' ),
( 'smb2',    'mbx3' ),
( 'smb3',    'mbx4' ),
( 'smb3',    'mbx5' ),
( 'mbx1',    'mbx6' ),
( 'mbx7',    'mbx4' ),
( 'smb4',    'mbx8' ),
( 'smb4',    'mbx9' ),
( 'mbx8',    'mbx10');

declare @ID     int,
        @Bucket int = 1    -- start from 1

-- get the minimum ID for start
select  @ID = min(ID) from @mailbox where Bucket is null

while   exists 
        (
            select  *
            from    @mailbox
            where   ID >= @ID
        )
begin
        -- if the mailbox is found in other row with Bucket value
        -- (Bucket is not null)
        if  exists
        (
            select  *
            from    @mailbox i
                    inner join @mailbox m   
                    on  i.ID    <> m.ID
                    and (
                            i.Mailbox   in (m.Mailbox, m.Trustee) 
                        or  i.Trustee   in (m.Mailbox, m.Trustee) 
                        )
            where   i.ID    = @ID
            and     m.Bucket    is not null
        )
        begin
            -- Update Bucket from other row
            update  i
            set     Bucket  = case  when i.Bucket is null
                                    or   i.Bucket > m.Bucket
                                    then m.Bucket
                                    else i.Bucket
                                    end
            from    @mailbox i
                    inner join @mailbox m   
                    on  i.ID    <> m.ID
                    and (
                            i.Mailbox   in (m.Mailbox, m.Trustee) 
                        or  i.Trustee   in (m.Mailbox, m.Trustee) 
                        )
            where   i.ID    = @ID
            and     m.Bucket    is not null

            -- Update other rows that might linked to current ID
            update  m
            set     Bucket  = case  when    i.Bucket > m.Bucket
                                    then    m.Bucket
                                    else    i.Bucket
                                    end
            from    @mailbox i
                    inner join @mailbox m   
                    on  i.ID    <> m.ID
                    and (
                            i.Mailbox   in (m.Mailbox, m.Trustee) 
                        or  i.Trustee   in (m.Mailbox, m.Trustee) 
                        )
            where   i.ID        = @ID
    end
    else
    begin
        -- no other row found with same mailbox. 
        -- Assign Bucket from @Bucket, increment @Bucket
        update  m
        set     Bucket  = @Bucket
        from    @mailbox m
        where   m.ID    = @ID;

        select  @Bucket = @Bucket + 1;
    end

    -- Get next ID
    select  @ID = min(ID) from @mailbox where ID > @ID;
end

select  *
from    @mailbox
order by ID