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

Here is my scenario:

这是我的情景：

I have a single table with 2 columns. ID and Value. ID is int and value is real.

我有一个包含2列的单个表。 ID和价值。 ID是int，值是实数。

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:

我想编写一个接收分组编号（组）和操作（Op）的SP，并按以下方式返回一个新表：

Group = 2, Op = Max

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

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

组定义要合并到一行中的行数（在新表中），操作定义要对行组执行的操作，我需要的操作是最大，最小和平均。最后一组可能包含的行数少于其他所有组。如果最后一个组具有单个值IDstart = IDEnd。 ID是唯一的，但可能有“差距”。

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

我正在寻找最快的方法，任何帮助将不胜感激。

Using SQL Server 2008 R2

使用SQL Server 2008 R2

Gilad.

吉拉德。

3 个解决方案

#1

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.
使用ROW_NUMBER（）函数和一些算法可以创建一个虚拟列，将每个ID放在指定大小的组中。
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.
可以对此语句的结果进行分组，并使用CASE语句应用您指定的运算符。如果您需要其他运算符，则只需展开此CASE语句即可。

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

3 个解决方案

#1

更多相关文章

随机推荐