SQL进阶的技巧：如何实现某列的累计乘积？

莫叫石榴姐

已于 2024-09-11 22:52:26 修改

阅读量874

点赞数 27

分类专栏：收获不止一点文章标签： sql 数据库前端

于 2024-09-11 22:46:29 首次发布

本文链接：https://blog.csdn.net/godlovedaniel/article/details/142151397

版权

收获不止一点专栏收录该内容

11 篇文章 2 订阅

订阅专栏

0 场景描述

在做数据处理的时候，尤其是复利累积的时候，有时候会有这样一场景，通过某种条件找到一列数据[X1,X2,X3...Xn],然后想要求y=X1X2X3...Xn。下面给出一个具体案例来详细解释这一问题，如下图所示，每个组的name值只有2个（2个A/B/C），当name=A or C时,price为value字段的乘积，当name=B时候，price为vlaue字段的和。

1 数据准备

create table multi_value as 
(

  select stack(
      12,
	  'A',2,'甲',
	  'B',5,'甲',
	  'C',1,'甲',
	  'B',7,'甲',
	  'B',7,'甲',
	  'A',1,'甲',
	  'C',4,'甲',
	  'A',5,'乙',
	  'B',4,'乙',
	  'C',6,'乙',
	  'B',4,'乙',
	  'A',3,'乙',
	  'C',5,'乙'
  
  )as(name,value,org)

);

2 问题分析

本问题的难点在于求value列的累乘积，这个粗看时没有办法通过SQL实现的，因为SQL里面的分组汇总函数就那么几个sum,avg,max,min,count没有办法直接求一组数据连续相乘。经过仔细研究，我们可以利用数学中的对数恒等式及对数加法原理进行求解，公式如下：

对数加法原理：

log（MN）= log M + log N

ln M*N = ln M + ln N

对数恒等式：

a^(LogaN)=N

e^(ln N) =N

根据上述公式那么

A*B = EXP(ln(A*B)) = EXP(lnA+lnB) = EXP(SUM(ln(FiledName)))

A*B = POWER(10, SUM(LOG(10,FiledName)))

即 A*B累计乘积就可以转换为对数运算+sum求和形式计算

完整的SQL如下：

select name
     , case
           when name in ('A', 'C') then exp(sum(ln(value)))
           when name in ('B') then sum(value) end price
     , org
from multi_value
group by org, name