Grouping values ​​from a table using the Min / Max / Avg operation

Question

Here is my scenario:

I have one table with two columns. ID and value. ID is int, and the value is real.

ID Value 1 6.7 2 8.9 3 4.5 5 3.2 8 2.5 9 2.1 10 1.0 15 2.3 18 2.4 19 4.0 20 3.2 

I would like to compose an SP that gets the group number (Group) and operation (Op) and returns a new table as follows:

Group = 2, Op = Max

 IDstart IDend Value 1 2 8.9 3 5 4.5 8 9 2.5 10 15 2.3 18 19 4.0 20 20 3.2 

Group = 3, Op = Min

 IDstart IDend Value 1 3 4.5 5 9 2.1 10 18 1.0 19 20 3.2 

The group determines how many rows are combined into one row (in a new table), and the operation determines which operation should be performed in the row group, the required operations are maximum, minimum and average. The last group may contain fewer lines than all other groups. if the last group has one value IDstart = IDEnd. The identifier is unique, but may have spaces.

I am looking for the fastest way to do this, any help would be appreciated.

Using SQL Server 2008 R2

Gilad.

Answer 1

The reasoning is as follows

• Using the ROW_NUMBER() function, and some arithmetic allows you to create a dummy column by placing each identifier in a group of the size you specify.
• the result of this statement can be grouped, and the statement you specify can be applied using the CASE statement. If you need additional operators, you will need to extend this CASE statement.

Script

 DECLARE @Group INTEGER DECLARE @Op VARCHAR(3) SET @Group = 3 SET @Op = 'MIN' ;WITH q(ID, Value) AS ( SELECT 1, 6.7 UNION ALL SELECT 2, 8.9 UNION ALL SELECT 3, 4.5 UNION ALL SELECT 5, 3.2 UNION ALL SELECT 8, 2.5 UNION ALL SELECT 9, 2.1 UNION ALL SELECT 10, 1.0 UNION ALL SELECT 15, 2.3 UNION ALL SELECT 18, 2.4 UNION ALL SELECT 19, 4.0 UNION ALL SELECT 20, 3.2 ) SELECT [IDStart] = MIN(ID) , [IDEnd] = MAX(ID) , [Value] = CASE WHEN @Op = 'MAX' THEN MAX(Value) WHEN @Op = 'MIN' THEN MIN(Value) WHEN @Op = 'AVG' THEN AVG(Value) END FROM ( SELECT ID , Value , GroupRow = (ROW_NUMBER() OVER (ORDER BY ID) - 1) / @Group FROM q ) q GROUP BY GroupRow 

Answer 2

You may find this helpful:

 SET @idx = 0; SET @grp_size = 3; SELECT MIN(`temp1`.`id`) as `IDstart`, MAX(`temp1`.`id`) as `IDend`, AVG(`temp1`.`value`) as `agregate` FROM ( SELECT ID AS `id` , @idx := @idx +1 / @grp_size , FLOOR( @idx ) AS `grouper`, `value` FROM `test1` ) as `temp1` GROUP BY `temp1`.`grouper` 

It is for MySQL, but it should look like SQL Server.

Answer 3

It meets your requirements. Change the @op parameter to MIN, MAX, or AVG and the @Group parameter to the size of the group. The NTILE ranking NTILE used to separate groups, and then ROW_NUMBER to identify the first / lat member of each group.

 DECLARE @t TABLE (id INT, VALUE REAL ) INSERT @t (id,VALUE) VALUES (1, 6.7), (2, 8.9), (3, 4.5), (5, 3.2), (8, 2.5), (9, 2.1), (10, 1.0), (15, 2.3), (18, 2.4), (19, 4.0), (20, 3.2) DECLARE @Group DECIMAL(5,1) = 3.0 DECLARE @Bucket INT DECLARE @op char(3) = 'MIN' --MAX, AVG SELECT @Bucket = CEILING(COUNT(1)/@Group) FROM @t ;WITH bucketCTE AS ( SELECT *,NTILE(@Bucket) OVER (ORDER BY id) bucket FROM @t ) ,rankCTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY bucket ORDER BY id ASC ) AS rn, ROW_NUMBER() OVER (PARTITION BY bucket ORDER BY id DESC ) AS rn2 FROM bucketCTE ) ,groupCTE AS ( SELECT AVG(VALUE) average, MIN(VALUE) minimum, MAX(VALUE) maximum, bucket FROM bucketCTE GROUP BY bucket ) SELECT r1.id minId, r2.id maxId , CASE WHEN @op = 'AVG' THEN g.average WHEN @op = 'MIN' THEN g.minimum WHEN @op = 'MAX' THEN g.maximum ELSE NULL END AS value FROM rankCTE AS r1 JOIN rankCTE AS r2 ON r2.bucket = r1.bucket AND r2.rn2 = 1 JOIN groupCTE AS g ON g.bucket = r1.bucket WHERE r1.rn = 1 ORDER BY r1.bucket