当增量值重新开始时如何排除行？

我是一个新手海报，但花了很多时间在这里研究答案。我不太清楚如何使用 SQL Server 2008 R2 创建 SQL 结果集，它可能应该使用更现代版本的超前/滞后。我试图根据一列的排序来聚合数据，但每个序列中可能有不同数量的实例。我知道序列已结束的唯一方法是当下一行具有较低的序列号时。所以它可能是 1-2、1-2-3-4、1-2-3，我必须弄清楚如何从中得到 3 个聚合。

源数据是如下所示的连接表（请帮我格式化）：

recordID instanceDate moduleID iResult interactionNum
1356    10/6/15 16:14   1        68          1
1357    10/7/15 16:22   1        100         2
1434    10/9/15 16:58   1        52          1
1435    10/11/15 17:00  1        60          2
1436    10/15/15 16:57  1        100         3
1437    10/15/15 16:59  1        100         4

我需要找到一种方法，根据最后一列中的值将本示例中的前 2 行与后 4 行分开。

我希望最终得到的是一个如下所示的结果集，它根据分组对 iResult 列进行平均，并从分组中获取第一个实例日期：

instanceDate    moduleID    iResult
10/6/15           1          84
10/9/15           1          78

如果我能找到一种分离组的方法，我可以使用 MIN 和 AVG 聚合得到这个结果。数据按instanceDate排序（请忽略此处的日期格式），然后当查询找到interactionNum

这是我到目前为止的查询（包括给出上述数据集的联接）：

SELECT 
    X.* 
FROM
   (SELECT TOP 100 PERCENT   
        instanceDate, b.ModuleID, iResult, b.interactionNum 
    FROM 
        (firstTable a  
    INNER JOIN 
        secondTable b ON b.someID = a.someID)       
    WHERE 
        a.someID = 2        
        AND b.otherID LIKE 'xyz'    
        AND a.ModuleID = 1
    ORDER BY 
        instanceDate) AS  X

OUTER APPLY

(SELECT TOP 1 
     *
 FROM
     (SELECT    
          instanceDate, d.ModuleID, iResult, d.interactionNum   
      FROM 
          (firstTable c  
      INNER JOIN 
          secondTable d ON d.someID = c.someID) 
      WHERE 
          c.someID = 2      
          AND d.otherID LIKE 'xyz'  
          AND c.ModuleID = 1    
          AND d.interactionNum = X.interactionNum
          AND c.instanceDate < X.instanceDate)  X2
      ORDER BY 
          instanceDate DESC) Y
WHERE 
    NOT EXISTS (SELECT Y.interactionNum INTERSECT SELECT X.interactionNum)

但这会返回一个临时结果集，如下所示：

instanceDate    ModuleID    iResult interactionNum
10/6/15 16:10   1            68         1
10/6/15 16:14   1            100        2
10/15/15 16:57  1            100        3
10/15/15 16:59  1            100        4

问题是，interactionNum 3、4 不属于这个结果集。当我循环此查询时，它们将进入下一个结果集。如何将它们排除在本次迭代的结果集中？我需要此查询的结果集仅包含前两行，“看到”源数据的第 3 行的 interactionNum 值低于第 2 行的值。

不确定应该使用什么 ModuleID，但我猜您正在寻找类似这样的东西：

select min (instanceDate), [moduleID], avg([iResult])
from (
  select *,row_number() over (partition by [moduleID] order by instanceDate) as RN
  from Table1
) X
group by [moduleID], RN - [interactionNum]

这里的想法是为每个 moduleid 创建一个带有 row_number 的流水号，然后使用该流水号与 InteractionNum 之间的差值作为分组标准。

示例中SQL小提琴 http://sqlfiddle.com/#!3/ab33c/1