时间戳

首页 > 解决方案 > 向有序交易组添加唯一 ID

sql - 向有序交易组添加唯一 ID

问题描述

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

| 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 |

我该怎么做？

标签： sqlsql-serversql-server-2012gaps-and-islands

解决方案

这是差距和孤岛问题的一种变体。正如 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

使用此示例数据：

编号 | 交易号 | 价值
-: | -------------: | ----:
 1 | 1 | 8
 2 | 2 | 343
 3 | 3 | 28
 4 | 4 | 102
 5 | 1 | 30
 6 | 2 | 5
 7 | 3 | 100
 8 | 1 | 12
 9 | 2 | 16
10 | 3 | 28
11 | 4 | 157
12 | 5 | 125

查询返回：

编号 | 交易号 | 价值 | grp
-: | -------------: | ----: | ---:
 1 | 1 | 8 | 1001
 2 | 2 | 第343章 1001
 3 | 3 | 28 | 1001
 4 | 4 | 102 | 1001
 5 | 1 | 30 | 1002
 6 | 2 | 5 | 1002
 7 | 3 | 100 | 1002
 8 | 1 | 12 | 1003
 9 | 2 | 16 | 1003
10 | 3 | 28 | 1003
11 | 4 | 157 | 1003
12 | 5 | 125 | 1003

SQL Server 2012 DB Fiddle 上的演示

推荐阅读