sql-server - 根据聚合创建大小均匀的组

Question

可能是一个新手问题，但我希望根据数据库总大小将我们的服务器库存分成几个大小均匀的组，并且很难弄清楚如何对它们进行分组。我认为 NTILE 可能会起作用，但我就是无法将注意力集中在平均分配组上。我下面的示例只是随机订购服务器。我希望结果是大小相当均匀的 3 组(显然不准确)。

使用 SQL Server 2012。感谢任何帮助。谢谢。

declare @Servers table (ServerName sysname, TotalSizeGB decimal (12,2))
insert into @Servers values
('Server1',123.45),
('Server2',234.56),
('Server3',345.67),
('Server4',456.78),
('Server5',567.89),
('Server6',678.90),
('Server7',789.01),
('Server8',890.12),
('Server9',901.23),
('Server10',1023.35)

select GroupNumber, sum(TotalSizeGB) as TotalSizeGB
from (
     select ServerName, sum(TotalSizeGB) as TotalSizeGB, ntile(3) over (order by newid()) as GroupNumber
     from (
          select ServerName, TotalSizeGB from @Servers
          ) x 
     group by ServerName
     ) y
group by GroupNumber

这里的预期输出是三组，每组约 2000GB。我预计它不会很准确，但至少很接近。如果按服务器分组，则可能如下所示:

ServerName  TotalSizeGB GroupNumber
Server10    1023.35 1  
Server1 123.45  1
Server5 567.89  1
Server3 345.67  1
Server4 456.78  2
Server7 789.01  2
Server6 678.90  2
Server2 234.56  3
Server9 901.23  3
Server8 890.12  3

如果我按每组计算总和，则结果将如下所示:

GroupNumber TotalSizeGB
1   2060.36
2   1924.69
3   2025.91

Answer 1

SELECT  *
FROM(
    SELECT  y.TotalSizeGB,
            CASE 
                WHEN y.AnotherGrp%2=0 AND y.PseudoGrpNumber=0 THEN 2
                WHEN y.AnotherGrp%2=0 AND y.PseudoGrpNumber=1 THEN 1
                WHEN y.AnotherGrp%2=0 AND y.PseudoGrpNumber=2 THEN 0
                ELSE y.PseudoGrpNumber
            END GrpNumber
    FROM(
        SELECT 
            x.ServerName,
            x.TotalSizeGB,
            (2+ROW_NUMBER() OVER(ORDER BY x.TotalSizeGB DESC))%3 PseudoGrpNumber,
            (2+ROW_NUMBER() OVER(ORDER BY x.TotalSizeGB DESC))/3 AnotherGrp,
            ROW_NUMBER() OVER(ORDER BY x.TotalSizeGB DESC) RowNum
        FROM    @Servers x
    )y
)z
PIVOT( SUM(z.TotalSizeGB) FOR z.GrpNumber IN([0],[1],[2]) ) pvt;

结果:

0       1       2
------- ------- -------
2048.02 1925.80 2037.14

一些解释:

这个想法是对 TotalSizeGB 列上的数据进行降序排序。然后，每 3 个连续行首先按 AnotherGrp 顺序分组在一起(列 DESC )，然后按 ASC 顺序分组在一起(列 PseudoGroNumber 和 GrpNumber )。如果执行SELECT * FROM () y派生表那么结果将是:

ServerName TotalSizeGB  PseudoGrpNumber AnotherGrp GrpNumber RowNum
---------- ------------ --------------- ---------- --------- ------
Server10   1023.35      0               1          0         1
Server9    901.23       1               1          1         2
Server8    890.12       2               1          2         3
Server7    789.01       0               2          2         4
Server6    678.90       1               2          1         5
Server5    567.89       2               2          0         6
Server4    456.78       0               3          0         7
Server3    345.67       1               3          1         8
Server2    234.56       2               3          2         9
Server1    123.45       0               4          2         10