基于聚合创建大小均匀的组 [英] Create evenly sized groups based on aggregate
问题描述
可能是一个新手问题,但我希望根据总数据库大小将我们的服务器库存分成几个大小均匀的组,并且很难弄清楚如何对它们进行分组.我认为 NTILE 可能会奏效,但我无法将这些组平均划分开来.我下面的示例只是随机订购服务器.我希望结果是大小相当均匀的 3 组(显然不会准确).
Probably a newbie question, but I'm looking to split our server inventory up into several evenly sized groups based on total database size, and am stumped figuring out how to group them. I think NTILE will work, maybe, but I just can't wrap my head around splitting the groups evenly. My example below is just ordering the servers randomly. I would like the results to be 3 groups of fairly even size (obviously won't be exact).
使用 SQL Server 2012.感谢任何帮助.谢谢.
Using SQL Server 2012. Any help is appreciated. Thanks.
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.我希望它不会准确,但至少接近.如果按服务器分组,则可能如下所示:
The expected output here would be three groups of about 2000GB each. I expect it won't be exact, but at least close. If grouping per server, it might look like this:
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
如果我每组拿一笔钱,它看起来像这样:
If I was taking a sum per group, it would look like this:
GroupNumber TotalSizeGB
1 2060.36
2 1924.69
3 2025.91
推荐答案
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 个连续行首先按 DESC
顺序分组在一起(列 AnotherGrp
),然后按 ASC
顺序(列 PseudoGroNumber
和 GrpNumber
).如果执行 SELECT * FROM () y
derivate table 那么结果将是:
The idea is to sort data descending on TotalSizeGB
column. Then every 3 sequential rows are grouped together (column AnotherGrp
) first in DESC
order and then in ASC
order (column PseudoGroNumber
and GrpNumber
). If it's executed SELECT * FROM () y
derivate table then the results will be:
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
这篇关于基于聚合创建大小均匀的组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!