SQL查询问题，计算信用卡的利息余额、本金余额和溢缴款余额

Question

问一个可能难度高的问题，由于职业需要，需计算信用卡的利息余额、本金余额和溢缴款。
是这样的，有一张sql表，表名是LCLS_XYK_TRAN_RECORD，有以下字段：
SER_NO，CARD_NO，VAL_DT，TRANS_DT，TRANS_TM，TRANS_TYPE，DESC_PRINT，CAP_AMT，INT_AMT，REPAY_AMT。这张表包含了众多卡号的交易明细。
对应中文名是：
序号，卡号，入账日期，交易日期，交易时间，类型代码，类型描述，利息金额，本金金额，还款金额。
我想做一个sql查询，来计算当卡号为6228027时的利息余额（ CAP_BAL），本金余额（INT_BAL），溢缴款（OVER_BAL）。
（查询结果还是要将全部字段展示出来的）
系统很坑爹，居然没有这些字段，而且单位是用Smartbi Insight里做sql查询的，更坑的是这个Smartbi Insight版本里不支持使用递归的方法，即with……as（……）的方法无法使用。MySQL变量是支持的，但可能是我写得不对。
重点讲讲怎么计算吧，事实上上面的字段关键要用到的是CAP_AMT，INT_AMT，REPAY_AMT这三个，即利息金额，本金金额，还款金额（还款金额均为负值），计算的时候都是按序号由小到大来计算的。每一行仅有这三个中的一个值，不会在同一行同时出现。当有利息金额CAP_AMT的时候，利息余额CAP_BAL增加，当有本金金额INT_AMT的时候，本金余额INT_BAL增加，当有还款金额REPAY_AMT的时候，要先判断上一行利息余额，（1）当上一行利息余额>0，则本行利息余额=上一行利息余额+本行还款金额，并且该数值最小为0，但若上一行利息余额+本行还款金额<0，同时还要再判断上一行本金余额是否>0，若有则与上一行本金余额相加，相加后仍<0，则产生溢缴款，溢缴款取绝对值。（2）当上一行利息余额=0，若上一行本金余额>0，则本行本金余额=上一行本金余额+本行还款金额，并且该数值最小为0，但若上一行本金余额+本行还款金额<0，则产生溢缴款，溢缴款取绝对值。（3）当上一行溢缴款>0时，本行产生的利息或本金要相抵，再来计算利息余额或本金余额。依此规律一行一行地计算。
这样说肯定很复杂。上个图吧：

|SER_NO|CARD_NO|TRANS_DT|TRANS_TM|CAP_AMT|INT_AMT|REPAY_AMT|CAP_BAL|INT_BAL|OVER_BAL|
|1|6228027|20170905|14:30:55|0|2200|0|0|2200|0|
|2|6228027|20170905|14:49:46|0|3932|0|0|6132|0|
|3|6228027|20170909|11:12:28|0|1106|0|0|7238|0|
|4|6228027|20170909|20:49:18|0|2136|0|0|9374|0|
|5|6228027|20170911|5:47:37|0|0|-3176|0|6198|0|
|6|6228027|20170915|9:56:41|0|21540|0|0|27738|0|
|7|6228027|20170916|12:49:23|0|15|0|0|27753|0|
|8|6228027|20170921|18:30:22|0|0|-27800|0|0|47|
|9|6228027|20170922|24:00:00|46.91|0|0|0|0|0.09|
|10|6228027|20170922|24:00:00|200|0|0|199.91|0|0|
|11|6228027|20170923|11:16:50|0|0|-200|0|0|0.09|
|12|6228027|20170928|20:24:07|0|50000|0|0|49999.91|0|
|13|6228027|20170929|19:41:21|0|0|-2878|0|47121.91|0|
|14|6228027|20171004|14:48:48|0|0|-47180|0|0|58.09|
|15|6228027|20171009|9:11:46|0|2296|0|0|2237.91|0|
|16|6228027|20171013|6:20:31|0|950|0|0|3187.91|0|
|17|6228027|20171013|6:27:07|0|1097|0|0|4284.91|0|
|18|6228027|20171013|9:09:02|0|12430|0|0|16714.91|0|
|19|6228027|20171014|13:18:13|0|1000|0|0|17714.91|0|
|20|6228027|20171017|8:35:03|0|22941|0|0|40655.91|0|
|21|6228027|20171022|24:00:00|120|0|0|120|40655.91|0|
|22|6228027|20171023|9:58:43|0|0|-320|0|40455.91|0|
|23|6228027|20171023|10:22:28|0|6000|0|0|46455.91|0|
|24|6228027|20171024|9:37:57|0|0|-46390|0|65.91|0|

如果可以实现，希望朋友指教，谢谢！

Answer 1

逻辑有点乱，我大概梳理了一下，需要进行以下步骤：

首先，使用窗口函数按照卡号（CARD_NO）和入账日期（VAL_DT）对交易明细表进行排序，以确保计算的顺序是按照时间排序。

创建三个变量：cap_bal（利息余额），int_bal（本金余额），over_bal（溢缴款），并初始化为0。

遍历表中的每一行记录，根据当前行的CAP_AMT、INT_AMT和REPAY_AMT值对上面三个变量进行相应的加减操作。具体规则如下：

如果当前行的CAP_AMT大于0，则将其加到cap_bal上。

如果当前行的INT_AMT大于0，则将其加到int_bal上。

如果当前行的REPAY_AMT小于0，则需要进行还款操作。在还款前需要判断上一行的cap_bal、int_bal和over_bal的值。

如果cap_bal大于0，则将本行REPAY_AMT与cap_bal相加，如果结果小于0，则将其绝对值记作over_bal。

如果cap_bal等于0且int_bal大于0，则将本行REPAY_AMT与int_bal相加，如果结果小于0，则将其绝对值记作over_bal。

如果over_bal大于0，则需要将本行REPAY_AMT与over_bal进行相抵。如果REPAY_AMT小于或等于over_bal，则将over_bal减去REPAY_AMT，并将REPAY_AMT设为0；否则将REPAY_AMT减去over_bal，并将over_bal设为0。

最后，根据卡号（CARD_NO）和入账日期（VAL_DT）分组，将变量cap_bal、int_bal和over_bal作为查询结果输出。
下面是这个查询的示例代码，您可以根据你的表结构和数据进行修改：

SELECT SER_NO, CARD_NO, VAL_DT, TRANS_DT, TRANS_TM, TRANS_TYPE, DESC_PRINT, CAP_AMT, INT_AMT, REPAY_AMT,
  @cap_bal := IF(CAP_AMT > 0, @cap_bal + CAP_AMT, @cap_bal) AS cap_bal,
  @int_bal := IF(INT_AMT > 0, @int_bal + INT_AMT, @int_bal) AS int_bal,
  @over_bal := IF(REPAY_AMT < 0,
    IF(@cap_bal > 0, IF(@cap_bal + REPAY_AMT < 0, ABS(@cap_bal + REPAY_AMT), 0),
      IF(@int_bal > 0, IF(@int_bal + REPAY_AMT < 0, ABS(@int_bal + REPAY_AMT), 0),
        IF(@over_bal > 0, IF(REPAY_AMT + @over_bal <= 0, 0, REPAY_AMT + @over_bal), REPAY_AMT)
      )
    ), @over_bal) AS over_bal
FROM LCLS_XYK_TRAN_RECORD, (SELECT @cap_bal := 0, @int_bal := 0, @over_bal := 0) AS t
WHERE CARD_NO = '6228027'
ORDER BY CARD_NO, VAL_DT, SER_NO