简析 SQL 数据分析题目——核销关系报表

SQL干货分享 2021-12-30

2895

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家早上好，我是你们的老伙计小刘。近日我在某狗尔公司已经加了三个月班了看到一道挺有意思的题目，今天和老爷们分享一下

图自网络不代表订阅号立场

题目介绍

众所周知，在商务系统的销售场景中，往往是先开票，后收款，再以收款核销发票（将收款和发票挂钩）。今天的题目就是模拟收款核销发票这件事情

有一张发票表 DEMO_INVOICES 数据如下

又有一张收款表 DEMO_RECEIPTS 数据如下

我们的目标就是区分客户，按照开票、收款的顺序将两表的数据进行匹配关联，也就是列出哪单收款核销哪张发票，以及核销掉其多少金额。目标结果大致如下

思路分析

刚看完题目的时候，小刘是感觉既逻辑清晰又无从下手。所以我先手工匹配了一下，沉浸式体验了一把其中的运作逻辑，以A客户的数据为例

首先，我们总是要从第一张发票和第一张收款开始，如前图，发票 1 有 300 元，收款 1 有 100 元，前者只能被后者部分核销 100，剩余 200

鉴于第一行核销后，发票 1 仍有剩余，所以到了第二行时，还是要优先核销它，而收款单要用到下一张，即收款 2。如今后者有 600 元，足够核销前者所余 200 元，并且还剩下 400 元

目光聚焦到第三行，发票 1 已在前面全部核销掉，所以发票进行到下一张，即发票 2。而因为收款 2 在上一行尚有余额，故这里继续用它。且收款 2 十分给力，将发票 2 的 100 元尽数核销后，仍然剩余 300，这意味着到了下一行，收款单继续沿用，而发票要推进一张

以此类推，就能排出完整的核销关系列表

从上面的分析中我们不难发现，列表的每一行都要决策发票和收款是沿用上一行的，还是要向下推进一张。作出这一决策的依据又可归纳为：视前一行哪边单据留有剩余而定。也就是说，前一行发票剩余了，那么本行就沿用发票而推进收款；反之收款剩余了，便沿用收款而取下一张发票；再如果前一行两边单据刚好相抵，如前图第七行，发票 4 剩余 100，收款 4 又刚好有 100，那么到了第八行，两张单据都要推进一张

可是每次都考虑前一行核销实际的单据余额，又是一环套一环的计算，我们能否让这一比较在每一行中相对独立得进行呢？

答案是肯定的，因为宏观来说，我们并不需要知道每一行各单据具体的余额——我们只需要知道是哪张单据有剩余即可。那么如果我们在排列两边单据的同时，计算出每行的累计数量，则在累计发票金额大于累计收款金额时，必然是发票有剩余；反之则是收款有剩余

综上所述，我们先使用递归查询来实现发票和收款的匹配，稍后再探讨如何计算核销金额

代码实现

SQL> with invoices as
  2   (select a.cust_num,
  3           a.inv_num,
  4           a.val,
  5           sum(a.val) over(partition by a.cust_num order by a.inv_num) tval,
  6           row_number() over(partition by a.cust_num order by a.inv_num) rn
  7      from demo_invoices a),
  8  receipts as
  9   (select b.cust_num,
 10           b.rec_num,
 11           b.val,
 12           sum(b.val) over(partition by b.cust_num order by b.rec_num) tval,
 13           row_number() over(partition by b.cust_num order by b.rec_num) rn
 14      from demo_receipts b)
 15  select i.cust_num, i.inv_num, i.val, r.rec_num, r.val
 16    from invoices i, receipts r
 17   where i.cust_num = r.cust_num
 18   start with i.rn = 1
 19          and r.rn = 1
 20  connect by i.cust_num = prior i.cust_num
 21         and (prior i.tval > prior r.tval and i.rn = prior i.rn and
 22             r.rn = prior r.rn + 1 or
 23             prior i.tval < prior r.tval and i.rn = prior i.rn + 1 and
 24             r.rn = prior r.rn or
 25             prior i.tval = prior r.tval and i.rn = prior i.rn + 1 and
 26             r.rn = prior r.rn + 1);
CUST_NUM INV_NUM        VAL REC_NUM           VAL
-------- ------- ---------- ---------- ----------
A        发票1          300 收款1             100
A        发票1          300 收款2             600
A        发票2          100 收款2             600
A        发票3          200 收款2             600
A        发票4          500 收款2             600
A        发票4          500 收款3             300
A        发票4          500 收款4             100
A        发票5          100 收款5             200
A        发票6          100 收款5             200
B        发票1          200 收款1             100
B        发票1          200 收款2             600
B        发票2          400 收款2             600
B        发票3          100 收款2             600
B        发票4          400 收款3             300
B        发票4          400 收款4             100

至此我们至少已经排列出了两边单据的匹配关系，不过在计算核销额之前，我想先说一说语句里 CONNECT BY 条件的简化

因为我们现在写的递归条件，是对前面分析思路的“直译”，它是在对比中决策发票和收款是否使用到下一张，也就是各自的序号 RN 是否加一

同样我们也可以反过来想，如果先把序号加一，再去决策这个一要不要减回来呢？这样做的目的就是把大小比较转化成计算一个要么是 -1 要么是 0 的运算，从而使用更简洁的表达式来写，前例递归条件后半部分括号里的那一大坨，进而就能简化成如下两句

i.rn = prior i.rn + 1 + least(sign(prior r.tval - prior i.tval), 0) and
r.rn = prior r.rn + 1 + least(sign(prior i.tval - prior r.tval), 0)

核销金额

为了表现得直观一些，我画了几张奇丑无比的图来表示每行核销金额的计算过程，还是以 A 客户的数据为例

上图表现的是发票和收款单据的排列结果，每一个色条都表示一张发票/收款。可以看出将来每一行核销都发生在色条的交界处

从图二开始，将核销金额表示在中间的部分。这是发生在收款 1 和 2 交界处的一次核销，取值为相较发票 1 更小的收款 1

图三捕捉到了下一个交界，这是发票 1 和 2 的交界，第二行核销随即在此处发生。此时收款 2 大于发票 1 的剩余部分，所以核销额取这个剩余部分的值

重点关注一下第四行核销，此时收款 2 的剩余部分大于发票 3 的金额，所以取发票 3 的金额为核销额。同时我们也观察到，这里收款 2 已使用的额度不仅是三行核销额的总和，也正是累计发票额减去收款 2 以前的累计收款哟

图五观察的是第六行核销。此处累计发票是多于累计收款的，这说明此行的核销额要从收款中取。按说此处累计收款刨去发票 4 以前的累计发票，是此处要发生的核销。但是在这中间又有收款 2 和 3 的交界，割裂出了两行核销，所以后一截收款 3 的值将作为此处的核销额（想象一下，如果没有这个交界，此处的核销额就是总剩余收款额。有了这个交界，收款 3 比剩余额更小，它能核销的极限就是它本身的值）

这是全部核销关系填补完成的示意图

从上面的推导中，我们得出计算核销额的表达式

case
  when rtval <= itval then
    least(rtval - (itval - ival), rval)
  when rtval > itval then
    least(itval - (rtval - rval), ival)
end

这也是一个“直译”的表达式，大家看上面也是 least，下面也是 least，如果把四个值搁在一块儿 least 行不行呢

least(r.tval - (i.tval - i.val), r.val, i.tval - (r.tval - r.val), i.val)

彳亍

感兴趣的小伙伴可以看看如下严谨的证明

代码实现

SQL> with invoices as
  2   (select a.cust_num,
  3           a.inv_num,
  4           a.val,
  5           sum(a.val) over(partition by a.cust_num order by a.inv_num) tval,
  6           row_number() over(partition by a.cust_num order by a.inv_num) rn
  7      from demo_invoices a),
  8  receipts as
  9   (select b.cust_num,
 10           b.rec_num,
 11           b.val,
 12           sum(b.val) over(partition by b.cust_num order by b.rec_num) tval,
 13           row_number() over(partition by b.cust_num order by b.rec_num) rn
 14      from demo_receipts b)
 15  select i.cust_num,
 16         i.inv_num,
 17         i.val,
 18         r.rec_num,
 19         r.val,
 20         least(r.tval - (i.tval - i.val),
 21               r.val,
 22               i.tval - (r.tval - r.val),
 23               i.val) aval
 24    from invoices i, receipts r
 25   where i.cust_num = r.cust_num
 26   start with i.rn = 1
 27          and r.rn = 1
 28  connect by i.rn = prior
 29             i.rn + 1 + least(sign(prior r.tval - prior i.tval), 0)
 30         and r.rn = prior
 31             r.rn + 1 + least(sign(prior i.tval - prior r.tval), 0)
 32         and i.cust_num = prior i.cust_num;
CUST_NUM INV_NUM        VAL REC_NUM           VAL       AVAL
-------- ------- ---------- ---------- ---------- ----------
A        发票1          300 收款1             100        100
A        发票1          300 收款2             600        200
A        发票2          100 收款2             600        100
A        发票3          200 收款2             600        200
A        发票4          500 收款2             600        100
A        发票4          500 收款3             300        300
A        发票4          500 收款4             100        100
A        发票5          100 收款5             200        100
A        发票6          100 收款5             200        100
B        发票1          200 收款1             100        100
B        发票1          200 收款2             600        100
B        发票2          400 收款2             600        400
B        发票3          100 收款2             600        100
B        发票4          400 收款3             300        300
B        发票4          400 收款4             100        100

今天的分享就到这里了。看官老爷们卧虎藏龙，想必还有更巧妙的解题方法，小刘恳请您不吝赐教

数据库

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

简析 SQL 数据分析题目——核销关系报表

评论