记一次数据连续性问题
前言
今天在进行报表数据质量检查时，发现有存在跨期校验金额不平的问题，在查证的过程中花了很多时间，但时候一回想发现其实是一个很容易的场景，就是当时脑子没转过弯来。特此记录一下，加强印象，下次遇到这类问题直接上手就可以干。
场景描述
对于银行存款类业务，每月需要出一份明细性的报表，描述每个账户的余额，以及当月的交易情况，包含两张表，存款余额表和存款发生额表，表结构大致如下：
本月新增了校验，验证每个存款产品类型的金额连续性，校验规则结果是：普通活期产品少了400亿，通知存款产品多了470亿。然后需要查证是什么原因导致连续性差异这么大。
查证过程
连续性校验
先简单介绍一下连续性校验，连续性校验是要求上月余额 + 本月收入 - 本月支出 = 本月余额。如小张支付宝3月余额 200 元， 4月支出100元，4月收入200元，4月余额300元。那连续性校验为：
200 + 200 - 100 = 300
3月余额 + 4月收入 - 4月支出 = 4月余额
如果4月余额为400，那就校验出错了，说明要么余额错误，要么有交易漏取了。
账户连续性
首先的思路是验证账户的连续性，看账户粒度的数据是否有不平的情况。如果单纯通过余额表上月数据关联本月数据再关联本月交易，然后计算，会漏校验一些上月没有，本月新增的账户,如下。
select m3.bal, m4.bal, (m3.bal + trx.trx_amt)
from dps_bal m4
left join dps_bal m3 -- 3月余额
on m4.act_id = m3.act_id
and m3.part_dt = '2022-03-31'
left join (
select act_id, sum((case when trx_dir = '1' then 1 * trx_amt else -1 * trx_amt end)) as trx_amt from dps_trx
where part_dt = '2022-04-30'
group by act_id
) trx -- 计算按账户粒度汇总的4月交易金额
on m4.act_id = trx.act_id
where m4.part_dt = '2022-04-30'
and m4.bal <> (m3.bal + trx.trx_amt)
可以先将所有涉及的账户信息插入到临时表，然后按账户粒度汇总，如果A账户3月或4月没有数据，则取0，这样就不会漏数了。验证方式伪代码如下：
CREATE TABLE TMP(
ACT_ID 账户号
,last_bal 上月余额
,cur_bal 本月余额
,cur_rcv 本月收入
,cur_pay 本月支出
)
;
-- 上月余额
INSERT INTO TMP
SELECT act_id, sum(bal), 0,0,0
FROM dps_bal
where part_dt = '2022-03-31'
group by act_id;
-- 本月余额
INSERT INTO TMP
SELECT act_id, 0, sum(bal),0,0
FROM dps_bal
where part_dt = '2022-04-30'
group by act_id;
INSERT INTO TMP
SELECT act_id, 0, 0, sum(bal),0
FROM dps_trx
where part_dt = '2022-04-30'
where trx_dir = '1'
group by act_id;
INSERT INTO TMP
SELECT act_id, 0, 0, 0,sum(bal)
FROM dps_trx
where part_dt = '2022-04-30'
where trx_dir = '0'
group by act_id;
select
act_id, sum(last_bal), sum(cur_bal), sum(cur_rcv ) ,sum(cur_pay),
sum(last_bal+cur_rcv-cur_pay - cur_bal) as "差异值"
from tmp
group by act_id
having sum(last_bal+cur_rcv-cur_pay - cur_bal) > 0
;
顺着这个思路，如果想要查产品粒度的连续性，那么将数据范围圈定在某个产品就好了，这样就可以查出来该产品本月具体是因为哪些明细导致出现连续性差异的，然后再抽取这些明细具体看看原因，即可很快分析出。代码如下
CREATE TABLE TMP(
ACT_ID 账户号
,last_bal 上月余额
,cur_bal 本月余额
,cur_rcv 本月收入
,cur_pay 本月支出
)
;
-- 上月余额
INSERT INTO TMP
SELECT act_id, sum(bal), 0,0,0
FROM dps_bal
where part_dt = '2022-03-31'
and dps_prod = '普通活期'
group by act_id;
-- 本月余额
INSERT INTO TMP
SELECT act_id, 0, sum(bal),0,0
FROM dps_bal
where part_dt = '2022-04-30'
and dps_prod = '普通活期'
group by act_id;
INSERT INTO TMP
SELECT act_id, 0, 0, sum(bal),0
FROM dps_trx
where part_dt = '2022-04-30'
where trx_dir = '1'
and dps_prod = '普通活期'
group by act_id;
INSERT INTO TMP
SELECT act_id, 0, 0, 0,sum(bal)
FROM dps_trx
where part_dt = '2022-04-30'
where trx_dir = '0'
and dps_prod = '普通活期'
group by act_id;
select
act_id, sum(last_bal), sum(cur_bal), sum(cur_rcv ) ,sum(cur_pay),
sum(last_bal+cur_rcv-cur_pay - cur_bal) as "差异值"
from tmp
group by act_id
having sum(last_bal+cur_rcv-cur_pay - cur_bal) <> 0
;
最终结论
最后结论是发现有两个主要原因：
- 不同的存款产品之间可以互相转换，可能3月是普通活期，但是4月就变成了通知存款，也可能反过来。
- 汇率差异，外币账户，3月的余额和4月的余额是一样的，但是由于3月和4月汇率上的差异，折合成人民币后，产生了差异。
