把一列数字分成大致相等的总数

如何按大小对表进行排序,然后对于每个表,将其放入当前总行数最少的一天中?这意味着最大的7张桌子将首先分布在白天。然后第8大的将与前7个中最小的一个一起,等等。你将继续用计划的最少的工作量来填充这一天。

小参考表最终出现的地方可能没有什么区别。

你可以编造一些不好的场景,但我希望它在实践中不会太复杂。

我不知道这是怎么回事 好的代码 扩展,但我要追求的解决方案是将作业列表放入按成本最高排序的优先级队列中,并将工作箱放入另一个按分配的最少工作排序的优先级队列中,然后从一个队列中弹出作业,并将其分配到顶部(最不忙)的工作箱中,直到没有剩余工作为止。

仅供参考,我就是这么做的。我想把这些“桶”放到一个持久化的表中,只需要每两周“重新计算”一次。否则,我担心如果我每天计算这些桶,一张表可能从一个桶跳到下一个桶。但是,我想经常为模式和DDL修改重新计算一次。这是那个片段。

-------------------------------------------------------------------------------------
--Get the total table size (by rows)
-------------------------------------------------------------------------------------


if object_id('tempdb..#Space') is not null
drop table #Space

SELECT 
    TableName = t.NAME,
    Schem = s.name,
    Pages = sum(a.total_pages),
    Grp = row_number() over (order by sum(a.total_pages) desc)
INTO #Space
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.name


-------------------------------------------------------------------------------------
--split the tables into 7 buckets by:
    --updating the Grp to the Grp with the lowest cumulative sum of all members by
    --ordering by the current cumulative sum of all members
-------------------------------------------------------------------------------------

declare @ct int = 8


while @ct <= (select max(Grp) from #Space)
begin

    update S
    set Grp = (select top 1 Grp from #Space where Grp < 8 order by sum(Pages) over (partition by Grp) asc)
    from #Space S
    where S.Grp = @ct

    set @ct = @ct + 1

end


insert into AdminTools..TableSpace (TableName
                                    ,Schem
                                    ,Pages
                                    ,Grp
                                    ,GrpPages
                                    ,LoadDate)
select 
    TableName
    ,Schem
    ,Pages
    ,Grp
    ,GrpPages = sum(Pages) over (partition by Grp)
    ,LoadDate = getdate()
from #Space
end