本文目錄：
資料準備
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 pdimport datetimeorderamt = 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；SQLload 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_amtfrom 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_pctfrom t_orderamt aleft join t_orderamt bon DATEDIFF（a。dt， b。dt） = 1left join t_orderamt con DATEDIFF（a。dt， c。dt） = 7order 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_pctfrom（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_amtfrom 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_pctfrom（select *， lag（orderamt， 1） over（order by dt） ld_amt， lag（orderamt， 7） over（order by dt） lw_amtfrom t_orderamt） b
pandas計算周同比和日環比
在pandas中，我們同樣首先按照上面的兩種思路進行計算。
方法一
：日期關聯的方法
import pandas as pdimport datetimeorderamt = 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版本，程式碼，資料等進行實戰，希望對你有所幫助。
