sql - 在sql中设置记录集的批号

Question

我在 SQL 中有下表

id,date,records
1,2019-03-28 01:22:12,5
2,2019-03-29 01:23:23,5
3,2019-03-30 01:28:54,5
4,2019-03-28 01:12:21,2
5,2019-03-12 01:08:11,1
6,2019-03-28 01:01:21,12
7,2019-03-12 01:02:11,1

我想要实现的是设置一个批次号，该批次号在移动总和值超过 15 后应该继续增加，并且移动总和也应该重置，所以我正在尝试为总移动总和值为 15 的记录创建批次

例如。如果移动总和变为 15，则批次数值应该增加，这将给我包含总值 15 的行。

所以我正在寻找的输出是

id,date,records, moving_sum,batch_number
1,2019-03-28 01:22:12,5,5,1
2,2019-03-29 01:23:23,5,10,1
3,2019-03-30 01:28:54,5,15,1
4,2019-03-28 01:12:21,2,2,2
5,2019-03-12 01:08:11,1,1,2
6,2019-03-28 01:01:21,2,12,2
7,2019-03-12 01:02:11,1,1,3

标签： sqldatabaserecursive-querycumulative-sum

Answer 1

您需要一个递归查询：

with 
    tab as (select t.*, row_number() over(order by id) rn from mytable t),
    cte as (
        select 
            id, 
            date, 
            records, 
            records moving_sum, 
            1 batch_number,
            rn
        from tab
        where rn = 1
        union all
        select
            t.id,
            t.date,
            t.records,
            case when c.moving_sum + t.records > 15 then t.records else c.moving_sum + t.records end,
            case when c.moving_sum + t.records > 15 then c.batch_number + 1 else c.batch_number end,
            t.rn
        from cte c
        inner join tab t on t.rn = c.rn + 1
    )
select id, date, records, moving_sum, batch_number from cte order by id

递归公用表表达式的语法因数据库而异，因此您可能需要根据您的数据库稍微调整一下。

另请注意，如果ids 从 开始1，并且始终无间隙地递增，则实际上您不是公用表表达式tab，您可以在第二个公用表表达式中替换rn为。id

DB Fiddle 上的演示：

编号 | 日期 | 记录 | 移动总和 | 批号
-: | :--------- | ------: | ---------: | ------------:
 1 | 2019-03-28 | 5 | 5 | 1
 2 | 2019-03-29 | 5 | 10 | 1
 3 | 2019-03-30 | 5 | 15 | 1
 4 | 2019-03-28 | 2 | 2 | 2
 5 | 2019-03-12 | 1 | 3 | 2
 6 | 2019-03-28 | 12 | 15 | 2
 7 | 2019-03-12 | 1 | 1 | 3