专栏首页猴子聊数据分析图解面试题:累计求和问题如何分析?
原创

图解面试题:累计求和问题如何分析?

​【题目】

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

其中,薪水是指该雇员在起始日期到结束日期这段时间内的薪水。当前员工是指结束日期 = '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 (order by <用于排序的列名>)

累计求平均值,用avg。

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

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

select 列1,列2,sum(列名) over (partition by <用于分组的列名>                  order by <用于排序的列名>) as 累计值的别名from 表名;

【举一反三】

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

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

参考答案:

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

查询结果

如何从零学会sql?

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 拼多多面试题:累计求和问题如何分析?

    该表记录了球队、球员号码、球员姓名、得分分数以及得分时间。现在球队要对比赛中表现突出的球员做出奖励。

    猴子数据分析
  • 图解面试题:如何分析游戏?

    猴子是一个班级的班主任,由于所带班级的学生成绩普遍不是很好。现在他需要找出每门课程中成绩最差的学生,然后有针对性的辅导。

    猴子聊数据分析
  • 图解面试题:如何分析中位数?

    学校每次考试完,都会有一个成绩表。例如,表中第1行表示编号为1的用户选择了C++岗位,该科目考了11001分。

    猴子数据分析
  • 图解面试题:双11用户如何分析?

    现有用户登录时间表,记录每个用户的id,姓名,邮箱地址和用户最后登录时间。表如下:

    猴子聊数据分析
  • 图解面试题:人均付费如何分析?

    2.表一中各地市ARPU(0,30),[30,50),[50-80),[80以上)用户数分别是多少?

    猴子聊数据分析
  • 图解面试题:如何分析用户满意度?

    “满意度表”记录了教师和学生对课程的满意程度。“是否满意”列里是老师和学生对课程的评价,其中“是”表示教师和学生都满意。

    猴子数据分析
  • 图解面试题:如何分析用户满意度?

     “满意度表”记录了教师和学生对课程的满意程度。“是否满意”列里是老师和学生对课程的评价,其中“是”表示教师和学生都满意。

    猴子数据分析
  • 滴滴面试题:打车业务问题如何分析?

    公司的app(类似滴滴、uber)为用户提供打车服务。现有四张表,分别是“司机数据”表,“订单数据”表,“在线时长数据”表,“城市匹配数据”表。(滴滴面试题)

    猴子聊数据分析
  • 图解面试题:如何分析红包领取情况?

    “用户活跃表”记录了用户的登录信息,包括用户标识、用户登录日期,以及是否是新用户(如果是新注册的用户值为1;如果是老用户,值为0)。

    猴子聊数据分析

扫码关注云+社区

领取腾讯云代金券