一场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实现的方式各有特色，代码并不复杂，但值得细细品味。公众号后台回复“对比四”可以获取本文pdf版本，代码，数据等进行实战，希望对你有所帮助。