Guest User

Untitled

a guest
Oct 16th, 2017
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.25 KB | None | 0 0
  1. declare @Servers table (ServerName sysname, TotalSizeGB decimal (12,2))
  2. insert into @Servers values
  3. ('Server1',123.45),
  4. ('Server2',234.56),
  5. ('Server3',345.67),
  6. ('Server4',456.78),
  7. ('Server5',567.89),
  8. ('Server6',678.90),
  9. ('Server7',789.01),
  10. ('Server8',890.12),
  11. ('Server9',901.23),
  12. ('Server10',1023.35)
  13.  
  14. select GroupNumber, sum(TotalSizeGB) as TotalSizeGB
  15. from (
  16. select ServerName, sum(TotalSizeGB) as TotalSizeGB, ntile(3) over (order by newid()) as GroupNumber
  17. from (
  18. select ServerName, TotalSizeGB from @Servers
  19. ) x
  20. group by ServerName
  21. ) y
  22. group by GroupNumber
  23.  
  24. ServerName TotalSizeGB GroupNumber
  25. Server10 1023.35 1
  26. Server1 123.45 1
  27. Server5 567.89 1
  28. Server3 345.67 1
  29. Server4 456.78 2
  30. Server7 789.01 2
  31. Server6 678.90 2
  32. Server2 234.56 3
  33. Server9 901.23 3
  34. Server8 890.12 3
  35.  
  36. GroupNumber TotalSizeGB
  37. 1 2060.36
  38. 2 1924.69
  39. 3 2025.91
  40.  
  41. SELECT *
  42. FROM(
  43. SELECT y.TotalSizeGB,
  44. CASE
  45. WHEN y.AnotherGrp%2=0 AND y.PseudoGrpNumber=0 THEN 2
  46. WHEN y.AnotherGrp%2=0 AND y.PseudoGrpNumber=1 THEN 1
  47. WHEN y.AnotherGrp%2=0 AND y.PseudoGrpNumber=2 THEN 0
  48. ELSE y.PseudoGrpNumber
  49. END GrpNumber
  50. FROM(
  51. SELECT
  52. x.ServerName,
  53. x.TotalSizeGB,
  54. (2+ROW_NUMBER() OVER(ORDER BY x.TotalSizeGB DESC))%3 PseudoGrpNumber,
  55. (2+ROW_NUMBER() OVER(ORDER BY x.TotalSizeGB DESC))/3 AnotherGrp,
  56. ROW_NUMBER() OVER(ORDER BY x.TotalSizeGB DESC) RowNum
  57. FROM @Servers x
  58. )y
  59. )z
  60. PIVOT( SUM(z.TotalSizeGB) FOR z.GrpNumber IN([0],[1],[2]) ) pvt;
  61.  
  62. 0 1 2
  63. ------- ------- -------
  64. 2048.02 1925.80 2037.14
  65.  
  66. ServerName TotalSizeGB PseudoGrpNumber AnotherGrp GrpNumber RowNum
  67. ---------- ------------ --------------- ---------- --------- ------
  68. Server10 1023.35 0 1 0 1
  69. Server9 901.23 1 1 1 2
  70. Server8 890.12 2 1 2 3
  71. Server7 789.01 0 2 2 4
  72. Server6 678.90 1 2 1 5
  73. Server5 567.89 2 2 0 6
  74. Server4 456.78 0 3 0 7
  75. Server3 345.67 1 3 1 8
  76. Server2 234.56 2 3 2 9
  77. Server1 123.45 0 4 2 10
  78.  
  79. ServerName GroupNo TotalSizeGB
  80. ---------- ----------- -----------
  81. Server1 3 123.45
  82. Server2 3 234.56
  83. Server3 2 345.67
  84. Server4 1 456.78
  85. Server5 2 567.89
  86. Server6 1 678.90
  87. Server7 3 789.01
  88. Server8 3 890.12
  89. Server9 1 901.23
  90. Server10 2 1023.35
  91.  
  92. GroupNo GroupSizeGb
  93. ----------- -----------
  94. 1 2036.91
  95. 2 1936.91
  96. 3 2037.14
  97.  
  98. ServerName GroupNo TotalSizeGB
  99. ---------- ----------- -----------
  100. Server1 3 123.45
  101. Server2 3 234.56
  102. Server3 2 345.67
  103. Server4 1 456.78
  104. Server5 3 567.89
  105. Server6 1 678.90
  106. Server7 2 789.01
  107. Server8 2 890.12
  108. Server9 1 901.23
  109. Server10 3 1023.35
  110.  
  111. GroupNo GroupSizeGb
  112. ----------- -----------
  113. 1 2036.91
  114. 2 2024.80
  115. 3 1949.25
  116.  
  117. set nocount on
  118.  
  119. -- Parameters
  120. declare
  121. @nGroups int, -- Number of groups to split servers to
  122. @tolerance float, -- let say 0.0 ... 0.1 (0.1 mean that (+/-)10% deviation allowed from target group size)
  123. @nTries int, -- refinement tries 100, 1000, 10000 or as much as you can wait if you are not satisfied with initial solution
  124. @mFactor float, -- refinement param 0.0 ... 1.0
  125. @tolerance2 float -- let say 0.1 ... 0.3
  126.  
  127. set @nGroups = 3
  128. set @tolerance = 0
  129. set @nTries = 1000
  130. set @mFactor = 0.3
  131. set @tolerance2 = 0.3
  132.  
  133.  
  134. -- Initial Data
  135. create table #Servers (ID int identity, ServerName sysname, TotalSizeGB decimal (12,2), primary key clustered(ID))
  136.  
  137. insert into #Servers (ServerName, TotalSizeGB) values
  138. ('Server1',123.45),
  139. ('Server2',234.56),
  140. ('Server3',345.67),
  141. ('Server4',456.78),
  142. ('Server5',567.89),
  143. ('Server6',678.90),
  144. ('Server7',789.01),
  145. ('Server8',890.12),
  146. ('Server9',901.23),
  147. ('Server10',1023.35)
  148.  
  149. create table #Groups (GroupNo int not NULL, primary key clustered (GroupNo))
  150. insert into #Groups (GroupNo)
  151. select N from (select row_number() over (order by @@spid) from sys.all_columns) S(N) where N <= @nGroups
  152.  
  153. create table #ServerGroups (ServerID int not NULL, GroupNo int not NULL, primary key clustered(ServerID))
  154. create index #IX_GroupServers_GroupNo on #ServerGroups (GroupNo)
  155.  
  156. declare
  157. @srvCnt int,
  158. @grSize decimal (12,2),
  159. @grNo int,
  160. @grSz decimal (12,2),
  161. @srvID int
  162.  
  163. select @srvCnt = count(1), @grSize = sum(TotalSizeGB) / @nGroups from #Servers
  164. select @grSize as [Target approx. group size]
  165.  
  166. -- Find initial solution
  167. while (select count(1) from #ServerGroups) < @srvCnt
  168. begin
  169. select top 1 @grNo = g.GroupNo
  170. from #Groups g
  171. left join #ServerGroups sg on sg.GroupNo = g.GroupNo
  172. left join #Servers s on s.ID = sg.ServerID
  173. group by g.GroupNo
  174. order by sum(s.TotalSizeGB)
  175.  
  176. select @grSz = IsNull(sum(s.TotalSizeGB), 0)
  177. from #Groups g
  178. left join #ServerGroups sg on sg.GroupNo = g.GroupNo
  179. left join #Servers s on s.ID = sg.ServerID
  180. where g.GroupNo = @grNo
  181.  
  182. select top 1 @srvID = ID
  183. from #Servers s
  184. where not exists (select 1 from #ServerGroups where ServerID = s.ID)
  185. order by abs(@grSize - @grSz - s.TotalSizeGB)
  186.  
  187. insert into #ServerGroups (ServerID, GroupNo) values (@srvID, @grNo)
  188. end
  189.  
  190. select g.GroupNo, SUM(s.TotalSizeGB) GroupSizeGb
  191. from #Groups g
  192. join #ServerGroups sg on sg.GroupNo = g.GroupNo
  193. join #Servers s on s.ID = sg.ServerID
  194. group by g.GroupNo
  195.  
  196.  
  197. -- Refinement
  198. declare @fTarg float
  199.  
  200. select @fTarg = sum(abs(case when abs(re) > @tolerance then re else 0 end))
  201. from (
  202. select g.GroupNo, SUM(s.TotalSizeGB) GroupSizeGb
  203. from #Groups g
  204. join #ServerGroups sg on sg.GroupNo = g.GroupNo
  205. join #Servers s on s.ID = sg.ServerID
  206. group by g.GroupNo
  207. ) t
  208. cross apply (select (GroupSizeGb - @grSize)/@grSize re) p
  209.  
  210. print @fTarg
  211.  
  212. if @fTarg > 0
  213. begin
  214.  
  215. create table #MServerGroups (ServerID int not NULL, GroupNo int not NULL, primary key clustered (ServerID))
  216. insert into #MServerGroups
  217. select ServerID, GroupNo from #ServerGroups
  218.  
  219. while @nTries > 0
  220. begin
  221. set @nTries = @nTries - 1
  222.  
  223. begin transaction
  224.  
  225. ;with MS as (
  226. select top (100*@mFactor) percent ServerID, GroupNo
  227. from #MServerGroups
  228. order by checksum(newid())
  229. )
  230. update msg
  231. set
  232. msg.GroupNo = case when msg.ServerID = tt.ServerID1 then tt.NewNo1 else tt.NewNo2 end
  233. from
  234. #MServerGroups msg
  235. join (
  236. select ServerID1, NewNo1, ServerID2, NewNo2
  237. from (
  238. select MS.ServerID as ServerID1, SS.GroupNo as NewNo1, SS.ServerID as ServerID2, MS.GroupNo as NewNo2, row_number() over (partition by SS.ServerID order by @@spid) as rn
  239. from MS
  240. join #Servers s on s.ID = MS.ServerID
  241. cross apply (
  242. select top 1 *
  243. from
  244. #Servers s2
  245. join #MServerGroups ms2 on ms2.ServerID = s2.ID
  246. where
  247. s2.ID != MS.ServerID and ms2.GroupNo != MS.GroupNo and abs(s2.TotalSizeGB - s.TotalSizeGB)/s.TotalSizeGB < @tolerance2
  248. order by checksum(newid())
  249. ) SS
  250. ) t
  251. where rn = 1
  252. )tt on msg.ServerID in (tt.ServerID1, tt.ServerID2)
  253.  
  254. if @@rowcount = 0
  255. begin
  256. rollback transaction
  257. continue;
  258. end
  259.  
  260. declare @fT float
  261.  
  262. select @fT = sum(abs(case when abs(re) > @tolerance then re else 0 end))
  263. from (
  264. select g.GroupNo, SUM(s.TotalSizeGB) GroupSizeGb
  265. from #Groups g
  266. join #MServerGroups sg on sg.GroupNo = g.GroupNo
  267. join #Servers s on s.ID = sg.ServerID
  268. group by g.GroupNo
  269. ) t
  270. cross apply (select (GroupSizeGb - @grSize)/@grSize re) p
  271.  
  272. if @fT < @fTarg
  273. begin
  274. set @fTarg = @ft
  275. print @fTarg -- the less this number, the better solution is
  276.  
  277. commit transaction
  278. end
  279. else
  280. rollback transaction
  281. end
  282.  
  283. update s
  284. set s.GroupNo = m.GroupNo
  285. from #MServerGroups m
  286. join #ServerGroups s on s.ServerID = m.ServerID
  287.  
  288. select g.GroupNo, SUM(s.TotalSizeGB) GroupSizeGb
  289. from #Groups g
  290. join #ServerGroups sg on sg.GroupNo = g.GroupNo
  291. join #Servers s on s.ID = sg.ServerID
  292. group by g.GroupNo
  293.  
  294. drop table #MServerGroups
  295.  
  296. end
  297. else
  298. print 'No refinement needed'
  299.  
  300. drop table #Groups
  301. drop table #ServerGroups
  302. drop table #Servers
  303.  
  304. declare @TotalSizeGB decimal;
  305. select @TotalSizeGB = sum(TotalSizeGB) from @Servers;
  306.  
  307. declare @Count int;
  308. select @Count = count(TotalSizeGB) from @Servers;
  309.  
  310. declare @GroupSize int;
  311. select @GroupSize = 3;
  312.  
  313. declare @NoofGroups int;
  314. select @NoofGroups = 3;
  315.  
  316. declare @UnitSizeGB decimal
  317. Set @UnitSizeGB =(@TotalSizeGB/@Count)*@NoofGroups;
  318.  
  319. Declare @Remainder decimal;
  320. Set @Remainder = @TotalSizeGB-(@UnitSizeGB*@NoofGroups)
  321.  
  322. Select GroupNumber,
  323. CASE
  324. WHEN gcount = @GroupSize THEN @UnitSizeGB
  325. WHEN gcount > @GroupSize THEN @UnitSizeGB+@Remainder
  326. END
  327. From (
  328. Select
  329. GroupNumber,count(ServerName) as gcount, @UnitSizeGB as UnitSizeGB from(
  330. Select ServerName,ntile(@GroupSize) over (order by newid()) as GroupNumber
  331. from (
  332. select ServerName, TotalSizeGB from @Servers ) x
  333. group by ServerName ) as d
  334. group by GroupNumber ) as ff
  335.  
  336. GroupNumber Size
  337. 1 2405
  338. 2 1803
  339. 3 1803
  340.  
  341. -- Source data:
  342. DECLARE @Servers TABLE (ServerName SYSNAME, TotalSizeGB DECIMAL (12,2))
  343. INSERT INTO @Servers VALUES
  344. ('Server1',123.45),
  345. ('Server2',234.56),
  346. ('Server3',345.67),
  347. ('Server4',456.78),
  348. ('Server5',567.89),
  349. ('Server6',678.90),
  350. ('Server7',789.01),
  351. ('Server8',890.12),
  352. ('Server9',901.23),
  353. ('Server10',1023.35)
  354.  
  355.  
  356. -- Solution start
  357. DECLARE @ServersChunked TABLE (
  358. ServerName SYSNAME,
  359. TotalSizeGB DECIMAL (12,2),
  360. RowNum INT,
  361. ChunkNo INT
  362. );
  363. DECLARE
  364. @ChunkCount INT = 3,
  365. @MinRowNum INT,
  366. @SmallestChunk INT;
  367.  
  368.  
  369. -- Copy table into variable (skip this if the original table can be amended to include the RowNum and ChunkNo fields)
  370. INSERT INTO @ServersChunked
  371. SELECT
  372. *,
  373. RowNum = ROW_NUMBER() OVER (ORDER BY TotalSizeGB DESC),
  374. ChunkNo = NULL
  375. FROM @Servers
  376.  
  377. -- Assign the initial chunks to largest tables
  378. UPDATE @ServersChunked
  379. SET ChunkNo = RowNum
  380. WHERE RowNum <= @ChunkCount
  381.  
  382.  
  383. -- Assign chunks to remaining tables
  384. WHILE EXISTS (SELECT 1 FROM @ServersChunked WHERE ChunkNo IS NULL) BEGIN
  385.  
  386. -- Find the next table (by descending row count)
  387. SELECT @MinRowNum = MIN(RowNum) FROM @ServersChunked WHERE ChunkNo IS NULL
  388.  
  389. -- Find the smallest chunk
  390. SELECT TOP 1 @SmallestChunk = ChunkNo
  391. FROM @ServersChunked
  392. WHERE ChunkNo IS NOT NULL
  393. GROUP BY ChunkNo
  394. ORDER BY Sum(TotalSizeGB) ASC
  395.  
  396. -- Assign the table to the chunk
  397. UPDATE @ServersChunked
  398. SET ChunkNo = @SmallestChunk
  399. WHERE RowNum = @MinRowNum
  400. END
  401.  
  402. ChunkNo SumTotalSizeGB
  403. 1 1936.91
  404. 2 2036.91
  405. 3 2037.14
Add Comment
Please, Sign In to add comment