浅析 SQL 数据分析题目——逐行调减

SQL干货分享 2021-08-13

819

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

各位看官老爷大家早上好，我是你们最亲爱的老伙计小刘

在最近的工作中，我遇到了一个非常有趣的需求：用户希望在计划销量汇总界面调整整个省的数据，从而实现将调整落实到该省各个市区的明细数据上去，这里的要点在于，并不是说按照各市区原计划销量的占比去调整，而是按照计划量从多至少排序，一行一行削减，直到满足调减幅度

例如某省六个市区的计划数据如下图

为了大家阅读方便，截图我已经按数量做了排序，我们可以看到，原始的省区汇总数据是 1070，我们先假设用户看到这个数字后，将之调整成了 900，则对明细数据产生的影响应如下

这是一个小幅的调整，而如果用户将汇总数量调整成了 100，则明细数据应该变为下图所示

今天小刘就来和大家分享一下我是如何解决这个问题的

正向凑数法

看到这个需求后，小刘首先想到的就是前几天刚刚分享过的“到发货报表”问题，它们确实极为相似——我们可以把总量的调减幅度看成是商品需求量，而把每个明细数据看成是一笔笔的到货数量

再详细一点就是说，我们要先计算出调减量，按明细数据降序排列后，依次去核对，如果这一行的原数量达不到剩余调减量，则应全部减掉，即更新成零，相应的剩余的调减总量也减少了，直到遇见某一行，将其数量部分或全部减去后，凑足了调减总量为止

那么动态地想一下，在循环到某一行的时候，其前序行要么是已经凑足了调减总量，要么就是还没凑足——尚没凑足的话，那前面的行就必须都全额减掉；而已经凑足了的话，当前行就不用动了

所以将迭代的事情拆解到每一步要做的事，就是比较一下前序行总量和调减总量，如果相差不小于本行数量，那么本行就全额调减，否则调减剩余数量即可

这里面一个核心的数据就是所谓的“前序行总量”，我们需要通过“开窗”来实现

SUM(d.schedule_qty) over(ORDER BY d.schedule_qty DESC rows BETWEEN unbounded preceding AND 1 preceding)

这是按照上面的分析原封不动写出的，但这么写又会带来一些麻烦，比如排在第一行的总是得到 NULL 值，还需要处理它，另外窗口声明写起来也很麻烦

所以我们将前面的公式做一下移项：假设将本行和前序行一并裁剪了，对比能不能达到调减总量

SUM(d.schedule_qty) over(ORDER BY d.schedule_qty DESC, d.detail_id)

由于聚合函数 SUM 在分析模式下默认的窗口是逻辑窗口：从排在第一的值到当前值的范围，但如果像上面这样把主键这种不会重复的值引入排序的最次位置，就间接地实现了物理窗口的效果，从而省去了写窗口声明的代码量

我们来看看成品

WITH sort_details AS
 (SELECT d.detail_id
        ,d.region_name
        ,d.schedule_qty
        ,SUM(d.schedule_qty) over(ORDER BY d.schedule_qty DESC, d.detail_id) sum_qty
    FROM demo_schedule_details d)
SELECT s.detail_id
      ,s.region_name
      ,s.schedule_qty
      ,s.sum_qty
      ,CASE
         WHEN s.sum_qty <= :reduction THEN
          0
         WHEN s.sum_qty - :reduction <= s.schedule_qty THEN
          s.sum_qty - :reduction
         ELSE
          s.schedule_qty
       END new_qty
  FROM sort_details s

当总共调减 70 时，应该只在第一行减掉 70：

当总共调减 900 时，前三行将被全部裁剪：

当总共调减 970 时，前三行被全部裁剪，而第四行被裁掉 70，剩下 30：

反向凑数法

说完了正向凑数，也就是去凑总调减量的数，我们再反过来思考一下这个问题，如果我们将需求所说的降序排列反过来，以升序排列，从较小的数开始遍历，统计前序行能不能凑足新的总量，也就是用户填写的那个数量，显然也是可以解决问题的

SQL> SELECT d.detail_id
  2        ,d.region_name
  3        ,d.schedule_qty
  4        ,SUM(d.schedule_qty) over(ORDER BY d.schedule_qty, d.detail_id DESC) sum_qty
  5    FROM demo_schedule_details d
  6  


 DETAIL_ID REGION_NAME          SCHEDULE_QTY    SUM_QTY
---------- -------------------- ------------ ----------
         3 丙市                           20         20
         6 己市                           50         70
         4 丁市                          100        170
         1 甲市                          100        270
         5 戊市                          300        570
         2 乙市                          500       1070


6 rows selected

在循环到某一行时，我们比较将本行与前序行全部加起来后能不能满足新的总量，不能满足的话，那么本行数量必须全部留下；如果已经超过了，那么要调减掉冒出的数量；当然，如果冒出的数量比当前行都多了，说明到某前序行为止已经凑足总数了，本行应全部裁剪

WITH sort_details AS
 (SELECT d.detail_id
        ,d.region_name
        ,d.schedule_qty
        ,SUM(d.schedule_qty) over(ORDER BY d.schedule_qty, d.detail_id DESC) sum_qty
    FROM demo_schedule_details d)
SELECT s.detail_id
      ,s.region_name
      ,s.schedule_qty
      ,s.sum_qty
      ,CASE
         WHEN s.sum_qty <= 1000 THEN
          s.schedule_qty
         WHEN s.sum_qty - 1000 <= s.schedule_qty THEN
          s.schedule_qty - (s.sum_qty - 1000)
         ELSE
          0
       END new_qty
  FROM sort_details s

当总共调减 70，也就是新总量为 1000 时，最后一行减掉 70：

当总共调减 900，也就是新总量为 170 时，最后三行被全部调减：

当总共调减 970，也就是新总量为 100 时，仅剩前两行保留，第三行被调减 70：

聪明的你，更喜欢哪种方法呢？

浅析 SQL 数据分析题目——逐行调减

评论