sql - 如何仅在表中的字段更改值之前对表中的字段进行分组(SQL Server)

Question

我有下表'ProductTable'

Product   | Series | Date  
  aaa     | a1     | 2016-05-17 08:02:54.960 
  aaa     | a1     | 2016-05-17 08:03:54.960
  aaa     | a1     | 2016-05-17 08:04:54.960
  bbb     | b1     | 2016-05-17 08:05:54.960
  bbb     | b1     | 2016-05-17 08:06:54.960
  aaa     | a1     | 2016-05-17 08:07:54.960
  aaa     | a1     | 2016-05-17 08:08:54.960

我尝试了谷歌的几个步骤，发现了一个名为 row_number() over 的东西，但由于我是 SQL SERVER 的新手，所以无法使用它获得确切的结果。我希望结果是这样的 - ->

Product   | Series | Qty |  Cumml.
  aaa     | a1     | 3   |    3
  bbb     | b1     | 2   |    5
  aaa     | a1     | 2   |    7

如何进行这种性质的分组？请帮忙....

Answer 1

这是一个标准的孤岛解决方案，带有一些累积和的窗口聚合函数:

DECLARE @p TABLE
    (
      Product CHAR(3) ,
      Date DATETIME
    )
INSERT  INTO @p
VALUES  ( 'aaa', '2016-05-17 08:02:54.960' ),
        ( 'aaa', '2016-05-17 08:03:54.960' ),
        ( 'aaa', '2016-05-17 08:04:54.960' ),
        ( 'bbb', '2016-05-17 08:05:54.960' ),
        ( 'bbb', '2016-05-17 08:06:54.960' ),
        ( 'aaa', '2016-05-17 08:07:54.960' ),
        ( 'aaa', '2016-05-17 08:08:54.960' );
WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY date )
                        - ROW_NUMBER() OVER ( PARTITION BY Product ORDER BY Date ) AS rn
               FROM     @p
             )
    SELECT  rn ,
            product ,
            COUNT(*) AS Cnt,
            SUM(COUNT(*)) OVER ( ORDER BY MAX(Date) ) AS Cumul
    FROM    cte
    GROUP BY rn ,
            product
    ORDER BY Cumul

输出:

rn  product Cnt Cumul
0   aaa     3   3
3   bbb     2   5
2   aaa     2   7