一场pandas与SQL的巅峰大战（四）
数据准备 SQL计算周同比和日环比 pandas计算周同比和日环比
在之前的三篇系列文章中，我们对比了pandas和SQL在数据方面的多项操作。
具体来讲，第一篇文章一场pandas与SQL的巅峰大战涉及到数据查看，去重计数，条件选择，合并连接，分组排序等操作。
第二篇文章一场pandas与SQL的巅峰大战（二）涉及字符串处理，窗口函数，行列转换，类型转换等操作。
第三篇文章一场pandas与SQL的巅峰大战（三）围绕日期操作展开，主要讨论了日期获取，日期转换，日期计算等内容。
本篇文章一起来学习常见的应用实例：如何在SQL和pandas中计算同环比。将分别在MySQL，Hive SQL和pandas中用多种方案来实现样例数据日环比，周同比计算。
◆ ◆ ◆ ◆ ◆
数据准备
同比和环比本身都是相对的概念。同比是指和上个周期内同期数据的对比，可以是年同比，月同比，周同比等。环比是指连续两个统计周期内数据的对比，可以是日环比，周环比，月环比等。工作中常见的是周同比和日环比。周同比即当天和上周同一天数据的变化百分比，日环比即当天和昨天数据的变化百分比。本文也主要计算周同比和日环比。数据概况如下，是随机生成的两个月的销售额数据。
数据样例如下所示，从左到右依次表示，id，日期，当日销售额，数据周期从2019-11-01到2019-12-31。公众号后台回复“对比四”，即可获取本文全部代码和数据。
pandas加载数据
import pandas as pd
import datetime
orderamt = pd.read_excel('orderamt.xlsx')
orderamt.head()MySQL加载数据
和前面的文章类似，使用navicate把我准备的orderamt.sql导入数据库中即可。
Hive加载数据
CREATE TABLE `t_orderamt`(
`id` int,
`dt` string,
`orderamt` float)
row format delimited fields terminated by ','
stored as textfile;
SQL
load data local inpath 'orderamt.txt' overwrite into table t_orderamt;
select * from t_orderamt limit 20;
按照上面的代码建表，然后把orderamt.txt的内容加载到表中即可，最终数据如上图所示。
SQL计算周同比和日环比
我们关注的是周同比和日环比，其实就是关注当天，昨天，7天前的数据，然后相应的算一下变化的百分比即可。思路一：自关联，关联条件是日期差分别是1和7，分别求出当天，昨天，7天前的数据，用三列形式展示，之后就可以进行作差和相除求得百分比。思路二：不进行关联，直接查询当前日期前一天和前七天的数据，同样以3列的形式展示。
来看一下SQL代码：
上面代码中我们关联了两次，条件分别是日期相差1天和日期相差7天。关联不上的则留空。
再来看另一种写法：
这种写法巧妙地使用表的别名查询出了前1天和前7天的金额，效果和第一种写法一样，不过这种写法可能小众一点。
回到上面的思路2，我们在前面的学习中知道，Hive中有窗口函数支持查询当前行前n行的数据，可以实现同样的效果。代码如下：
select *,
lag(orderamt, 1) over(order by dt) ld_amt,
lag(orderamt, 7) over(order by dt) lw_amt
from t_orderamt;
以上面的代码为基础，稍加修改，增加计算百分比的代码，就可以分别得到周同比和日环比。
--第一段修改
select a.*, concat(round(((a.orderamt - b.orderamt) / b.orderamt) * 100,2), '%') as ld_pct,
concat(round(((a.orderamt - c.orderamt) / c.orderamt) * 100,2), '%') as lw_pct
from t_orderamt a
left join t_orderamt b
on DATEDIFF(a.dt, b.dt) = 1
left join t_orderamt c
on DATEDIFF(a.dt, c.dt) = 7
order by dt
;
--第二段修改
select
b.id, b.dt, b.orderamt,
concat(round(((b.orderamt - ld_amt) / ld_amt) * 100,2), '%') as ld_pct,
concat(round(((b.orderamt - lw_amt) / lw_amt) * 100,2), '%') as lw_pct
from
(
select *,
(select orderamt from t_orderamt where dt = date_add(a.dt, interval -1 day)) ld_amt,
(select orderamt from t_orderamt where dt = date_add(a.dt, interval -7 day)) lw_amt
from t_orderamt a
) b
;
--第三段修改
select
b.id, b.dt, b.orderamt,
concat(round(((b.orderamt - ld_amt) / ld_amt) * 100,2), '%') as ld_pct,
concat(round(((b.orderamt - lw_amt) / lw_amt) * 100,2), '%') as lw_pct
from
(
select *, lag(orderamt, 1) over(order by dt) ld_amt,
lag(orderamt, 7) over(order by dt) lw_amt
from t_orderamt
) b 
pandas计算周同比和日环比
在pandas中，我们同样首先按照上面的两种思路进行计算。
方法一：日期关联的方法
import pandas as pd
import datetime
orderamt = pd.read_excel('orderamt.xlsx')
#orderamt['dt'] = orderamt['dt'].apply(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d'))#为了便于日期加减，将dt转换为datetime64[ns]的格式，视情况运行该句
#分别构造两个dateframe用于关联
orderamt_plus_1 = orderamt.copy()
orderamt_plus_7 = orderamt.copy()
orderamt_plus_1['dt'] = orderamt_plus_1['dt'] + datetime.timedelta(days=1)
orderamt_plus_7['dt'] = orderamt_plus_7['dt'] + datetime.timedelta(days=7)
orderamt_1 = pd.merge(orderamt, orderamt_plus_1, on=['dt'],how='left')
orderamt_1_7 = pd.merge(orderamt_1, orderamt_plus_7, on=['dt'],how='left')
orderamt_all = orderamt_1_7[['id_x', 'dt', 'amt_x', 'amt_y', 'amt']]
方法二：应用shift函数，直接选取前面n行的方法：
orderamt = pd.read_excel('orderamt.xlsx')
orderamt['ld_amt'] = orderamt['amt'].shift(1)
orderamt['lw_amt'] = orderamt['amt'].shift(7)
orderamt
这样得到的效果和SQL方式是一致的。如果要计算百分比，同样是稍微加工即可：
#接方法一代码
orderamt_all['ld_pct'] = (orderamt_all['amt_x'] - orderamt_all['amt_y']) / orderamt_all['amt_y']
orderamt_all['lw_pct'] = (orderamt_all['amt_x'] - orderamt_all['amt']) / orderamt_all['amt']
orderamt_all
#接方法二代码
orderamt['ld_pct'] = (orderamt['amt'] - orderamt['ld_amt']) / orderamt['ld_amt']
orderamt['lw_pct'] = (orderamt['amt'] - orderamt['lw_amt']) / orderamt['lw_amt']
orderamt
在pandas中，还有专门的计算同环比的函数pct_change。
方法三：使用pandas的pct_change()函数计算
orderamt = pd.read_excel('orderamt.xlsx')
orderamt['ld_pct'] = orderamt['amt'].pct_change()
orderamt['lw_pct'] = orderamt['amt'].pct_change(7)
orderamt
上面的代码中，我们都没有用百分比的形式保留结果，这里提供一种方式。
#接方法三，方法一二类似
orderamt['ld_pct'] = orderamt['ld_pct'].apply(lambda x: format(x, '.2%'))
orderamt['lw_pct'] = orderamt['lw_pct'].apply(lambda x: format(x, '.2%'))
orderamt
至此，我们完成了SQL和pandas中对于周同比和日环比计算的过程。
◆ ◆ ◆ ◆ ◆
小结
本篇文章中，我们使用SQL和pandas的多种方法对常见的周同比和日环比进行计算。在同样的思路指导下，SQL和pandas实现的方式各有特色，代码并不复杂，但值得细细品味。
推荐阅读：