views:

202

answers:

3

I have a database for which I need to aggregate records into another smaller set. This result set should contain the difference between maximum and minumum of specific columns of the original records where they add up to certain SUM, a closed interval constant C.

The constant C determines how the original records are aggregated and no entry in the resulting set ever exceeds it. Naturally I am supposed to run this in natural primary key order..

To illustrate: table has:

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

...all are int datatype.

I am after a result set that has entries where the MAX(maxColumn) - MIN(minColumn) for that group such that when their difference is summed up it is less or equal to constant C.

Apart from the MAX(maxColumn) and MIN(minColumn) value I also need the FIRST record column [a] and LAST record column [b] values before creating a new entry in this result set. Finally, the N column should be SUMmed for all original records in a group.

Is there an efficient way to do this without cursors?

-----[Trivial Sample]------------------------------------------------------------

I am attempting to group-by a slightly complicated form of a running sum, constant C.

There is only one table, columns are all of int type and sample data

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)

Thus for:

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

I need the result set to look like, for a constant C of 210 :

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

[ Adding the bounty and sample as discussed below]

For C = 381, It should contain 2 rows:

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


Hope this demonstrates the problem better.. and for a constant C say 1000 you would get 1 record in the result:

firstA | lastB | MIN_minColumn | MAX_maxColumn | SUM_N
5           12             100             800   10000
+1  A: 

declare @c int

select @c = 210

select firstA = min(a), lastB = max(b), MIN_minColumn = min(minColumn), MAX_maxColumn = max(maxColumn), SUM_N = sum(N) from @t where minColumn <= @c

union all

select a, b, minColumn, maxColumn, N from @t where minColumn > @c

Squirrel
Pretty close, thanks, but not generating the right result set for C = 381. It should contain 2 rows:{ 5, 8, 100, 300, 3000 }{ 9, 12, 420, 800, 7000 }But instead it generates 3 as in sample above.
rama-jka toti
Not sure how to pull it off, but I'm looking for a WHERE clause to have the 'grouping' such that ( MAX_maxcolumn - MIN_mincolumn ) <= C.. or perhaps there is another way in order to achieve handling such as the one for C=381 above.
rama-jka toti
Squirrel
rama-jka toti
+1  A: 

I am a little confused on the grouping logic for result you are trying to produce, but from the description of what you are looking for, I think you need a HAVING clause. You should be able to do something like:

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

...in order to filter out the difference between your max and min values, once you've determined your grouping. Hope this is helpful

RMorrisey
The editors were kind enough to format the post to perfection :) The samples above cater for 4 scenarios that illustrate the problem, which I admit is a bit odd.I am open to suggestions on the simplest, shortest, elegant way of doing this, I will gladly repost with optimisation-heading with a bounty for it and reference to an accepted solution.. but I'm not sure HAVING would produce the correct results for the samples above.
rama-jka toti
+2  A: 
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.

*/
najmeddine
Wonderful.. The only problem I am having is with the GROUP BY not containing a column that is an outer reference, at least MS SQL reports this parsing error. I presume it was written on another DB? Removing it will still produce the correct results but in practice I would like it to be there for [key] changes (ie. key is often compound in production). Any suggestions on that? Will accept the answer regardless...
rama-jka toti
najmeddine
Have narrowed it down to difference dating back all the way to SQL 2000 which accepted group-by on single aggregation clause or similar. The problem is with created/real tables, where GROUP BY will not pass the parsing.. otherwise on the sample and the table *variable* it works.
rama-jka toti
yeah, no problem.
najmeddine
Ok, just to confirm that for the problem as presented (and it was a confusing exercise) the solution DOES work. For a slightly modified data it requires minor changes but that's to be expected. Wonderful work Najm, thanks for additional help and your time. I wish I could upvote on the solution again or some option SO doesn't have yet :)..
rama-jka toti