将唯一id添加到已排序事务的组中

Question

我目前有一个表，其中包含按顺序为每组排序的事务，如下所示：

| transaction_no | value |
|----------------|-------|
|              1 |     8 |
|              2 |   343 |
|              3 |    28 |
|              4 |   102 |
|              1 |    30 |
|              2 |     5 |
|              3 |   100 |
|              1 |    12 |
|              2 |    16 |
|              3 |    28 |
|              4 |   157 |
|              5 |   125 |

然而，我有兴趣添加另一列，为每个列分配一个唯一的ID分组(事务集，其中transaction_no以1开头，以x结尾其中紧接在x之后的transaction_no是1(。所以目标是这样一张表：

| transaction_no | value | stmt_id |
|----------------|-------|---------|
|              1 |     8 |    1001 |
|              2 |   343 |    1001 |
|              3 |    28 |    1001 |
|              4 |   102 |    1001 |
|              1 |    30 |    1002 |
|              2 |     5 |    1002 |
|              3 |   100 |    1002 |
|              1 |    12 |    1003 |
|              2 |    16 |    1003 |
|              3 |    28 |    1003 |
|              4 |   157 |    1003 |
|              5 |   125 |    1003 |

我该怎么做？

Answer 1

这是间隙和孤岛问题的变体。正如Gordon Linoff所评论的那样，为了使其可解，您需要一个可用于对行进行排序的列。我假设存在这样一个列，它被称为id。

典型的解决方案包括对记录进行排序并执行窗口求和。当总排名和窗口总和之间的差异发生变化时，一个新的组开始。

考虑以下查询：

select 
id,
transaction,
value, 
1000 
+ rn 
- sum(case when transaction_no = lag_transaction_no + 1 then 1 else 0 end) 
over(order by id) grp
from (
select 
t.*,
row_number() over(order by id) rn,
lag(transaction_no) over(order by id) lag_transaction_no
from mytable t
) t

使用此示例数据：

id|transaction_no|value-：|---------------：|----：1|1|82|2|3433|3|284|4|1025|1|306|2|57 | 3 | 1008|1|129 | 2 | 1610 | 3 | 2811 | 4 | 15712|5|125

查询返回：

id|transaction_no|value|grp-：|---------------：|----：|----1|1|8|10012|2|343|10013|3|28|10014|4|102|10015 | 1 | 30 | 10026|2|5|10027 | 3 | 100 | 10028|1|12|10039 | 2 | 16 | 100310 | 3 | 28 | 100311 | 4 | 157 | 100312 | 5 | 125 | 1003

SQL Server 2012数据库Fiddle上的演示