通过交替行组进行 SQL 分区

我有一个类似这样的数据表。

|Key|LotId|TransactionType|Quantity|Destination
|1  |A    |Transform      |NULL    |Foo
|2  |A    |Transform      |NULL    |Bar
|3  |A    |Consume        |100     |NULL
|4  |B    |Transform      |NULL    |Bob
|5  |B    |Transform      |NULL    |Fred
|6  |B    |Consume        |75      |NULL
|7  |B    |Consume        |50      |NULL
|8  |B    |Transform      |NULL    |Sally
|9  |B    |Transform      |NULL    |Fred
|10 |B    |Consume        |60      |NULL
|11 |C    |Transform      |NULL    |Bar
|12 |C    |Transform      |NULL    |Fred
|13 |C    |Consume        |25      |NULL

转换线告诉我我的数量去了哪里，消耗线告诉我使用了多少数量。消耗线适用于该 LotId 的所有先前变换线，直到先前的 LotId，或者如果它与先前的变换和消耗分组具有相同的 LotId。更麻烦的是，一组内的转换和消耗线的数量是可变的。我所做的一件事是，变换线首先出现，然后消耗，下次我遇到变换时，我知道一个新的分组已经开始。

|Key|LotId|TransactionType|Quantity|Destination|Grouping
|1  |A    |Transform      |NULL    |Foo        |A1
|2  |A    |Transform      |NULL    |Bar        |A1
|3  |A    |Consume        |100     |NULL       |A1
---------------------------------------------------------
|4  |B    |Transform      |NULL    |Bob        |B1
|5  |B    |Transform      |NULL    |Fred       |B1
|6  |B    |Consume        |75      |NULL       |B1
|7  |B    |Consume        |50      |NULL       |B1
---------------------------------------------------------
|8  |B    |Transform      |NULL    |Sally      |B2
|9  |B    |Transform      |NULL    |Fred       |B2
|10 |B    |Consume        |60      |NULL       |B2
---------------------------------------------------------
|11 |C    |Transform      |NULL    |Bar        |C1
|12 |C    |Transform      |NULL    |Fred       |C1
|13 |C    |Consume        |25      |NULL       |C1

（出于本示例的目的，我们假设数量在所有各方之间平均分配）

• A1 组 Foo 和 Bar 各 100 人
• B1 组 Bob 和 Fred 各有 125 人
• B2 组 Sally 和 Fred 各 60 人
• C1组，巴尔和弗雷德各有25人

使用sqlRANK(), DENSE_RANK(), & ROW_NUMBER()窗口我正在尝试制定一个查询，它将给我这个分组。一旦我能够获得这一分组，我就应该能够将数据重新连接到自身上，并最终确定我的每个目的地收到了多少数据。

这是在 SQL2008 上的。

使用组合公用表表达式 https://msdn.microsoft.com/en-us/library/ms175972.aspx, outer apply() https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx, and dense_rank() https://msdn.microsoft.com/en-us/library/ms173825.aspx

注意：我更改了列Key to tKey所以我不必在它周围使用方括号。

;with cte as (
  select *
    , PrevTransactionType=isnull(x.Prev_TransactionType,'Consume')
  from t
  outer apply (
      select top 1
        Prev_TransactionType = TransactionType
      from t as i
      where i.tKey < t.tKey
      order by i.tKey desc 
      ) as x
)
select  t.tKey, t.LotId, t.TransactionType, t.Quantity, t.Destination
 , Grouping = LotId + convert(varchar(10),dense_rank() over (
    partition by LotId 
    order by GroupNumber
    )
  )
from cte as t
outer apply (
  select top 1 
    GroupNumber = i.tKey
    from cte as i
    where i.tKey <= t.tKey
      and i.TransactionType = 'Transform'
      and i.PrevTransactionType = 'Consume'
    order by i.tKey desc
    ) x

测试设置：http://rextester.com/LWV40248 http://rextester.com/LWV40248

results:

+------+-------+-----------------+----------+-------------+----------+
| tKey | LotId | TransactionType | Quantity | Destination | Grouping |
+------+-------+-----------------+----------+-------------+----------+
|    1 | A     | Transform       | NULL     | Foo         | A1       |
|    2 | A     | Transform       | NULL     | Bar         | A1       |
|    3 | A     | Consume         | 100      | NULL        | A1       |
|    4 | B     | Transform       | NULL     | Bob         | B1       |
|    5 | B     | Transform       | NULL     | Fred        | B1       |
|    6 | B     | Consume         | 75       | NULL        | B1       |
|    7 | B     | Consume         | 50       | NULL        | B1       |
|    8 | B     | Transform       | NULL     | Sally       | B2       |
|    9 | B     | Transform       | NULL     | Fred        | B2       |
|   10 | B     | Consume         | 60       | NULL        | B2       |
|   11 | C     | Transform       | NULL     | Bar         | C1       |
|   12 | C     | Transform       | NULL     | Fred        | C1       |
|   13 | C     | Consume         | 25       | NULL        | C1       |
+------+-------+-----------------+----------+-------------+----------+