SQL进阶技巧：如何计算先进先出的收支平衡问题？

目录

0 需求描述

1 数据准备

2 数据分析

2.1 问题分析

求解目标

表结构与数据含义

结果格式要求解释

2.2 SQL求解

3 小结 

0 需求描述

假设表A为收入数据，表B为支出数据。
请计算每笔收入在哪天花了多少，每笔支出花的哪天的多少收入?

表A:

表B: 

结果表：

1 数据准备

CREATE TABLE table_A (
    id STRING,
    date_id STRING,
    value INT
);
INSERT INTO TABLE table_A VALUES
('A001', '2018-01-01', 1000),
('A002', '2018-01-03', -100),
('A003', '2018-02-01', 1000),
('A004', '2018-03-01', 400);

CREATE TABLE table_B (
    id STRING,
    date_id STRING,
    value INT
);
INSERT INTO TABLE table_B VALUES
('B001', '2018-01-02', 800),
('B002', '2018-02-02', 800),
('B003', '2018-03-04', 700);

2 数据分析

2.1 问题分析

求解目标

题目设定了存在两张表，分别是表 A（代表收入数据）和表 B（代表支出数据）。整体目标是要梳理清楚每一笔收入与支出之间的对应关系，具体来说，就是要明确每笔收入在哪些天支出了多少，以及每笔支出对应的收入是多少，最终以特定的结果格式呈现出来，展示出收入、支出以及各自剩余值等关键信息。

表结构与数据含义

• 表 A：
  • 有三个字段，id字段用于唯一标识每一笔收入记录，类似一个编号；date_id字段记录了这笔收入发生的日期；value字段则代表具体的收入数值（正数表示收入）。例如数据('A001', '2018 - 01 - 01', '1000')就表示编号为A001的收入记录，在 2018 年 1 月 1 日有 1000 的收入。
  • 从数据示例来看，表 A 中存在不同日期、不同金额的多笔收入记录，而且收入金额有正有负（如A002的收入值为-100，可能意味着某种退款等情况当作反向收入来记录了）。
• 表 B：
  • 同样包含三个字段，id用于唯一标识每一笔支出记录；date_id是支出发生的日期；value为具体的支出数值（正数表示支出）。像('B001', '2018 - 01 - 02', '800')这条记录就是说编号为B001的支出，在 2018 年 1 月 2 日支出了 800。

结果字段取数逻辑说明

id_A：对应表 A 中每笔收入记录的唯一标识，用于明确是哪一笔具体的收入。

date_id_A：

• 取自表 A 的date_id字段，代表着每笔收入发生的具体日期。在对表 A 进行数据处理（如计算累计收入等操作）以及后续与表 B 进行连接时，始终保持该日期信息，最终在结果表中呈现出来，目的是按照时间顺序梳理收入与支出之间的关联关系。
• 比如，对于上述提到的表 A 中A001这笔收入对应的记录，其date_id_A列的值就是2018 - 01 - 01，这样可以直观看到这笔收入在时间轴上所处的位置，进而与其他日期的支出情况做对比和匹配。

value_A：表 A 中这笔收入的具体金额数值，是后续计算收支剩余等情况的基础数据。

• 就是表 A 中的value字段，也就是每笔收入的具体金额数值。它是后续计算收支剩余情况的基础数据。在整个查询语句执行过程中，从表 A 中取出该字段的值，原样呈现在结果表中，让我们清楚知道每笔收入一开始的金额大小，便于后续分析收支平衡和资金流转情况。
• 像表 A 里记录的('A001', '2018 - 01 - 01', '1000')，在结果表相应行的value_A列就会显示1000，代表这笔收入最初的金额数量。

sub_A：

• 通过a.cum_value_a - b.cum_value_b计算得出。其中，cum_value_a是表 A 经过分析函数计算得到的累计收入值（在公共表表达式cte_a中，利用SUM(value) OVER (ORDER BY date_id)按照日期顺序累计每笔收入得到），cum_value_b是表 B 经过类似分析函数计算得到的累计支出值（在公共表表达式cte_b中，通过SUM(value) OVER (ORDER BY date_id)按日期顺序累计每笔支出得到）。
• 这个计算的含义是，在遵循先进先出的收支匹配原则下，用表 A 到当前收入记录为止的累计收入减去表 B 到当前支出记录（与该收入匹配的支出）为止的累计支出，得到的就是表 A 收入值在每次支出后的剩余值。例如，某时刻表 A 累计收入是 1000，表 B 累计支出是 800，那么此时sub_A的值就是1000 - 800 = 200，表示在经过相应支出后，收入还剩余 200。

sub_B：

• 通过b.cum_value_b - a.cum_value_a来计算。同样，这里的cum_value_a和cum_value_b含义与前面相同，分别是表 A 的累计收入和表 B 的累计支出。
• 该计算表示在先进先出的原则下，考虑表 B 支出在每次有收入进来后的剩余情况。即表 B 到当前支出记录为止的累计支出减去表 A 到与之匹配的当前收入记录为止的累计收入，得到表 B 支出值在每次收入后的剩余值。比如，表 B 累计支出是 800，表 A 累计收入是 1000，此时sub_B的值就是800 - 1000 = -200，意味着有了这笔收入后，支出资金相对出现了 “盈余”（从数值上看是负数表示这种反向的情况）。

value_B：

• 直接取自表 B 的value字段，即每笔支出的具体金额数值。它展示了每次支出的规模大小，是分析收支关系、计算支出剩余情况等不可或缺的基础数据。在结果表中呈现出来，方便查看每笔具体支出的金额情况以及和对应的收入进行对比分析。
• 例如表 B 中的记录('B001', '2018 - 01 - 02', '800')，在结果表相关行的value_B列就会显示800，明确这是该笔支出的金额。

date_id_B：

• 来自表 B 的date_id字段，记录了每笔支出发生的具体日期。在对表 B 进行数据处理以及和表 A 进行连接匹配的过程中，保留该日期信息并在结果表中展示出来，主要是为了按照时间先后顺序合理地将支出与收入进行匹配，遵循先进先出的资金流转逻辑，使整个收支关系在时间维度上清晰呈现。
• 像('B001', '2018 - 01 - 02', '800')这条表 B 记录，在结果表对应的行里date_id_B列就会显示2018 - 01 - 02，用于和收入日期等信息配合分析收支对应情况。

id_B：

• 对应表 B 中的id字段，是每笔支出记录的唯一标识。在结果表中展示出来，用于区分不同的支出项目，方便在分析整体收支情况时准确追踪每一笔具体的支出，就如同id_A用于区分不同收入记录一样。
• 例如，表 B 中的某条支出记录('B001', '2018 - 01 - 02', '800')，在结果表相应行的id_B列就会显示B001，代表这是该笔特定支出的相关信息。

2.2 SQL求解

步骤1：计算表 A 的累计收入

-- 计算表A的累计收入
SELECT
    id,
    date_id,
    value,
    SUM(value) OVER (ORDER BY date_id) AS cum_value_a
FROM t_a
    ;

• 解释：
  • 对于table_A中的每一行，通过SUM(value) OVER (ORDER BY date_id)分析函数计算累计收入。ORDER BY date_id表示按照日期顺序进行累计计算。

步骤2：计算表 B 的累计支出

-- 计算表B的累计支出
SELECT
    id,
    date_id,
    value,
    SUM(value) OVER (ORDER BY date_id) AS cum_value_b
FROM
    t_b;

• 解释：
  • 对于table_B中的每一行，通过SUM(value) OVER (ORDER BY date_id)分析函数计算累计支出。ORDER BY date_id表示按照日期顺序进行累计计算。

步骤3：连接处理后的表 A 和表 B 并计算最终结果

-- 连接处理后的表A和表B
SELECT
    a.id AS id_A,
    a.date_id AS date_id_A,
    a.value AS value_A,
    -- 计算表A每次支出后的剩余收入值
    a.cum_value_a - b.cum_value_b AS sub_A,
    -- 计算表B每次收入后的剩余支出值
    b.cum_value_b - a.cum_value_a AS sub_B,
    b.value AS value_B,
    b.date_id AS date_id_B,
    b.id AS id_B
FROM
    (
        -- 表A的累计收入计算结果
        SELECT
            id,
            date_id,
            value,
            SUM(value) OVER (ORDER BY date_id) AS cum_value_a
        FROM
            t_A
    ) a
    LEFT JOIN
    (
        -- 表B的累计支出计算结果
        SELECT
            id,
            date_id,
            value,
            SUM(value) OVER (ORDER BY date_id) AS cum_value_b
        FROM
            t_B
    ) b
    ON (a.cum_value_a >= b.cum_value_b AND a.cum_value_a - b.cum_value_b <= a.value)
    OR (a.cum_value_a < b.cum_value_b AND b.cum_value_b - a.cum_value_a <= b.value)
ORDER BY
    a.date_id, b.date_id;

• 解释：
  • 首先，将之前计算累计收入的表 A 查询和计算累计支出的表 B 查询作为子查询，分别命名为a和b。
  • 然后进行LEFT JOIN连接，连接条件基于先进先出原则：
    • 当a.cum_value_a >= b.cum_value_b（表 A 的累计收入大于等于表 B 的累计支出）并且a.cum_value_a - b.cum_value_b <= a.value（表 A 的累计收入减去表 B 的累计支出小于等于表 A 当前行的收入值）时，进行连接。
    • 或者当a.cum_value_a < b.cum_value_b（表 A 的累计收入小于表 B 的累计支出）并且b.cum_value_b - a.cum_value_a <= b.value（表 B 的累计支出减去表 A 的累计收入小于等于表 B 当前行的支出值）时，进行连接。
  • 在选择的列中：
    • a.cum_value_a - b.cum_value_b计算表 A 每次支出后的剩余收入值（sub_A）。
    • b.cum_value_b - a.cum_value_a计算表 B 每次收入后的剩余支出值（sub_B）。
• 最终结果示例：

  id_A	date_id_A	value_A	sub_A	sub_B	value_B	date_id_B	id_B
  A001	2018-01-01	1000	200	0	800	2018-01-02	B001
  A001	2018-01-01	1000	200	0	800	2018-02-02	B002
  A002	2018-01-03	-100	100	0	800	2018-02-02	B002
  A003	2018-02-01	1000	300	0	800	2018-02-02	B002
  A003	2018-02-01	1000	300	0	700	2018-03-04	B003
  A004	2018-03-01	400	0	0	700	2018-03-04	B003

3 小结 

本问题核心逻辑遵循先进先出原则来匹配收入和支出。可以想象成有一个资金池，收入是往资金池里加钱，支出是从资金池里扣钱，先进入资金池的收入要先用于支付后续的支出，按照时间顺序来处理每一笔收支的对应关系。比如，较早日期的收入要优先去冲抵后续发生的支出，在计算收支剩余值的时候就要考虑这种先后顺序以及收支金额大小的相互影响，从而准确得出每一步收支后的剩余情况以及它们之间的对应关系。

总之，这道题就是通过对给定的收入表和支出表，按照先进先出的资金流转逻辑，利用数据库SQL 语言准确计算并展示出收支之间详细的对应和剩余情况。

如果您觉得本文还不错，对你有帮助，那么不妨可以关注一下我的数字化建设实践之路专栏，这里的内容会更精彩。

专栏 原价99，现在活动价59.9，按照阶梯式增长，还差5个人上升到69.9，最终恢复到原价。

专栏优势：
（1）一次收费持续更新。

（2）实战中总结的SQL技巧，帮助SQLBOY 在SQL语言上有质的飞越，无论你应对业务难题及面试都会游刃有余【全网唯一讲SQL实战技巧，方法独特】

 SQL很简单，可你却写不好？每天一点点，收获不止一点点-CSDN博客 

（3）实战中数仓建模技巧总结，让你认识不一样的数仓。【数据建模+业务建模，不一样的认知体系】（如果只懂数据建模而不懂业务建模，数仓体系认知是不全面的） 

（4）数字化建设当中遇到难题解决思路及问题思考。

我的专栏具体链接如下

  数字化建设通关指南_莫叫石榴姐的博客-CSDN博客