SQL Server – Sum by Implicit Group Using Window Functions

order-bysql serverwindow functions

I'm sure I'm missing an obvious solution on this, but I'm trying to summarize values for groups that are defined by an explicit group number and an implicit ordering. I'm sure that doesn't make this much clearer, so let's say I have this example source heap table:

GroupID     Value
----------- -----------
1           5
1           5
1           3
2           4
2           1
1           4
2           3
2           5
2           2
1           1

I would like a query that provides me with the following results:

GroupID     Values
----------- -----------
1           13
2           5
1           4
2           10
1           1

The implied ordering is the challenge that I've just not found a way to get around… yet. Any help would be appreciated.

I was hoping I could create a deterministic row ordering with a query similar to the following:

SELECT    * 
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum  
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

I am hoping this forces an allocation order scan which would then give me a deterministic row order.

Sadly, I'm stuck with the data as is. I've got no other indicators here such as date, etc. that would provide any set order. My hope was the trick outlined above would suffice, but I'm not entirely certain it will.

EDIT: Just to close this up as I'm know there were questions around why I was asking this, I had a series of heap tables, named by month/year, that contained line-item amounts that the business wanted to be summed up by day (which they correlated to the implied groups in my question). Because it doesn't look feasible to do this effectively, we've settled on aggregating at the month (e.g. table) level, so this post helped me justify the alteration to the business requirements. Thanks for everyone's input!

Best Answer

The "implicit" group you mention seems to be based on the row order. Unlike a spreadsheet or text file, a relational table is logically an unordered set of rows regardless of whether it is stored as a heap or has a clustered index. It will not be possible to write a query to provide the desired results unless you have another column to facilitate the grouping.

I was hoping I could create a deterministic row ordering with a query similar to the following:
SELECT      * 
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum  
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

Sorry to be the bearer of bad news, but ordering by a literal will not guarantee deterministic ordering (and even if it did, you'd need an ORDER BY clause there). If it appears to do so, it is only by happenstance. Any incremental column will work, though.

I am hoping this forces an allocation order scan which would then give me a deterministic row order.

An allocation-ordered scan is no more deterministic than any other implementation; you're just (unsafely) relying on a different observed behaviour.

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Mysql – How to do a complex GROUP BY in MySQL

What about doing a little math against your ID column to dynamically generate the group?

SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp

This would give you groups of 10 based on the ID of the record. I used your animals table above to generate the data below.

Sample data

 INSERT INTO animals VALUES
 ('mammal',10,'dog'),('mammal',11,'dog'),('mammal',12,'dog'),
 ('mammal',21,'cat'),('mammal',22,'cat'),('mammal',23,'cat'),
 ('mammal',24,'cat'),('mammal',25,'cat'),('mammal',26,'cat'),
 ('bird',30,'penguin'),('bird',31,'penguin'),('bird',32,'penguin'),
 ('bird',33,'penguin'),('fish',44,'lax'),('fish',45,'lax'),
 ('fish',46,'lax'),('fish',47,'lax'),('fish',48,'lax'),
 ('mammal',31,'whale'),*'fish',51,'lax'),('fish',52,'lax'),
 ('fish',53,'lax'),('fish',54,'lax'),('bird',10,'ostrich');

Query Output

 +--------+--------+
 | grp    | id_grp |
 +--------+--------+
 | fish   |      4 |
 | fish   |      5 |
 | mammal |      1 |
 | mammal |      2 |
 | mammal |      3 |
 | bird   |      1 |
 | bird   |      3 |
 +--------+--------+
 7 rows in set (0.00 sec)

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

Mysql – How to do a complex GROUP BY in MySQL

Related Question