SQL聚合函数用于减小结果集

Question

我有一个数据库，需要将记录聚合成另一个较小的集合。这个结果集应该包含原始记录特定列的最大值和最小值之间的差异，其中它们相加得到某个SUM（一个闭区间常数C）。

常数C确定了如何聚合原始记录，并且结果集中的任何条目都不会超过它。自然地，我应该按照自然主键顺序运行它。

举例来说：表格有：

• [key]
• [a]
• [b]
• [minColumn]
• [maxColumn]
• [N]

......全部都是int数据类型。

我需要一个结果集，其中MAX(maxColumn) - MIN(minColumn)的差值对于该组而言，当它们的差值总和小于或等于常数C时。除了MAX(maxColumn)和MIN(minColumn)值之外，我还需要在创建此结果集之前获得FIRST记录列[a]和LAST记录列[b]值。最后，应为组中所有原始记录SUM N列。

是否有一种有效的方法可以在没有游标的情况下完成这项任务？

-----[简单示例]------------------------------------------------------------

我正在尝试按稍微复杂的形式分组运行总和，即常数C。

只有一个表格，所有列都是int类型，样本数据如下：

declare @t table (
  PK int primary key
    , int a, int b, int minColumn, int maxColumn, int N 
)

insert @t values (1,5,6,100,200,1000)
insert @t values (2,7,8,210,300,2000)
insert @t values (3,9,10,420,600,3000)
insert @t values (4,11,12,640,800,4000)

因此，对于：

key, a,   b, minColumn, maxColumn,    N
---------------------------------------
1,   5,   6,       100,       200, 1000 
2,   7,   8,       210,       300, 2000 
3,   9,  10,       420,       600, 3000 
4,   11, 12,       640,       800, 4000 

我需要结果集看起来像这样，对于常数C为210：

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5       8                  100             300    3000 
9       10                 420             600    3000 
11      12                 640             800    4000 

[按照下面讨论的添加赏金和示例]

对于 C = 381，它应该包含 2 行：

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5            8             100             300    3000 
9           12             420             800    7000

希望这能更好地展示问题..如果有一个常数C，比如1000，你将在结果中得到1条记录：

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5           12             100             800   10000

- rama-jka toti

---

一组示例数据和示例结果将澄清这一点。 - dnagirl

你想根据什么分组？ - D'Arcy Rittich

你的问题缺乏解决所需的几乎所有内容：表格、列、样本数据、分组细节和期望结果。 - KM.

简单的例子已经添加，请让我知道是否需要进一步解释问题。谢谢。 - rama-jka toti

我正在尝试跟随您的示例，但我不清楚您是如何进行分组的。为什么结果集中的行数会根据C的值而变化？ - RMorrisey

Answer 1

DECLARE @c int
SELECT @c = 210

SELECT MIN(a) firstA,
       MAX(b) lastB, 
       MIN(minColumn) MIN_minColumn, 
       MAX(maxColumn) MAX_maxColumn, 
       SUM(N) SUM_N
FROM @t t 
JOIN (SELECT key, floor(sum/@c) as rank
        FROM (SELECT key, 
                     (SELECT SUM(t2.maxColumn - t2.minColumn) 
                        FROM @t t2 
                       WHERE t2.key <= t1.key 
                    GROUP BY t1.key) as sum
               FROM @t t1) A
     ) B on B.key = t.key
GROUP BY B.rank

/*

Table A: for each key, calculating SUM[maxColumn-minColumn] of all keys below it.
Table B: for each key, using the sum in A, calculating a rank so that:
  sum = (rank + y)*@c where 0 <= y < 1. 
  ex: @c=210, rank(100) = 0, rank(200) = 0, rank(220) = 1, ...
finally grouping by rank, you'll have what you want.

*/

- manji

---

很棒.. 我遇到的唯一问题是GROUP BY中不包含一个外部引用的列，至少MS SQL报告了这个解析错误。我猜它是在另一个数据库上编写的？尽管移除它仍然会产生正确的结果，但在实际操作中，我希望它在[key]更改时仍然存在(即生产中key通常是复合的)。有什么建议吗？无论如何都会接受答案... - rama-jka toti

谢谢。我不太清楚你想要做什么。我在 SQL Server 2005 上测试了这个查询，它是有效的。你是想修改它来添加其他列，还是将其作为连接表在另一个查询中使用？ - manji

已经缩小到差异追溯到 SQL 2000，它接受单个聚合子句或类似的 group-by。问题出在创建/真实表上，其中 GROUP BY 将无法通过解析...否则在样本和表变量上可以工作。 - rama-jka toti

好的，只是确认一下，对于所提出的问题（这是一个令人困惑的练习），解决方案确实有效。对于稍微修改过的数据，需要进行一些小的更改，但这是可以预料的。Najm，你做得很棒，感谢你的额外帮助和时间。我希望我能再次为解决方案点赞或者使用一些SO还没有的选项 :)。 - rama-jka toti

Answer 2

我对你想要生成的结果的分组逻辑有点困惑，但从你所描述的内容来看，我认为你需要使用HAVING子句。你应该可以这样做：

SELECT groupingA, groupingB, MAX(a) - MIN(b)
FROM ...
GROUP BY groupingA, groupingB
HAVING (MAX(a) - MIN(b)) < C

...为了过滤掉您的最大值和最小值之间的差异，一旦确定了分组，希望这对您有所帮助

- RMorrisey

---

编辑们非常友好地将帖子格式化得完美无缺 :) 上面的示例涵盖了4种情况，说明了这个问题，我承认有点奇怪。我愿意听取关于最简单、最短、最优雅的解决方法的建议，如果有人提供了被接受的解决方案，我会很高兴地重新发布并附上优化标题和赏金.. 但我不确定HAVING是否能产生上述示例的正确结果。 - rama-jka toti

Answer 3

声明 @c int

选择 @c = 210

选择 firstA = min(a), lastB = max(b), MIN_minColumn = min(minColumn), MAX_maxColumn = max(maxColumn), SUM_N = sum(N) 从 @t 其中 minColumn <= @c

联合全部

选择 a, b, minColumn, maxColumn, N 从 @t 其中 minColumn > @c

- Squirrel

---

非常接近，谢谢，但是对于C = 381没有生成正确的结果集。它应该包含2行：{ 5, 8, 100, 300, 3000 } { 9, 12, 420, 800, 7000 }但实际上它生成了3行，就像上面的示例一样。 - rama-jka toti

我不确定怎样实现，但我正在寻找一个WHERE语句的'组合'，使得 ( MAX_maxcolumn - MIN_mincolumn ) <= C.. 或者也许有别的方法来实现像上面C=381的情况处理。 - rama-jka toti

“MAX_maxcolumn - MIN_mincolumn ) <= C” 这个条件是相对于第一行还是可以在任何地方？ 对于以下样本，C = 381，预期结果是什么？ {1, 5, 6, 100, 200, 1000} {2, 7, 8, 210, 300, 2000} {3, 9, 10, 420, 600, 3000} {4, 11,12, 640, 800, 4000} {5, 13,14, 810, 850, 4000} {6, 15,16, 860, 870, 4000}记录5和6是否也会“分组”成一个记录？ - Squirrel

谢谢。它绝对不是相对于第一行，而更像是可以“任何地方”，即相对于“分组”的最后一条记录，或者更好地说，结果的最新记录。您的示例将生成{5,6,100,300,3000}，{9,12,420,800,7000}，{13,16,810,870,8000}...所以是的，5和6将被分组为1个记录。 - rama-jka toti