在SQL中按计数分区 [英] Partitioning by counts in SQL

问题描述

此问题与问题此处相关。我有一张这样的表：

This issue is related to question I asked here. I have a table that looks like this:

Item    Count
1       1
2       4
3       8
4       2
5       6
6       3

我需要将例如小于5的项目分组到一个新的组中，并且每个组的总和应该至少为5.结果应如下所示：

I need to group items that are, for example, less than 5 into a new group and the total of each groups should be at least 5. The result should look like this:

Item    Group   Count  
1       1       1
2       1       4
3       2       8
4       3       2
5       4       6
6       3       3

如何实现？非常感谢。

推荐答案

为什么这不是正确的结果？

Why isn't this a correct result?

Item    Group   Count  
1       1       1
2       2       4
3       3       8
4       4       2
5       5       6
6       1       3

还是这个？

Item    Group   Count  
1       1       1
2       2       4
3       3       8
4       4       2
5       5       6
6       6       3

似乎我想要解决答案将项目分组以最小化组的数量，并最大化每组中的项目数量，不超过限制5'。这听起来像背包问题。也许你应该阅读 Celko的SQL Stumper：类调度问题和提出的解决方案。其他人也已经解决了这个问题，例如。 而现在完全不合适的使用SQL Server 。抬头：这不是一个微不足道的问题。任何天真的算法将会死亡，试图在1M行表上解决这个问题。

Seems to me that you're trying to solve the answer 'how to group the items as to minimize the number of groups and maximize the number of items in each group, w/o exceeding the limit 5'. Which sounds a lot like the Knapsack problem. Perhaps a you should read the Celko's SQL Stumper: The Class Scheduling Problem and the solutions proposed. Others have also approached this problem, eg. And now for a completely inappropriate use of SQL Server. Heads up: this is no a trivial problem by any means. Any naive algorithm will die a slow death attempting to solve it on a 1M rows table...

这篇关于在SQL中按计数分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！