一场pandas与SQL的巅峰大战(五)

本文目录：

数据准备

MySQL 计算累计百分比

1.不分组情况

2.分组情况

Hive SQL计算累计百分比

1.不分组情况

2.分组情况

pandas计算累计百分比

1.不分组情况

cumsum函数

expanding函数

rolling函数

2.分组情况

cumsum函数

expanding函数

rolling函数

小结

在之前的四篇系列文章中，我们对比了pandas和SQL在数据方面的多项操作。

具体来讲，第一篇文章一场pandas与SQL的巅峰大战涉及到数据查看，去重计数，条件选择，合并连接，分组排序等操作。

第二篇文章一场pandas与SQL的巅峰大战（二）涉及字符串处理，窗口函数，行列转换，类型转换等操作。

第三篇文章一场pandas与SQL的巅峰大战（三）围绕日期操作展开，主要讨论了日期获取，日期转换，日期计算等内容。

第四篇文章一场pandas与SQL的巅峰大战（四）学习了在MySQL，Hive SQL和pandas中用多种方式计算日环比，周同比的方法。

本篇文章一起来探讨如何在SQL和pandas中计算累计百分比。仍然分别在MySQL，Hive SQL和pandas中用多种方案来实现。

◆ ◆ ◆ ◆ ◆

数据准备

我们仍然使用前一篇的orderamt数据，数据导入方式可以参考之前的内容。需要分别在MySQL，Hive，pandas中进行数据导入，在此不作赘述。在我的公众号后台回复“对比五”，可以获取本文全部代码和数据。数据的样例为：

我们的目标是，计算累计到当天的销售额占总销售额的比例。在实现时，首先分别计算出累计到当天的销售金额和总计的金额，然后就可以很方便的求出比例了。

MySQL计算累计百分比

1.不分组情况

最直观的思路是，对每一行的金额，都累加从第一行到当前行的金额。在MySQL中，可以考虑自连接的方式，但需要使用不等值连接。代码和结果如下：

select a.id, a.dt, a.orderamt, sum(b.orderamt) as cum--对b表的金额进行求和
from t_orderamt ajoin t_orderamt bon a.dt >= b.dt--使用不等值连接
group by a.id, a.dt, a.orderamt

图中的cum列即是我们想要求的累加值。而所有销售金额的总计值，我们可以直接使用sum求出。

select sum(orderamt) as total
from t_orderamt

结合上面的两段SQL，就可以求得累计的百分比，注意连接条件我们使用了1=1这种恒成立的方式。代码和结果如下：

select c.id, c.orderamt, c.cum, concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from (select a.id, a.dt, a.orderamt, sum(b.orderamt) as cum
from t_orderamt ajoin t_orderamt bon a.dt >= b.dtgroup by a.id, a.dt, a.orderamt) c 
left join 
(select sum(orderamt) as totalfrom t_orderamt
) d on 1 = 1 

2.分组情况

需要思考：我们的原始数据是两个月的数据，目前我们的算法是把两个月的销售额累计到一起算的。但在实际中可能更多会关心每天的累计销售额分别占当月的百分比。如何能按照月份分组求每组的累计百分比呢？

首先仍然是求累计金额，但要分月累计。在上面的基础上加上月份相等条件即可，从结果中可以看到，在11月和12月cum列是分别累计的。

select substr(a.dt, 1, 7) as mon, a.dt, a.orderamt, sum(b.orderamt) as cum
from t_orderamt a
join t_orderamt bon a.dt >= b.dt and substr(a.dt, 1, 7) = substr(b.dt, 1, 7)--增加了这个条件
group by substr(a.dt, 1, 7), a.dt, a.orderamt

求每月总计金额的代码比较简单：

select substr(a.dt, 1, 7) as mon, sum(orderamt) as totalfrom t_orderamt agroup by substr(a.dt, 1, 7)

同样的，我们把两段代码进行合并，就得到每月的累计百分比情况：

select c.mon, c.dt, c.orderamt, c.cum, d.total,concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from(select substr(a.dt, 1, 7) as mon, a.dt, a.orderamt, sum(b.orderamt) as cum
from t_orderamt a
join t_orderamt b
on a.dt >= b.dt and substr(a.dt, 1, 7) = substr(b.dt, 1, 7)
group by substr(a.dt, 1, 7), a.dt, a.orderamt) c 
left join(select substr(a.dt, 1, 7) as mon, sum(orderamt) as total
from t_orderamt a
group by substr(a.dt, 1, 7)) d on c.mon = d.mon

Hive 计算累计百分比

1.不分组情况

Hive SQL中我们可以沿用MySQL中的思路，但需要注意，Hive 不支持在on中写不等号的连接条件，虽然可以采用where的方式改造一下，代码如下所示。但这并不是最优的方案。我们可以使用Hive中的窗口函数，很方便的计算累计值。

--where方法
select a.id, a.dt, a.orderamt, sum(b.orderamt) as cum--对b表的金额进行求和
from t_orderamt ajoin t_orderamt bon 1=1where a.dt >= b.dt--使用不等值连接
group by a.id, a.dt, a.orderamt--窗口函数select *, sum(orderamt) over(order by dt) as cum
from t_orderamt;

两段代码的执行结果都如下图所示：

接下来我们重点看窗口函数的方式。在计算总计值的时候和前面MySQL的方式类似，累计百分比的计算也是需要把两部分代码结合在一起。

select c.id, c.dt, c.orderamt, c.cum, concat(round((c.cum / d.total) * 100, 2), '%') as cum_pcfrom(select *, sum(orderamt) over(order by dt) as cum from t_orderamt) cleft join(select sum(orderamt) as totalfrom t_orderamt) d on 1 = 1--在Hive中这个条件可以不写

2.分组情况

分组的情况，在窗口函数里是可以用partition by直接指定分组的，见如下代码

select id, substr(dt, 1, 7) as mon, dt, orderamt, 
  sum(orderamt) over(partition by substr(dt, 1, 7) order by dt) as cum
from t_orderamt;

可以看到，同前面的分组情况一样，在11月和12月cum列是分别累计的。

接下来也很容易就写出分组计算累计百分比的代码，结果和上面也是一致的。

select c.mon, c.dt, c.orderamt, c.cum, d.total,concat(round((c.cum / d.total) * 100, 2), '%') as cum_pct
from(select id, substr(dt, 1, 7) as mon, dt, orderamt, sum(orderamt) over(partition by substr(dt, 1, 7) order by dt) as cum
from t_orderamt) c 
left join(select substr(dt, 1, 7) as mon, sum(orderamt) as total
from t_orderamt group by substr(dt, 1, 7)) d on c.mon = d.mon

pandas计算累计百分比

在pandas中，提供了专门的函数来计算累计值，分别是cumsum函数，expanding函数，rolling函数。我们一起来看一下使用三种函数计算分组和不分组累计百分比的方法。

1.不分组情况

cumsum函数

cumsum是pandas中专门用于计算累计和的函数。类似的函数还有cumprod计算累计积，cummax计算前n个值的最大值，cummin计算前n个值的最小值。

import pandas as pd
orderamt = pd.read_excel('orderamt.xlsx')
orderamt['cum_amt'] = orderamt['amt'].cumsum()
orderamt.head(15)

直接对amt列使用cumsum函数即可计算累计值，结果和用SQL计算得到的一致。

计算累计的百分比也很容易。

orderamt['cum_amt_pct'] = orderamt['cum_amt'] / orderamt['amt'].sum(orderamt.head(15)

关于结果如何显示成百分比的形式，可以参考上一篇文章，此处略 。

expanding函数

pandas中的expanding函数是窗口函数的一种，它不固定窗口的大小，而是进行累计的计算。类似于cumsum()，但更强大。

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')#得到字符串形式的月份
orderamt['cum_expand'] = orderamt.expanding(min_periods=1)['amt'].sum()
orderamt.head(15)

参数min_periods表示最小的观测窗口，默认为1，可以设置为其他值，但如果窗口内记录数不足该值，则会显示NA。

有了累计值，计算累计的百分比，可以按照cumsum中的方法进行，此处省略。

rolling函数

rolling函数与expanding相比，主要是固定了窗口大小。当窗口超过dataframe的长度时，可以实现与expanding同样的效果。上面的代码使用rolling函数的方式可以改写如下，注意指定了window参数为len(orderamt)：

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')#得到字符串形式的月份
orderamt['cum_roll'] = orderamt.rolling(window=len(orderamt), min_periods=1)['amt'].sum()
orderamt.head(15)

此处同样省略计算累计百分比的代码。

2.分组情况

cumsum函数

#添加pandas显示设置，显示所有行
pd.set_option('display.max_rows', None)
orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')#分组后对amt求累计和
orderamt['cum_mon'] = orderamt.groupby('mon')['amt'].cumsum()
orderamt

接下来计算分组的总计值，这里用到了pandas中的transform函数，可以把分组后计算的总计值写入原dataframe。如果你不是很理解，可以参考下面这篇文章，讲的很清楚。

https://www.jianshu.com/p/509d7b97088c

orderamt['mon_total'] = orderamt.groupby('mon')["amt"].transform('sum'orderamt['grp_cum_pct'] = orderamt['cum_mon'] / orderamt['mon_total']orderamt

结果和前面SQL计算的是一致的。此处同样省略了转换百分比格式的代码，可参考前一篇文章。

expanding函数

分组情况下使用expanding函数需要和groupby结合，注意得到的结果是多重索引，需要取values才能赋值给原dataframe。

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')
orderamt_mon_group = orderamt.groupby('mon').expanding(min_periods=1)['amt'].sum()
#这里的orderamt_mon_group索引会有两重，我们直接取values的值就可以和原dataframe拼接在一起
orderamt['orderamt_mon_group'] = orderamt_mon_group.values
orderamt

接下来就可以用前面同样的方法，计算分组的总计值，然后求得分组累计百分比了。

rolling函数

通过上文我们知道，rolling函数与expanding函数的代码几乎一样，需要加上window参数。如下所示：

orderamt = pd.read_excel('orderamt.xlsx')
orderamt['mon'] = orderamt['dt'].dt.strftime('%Y-%m')
orderamt_mon_group_roll = orderamt.groupby('mon').rolling(len(orderamt),min_periods=1)['amt'].sum()
#这里的orderamt_mon_group_roll索引会有两重，我们直接取values的值就可以和原dataframe拼接在一起
orderamt['orderamt_mon_group_roll'] = orderamt_mon_group_roll.values
orderamt

结果和上面的是一致的。

至此，我们用多种方法实现了对于累计百分比的计算。

小结

本篇我们计算了分组和不分组情况的累计百分比。在MySQL中用了不等值连接的方法，在Hive SQL中使用了sum窗口函数。在pandas中学习了cumsum，expanding，rolling函数，最终都需要将累加值除以总计值得出累计百分比。本文代码较多，您可以在公众号后台回复“对比五”可以获取本文pdf版本，代码，数据等进行实战，希望对你有所帮助。

◆ ◆ ◆ ◆ ◆

推荐阅读：

1.一场pandas与SQL的巅峰大战

2.一场pandas与SQL的巅峰大战（二）

3.一场pandas与SQL的巅峰大战（三）

4.一场pandas与SQL的巅峰大战（四）

5.常用Hive函数的学习和总结