DWD结构-22

发表于 更新于 分类于 ， 阅读次数： 评论数：

如题所示，本文为了约束dwd层的表结构，不够似乎没有完成
dwd层的表有十二个字段，与处理程序用到输出变量如下：

用途 表字段 输出变量
交易日期 TRAD_DATE DEFSTR1
交易时间 TRAD_TIME DEFSTR2
余额 BALANCE DEFSTR3
交易金额 AMOUNT DEFSTR4
收支方向 PAYENT_DIRCE DEFSTR5
币种 CURRENCY DEFSTR6
交易本方 OWN DEFSTR7
交易对方 COUNTERPARTY DEFSTR8
交易单号 T_ORDERNO DEFSTR9
交易备注 T_NOTES DEFSTR10
备注 REMAKE DEFSTR11
暂定 DEFSTR DEFSTR12
暂定 DEFSTR DEFSTR12

交易日期

交易日期使用日期类型存储，只保存年月日信息,使用date类型存储。

  • 农行、招行：由于原本的交易时间信息不含有时间，只有日期，所以使用TO_DATE()将字符串转日期即可；
  • 支付宝、微信：交易时间带有时间信息，字符串转日期之前需要先截取空格之前的日期信息；

    交易时间

    时间还是时分秒（hh:mm:ss）的格式,系统通过字符串存储
    • 农行：由于可能存在小时只有一位（例如：9:32:51）,所以争对这部分需要补零
    • 招行：账单没有时间信息，默认00:00:00
    • 支付宝：需要处理的点有时间与日期信息在同一个字符串、小时位数不会补零、时间信息没有秒钟信息，综上：需要通过空格截取字符、通过长度判断是否补零、在处理后的字符串加上:00
    • 微信：类似支付宝处理，不过时间字符带有秒钟信息

余额

余额采用保留两位小数的数值类型存储

  • 农行：直接将账单对应字段转为数值即可
  • 招商：因为ODS的表带了空格，所以需要去空格后再转数字
  • 支付宝、微信：账单不含余额信息，默认为0

交易金额

交易金额只存储数字，不区分收入还是支出，也就是不区分正负

  • 支付宝：直接将相关字段转换为数值
  • 农行：交易金额有正负之分，所以转数值后需要取绝对值
  • 招商：需要将余额字段中的,去掉后再转数值，然后取绝对值
  • 微信：交易金额信息中带有人民币符号，所以需要去掉￥后转为数值

收支方向

收支方向只区分收入与支出

农行

根据交易金额判断：如果交易金额判断为负数则是支出，否则为收入。

招商

根据交易金额判断：如果交易金额判断为负数则是支出，否则为收入。

支付宝

这个表有收入、支出、其他三种状态。当状态为其他时，要根据商品说明判断。

  • LIKE'%充值%'，支出
  • LIKE'%还款%' 支出
  • LIKE'%收益发放%' 收入
  • LIKE'%工资理财%' 支出
  • LIKE'%提现%' 收入
  • LIKE'%退款%' 收入
  • LIKE'%余额宝-转出到余额%' 收入
  • 其他的项归于未知

微信

收支方向可以使用系统已经出来的，不过对应系统标记为/的需要借助账单中的当前状态判断，原因在于关于这类的记录，本方和对记录都是银行卡。它对应的实际业务是微信充值和提现操作。所以定义微信提现为支出，微信充值为收入。

币种

目前默认所以交易都是人民币（CNY）

本方

农行

本方默认农行（ABC）

招行

本方默认招行（CMBC）

支付宝

交易对方可能有两方，两方的话通过&区分。将支付宝中余额宝、和余额的建议同一为支付宝口径，账单中所有的本方对照如下：

  • 中国农业银行储蓄卡(0076):ABC
  • 余额宝:ALI
  • 余额:ALI
  • 红包:支付宝红包
  • 支付宝随机立减:支付宝红包
  • 现金抵价券:支付宝红包
  • 花呗分期(3期):花呗
  • 花呗:花呗
  • 现金抵价券:支付宝红包

    微信

    交易本方比较多数可以了直接通过支付方式一一对应，稍微麻烦的是支付方式中的/和农行。他们需要根据当前状态判断。目前支付方式为/的都是微信；关于涉及微信与农行流转的信息，由于在收支方向规定了从微信提现为支付，所以本方是微信，反则为农行，其他的对应关系如下：
  • 农业银行(0076)：ABC
  • 交通银行(0757)：BOC
  • 零钱：TENCE
  • 工商银行(4868)：ICBC

对方

农行

交易摘要有：支付宝 、财付通 、代收业务 、网银在线、银联入账、消费、退货、微信支付、利息税、平安付科、转存、结息、代付、2022年费，对照表如下：

  • 支付宝：Ali
  • 财付通：Tence
  • 微信支付：Tence
  • 消费：根据交易对手判断，财付通开头的就是Tence，其他的归为未知
  • 银联入账：如果对方是财付通开头则为Tence，否其他归为未知
  • 退货：根据交易对手判断，财付通开头则为就是Tence，目前没有未知
  • 代付：对方进行区别，都是以支付宝和财付通，对应Ali和Tence
  • 平安付科：交行信用卡（后面可能有变）
  • 转存：对方如果是王兴强，则为交通信用卡
  • 代收业务：交行信用卡
  • 网银在线：未知
  • 利息税：税务局
  • 结息：农行
  • 2022年费：农行

    招行

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    S1:=translate(IN_DEFSTR8,'a1234 567890""','#');--剔除数字
    S2:=TRANSLATE(IN_DEFSTR8,', ""'||S1,'#');
    CASE S2 
             WHEN '001980099990002' THEN OUT_DEFSTR8:='ABC';
             WHEN '110934310110802' THEN OUT_DEFSTR8:='中国铁路网络有限公司';
             WHEN '123912485010902' THEN OUT_DEFSTR8:= '重庆优服普创科技有限公司';
             WHEN '484200016666666' THEN OUT_DEFSTR8:= 'TENCE';
             WHEN '755913807510606' THEN OUT_DEFSTR8:= '联通话费';
             WHEN '802301048140003' THEN OUT_DEFSTR8:= '联通话费';
             WHEN '898500041114570' THEN OUT_DEFSTR8:= '重庆交通';
             WHEN '898500041116161' THEN OUT_DEFSTR8:= '重庆交通';
             WHEN '912352920611009110' THEN OUT_DEFSTR8:= '招行';
             WHEN '912352920611009810' THEN OUT_DEFSTR8:= '招行';
             ELSE OUT_DEFSTR8:= '未知';
           END CASE;

    支付宝

    支付宝账单的对方只区分支付宝、农行两部分，其它不做翻译。对照表如下：
  • 银华基金管理股份有限公司：支付宝公司
  • 余额宝：ALI
  • 中国农业银行：ABC
  • 王兴强：ALI

    微信

    暂时交易对方只处理微信的提现与充值业务，其他的对方信息太杂了。同本方的判断：由于在收支方向规定了从微信提现为支付，所以对方是农行，所以把给零钱充值单独拧出来判断就可以了