Sequential Group By在sql server中 [英] Sequential Group By in sql server

问题描述

对于此表：

  + ---- + -------- + ---- --- + 
 | ID |状态|值| 
 + ---- + -------- + ------- + 
 | 1 | 1 | 4 | 
 | 2 | 1 | 7 | 
 | 3 | 1 | 9 | 
 | 4 | 2 | 1 | 
 | 5 | 2 | 7 | 
 | 6 | 1 | 8 | 
 | 7 | 1 | 9 | 
 | 8 | 2 | 1 | 
 | 9 | 0 | 4 | 
 | 10 | 0 | 3 | 
 | 11 | 0 | 8 | 
 | 12 | 1 | 9 | 
 | 13 | 3 | 1 | 
 + ---- + -------- + ------- + 
  

我需要使用相同的 Status 来计算顺序组以产生此结果。

  + -------- + ------------ + 
 |状态|总和（Value）| 
 + -------- + ------------ + 
 | 1 | 20 | 
 | 2 | 8 | 
 | 1 | 17 | 
 | 2 | 1 | 
 | 0 | 15 | 
 | 1 | 9 | 
 | 3 | 1 | 
 + -------- + ------------ + 
  

如何在SQL Server中执行此操作？

注意： ID中的值

解决方案

根据我向您的问题添加的标签，这是一个缺口和孤岛问题。

表现最佳的解决方案可能是

  WITH T 
 $ as（SELECT *，
 ID --ROW_NUMBER（）OVER（PARTITION BY [STATUS] ORDER BY [ID]）AS GRP 
 FROM YourTable）
 SELECT [STATUS]，
 SUM（[VALUE]）AS [SUM（VALUE）] 
 FROM T 
 GROUP BY [STATUS]，
 Grp 
 ORDER BY MIN（ID）
  

如果 ID 值不能保证连续，需要使用

  ROW_NUMBER（）OVER（ORDER BY [ID]） -  
 ROW_NUMBER（）OVER（PARTITION BY [STATUS] ORDER BY [ID]）AS Grp 
  

取而代之的是CTE定义。

SQL小提琴

For this Table:

+----+--------+-------+
| ID | Status | Value |
+----+--------+-------+
|  1 |      1 |     4 |
|  2 |      1 |     7 |
|  3 |      1 |     9 |
|  4 |      2 |     1 |
|  5 |      2 |     7 |
|  6 |      1 |     8 |
|  7 |      1 |     9 |
|  8 |      2 |     1 |
|  9 |      0 |     4 |
| 10 |      0 |     3 |
| 11 |      0 |     8 |
| 12 |      1 |     9 |
| 13 |      3 |     1 |
+----+--------+-------+

I need to sum sequential groups with the same Status to produce this result.

+--------+------------+
| Status | Sum(Value) |
+--------+------------+
|      1 |         20 |
|      2 |          8 |
|      1 |         17 |
|      2 |          1 |
|      0 |         15 |
|      1 |          9 |
|      3 |          1 |
+--------+------------+

How can I do that in SQL Server?

NB: The values in the ID column are contiguous.

解决方案

Per the tag I added to your question this is a gaps and islands problem.

The best performing solution will likely be

WITH T
     AS (SELECT *,
                ID - ROW_NUMBER() OVER (PARTITION BY [STATUS] ORDER BY [ID]) AS Grp
         FROM   YourTable)
SELECT [STATUS],
       SUM([VALUE]) AS [SUM(VALUE)]
FROM   T
GROUP  BY [STATUS],
          Grp
ORDER  BY MIN(ID)

If the ID values were not guaranteed contiguous as stated then you would need to use

ROW_NUMBER() OVER (ORDER BY [ID]) - 
       ROW_NUMBER() OVER (PARTITION BY [STATUS] ORDER BY [ID]) AS Grp

Instead in the CTE definition.

SQL Fiddle

这篇关于Sequential Group By在sql server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！