自动对账系统的设计实现

需求背景

现在每个月大概有几百万个订单&＃xff0c;来自十几个不同的渠道&＃xff0c;同时区分货币和子渠道&＃xff0c;财务在进行对账的时候只能从对方后台下载excel对账账单&＃xff0c;然后从我方统计出相应时间内的订单数&＃xff0c;然后挨个excel进行累加求和&＃xff0c;最后对比总单数和总金额。

痛点

1. 这样整体进行对账无法确认差异明细&＃xff0c;一旦出现差异很难在上百万的订单中找出差异的订单。
2. 每个渠道散落在不同的国家&＃xff0c;渠道后台导出的时间和我方后台使用的时间时区并不一致&＃xff0c;可能会导致由于时区产生的几小时数据误差(比如我方是UTC&＃xff0c;对方是UTC&＃43;8&＃xff0c;对方以UTC&＃43;8的标准导出2月份数据实际上和我方的2月份数据有8小时的数据是不准确的)。
3. 手动对账耗时时间长&＃xff0c;有些渠道为了每个账单大小适中&＃xff0c;会将一个月的数据分成几个甚至几十个账单&＃xff0c;人工计算慢并且容易出错

解决方案

1. 获得渠道方的账单&＃xff0c;通过手动上传或者sftp&＃xff0c;ftps等方法获得并存储到我们自己的对象存储云上&＃xff0c;并且将账单的渠道名称&＃xff0c;账单类型&＃xff0c;时区&＃xff0c;云下载链接入库
2. 解析已经入库的账单&＃xff0c;抽取出重要的字段&＃xff0c;比如金额&＃xff0c;渠道费&＃xff0c;税&＃xff0c;货币&＃xff0c;订单时间等等&＃xff0c;将这些数据导入到新的表里。
3. 将2里面创建的表和我方后台的表按照时间和渠道进行逐条比对&＃xff0c;确认出无差异&＃xff0c;我方缺订单&＃xff0c;对方缺订单&＃xff0c;双方都有但是金额有差异等情况
4. 每对比一条数据就会产生一条新的对比数据&＃xff0c;将数据插入到新的表中供后续报表展示使用
5. 根据4中产出的明细表按照时间和渠道来计算出各个月每个渠道的总体差异值&＃xff0c;得到各个渠道在各个月的总差异笔数和总差异金额。

遇到的问题

• 首先最麻烦的事情是时区&＃xff0c;每个渠道给出的对账单的时区都不太一样&＃xff0c;当我们想要进行对账的时候就很有可能有误差。
  比如我方时区为UTC&＃xff0c;对方时区为UTC&＃43;8
  对方给出的1月份账单中订单从2022-01-01 00:00:00 到 2022-02-01 00:00:00
  但是这个时间实际上是UTC&＃43;8的时间&＃xff0c;对应到我们这边&＃xff0c;订单时间其实是2021-12-30 16:00:00 到 2022-01-31 16:00:00&＃xff0c;这样就会有8个小时的数据有差异。
  解决方法&＃xff1a;每个渠道都用对方的时区来对账&＃xff0c;在上述的问题中&＃xff0c;我们拿到订单之后不再以我方的“1月份”为标准来进行对账&＃xff0c;而是从我方取出2021-12-30 16:00:00 到 2022-01-31 16:00:00来进行对。其他时区也以此类推。

• 每个渠道给出的账单格式不同&＃xff0c;需要独立处理

• 运行时间较慢&＃xff0c;主要耗时在从云端下载对账文件以及解决方法第3步中的数据逐条比对&＃xff0c;逐条比对本质上是将两张表进行外链接来实现的&＃xff0c;累得到双方都有的&＃xff0c;只有对方/我方有的数据&＃xff0c;这个过程是两个较大的表进行链接&＃xff0c;比较耗时

注意事项

• join语句执行很慢&＃xff0c;最好使用离线库来执行&＃xff0c;不要影响线上业务
• 尽可能复用代码&＃xff0c;同时注意可扩展性&＃xff0c;因为不同渠道的账单差别非常大&＃xff0c;很多地方都需要重新format&＃xff0c;保证每个字段都能够直接或者间接拿到或者找到合理的默认值
• 每次解析要做到幂等&＃xff0c;同一个文件多次解析后结果相同
• 上传文件时进行格式检验&＃xff0c;一旦错误的数据插入到数据集中很难将其挑选出来
• 即便是解决了时区的问题&＃xff0c;依旧存在时间上不可解决的误差&＃xff0c;因为对方记录的“订单时间”和我方记录的“订单时间不是一码事”&＃xff0c;有可能我方记录的是订单生成时间/发起支付请求的时间&＃xff0c;但是对方给出的时间是在对方后台建立交易的时间&＃xff0c;这中间有一些误差&＃xff0c;而且每个渠道都不一样。