时间戳

首页 > 解决方案 > 用于分组事务的数据库模式模式

sql - 用于分组事务的数据库模式模式

问题描述

我正在研究一个会计系统，其中有一种方法可以恢复错误进行的交易。有些流程在生成交易的发票上运行。一个流程可以为一张发票生成多笔交易。可以在发票上运行多个进程。

架构如下所示：

Transactions
========================================================
Id | InvoiceId | InvoiceProcessType | Amount  | CreatedOn
1     1            23                  10.00      Today
2     1            23                  13.00      Today
3     1            23                  17.00      Yesterday
4     1            23                  32.00      Yesterday

现在 1 和 2 一起发生，3 和 4 一起发生，我想恢复后者（3,4），将它们分组的可能解决方案是什么。

一种可能的解决方案是添加一个ProcessCount在每个进程上递增的列。新架构如下所示。

Transactions
==============================================================================
Id | InvoiceId | InvoiceProcessType | Amount  | CreatedOn     | ProcessCount
1     1            23                  10.00      Today          1 
2     1            23                  13.00      Today          1 
3     1            23                  17.00      Yesterday      2
4     1            23                  32.00      Yesterday      2

还有其他方法可以实现吗？TIA

标签： sqlsql-serverdatabase-design

解决方案

如果您将批处理基于createdon日期/时间值之间的任意时间范围，那么您可以使用lag()和累积总和。例如，如果两行在一小时内属于同一批次，则：

select t.*,
       sum(case when prev_createdon > dateadd(hour, -1, createdon) then 0 else 1 end) over 
           (partition by invoiceid order by createdon, id) as processcount
from (select t.*,
             lag(createdon) over (partition by invoiceid order by createdon, id) as prev_createdon
      from transactions t
     ) t;

也就是说，您的处理似乎需要增强。每次代码运行时，都应该在某个表中插入一行（比如processes）。从该插入生成的 id 应该用于插入到transactions. 这样，您可以保留有关何时——以及谁和什么等等——插入特定交易的信息。

推荐阅读