如何在SQL Server中按文件数将文件夹划分为近似均衡的组

我在SQL Server中有一张表，记录了50-100个文件夹及其对应的文件数量（范围从1到数百万不等），总文件数不固定。需要指定分组数量（通常4组，最多8组），将这些文件夹划分为指定数量的组，使每组的总文件数相对均衡。

尝试过的方法及问题 #

• 曾用RANK()函数按文件数排名后循环分配（如1、5、9...进组1，2、6、10...进组2），但文件夹文件数差异极大时无法满足均衡要求
• NTILE()函数也不适用
• 设想过通过SUM()计算总文件数，结合组数设定目标值，再用累计求和分组，但不确定灵活性

示例数据 #

CREATE TABLE HypotheticalValues(SubFolder varchar(50), FileCount int);
INSERT INTO HypotheticalValues
VALUES ('Folder_01',1641315),('Folder_02',1455152),('Folder_03',844307),('Folder_04',832644),('Folder_05',450814),('Folder_06',374512),('Folder_07',343619),('Folder_08',227136),('Folder_09',167329),('Folder_10',164884),('Folder_11',148423),('Folder_12',94408),('Folder_13',92737),('Folder_14',84281),('Folder_15',73686),('Folder_16',58376),('Folder_17',56164),('Folder_18',34627),('Folder_19',29531),('Folder_20',29278),('Folder_21',28901),('Folder_22',28473),('Folder_23',27756),('Folder_24',24234),('Folder_25',23026),('Folder_26',22009),('Folder_27',18153),('Folder_28',17066),('Folder_29',12946),('Folder_30',10221),('Folder_31',7393),('Folder_32',6710),('Folder_33',6339),('Folder_34',5002),('Folder_35',4525),('Folder_36',2988),('Folder_37',2749),('Folder_38',2718),('Folder_39',2419),('Folder_40',1661),('Folder_41',569),('Folder_42',381),('Folder_43',233),('Folder_44',217),('Folder_45',209),('Folder_46',151),('Folder_47',134),('Folder_48',115),('Folder_49',103),('Folder_50',80),('Folder_51',60),('Folder_52',60),('Folder_53',54),('Folder_54',48),('Folder_55',24),('Folder_56',18),('Folder_57',15),('Folder_58',15),('Folder_59',10),('Folder_60',8),('Folder_61',8),('Folder_62',5),('Folder_63',4),('Folder_64',4),('Folder_65',4),('Folder_66',3),('Folder_67',2),('Folder_68',2),('Folder_69',2),('Folder_70',2),('Folder_71',2),('Folder_72',1),('Folder_73',1),('Folder_74',1);

期望结果（@NumGroups=4） #

SubFolder       FileCount   Group
------------------- 
Folder_01       1641315     1
Folder_12       94408       1
Folder_19       29531       1
Folder_20       29278       1
Folder_21       28901       1
Folder_28       17066       1
Folder_29       12946       1
Folder_30       10221       1
Folder_44       217         1
Folder_45       209         1
Folder_46       151         1
Folder_47       134         1
Folder_48       115         1
Folder_49       103         1   
Folder_64       4           1
Folder_65       4           1
Folder_66       3           1
Folder_67       2           1
Folder_68       2           1
Folder_69       2           1
Folder_70       2           1
Folder_71       2           1
Folder_72       1           1
Folder_73       1           1
Folder_74       1           1  -- 1864619 files total
Folder_02       1455152     2
Folder_07       343619      2
Folder_25       23026       2
Folder_26       22009       2
Folder_27       18153       2
Folder_39       2419        2
Folder_40       1661        2
Folder_41       569         2
Folder_42       381         2
Folder_43       233         2
Folder_57       15          2
Folder_58       15          2
Folder_59       10          2
Folder_60       8           2
Folder_61       8           2
Folder_62       5           2
Folder_63       4           2  -- 1867287 files total
Folder_03       844307      3
Folder_06       374512      3
Folder_09       167329      3
Folder_10       164884      3
Folder_15       73686       3
Folder_16       58376       3
Folder_17       56164       3
Folder_18       34627       3
Folder_24       24234       3
Folder_23       27756       3
Folder_31       7393        3
Folder_32       6710        3
Folder_33       6339        3
Folder_34       5002        3
Folder_35       4525        3
Folder_36       2988        3
Folder_37       2749        3
Folder_38       2718        3  -- 1864299 files total
Folder_04       832644      4
Folder_05       450814      4
Folder_08       227136      4
Folder_11       148423      4
Folder_13       92737       4
Folder_14       84281       4
Folder_22       28473       4
Folder_50       80          4
Folder_51       60          4
Folder_52       60          4
Folder_53       54          4
Folder_54       48          4
Folder_55       24          4
Folder_56       18          4  -- 1864852 files total

分组要求 #

分组无需完全精确，只需近似均衡，通常各组文件数差值控制在20万以内，最大的1-2个文件夹可适当放宽限制。

内容的提问来源于stack exchange，提问作者WJTownsend