按Min/Max/Avg操作对表中的值进行分组

Question

下面是我的场景:

我有一个2列的表。ID和Value。ID为整型，value为实值。

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

我想编写一个SP，它接收分组号(Group)和操作(Op)，并以以下方式返回一个新表:

组= 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

组= 3，Op = Min

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

Group定义了将多少行合并为单行(在新表中)，operation定义了对这组行执行什么操作，我需要的操作是最大，最小和平均。最后一个组可能比所有其他组包含更少的行。如果最后一组只有一个值，则IDstart = IDEnd。ID是唯一的，但可能有"空白"。

我正在寻找最快的方法来做到这一点，任何帮助将不胜感激。

使用SQL Server 2008 R2

吉拉德。

Answer 1

推理是这样的

• 使用ROW_NUMBER()函数和一些算术允许您创建一个虚拟列，将每个ID放在您指定大小的组中。
• 可以对该语句的结果进行分组，并且可以使用CASE语句应用指定的操作符。如果你需要额外的操作符，你只需要扩展这个CASE语句。

脚本

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

您可能会发现这很有用:

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`

Answer 3

这似乎符合您的要求。将"@op"参数的值修改为"MIN"、"MAX"或"AVG"，将"@Group"参数的值修改为组的大小。NTILE排序函数用于划分组，ROW_NUMBER用于识别每个组的第一个/最后一个成员。

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