较小结果集的 SQL 聚合

Question

我有一个数据库，我需要将其记录聚合到另一个较小的集合中。此结果集应包含原始记录的特定列的最大值和最小值之差，它们加起来等于某个 SUM，一个闭区间常数 C。

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

为了说明:表有:

• [键]
• [一个]
• [b]
• [最小列]
• [最大列数]
• [N]

...都是 int 数据类型。

除了 MAX(maxColumn) 和 MIN(minColumn) 值之外，在该结果集中创建新条目之前，我还需要 FIRST 记录列 [a] 和 LAST 记录列 [b] 值。最后，应对一组中的所有原始记录求和 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 

对于 210 的常量 C，我需要结果集看起来像:

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

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.

*/