SQL进阶技巧：如何使用差值累计计算思想巧解数学中递归计算问题

会飞的一十六 2024-07-27

点击「蓝色」字体关注我们！

“在人生的头25年，我渴望自由；在接下来的25年，我渴望自律；后25年，我意识到自律就是自由。”

本文核心思想

SQL累计计算过程：迭代过程

X1 - Y1

X2 - Y2 + X1 - Y1

X3 -Y3 + X2 - Y2 + X1 - Y1

X4 -Y4 + X3 -Y3 + X2 - Y2 + X1 - Y1

.... ...

业务需求

02 问题分析

上述问题计算过程我们用如下表所示;

由上述计算过程发现，我们要获得当前行的结果值，就需要得到上一行的计算结果值，也就是说当前行计算值依赖上一行的计算结果值，而上一行的值是根据公式动态算出的，那么此时思维就会进入一个误区，怎么根据lag()函数获取上一行动态计算结果？需要递归计算才可以吗？我们针对项目A进行分析，设当前金额为X1,X2,X3，X4 变动总金额均值为a，我们做如下计算：

在计算前我们做符号的统一，根据需求，我们当前计算的结果值都是要被计算到下一行，当差值为正数作为减数，当差值为负数则作为被减数，总之当前行计算结果无论正负都是要被加到下一行，因而具有如下推导

第一行：X1 -a

第二行：X2 -a + （X1 -a）

第三行：X3 -a + (X2 -a + （X1 -a)

第四行：X4- a +(X3 -a +X2 -a + （X1 -a))

将上述计算过程进行整理：

第一行：X1 -a

第二行：X1+X2-2a

第三行：X1+X2+X3-3a

第四行：x1+x2+x3+x4 -4a

。。。。

第n 行：x 1+ x2 +x3+...+Xn - na

通过上面的推导可以看出上述复杂的迭代计算过程便转换为累计计算过程，其中x1+x2+x3+..+xn的过程就是一行行累加的过程，na也是累加到当前行的结果。因此我们理解到在SQL累计计算中其本质就是迭代计算的过程，比如每行值的累计计算，我们用伪代码描述如下：

int s=0；int =100；

for(i=1,i<n,i++){

s=s+i

上述代码反应的也是累计计算的过程，用SQL语言表达就是：

sum(i) over( order by i)

数学语言表达就是：

an=an-1 +d

an-1=an-2 +d

... ...

S=a1 + a1+d +(a1+d) +d + ((a1+d) + d) + d +... +a1+(n-1)d

=na1 + d + 2d + 3d +...+(n-1)d

= na1 +(1+2+3+..+(n-1)) *d

=na1 +n(n-1)/2 d

————————————————

03 问题解决

构建表

with data as (
    select 'A' as p_id,'a1' as s_id, 10 cur_money , 80 var_total_money union all
    select 'A' as p_id,'a2' as s_id, 20 cur_money , 80 var_total_money union all
    select 'A' as p_id,'a3' as s_id, 40 cur_money , 80 var_total_money union all
    select 'A' as p_id,'a4' as s_id, 30 cur_money , 80 var_total_money union all
    select 'B' as p_id,'b1' as s_id, 5 cur_money , 15 var_total_money union all
    select 'B' as p_id,'b2' as s_id, 1 cur_money , 15 var_total_money union all
    select 'B' as p_id,'b3' as s_id, 10 cur_money, 15 var_total_money   
)

计算SQL

第一步根据上述分析算出迭代过程中的值

with data as (select 'A' as p_id, 'a1' as s_id, 10 cur_money, 80 var_total_money
              union all
              select 'A' as p_id, 'a2' as s_id, 20 cur_money, 80 var_total_money
              union all
              select 'A' as p_id, 'a3' as s_id, 40 cur_money, 80 var_total_money
              union all
              select 'A' as p_id, 'a4' as s_id, 30 cur_money, 80 var_total_money
              union all
              select 'B' as p_id, 'b1' as s_id, 5 cur_money, 15 var_total_money
              union all
              select 'B' as p_id, 'b2' as s_id, 1 cur_money, 15 var_total_money
              union all
              select 'B' as p_id, 'b3' as s_id, 10 cur_money, 15 var_total_money)
 
select p_id,
       s_id,
       sum(cur_money) over (partition by p_id order by s_id)
           - sum(avg_var_total_money) over (partition by p_id order by s_id) cur_money,
       var_total_money
from (select p_id,
             s_id,
             cur_money,
             var_total_money,
             var_total_money  count(*) over (partition by p_id) avg_var_total_money
      from data) t
;

第二步：利用定义的规则将负数值改为0

case when cur_money < 0 then 0 else cur_money end

具体SQL如下：

with data as (
    select 'A' as p_id,'a1' as s_id, 10 cur_money , 80 var_total_money union all
    select 'A' as p_id,'a2' as s_id, 20 cur_money , 80 var_total_money union all
    select 'A' as p_id,'a3' as s_id, 40 cur_money , 80 var_total_money union all
    select 'A' as p_id,'a4' as s_id, 30 cur_money , 80 var_total_money union all
    select 'B' as p_id,'b1' as s_id, 5 cur_money , 15 var_total_money union all
    select 'B' as p_id,'b2' as s_id, 1 cur_money , 15 var_total_money union all
    select 'B' as p_id,'b3' as s_id, 10 cur_money, 15 var_total_money   
)
select p_id,
       s_id,
       case when cur_money < 0 then 0 else cur_money end cur_money,
       var_total_money
from (select p_id,
             s_id,
             sum(cur_money) over (partition by p_id order by s_id)
                 - sum(avg_var_total_money) over (partition by p_id order by s_id) cur_money,
             var_total_money
      from (select p_id,
                   s_id,
                   cur_money,
                   var_total_money,
                   var_total_money  count(*) over (partition by p_id) avg_var_total_money
            from data) t) t
;

04 小结

本文给出了一种利用SQL语言如何解决数学上递归问题。解决问题的核心在于对差值累计计算的认识，差值累计计算的本质也就是数学中的迭代计算思想在SQL语言中的体现。本文中所描述的问题需要将上一行的计算结果拿出来放到下一行继续叠加，按照直观的思路需要用lag函数去取上一行值但是lag函数获取的往往都是明确的结果值，而这种需要动态计算，于是会走到递归计算的误区中，而需求中所描述的计算方式就是递归的思路，容易产生误解，但仔细推导我们不难发现其实就是每行按照固定的公式算出差值的累加，即字段X的累计值减去字段Y的累计值等于字段X与字段Y差值的累计，我们将本文主要思想用如下公式描述：