分组求和计算_图解面试题：累计求和问题如何分析？

【题目】

“薪水表”中记录了员工发放的薪水。包含雇员编号，薪水、起始日期、结束日期。

其中，薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '9999-01-01'的员工。

业务问题：按照雇员编号升序排列，查找薪水的累计和(累计薪水)。其中累计薪水是前N个当前员工( 结束日期 = '9999-01-01')的薪水的累计和，其他以此类推。

【解题步骤】

1.先筛选出当前员工( 结束日期 = '9999-01-01')的薪水

select 雇员编号,薪水from 薪水表where 结束日期 = '9999-01-01'

查询结果

2.什么是累计薪水？

由题意可以看出输出结果需要包含薪水和累计薪水。累计薪水是前N个当前员工的薪水的累计和得出。

举个例子，如下图：

第1行的累计薪水为雇员编号(10001)的薪水，

第2行的累计薪水为雇员编号(10001)、雇员编号(10002)的薪水之和，

第3行的累计薪水为雇员编号(10001)、雇员编号(10002)、雇员编号(10003)的薪水之和

依次类推...

3.如何计算出每行的累计薪水？

(1)方法1，用窗口函数(推荐)

在《猴子 从零学会SQL》里讲过窗口函数的基本语法如下：

<窗口函数> over (partition by <用于分组的列名>                               order by <用于排序的列名>)

用聚合函数作为窗口函数，有累计的功能。因为本题是累计“求和”，所以用聚合函数sum。

select 雇员编号,薪水,sum(薪水) over (order by 雇员编号) as 累计薪水from 薪水表where 结束日期 = '9999-01-01';

查询结果

(2)方法2，用自联结(不推荐)

“薪水表”中只有“雇员编号”和“薪水表”，根据上述累计薪水的计算方法，

因此我们需要得到下图所示的表1才能计算累计薪水，左边是雇员编号以及对应的当前薪水，右边则是左边雇员编号对应的求累计薪水需要用到的雇员编号和薪水。

如计算左边雇员编号10002的累计薪水则需用到右边雇员编号(1)中10001和10002两人的当前薪水，且需要满足右边雇员编号(1)<=左边雇员编号

根据左边的雇员编号和薪水分组，再对右边的薪水(1)进行求和，即可得出每个雇员编号对应的累计薪水。

那么，上述的表是如何得出的呢？薪水表中只有一列雇员编号和一列薪水，因此我们需要复制一张薪水表并与原来的合并，需要用到自联结，语法如下：

select 列名 from 表名 as 别名1,表名 as 别名2;

select *from 薪水表 as s1,薪水表 as s2;

需要加上什么条件吗？显然观察上述图表，需满足雇员编号(1)<=雇员编号，而题意当前员工的薪水需要满足结束日期 = '9999-01-01'，并按雇员编号升序排列：

select s1.雇员编号,s1.薪水,s2.雇员编号,s2.薪水from 薪水表 as s1,薪水表 as s2where s2.雇员编号 <= s1.雇员编号 and s1.结束日期 = '9999-01-01' and s2.结束日期 = '9999-01-01'order by s1.雇员编号;

最后用 group by 对雇员编号,薪水进行分组，并用 sum 函数对薪水(1)进行求和：

select s1.雇员编号,s1.薪水,sum(s2.薪水) as 累计薪水from 薪水表 as s1,薪水表 as s2where s2.雇员编号 <= s1.雇员编号 and s1.结束日期 = '9999-01-01' and s2.结束日期 = '9999-01-01'group by s1.雇员编号,s1.薪水order by s1.雇员编号;

【本题考点】

对于“累计”问题，要想到用聚合函数作为窗口函数。比如累计求和，用sum。

sum(列名) over (partition by <用于分组的列名>                               order by <用于排序的列名>)

累计求平均值，用avg。

avg(列名) over (partition by <用于分组的列名>                               order by <用于排序的列名>)

所以，我们可以得出“累计求和”问题的万能模板是：

select 列1,列2,sum(列2) over (order by 列1) as 累计值select 列1,列2,sum(列名) over (partition by                   order by ) as 累计值的别名from 表名;

【举一反三】

下表为确诊人数表，包含日期和该日期对应的新增确诊人数

按照日期进行升序排列，查找日期、确诊人数以及对应的累计确诊人数。

参考答案：

select 日期,确诊人数,sum(确诊人数) over (order by 日期) as 累计确诊人数from 确诊人数表;

查询结果

推荐：如何从零学会sql？