使用Excel表格辅助分析，为复杂问题编写SQL

对于复杂的业务逻辑，我们可以采用画流程图的方式将整个流程可视化，从而更好地理解和把握，以高效高质地完成编码任务。同样地，对于复杂的查询操作，我们可以先通过表格推演的方式进行思考和分析，帮助我们更好地理清思路，以便更好地设计和编写查询SQL。

下面我们通过实际案例来介绍这个分析过程。

假设我们有一个库存表，其中包含商品id、批号、库存数量等信息。一个需剔除库存表，包含商品id、剔除数量等信息。我们需要逐个批次剔除相应数量的库存。示例数据如下：

库存表
| 商品id | 批号 | 库存数量 |
| g0001 | 01 | 1000 |
| g0001 | 02 | 500 |
| g0001 | 03 | 500 |
| g0002 | 04 | 1000 |

需剔除库存表
| 商品id | 剔除数量 |
| g0001 | 1200 |
| g0002 | 200 |

剔除结果
| 商品id | 批号 | 库存数量 |
| g0001 | 01 | 0 |
| g0001 | 02 | 300 |
| g0001 | 03 | 500 |
| g0002 | 04 | 800 |

为实现剔除库存，可以通过应用程序，也可以通过存储过程。但本案例我们希望通过一个查询来实现，分析过程如下：

先将样例数据录入Excel，为方便分析剔除，将每个商品需剔除库存数据列在后面，同一商品同一剔除数量。这个数据使用左连接可以轻松得到，如下图所示：

然后我们希望得到每个商品按批次累计求和的结果，以清楚掌握各批次剔除库存情况：

在Excel中，累计求和样例数据少可人工计算后填入，也可通过SUMIF函数实现。如果在SQL中不知道怎样累计求和没关系，知道自己需要什么，搜索引擎可以帮我们找到方法，sum()结合开窗函数over()可以实现这个目标。分析上图表格数据，找到其中的规律，商品g0001在第二个批次可以剔除完数据，商品g0002在第一个批次剔除完。我们需要给同一商品每个批次分配一个序号，以明确是在第几个批次中完成剔除：

分组编号可人工填入，也可通过COUNTIF函数实现。如果在SQL中不知道怎样分组编号也没关系，通过搜索引擎了解到row_number()结合开窗函数可以实现这个目标。继续分析上图表格数据，根据累计库存数量与剔除数量比较，判断某个商品可以在第几个批次完成剔除，我们暂且称呼这个批次对应的序号为够用批次序号：

上图中黑色部分是表中实际存在的数据，绿色部分是经过一个个步骤可以计算得到的，有了上述数据，我们就可以得到剩余库存：

经过以上分析，我们现在可以开始编写SQL语句。这里还需要用到我们前一篇博文中的with as语法。

-- 上述表格截图，前3幅图的数据可以通过一个子查询完成，存放在累计库存临时表
with sum_inventory as (
  -- 通过左连接查询得到商品id、批号、库存数量、剔除数量
  select i.goods_id, i.lot_no, i.quantity, nvl(b.remove_quantity, 0) remove_quantity,
  -- 通过求和函数，结合开窗函数，按商品分组、按批次排序，累计求和，并且生成批次序号
    sum(i.quantity) over(partition by i.goods_id order by i.lot_no) accumulated_quantity,
    row_number() over(partition by i.goods_id order by i.lot_no) lot_seq
  from inventory i left join remove_inventory r on i.goods_id = r.goods_id
),
-- 在上述子查询基础上，生成第4幅图片表格所需的够用批次序号，存放在剔除够用批次序号临时表
remove_enough_lot_seq as (
  select goods_id, min(lot_seq) enough_lot_seq
  from sum_inventory
  where accumulated_quantity >= remove_quantity
  group by goods_id
)
-- 使用内连接查询累计库存临时表和剔除够用批次序号临时表，连接条件为两个临时表中的商品id，得到各批次商品的剩余库存
select si.goods_id, si.lot_no,
  case when si.lot_seq < rels.enough_lot_seq then 0
  when si.lot_seq = rels.enough_lot_seq then si.accumulated_quantity - rels.remove_quantity
  else si.accumulated_quantity end as remaining_quantity
from sum_inventory si, remove_enough_lot_seq rels
where si.goods_id = rels.goods_id;

借助Excel表格推演的方式，可以直观地从现有数据中发现数据之间的规律，计算出中间数据，让我们朝着目标数据的方向前进，从而轻易地编写出合适的SQL。