拉链表应用——财务总账

需求

请看下面的图片，下面是一个财务总账的一部分，为的是给大家讲清楚，什么是总账。

所谓总账就是上图所示的数据，一共有 5 列，每一列的解释为：

• 科目： 这是财务科目的名称，其实还是要有财务科目的编号
• 上期结余：上个周期期末，最后一天各个科目的余额。
• 本期收入：在本周期内，向科目收入了多少
• 本期支出：在本周期内，从科目内拿出多少
• 本期结余：在本周期内，科目中剩余多少

总账是从哪里来的

总账是从每张凭证中来的。我们来看看什么是凭证：

凭证里面有下面几个关键的东西：

• 记账日期：是支出或者收入记到凭证上的日期
• 会计科目
• 金额数据量

计算逻辑

其实在数据库里面，凭证的表如下所示：

日期	科目	金额
2020-01-01	原材料	10
2020-01-02	原材料	-20
2020-01-03	原材料	10
2020-01-04	原材料	40
2020-01-05	原材料	50
2020-01-06	原材料	-60
2020-01-07	原材料	70
2020-01-08	原材料	-10
2020-01-09	原材料	90

上面的表中，我们可以看到有负数，复数代表支出，正数代表收入，在财务中的，这叫做借贷。

拉链表的定义

说了这么半天，说了明细表、凭证，怎么还不说，拉链表呢？不要着急，拉链表就来了。

我们先定义一下 zipper (拉链表)的数据字典：

字段名称	字段解释	字段类型
start_date_d	拉链节点开始的日期	string
end_date_d	拉链节点结束的日期	string
account	会计科目	string
account_value	会计科目期间金额	decimal(30,4)
last_updatetime	记录插入的时间戳	datetime

在做拉链之前，我们定一下做拉链的周期是 1 天，也是每 1 天跑一下数据。

那么1天对一条记录后，就达到了降维的目的。假如 1 天产生了100条凭证明细数据，我们只需要使用一条记录即可

计算拉链表有分两步做，分别是：

• 初始化阶段
• 增量阶段

初始化阶段

拉链表的初始化阶段是第一次向拉链表插入初始数据是的数据处理逻辑。
我们使用 row_number 来取出第一天产生数据的日期和值，作为初始值。
在初始化之前，我们先定义一下拉链的建表语句：

CREATE TABLE master.dbo.bd_zipper_table (
	start_d varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
	end_d varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
	account varchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL,
	value_account int NOT NULL,
	last_updatetime datetime NOT NULL
) ;

初始化的阶段：

with detail as (
-- 每天凭证的记录情况
	select start_d
	      ,end_d 
	      ,account
	      ,value_account
	from dbo.bd_zipper_table
), zipper as (
-- 制作拉链表
  select date_d
        ,account
        ,value_account  
		,ROW_NUMBER() over(partition by account order by date_d) as rn
  from detail
--  where date_d between '2020-01-01' and '2020-01-01' 
)
INSERT INTO dbo.bd_zipper_table
select date_d as start_d
	  ,'9999-12-31' as end_d 
	  ,account
	  ,value_account
	  ,getdate() as last_updatetime into 
from zipper
where rn = 1 

增量阶段

实现增量的方式向拉链表中插入新的数据。

with detail as (
-- 每天凭证的记录情况
            select '2020-01-01' as date_d ,'原材料' as account,10 as value_account
  union all select '2020-01-02' as date_d ,'原材料' as account,20 as value_account
  union all select '2020-01-04' as date_d ,'管理费用' as account,20 as value_account
)
select a.start_d 
      ,b.date_d as end_d 
	  ,a.account 
	  ,a.value_account
from dbo.bd_zipper_table as a 
left join (
	select account
	      ,max(date_d) as date_d
          ,sum(value_account) as value_account  
	from detail
	where date_d between '2020-01-02' and '2020-01-02' 
	group by account
) as b 
on a.account = b.account
where b.date_d > a.start_d
union all 
select b.date_d 
      ,'9999-12-31' as end_d 
	  ,b.account
	  ,b.value_account
from  (
	select account
	      ,max(date_d) as date_d
          ,sum(value_account) as value_account  
	from detail
	where date_d between '2020-01-02' and '2020-01-02' 
	group by account
) as b 
left join dbo.bd_zipper_table as a 
on a.account = b.account
order by account , start_d

为了执行方便我们把这些东西都放到 sqlserver 的存储过程中。

先来新建一个表记录凭证的明细数据。

CREATE TABLE master.dbo.bd_certificate (
	date_d varchar(10) COLLATE Chinese_PRC_CI_AS NOT NULL,
	account varchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL,
	value_account int NOT NULL,
	last_updatetime datetime NOT NULL
) ;


INSERT INTO master.dbo.bd_certificate
(date_d, account, value_account, last_updatetime)
VALUES('2020-01-05', '原材料', 34, getdate())
,('2020-01-06', '原材料', 31, getdate());

然后建存储过程：

ALTER PROCEDURE dbo.sp_zipper_table_insert(@s_date varchar(50) , @e_date varchar(50))
AS 

-- 先新建一个临时表，用它来保存新增的数据
	select * into #temp_zipper_increament_date from dbo.bd_zipper_table where end_d <> '9999-12-31'; 
	
	
	with detail as (
	-- 每天凭证的记录情况
	   select  date_d, account, value_account, last_updatetime
	   from dbo.bd_certificate
	   where date_d between @s_date and @e_date
	)
	insert into #temp_zipper_increament_date
	-- 处理老科目，没有新记账的数据
	select a.start_d 
	      ,a.end_d as end_d 
		  ,a.account 
		  ,a.value_account
		  ,getdate()
	from dbo.bd_zipper_table as a 
	left join (
		select account
		      ,max(date_d) as date_d
	          ,sum(value_account) as value_account  
		from detail
	
		group by account
	) as b 
	on a.account = b.account
	where b.account is null 
	union all 	
	-- 处理老科目，有新记账的数据，将 end_d='9999-12-31' 处理成有开始和结束的记录
	select a.start_d 
	      ,b.date_d as end_d 
		  ,a.account 
		  ,a.value_account
		  ,getdate()
	from dbo.bd_zipper_table as a 
	left join (
		select account
		      ,max(date_d) as date_d
	          ,sum(value_account) as value_account  
		from detail
		group by account
	) as b 
	on a.account = b.account
	where b.date_d > a.start_d
	and a.end_d = '9999-12-31'
	union all 
	-- 处理老科目，有新记账的数据，将 end_d='9999-12-31'处理成，开始日期等于新记账日期的记录
	select b.date_d 
	      ,'9999-12-31' as end_d 
		  ,b.account
		  ,b.value_account
		  ,getdate()
	from  (
		select account
		      ,max(date_d) as date_d
	          ,sum(value_account) as value_account  
		from detail
		group by account
	) as b 
	inner join (
	  select account
	  from dbo.bd_zipper_table
	  group by account 
	) as a 
	on a.account = b.account  
	union all 
	-- 处理新出现的科目
	select b.date_d 
	      ,'9999-12-31' as end_d 
		  ,b.account
		  ,b.value_account
		  ,getdate()
	from  (
		select account
		      ,max(date_d) as date_d
	          ,sum(value_account) as value_account  
		from detail
		group by account
	) as b 
	left join (
	  select account
	  from dbo.bd_zipper_table
	  group by account 
	) as a 
	on a.account = b.account 
	where a.account is null 
 

	TRUNCATE TABLE master.dbo.bd_zipper_table
	
	insert into master.dbo.bd_zipper_table
	select * from #temp_zipper_increament_date	      

分析一下，里面都做了什么：

1. 新建中间表 #temp_zipper_increament_date，它的作用是保存拉链表的计算结果。
2. 处理四种数据
   1. 处理老科目，没有新记账的数据，这样的科目
   2. 处理老科目，有新记账的数据，将 end_d=‘9999-12-31’ 处理成有开始和结束的记录
   3. 处理老科目，有新记账的数据，将 end_d='9999-12-31’处理成，开始日期等于新记账日期的记录
   4. 处理新出现的科目

数据汇总阶段

在做完上面的操作后，我们可以将原来的明细数据降维到了三天一条。这样会大大减少了数量。接下来，我们做的是将拉链表的数据汇总起来。

例如，我们输入任意一个时间段的开始（#start）和结束（#end）日期，我们就可以获得这个时间段科目的起初、收入、支出、期末的汇总值。

请看看下图，

	select *
	from dbo.bd_zipper_table

拉链表中 start_d 和 end_d 落在在 #start ~ #end 的情况有如下所示：

1. start_d <= #start，数据全部落在了期初里面，所以这部分数据加到期初。
2. end_date > #start and start_d <= #start，由于拉链表中的区间是前开后闭，所以这部分数据应该落到期初里面
3. start_d > #start and end_d <= #end，数据全部落在参数开始和结束的区间里面，这里面的数据要作为期间费用的支出和收入
4. start_d < #end and end_d >= #end，这部分数据落在参数开始和结束了，要作为支出或收入。
5. start_d < #end

我们需要 1~4 这四种情况。

所以汇总后的存储过程如下所示：

ALTER PROCEDURE dbo.sp_account_sum(@s_date varchar(50) , @e_date varchar(50))
AS 
select account
      ,sum( case when (start_d <= @s_date) or (end_d > @s_date and start_d <= @e_date) then value_account else 0 end ) as before_account 
      ,sum( case when (start_d > @s_date and end_d <= @e_date) or (start_d < @s_date and end_d >= @e_date) and value_account > 0 then value_account else 0 end ) as in_value_account 
      ,sum( case when (start_d > @s_date and end_d <= @e_date) or (start_d < @s_date and end_d >= @e_date) and value_account < 0 then value_account else 0 end ) as in_value_account
      from dbo.bd_zipper_table
where (start_d <= @s_date)
   or (end_d > @s_date and start_d <= @e_date)
   or (start_d > @s_date and end_d <= @e_date)
   or (start_d < @s_date and end_d >= @e_date)
group by account 

拉链表的定义

只记录某条记录在一段时间内的状态变化。这样就减少的数据冗余。