本文目录:
数据准备
SQL计算周同比和日环比
pandas计算周同比和日环比
在之前的三篇系列文章中,我们对比了pandas和SQL在数据方面的多项操作。
具体来讲,第一篇文章一场pandas与SQL的巅峰大战涉及到数据查看,去重计数,条件选择,合并连接,分组排序等操作。
第二篇文章一场pandas与SQL的巅峰大战(二)涉及字符串处理,窗口函数,行列转换,类型转换等操作。
第三篇文章一场pandas与SQL的巅峰大战(三)围绕日期操作展开,主要讨论了日期获取,日期转换,日期计算等内容。
本篇文章一起来学习常见的应用实例:如何在SQL和pandas中计算同环比。将分别在MySQL,Hive SQL和pandas中用多种方案来实现样例数据日环比,周同比计算。
◆ ◆ ◆ ◆ ◆
数据准备
同比和环比本身都是相对的概念。同比是指和上个周期内同期数据的对比,可以是年同比,月同比,周同比等。环比是指连续两个统计周期内数据的对比,可以是日环比,周环比,月环比等。工作中常见的是周同比和日环比。周同比即当天和上周同一天数据的变化百分比,日环比即当天和昨天数据的变化百分比。本文也主要计算周同比和日环比。数据概况如下,是随机生成的两个月的销售额数据。
数据样例如下所示,从左到右依次表示,id,日期,当日销售额,数据周期从2019-11-01到2019-12-31。公众号后台回复“对比四”,即可获取本文全部代码和数据。
![670a2a3319fb0d50c3189c79c440f7e7.png](https://i-blog.csdnimg.cn/blog_migrate/87984ffad2b85fde9277a2b4b1fcbece.jpeg)
pandas加载数据
import pandas as pd
import datetime
orderamt = pd.read_excel('orderamt.xlsx')
orderamt.head()
MySQL加载数据
![0090d6c6b4ca91db9e142a251d027e4e.png](https://i-blog.csdnimg.cn/blog_migrate/95d68733436b960ef61716d1b98515d0.jpeg)
和前面的文章类似,使用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;
![77ca6d3c01c39cebd6c81f4500869a7d.png](https://i-blog.csdnimg.cn/blog_migrate/31e4cb9f5e6416a230eda61f2be26a5d.jpeg)
按照上面的代码建表,然后把orderamt.txt
的内容加载到表中即可,最终数据如上图所示。
SQL计算周同比和日环比
我们关注的是周同比和日环比,其实就是关注当天,昨天,7天前的数据,然后相应的算一下变化的百分比即可。思路一:自关联,关联条件是日期差分别是1和7,分别求出当天,昨天,7天前的数据,用三列形式展示,之后就可以进行作差和相除求得百分比。思路二:不进行关联,直接查询当前日期前一天和前七天的数据,同样以3列的形式展示。
来看一下SQL代码:
![fadad7c3682d605bdf7fca1fb0bee328.png](https://i-blog.csdnimg.cn/blog_migrate/df42f799fa38bbda0eaf16bbd948405a.jpeg)
上面代码中我们关联了两次,条件分别是日期相差1天和日期相差7天。关联不上的则留空。
再来看另一种写法:
![c7424aa322effcd939ead8d04340a1f5.png](https://i-blog.csdnimg.cn/blog_migrate/b90ff633b0df425e4d8260902a617250.jpeg)
这种写法巧妙地使用表的别名查询出了前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;
![b5b16d7eec07a94ff90833c4d232efad.png](https://i-blog.csdnimg.cn/blog_migrate/fa874c74f15567911a411c8611d69a32.jpeg)
以上面的代码为基础,稍加修改,增加计算百分比的代码,就可以分别得到周同比和日环比。
--第一段修改
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
![2d7f7150559eb8e5b36b074d03b50abb.png](https://i-blog.csdnimg.cn/blog_migrate/a39c52b98342a4060bfe93cfd6f475aa.jpeg)
![b76f4956dd6a89868a1bc4bdb3160026.png](https://i-blog.csdnimg.cn/blog_migrate/1004d5aa23e786fd659048e4d930ccb2.jpeg)
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']]
![2215417417691d01abb57e5885bf0315.png](https://i-blog.csdnimg.cn/blog_migrate/ec070a945338e9fd2629cb03f5cd8136.jpeg)
方法二:应用shift函数,直接选取前面n行的方法:
orderamt = pd.read_excel('orderamt.xlsx')
orderamt['ld_amt'] = orderamt['amt'].shift(1)
orderamt['lw_amt'] = orderamt['amt'].shift(7)
orderamt
![adf850a6f1252346184cef49f72b79af.png](https://i-blog.csdnimg.cn/blog_migrate/8f3e5795bd0e0dd9d3bbbc1bb80268eb.jpeg)
这样得到的效果和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
![dabf46817bf99f9bb6787777bbb41f87.png](https://i-blog.csdnimg.cn/blog_migrate/2a632049250db9b9ba9c6c30340999cf.jpeg)
#接方法二代码
orderamt['ld_pct'] = (orderamt['amt'] - orderamt['ld_amt']) / orderamt['ld_amt']
orderamt['lw_pct'] = (orderamt['amt'] - orderamt['lw_amt']) / orderamt['lw_amt']
orderamt
![43ce49342647e9ef2073bac276cf6be8.png](https://i-blog.csdnimg.cn/blog_migrate/7044363f46d6ad74255ad55f8ab31781.jpeg)
在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
![05bd16bc36eb5381eeab95bda2e12092.png](https://i-blog.csdnimg.cn/blog_migrate/f4576aa38727d032e784483ee6895767.jpeg)
上面的代码中,我们都没有用百分比的形式保留结果,这里提供一种方式。
#接方法三,方法一二类似
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
![459de932d66df46b9ea58bf0b01cfa75.png](https://i-blog.csdnimg.cn/blog_migrate/6530b22842e159de3ac96b72f9b056c3.jpeg)
至此,我们完成了SQL和pandas中对于周同比和日环比计算的过程。
◆ ◆ ◆ ◆ ◆
小结
本篇文章中,我们使用SQL和pandas的多种方法对常见的周同比和日环比进行计算。在同样的思路指导下,SQL和pandas实现的方式各有特色,代码并不复杂,但值得细细品味。公众号后台回复“对比四”可以获取本文pdf版本,代码,数据等进行实战,希望对你有所帮助。
![76ce7ef957c5cac6c5ed9eb03441011b.gif](https://i-blog.csdnimg.cn/blog_migrate/7fdb5093c503176879718ff2118da277.gif)
推荐阅读:
1.一场pandas与SQL的巅峰大战
2.一场pandas与SQL的巅峰大战(二)
3.一场pandas与SQL的巅峰大战(三)
4.常用Hive函数的学习和总结