Grouping values from a table by Min/Max/Avg operation

Here is my scenario:

I have a single table with 2 columns. ID and Value. ID is int and 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 a SP that receives a grouping number (Group) and an operation (Op) and returns a new table in the following manner:

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

Group defines how many rows to combine into a single row (in the new table) and operation defines what operation to do on the group of rows, the operations I need are maximum, minimum and average. The last group may contain less rows than all the rest of the groups. if the last group has a single value IDstart = IDEnd. ID is unique but may have 'gaps'.

I'm looking for the fastest way to do this, any help will be appreciated.

Using SQL Server 2008 R2

Gilad.

---

Reasoning goes like this

• Using the ROW_NUMBER() function and some arithmetic allows you to create a dummy column placing each ID in a group of the size you specify.
• the result of this statement can be grouped and the operator you specify can be applied using a CASE statement. Should you need additional operators, you would only have to expand 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

---

You may find this useful:

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's for MySQL but it should be similar for SQL Server.

---

This appears to meet your requirement. Change the value of the @op parameter to MIN, MAX or AVG, and the @Group parameter to the size of the group. The NTILE ranking function is used to partition the groups, 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