適合時間序列資料的計算指令碼

cainiao_M發表於2020-12-10
指令碼

時間序列資料在這裡指按時間排序的日常業務資料。對時間序列資料進行計算時，不僅涉及季度、月份、工作日、週末等常規計算，還經常遇到較為複雜的有序運算，這就要求指令碼語言應具備相應的計算能力。一般用於處理時間序列資料的計算指令碼有SQL、Python Pandas、esProc，下面就讓我們深入瞭解這些指令碼，看看它們的能力差異。

SQL

SQL歷史悠久使用者眾多，在其模型框架內早已發展到極限，幾乎每種簡單運算都能找到對應的SQL解法，這其中就包括有序運算。

比如比上期的例子：表stock1001儲存某支股票的交易資訊，主要欄位有交易日期transDate、收盤價price，請計算每個交易日與上個交易日相比收盤價的增長率。

這個例子屬於相對位置計算，如果使用視窗函式，SQL寫法相對容易：

select transDate,price,price/lag(price) over(order by transDate)-1 comp from stock1001

但有些SQL不支援視窗函式，實現比上期就會麻煩得多：

With A as(SELECT    t1.transDate,t1.price, COUNT(*) AS rk      
    FROM stock1001 AS t1,           
        stock1001 AS t2       
    WHERE t1.transDate   >t2.transDate or (t1.transDate=t2.transDate and t1.price<=t2.price)      
    GROUP BY  t1.transDate, t1.price      
    ORDER BY rk)
select t1.transDate,(t1.price/t2.price-1) comp  from A as t1 left join  A as t2on t1.rk=t2.rk+1

上述程式碼之所以麻煩，首先是因為SQL是基於無序集合的，本身沒有序號，不方便進行有序運算，為了實現有序運算，就要為無序集合硬造出序號列，這個過程需要自關聯和分組彙總，程式碼比較複雜。其次，比上期屬於相對位置計算，如果SQL有相對序號，這種計算會簡單許多，但SQL沒有相對序號，只能將上一行關聯到本行，變相實現相鄰位置計算，程式碼因此變得複雜。

基於無序集合的SQL不方便實現有序運算，視窗函式雖然可以緩解這一狀況，但如果運算再複雜時，也依然麻煩。

比如中位數的例子：scores表儲存學生成績，主要欄位有學生編號studentdid、數學成績math，請計算數學成績的中位數。中位數的定義是：如果記錄總數L為偶數，則返回中間兩個值的均值（序號分別為L/2和L/2+1）；如果L為奇數，則返回唯一的中間值（序號為(L+1)/2）。

SQL計算中位數的程式碼：

With A as (select studentdid,math,   row_number() over (order by math) rk from scores),
B as  (select count(1)  L from scores)
select avg(math)  from A where   rk in (                
        select case when   mod(L,2)=1 then   ((L+1) div 2)  else ( L div 2) end no from B                
        union                
        select case when   mod(L,2)=1 then  ((L+1) div 2)  else (L div 2)+1 end  from B))

可以看到，雖然已經使用了視窗函式，但SQL仍然很複雜。生成序號的過程對於有序集合來說是多餘的，但對SQL來說就是必不可少的步驟，尤其是本例這種必須顯式使用序號的情況，這讓程式碼顯得複雜。SQL實現分支判斷也較麻煩，所以對L為奇數的情況進行處理時，並沒有返回唯一的中間值，而是兩個同樣的中間值求平均，這個技巧雖然可以簡化分支判斷，但理解起來稍有困難。

如果使用取整函式，則可以巧妙地跳過判斷過程，在簡化程式碼的同時計算出中位數。但這種技巧與中位數的原始定義不同，會造成理解困難，這裡沒有采用。

再看一個稍複雜的例子：連續上漲天數。庫表AAPL儲存某支股票的股價資訊，主要欄位有交易日期transDate、收盤價price，請計算該股票最長的連續上漲天數。SQL如下：

select max(continue_inc_days) 
from (select count(*) continue_inc_days        
    from (select sum(inc_de_flag) over(order by transDate) continue_de_days            
    from (select transDate,                      
        case when                          
            price>LAG(price)   over(order by transDate)                     
            then 0 else 1 end inc_de_flag                  
            from AAPL) )
group by continue_de_days)

按自然思路實現這個任務時，應對日期有序的股票記錄進行迴圈，如果本條記錄與上一條記錄相比是上漲的，則將連續上漲天數（初始為0）加1，如果是下跌的，則將連續上漲天數和當前最大連續上漲天數（初始為0）相比，選出新的當前最大連續上漲天數，再將連續上漲天數清0。如此迴圈直到結束，當前最大連續上漲天數即最終的最大連續上漲天數。

但SQL不擅長有序計算，無法用上述自然思路實現，只能用一些難懂的技巧。把按日期有序的股票記錄分成若干組，連續上漲的記錄分成同一組，也就是說，某天的股價比上一天是上漲的，則和上一天記錄分到同一組，如果下跌了，則開始一個新組。最後看所有分組中最大的成員數量，也就是最多連續上漲的天數。

對於這兩個稍複雜的有序運算例子，SQL實現起來就已經很困難了，一旦遇到更復雜的運算，SQL幾乎無法完成。之所以出現這種結果，是因為SQL的理論基礎就是無序集合，這種天然缺陷無論怎樣打補丁，都無法從根本上解決問題。

Python Pandas

Pandas是Python的結構化計算庫，常被用作時間序列資料的計算指令碼。

作為結構化計算函式庫，Pandas可以輕鬆實現簡單的有序計算。比如，同樣計算比上期，Pandas程式碼是這樣的：

import pandas as pd  
stock1001=pd.read_csv('d:/stock1001.csv')     #return  as a DataFrame
stock1001 ['comp'] = stock1001.math/ stock1001.shift(1).math-1

上述前兩句是為了從檔案讀取資料，核心程式碼僅有一句。需要注意的是，Pandas並不能表示前一行，從而直接實現相對位置計算，但可以用shift(1)函式將列整體下移一行，從而變相實現相對位置計算。程式碼中行和列、前一行和下一行看上去很像，初學者容易混淆。

作為現代程式語言，Pandas在有序計算方面要比SQL先進，主要體現在Pandas基於有序集合構建，dataFrame資料型別天生具有序號，適合進行有序計算。前面那些稍複雜的有序計算，用SQL會非常困難，用Pandas就相對容易。

同樣計算中位數，Pandas核心程式碼如下：

…  
df=pd.read_csv('d:/scores.csv')       #return  as a DataFrame  math=df['math']  
L=len(df)  
if L % 2 == 1:      
    result= math[int(L / 2)]  
else:      
    result= (math[int(L / 2 - 1)] +   math[int(L / 2)]) / 2  print(result) |

上述程式碼中，Pandas可以直接用[N]表示序號，而不用額外製造序號，程式碼因此得到簡化。其次，Pandas是過程性語言，分支判斷比SQL易於理解，也不需要技巧來簡化程式碼。

同樣稍複雜的例子最長連續上漲天數，Pandas也比SQL容易實現。核心程式碼如下：

…
aapl = pd.read_sql_query("select price from AAPL order by   transDate", conn)
continue_inc_days=0 ; max_continue_inc_days=0
for i in aapl['price'].shift(0)>aapl['price'].shift(1):            
    continue_inc_days =0 if   i==False else continue_inc_days +1    
    max_continue_inc_days = continue_inc_days   if max_continue_inc_days < continue_inc_days else max_continue_inc_days
print(max_continue_inc_days)
conn.close()

本例中，Pandas可以按照自然思路實現，而不必採取難懂的技巧，程式碼的表達效率要比SQL高得多。

有點遺憾的是， 有序計算常常要涉及相對位置計算，但Pandas不能直接表達相對位置，只能把列下移一行來變相表示本行的上一行，理解時有點困難。

Pandas在有序計算方面的確比SQL容易些，但遇到更復雜的情況，Pandas也會變得很繁瑣，下面試舉兩例。

比如過濾累計值的例子：表sales儲存客戶的銷售額資料，主要欄位有客戶client、銷售額amount，請找出銷售額累計佔到一半的前n個大客戶，並按銷售額從大到小排序。Pandas程式碼如下：

import pandas as pd
sale_info = pd.read_csv("d:/sales.csv")
sale_info.sort_values(by=‘Amount’,inplace=True,ascending=False)
half_amount = sale_info[‘Amount’].sum()/2
vip_list = []
amount = 0
for client_info in sale_info.itertuples():    
    amount += getattr(client_info, ‘Amount’)    
    if amount < half_amount:              
        vip_list.append(getattr(client_info, ‘Client’))    
    else:              
        vip_list.append(getattr(client_info, ‘Client’))        breakprint(vip_list) |

再比如計算股價最高3天的漲幅：表stock1001儲存某支股票的每日股價，主要欄位有交易日期transDate、收盤價price，請將股價最高的三天按逆序排列，計算每一天相比前一天的漲幅。Pandas程式碼如下：

import pandas as pd
stock1001 = pd.read_csv("d:/stock1001_price.txt",sep   = ‘\t’)
CL = stock1001[‘CL’]
CL_psort = CL.argsort()[::-1].iloc[:3].values
CL_psort_shift1 = CL_psort-1
CL_rise = CL[CL_psort].values/CL[CL_psort_shift1].values-1
max_3 = stock1001.loc[CL_psort].reset_index(drop = True)
max_3[‘RISE’] = CL_rise
print(max_3)

這些更復雜的例子也需要用到一些難懂的技巧去實現，不僅難以編寫，而且難以讀懂，這裡就不再詳細解釋。

esProc

與Pandas類似，esProc也具有豐富的結構化計算函式，與Pandas不同的是，esProc除了基於有序集合並支援序號機制外，還提供了方便的相鄰引用機制，以及豐富的位置函式，從而快捷方便地實現有序計算。

對於簡單的有序計算，esProc和其他計算指令碼一樣，都可以輕鬆實現。比如同樣比上期的esProc程式碼：

A B
1 =file(“d:/stock1001.csv”).import@tc() /讀csv檔案
2 =A1.derive(price/price[-1]-1:comp) /用相對位置計算比上期

上面程式碼A1從csv檔案取數，A2是核心程式碼。esProc可以用直觀易懂的[-1]表示相對本行的前一行，這是Pandas和SQL都沒有的功能，也是esProc更為專業的表現。

同樣計算中位數，esProc核心程式碼如下：

A
1 …
2 =L=A1.len()
3 =if(A2%2==0,A1([L/2,L/2+1]).avg(math),A1((L+1)/2).math)

上述程式碼中，esProc可以直接用[N]表示序號，而不用額外製造序號，程式碼更為簡潔。esProc同樣是過程性語法，既可以用if/else語句實現大段的分支，也可以像本例一樣，用if函式實現簡潔的判斷。

同樣稍複雜的例子最長連續上漲天數，esProc也比SQL/Pandas容易實現。核心程式碼如下：

A
1 …
2 =a=0,A1.max(a=if(price>price[-1],a+1,0))

本例中，esProc可以按照自然思路實現，而不必採取特殊的技巧，程式碼表達效率要比SQL更高。除此外， esProc既可以用迴圈語句實現大段的迴圈，也可以像本例一樣，用迴圈函式max實現簡潔的迴圈聚合。

esProc是更為專業的結構化計算語言，即使遇到更復雜的有序計算，也能較為輕鬆地實現。

比如過濾累計值的例子，esProc只需如下程式碼：

A B
1 =demo.query(“select client,amount from sales”).sort(amount:-1) 取數並逆序排序
2 =A1.cumulate(amount) 計算累計序列
3 =A2.m(-1)/2 最後的累計值即是總和
4 =A2.pselect(~>=A3) 超過一半的位置
5 =A1(to(A4))

本例按自然思維實現，先在A2計算出從最大的客戶到每個客戶的累計值，再在A3算出最大累計值的一半，在A4算出累計值大於A3的位置，最後按位置取資料就是所需結果。這裡有體現esProc專業性的兩處特色，其一是A3中的m函式，該函式可以逆序取數，-1表示倒數第一條；其二是A4中的pselect，可以按條件返回序號。這兩種函式都可以有效簡化有序計算。

再比如計算股價最高那3天的漲幅，esProc只需如下程式碼：

A B
1 =file(“d:/stock1001.csv”).import@tc() /取數
2 =A1.ptop(-3,price) /股價最高的3天的位置
3 =A1.calc(A2,price/price[-1]-1) /計算這三天的漲幅
4 =A1(A2).new(transDate,price,A3(#):comp) /用列拼出二維表

上述程式碼中，A2中的ptop表示前N條的位置，和前面的pselect類似，返回的不是記錄的集合，而是序號的集合，類似這樣的函式在esProc中還有很多，其目的都是簡化有序計算。A4中的#也是esProc的特色，直接表示序號欄位，使用起來非常方便，不必像SQL那樣額外製造，或Pandas那樣設定index。

經過比較我們可以發現，esProc具備豐富的結構化函式，是專業的結構化計算語言，可以輕鬆實現常見的有序計算，即使更復雜的計算也能有效簡化，是更加理想的時間序列資料計算指令碼。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章