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
3 小结
本题主要利用数学中对数加法原理及对数恒等式进行求解,具有很强的技巧性,在实际分析中如复利累积场景中会遇到。
觉得以上内容还不错的,想学更多SQL技巧的可以关注以下专栏
数字化建设通关指南
专栏原价99,现在活动价39.9,按照阶梯式增长,直到恢复原价
主要内容:
(1)SQL进阶实战技巧
可以参考如下教程,具体链接如下
上面链接中的文章及技巧会不定期更新。
(2)数仓建模实战技巧和个人心得
1)新人入职新公司后应如何快速了解业务?
2)以业务视角看宽表化建设?
3) 维度建模 or 关系型建模?
4)业务模型与数据模型有什么区别?业务阶段的模型该如何建设?
5)业务指标体系该如何建设?指标体系该如何维护?指标平台应如何建设?指标体系 该由谁来搭建?
6)如何优雅设计DWS层?DWS层模型好坏该如何评价?
7)指标发生异常,该如何排查?应从哪些方面入手寻找问题点?
8) 数据架构的选择,mpp or hadoop?
9)数仓团队应如何体现自己的业务价值,讲好数据故事?
10)BI与大数据有什么关系?BI与信息化、数字化之间有什么关系?BI与报表之间的关 系?
11)数据部门如何与业务部门沟通,并规划指引业务需求?
文章不限于以上内容,有新的想法也会及时更新到该专栏。
具体专栏链接如下: