SQL 的後計算指令碼

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

大多數情況下，我們用SQL（儲存過程）就可以完成資料庫計算，但如果遇到SQL不擅長的某些複雜運算，就只能用其他程式語言把資料讀出庫外，然後在資料庫外完成計算，這樣的程式語言經常是以簡單指令碼的形式出現，我們在這裡稱為SQL的後計算指令碼。

SQL不擅長的運算主要包括複雜的集合計算、有序計算、關聯計算、多步驟計算等。SQL集合化不夠徹底，沒有顯式的集合資料型別，導致計算過程中產生的集合難以複用，比如分組後必須強制彙總，而基於分組後的子集無法再計算；SQL基於無序集合理論設計，處理跨行組及排名等有序運算非常麻煩，經常用JOIN或子查詢臨時生成序號，不僅難寫而且運算效率很低。SQL還不支援記錄的引用，只能用子查詢或JOIN語句描述關聯關係，一旦遇到層級較多或自關聯的情況，程式碼就會異常複雜；SQL本身也不提倡多步驟程式碼，經常迫使程式設計師寫出巢狀很多層的長語句，雖然用儲存過程可以一定程度解決這個問題，但有時實際環境不允許我們使用儲存過程，比如DBA嚴格控制儲存過程的許可權、舊資料庫和小型資料庫不支援儲存過程等，而且儲存過程的除錯也很不方便，並不是很適合寫出有過程的計算。

除了上述複雜運算，還有一些情況也會用到SQL的後計算指令碼。比如，計算邏輯需要在不同種類的資料庫間遷移，涉及到非關聯式資料庫；輸入源或輸出目標不止資料庫，而是Excel、文字等檔案；還可能在多個資料庫之間進行混合計算。這些都會涉及庫外計算，用到SQL的後計算指令碼。

對SQL的後計算指令碼而言，最重要的功能當然還是實現SQL不擅長的那些複雜運算。除此之外，最好還能具備一些更高階的特性，比如計算檔案、非關聯式資料庫等多樣性資料、能處理較大的資料量、運算效能 不能太慢等。當然，最基本的是要方便地支援讀寫資料庫，這樣才能實現SQL的後計算。

常見的用於SQL後計算指令碼有JAVA、Python pandas、esProc，下面就讓我們深入瞭解這些指令碼，看看它們進行SQL後計算時的能力差異。

JAVA

C++、JAVA等高階語言理論上無所不能，自然也能實現SQL不擅長的運算。JAVA支援泛型，集合化比較徹底，可以實現複雜的集合運算。JAVA的陣列本來就有序號，可以實現有序運算。JAVA支援物件引用，可用引用來表示關係，關聯運算也沒什麼問題。JAVA支援分支、迴圈等過程性語法，可輕鬆實現多步驟複雜運算。

但是，JAVA缺乏結構化類庫，連最簡單的結構化計算都必須硬編碼實現，最基本的結構化資料型別也要手工建立，這會導致程式碼冗長繁瑣。

舉個有序計算的例子：求某支股票最長連續上漲天數。庫表AAPL儲存某支股票的股價資訊，主要欄位有交易日期、收盤價，請計算該股票最長的連續上漲天數。

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

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

具體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)

這段SQL並不算很長，但巢狀了四層，所用技巧古怪難懂，一般人很難想出這樣的程式碼。

用JAVA實現時，就可以迴歸自然思路：

 package stock;
 import java.sql.*;
 public class APP{
     public static void main(String[] args) throws SQLException,                     ClassNotFoundException   {              
    Connection con = null;             
    Class.forName("com.mysql.cj.jdbc.Driver");              
    con = DriverManager.getConnection(                                         "jdbc:mysql://127.0.0.1:3306/mysql?&useSSL=false&serverTimezone=UTC",                                         "root", "");              
    String dql = "select   * from AAPL order by transDate";                  
    PreparedStatement stmt = con.prepareStatement(dql,                               
    ResultSet.TYPE_SCROLL_INSENSITIVE,       
    ResultSet.CONCUR_READ_ONLY);                  
    ResultSet aapl = stmt.executeQuery();              
    int continue_inc_days = 0;              
    int max_continue_inc_days = 0;              
    float last_Price = 0;              
    while (aapl.next())         
        {float price = aapl.getFloat("price");                     
        if (price >= last_Price) {                           
            continue_inc_days++;                     
            } else{                           
                if (continue_inc_days >= max_continue_inc_days) {                                  
                max_continue_inc_days = continue_inc_days;                                   
                }                           
            continue_inc_days = 0;                     }                     
            last_Price = price;              }                      
                System.out.println("max_continue_inc_days=" + max_continue_inc_days);              if (con != null)                     
                con.close();       }}

後面那段程式碼就是前面講述的思路，只要一層迴圈就可以完成了。

然而，我們也發現，Java寫出的這段程式碼，雖然思路簡單，難度不大，但顯然程式碼很冗長。

這個問題的複雜度並不高，還沒涉及到常見的分組、連線等結構化資料計算，否則程式碼量將更為驚人，限於篇幅，就不再用JAVA舉例了。

在多樣性資料、最佳化效能、處理大資料等高階功能方面，JAVA的特點同樣是“能實現，但太繁瑣”，這裡也不再贅述。

JAVA是個優秀的企業級通用語言，但通用的另一層意思往往是不專業，換句話說，JAVA缺乏專業的結構化計算類庫，程式碼冗長繁瑣，算不上理想的SQL後計算指令碼。

Python pandas

Python有簡捷的語法，還擁有眾多的第三方函式庫，其中就有服務於結構化計算的Pandas。也正因為如此，Pandas常被用作SQL的後計算指令碼。

作為結構化計算函式庫，Pandas簡化SQL複雜運算的能力要比JAVA強很多。

比如，同樣的有序運算 “求最長連續上漲天數”，Pandas程式碼是這樣的：

import pymysql
import pandas as pd
conn =   pymysql.connect(    
    host = "127.0.0.1",    
    port = 3306,       
    user = "root",    
    password = "",    
    database = "mysql",
    )
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提供了用於結構化計算的資料結構dataFrame，這種資料結構天然帶序號，在有序運算中可以簡化程式碼，比JAVA更容易進行跨行取數。此外，Pandas對SQL取數的封裝也很緊湊，比JAVA程式碼更加簡短。

再比如集合計算例子：一行拆分為多行。庫表tb有2個欄位，其中ANOMALIES儲存以空格為分隔符的字串，需將ANOMALIES按空格拆分，使每個ID欄位對應一個成員。

處理前的資料

ID ANOMALIES
1 A1 B1 C1 D1
2 A2
3 A3 B3 C3
4 A3 B4 D4
… …

處理後的資料：

ID ANOMALIES
1 A1
1 B1
1 C1
1 D1
2 A2
… …

Pandas核心程式碼如下（省略資料庫輸入輸出，下同）：

 …
 split_dict = pd.read_sql_query("select * from tb", conn)
 split_list = []
 for key,value in split_dict.items():    
     anomalies = value[0].split(*' '*)    
    key_array = np.tile(key,len(anomalies))    
    split_df =   pd.DataFrame(np.array([key_array,anomalies]).T,columns=[*'ID'*,*'ANOMALIES'*])    
    split_list.append(split_df)
    df = pd.concat(split_list,ignore_index=True)

上述程式碼中，Pandas用集合函式將字串直接拆分為dataFrame，再用集合函式將多個dataFrame直接合並，程式碼非常簡練。JAVA雖然可以實現類似的功能，但都要手工實現，程式碼要繁瑣得多。

作為結構化計算函式庫，Pandas程式碼的確比JAVA簡練，但這僅限於複雜度有限的情況下，如果複雜度進一步提高，Pandas程式碼也會變得冗長難懂。

比如這個涉及集合計算+有序計算的例子：連續值班情況。庫表duty記錄著每日值班情況，一個人通常會持續值班幾個工作日，之後再換人，現在請根據duty依次計算出每個人連續的值班情況。資料結構示意如下：

處理前（duty）

Date Name
2018-03-01 Emily
2018-03-02 Emily
2018-03-04 Emily
2018-03-04 Johnson
2018-04-05 Ashley
2018-03-06 Emily
2018-03-07 Emily
… …

處理後

Name Begin End
Emily 2018-03-01 2018-03-03
Johnson 2018-03-04 2018-03-04
Ashley 2018-03-05 2018-03-05
Emily 2018-03-06 2018-03-07
… … …

| | |

核心的pandas程式碼如下：

 ……
 duty = pd.read_sql_query("select date,name from duty order by   date", conn)
 name_rec = ''
 start = 0
 duty_list = []
 for i in range(len(duty)):    
    if name_rec == '':        
        name_rec = duty['name'][i]    
    if name_rec != duty['name'][i]:        
        begin =   duty['date'].loc[start:i-1].values[0]        
        end =   duty['date'].loc[start:i-1].values[-1]              
        duty_list.append([name_rec,begin,end])        
        start = i        
        name_rec = duty['name'][i]
begin = duty['date'].loc[start:i].values[0]
end = duty['date'].loc[start:i].values[-1]
duty_list.append([name_rec,begin,end])
duty_b_e = pd.DataFrame(duty_list,columns=['name','begin','end'])

上面已經省略了資料庫輸出輸出的過程，可以看到程式碼還是有點繁瑣。

再比如集合計算+多步驟運算的例子：計算分期貸款明細。庫表loan記錄著貸款資訊，包括貸款 ID，貸款總額、期數、年利率，示意如下：

LoanID LoanAmt Term Rate
L01 100000 5 4.8
L02 20000 2 5.0
L03 500000 12 4.5

需要計算出各期明細，包括：當期還款額、當期利息、當期本金、剩餘本金。計算結果如下：

LoanID LoanAmt Payment Term Rate interest princepal princeplebalance
L01 100000 20238.13 5 4.75 395.83 19842.29 80159.71
L01 100000 20238.13 5 4.75 317.29 19920.83 60236.87
L01 100000 20238.13 5 4.75 238.44 19999.69 40237.18
L01 100000 20238.13 5 4.75 159.27 20078.85 20158.33
…

實現上述運算的Pandas核心程式碼如下：

 loan_data = pd.read_sql_query("select loanID,LoanAmt,Term,Rate from   loan", conn)
 loan_data['mrate'] = loan_data['Rate']/(100*12)
 loan_data['mpayment'] =   loan_data['LoanAmt']*loan_data['mrate']*np.power(1+loan_data['mrate'],loan_data['Term'])   \                          /(np.power(1+loan_data['mrate'],loan_data['Term'])-1)
 loan_term_list = []
 for i in range(len(loan_data)):    
     loanid =   np.tile(loan_data.loc[i]['LoanID'],loan_data.loc[i]['Term'])    
    loanamt = np.tile(loan_data.loc[i]['LoanAmt'],loan_data.loc[i]['Term'])    
    term =   np.tile(loan_data.loc[i]['Term'],loan_data.loc[i]['Term'])    
    rate =   np.tile(loan_data.loc[i]['Rate'],loan_data.loc[i]['Term'])    
    payment =   np.tile(np.array(loan_data.loc[i]['mpayment']),loan_data.loc[i]['Term'])    
    interest = np.zeros(len(loanamt))    
    principal = np.zeros(len(loanamt))    
    principalbalance  = np.zeros(len(loanamt))    
    loan_amt = loanamt[0]    
    for j in range(len(loanamt)):        
        interest[j] =   loan_amt*loan_data.loc[i]['mrate']        
        principal[j] = payment[j]   - interest[j]        
        principalbalance[j] =   loan_amt - principal[j]        
        loan_amt =   principalbalance[j]    
        loan_data_df =   pd.DataFrame(np.transpose(np.array([loanid,loanamt,term,rate,payment,interest,principal,principalbalance])),                  
            columns =   ['loanid','loanamt','term','rate','payment','interest','principal','principalbalance'])    
        loan_term_list.append(loan_data_df)
loan_term_pay = pd.concat(loan_term_list,ignore_index=True)
print(loan_term_pay)

可以看到，在簡化SQL複雜運算方面Python雖然比JAVA強很多，但只限於簡單情況，如果需求再複雜些，程式碼也會變得冗長難懂。之所以出現這種現象，可能因為Pandas只是第三方函式庫，不能得到Python從語法層面的底層支撐，設計的專業性也不足。

Pandas的專業性不足，還體現在多樣性資料上。Pandas沒有為各類資料來源開發統一介面，只支援常見的本地檔案，但不支援複雜的資料來源，比如Hadoop、MongoDB，使用者還要自己尋找第三方（實際是第四方）函式庫，並編寫複雜的訪問程式碼。Pandas甚至沒有統一資料庫介面，比如MySQL就有好幾種第三方函式庫，常見的有PyMySQL、sqlalchemy、MySQLdb。不過，這個問題對於大多數桌面應用場景還不嚴重，常見的資料庫Python 基本上都能簡單地支援。

對於多源混合關聯問題，只要能讀出各種資料來源的資料，基本上也就能實現了，Pandas在這方面的表現基本令人滿意。不過，還是上面的說法，對於簡單的混合關聯關係，Pandas都容易實現，而一旦出現較複雜的關聯運算，實現過程就會變得困難起來。

在大資料量方面，Pandas的表現就不盡如人意了。Pandas沒有遊標資料型別，這導致解決較大資料量的計算時，必須硬編碼實現迴圈取數，而不能自動進行內外存交換，程式碼因此異常繁瑣。詳情可參考《How Python Handles Big Files》

Pandas的運算效能也一般，但基本夠用。令人經常詬病的主要是多執行緒並行，Python下很難實現此類運算。比如資料庫IO一般都較慢，但可以在資料庫不忙時使用並行取數的辦法來提高取數效能。而Python要藉助其他第三方函式庫才能實現並行，程式碼異常繁瑣，且在表達效率、執行效率、穩定性等方便均缺乏保障。

Pandas雖然是結構化計算函式庫，但仍不夠好用。

esProc

與Pandas類似，esProc也具有豐富的結構化計算函式，與Pandas不同的是， esProc是由商業公司支援的產品，是專業的結構化計算語言，而不是開源社群的第三方庫函式，也不存在一個鬆散的上級組織。esProc可以從全域性角度設計一致的結構化計算語法，可以自底向上設計統一的結構化資料型別，使函式之間以最大的靈活度搭配組合，從而快捷方便地解決SQL後計算中遇到的問題。

作為專業的結構化計算語言，esProc擅長簡化SQL複雜運算，比如，求最長連續上漲天數，實現前面說過的自然思路，esProc只需短短2行：

A
1 =mysqlDB.query@x(“select price from AAPL order by transDate”)
2 =a=0,A1.max(a=if(price>price[-1],a+1,0))

上述程式碼使用了序表和迴圈函式，序表是專用於結構化計算的資料結構，可以比Pandas更容易進行跨行取數，可以更方便地實現有序計算，迴圈函式可以避免大部分的for語句（複雜情況下還是應該用for），可以大幅簡化程式碼。此外，esProc對SQL取數的封裝更緊湊，比Pandas程式碼更加簡短。

再比如一行拆分為多行，esProc程式碼依然簡短：

A
1 =orcl.query@x(“select * from tb”)
2 =A1.news(ANOMALIES.split(“ “);ID,~: ANOMALIES)

即使需求複雜度進一步提高，esProc仍然可以輕鬆實現。

比如連續值班情況，esProc程式碼要比Pandas簡短很多：

A
1 =orcl.query(“select date,name from duty order by date”)
2 =A1.group@o(name)
3 =A2.new(name,.m(1).date:begin,.m(-1).date:end)

再比如計算分期貸款明細，esProc同樣比Pandas簡短：

A
1 =orcl.query@x(“select loanID,LoanAmt,Term,Rate from loan”)
2 =A1.derive(Rate/100/12:mRate,LoanAmtmRatepower((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment)
3 =A2.news((t=LoanAmt,Term);LoanID, LoanAmt, mPayment:payment, Term, Rate, t* mRate:interest, payment-interest:principal, t=t-principal:principlebalance)

對於Pandas很難實現的複雜運算，esProc通常也能輕鬆實現，而且程式碼不難。比如涉及多步驟演算法+集合運算+動態表結構的任務：將子表橫向插入子表。

源表關係

Order（主表） OrderDetail（子表）
ID(pk) ß- OrderID(PK)
Customer Number(pk)
Date Product
Amount

目標結果

ID Customer Date Product1 Amount1 Product2 Amount2 Product3 Amount3
1 3 2019-01-01 Apple 5 Milk 3 Salt 1
2 5 2019-01-02 Beef 2 Pork 4
3 2 2019-01-02 Pizza 3

esProc可以大幅簡化這段程式碼：

A B
1 =orcl.query@x(“select * from OrderDetail left join Order on Order.ID=OrderDetail.OrderID”)
2 =A1.group(ID) =A2.max(.count()).(“Product”+string()+”,”+”Amount”+string(~)).concat@c()
3 =create(ID,Customer,Date,${B2}) >A2.run(A3.record([ID,Customer,Date]

作為專業的結構化計算語言，esProc不僅可以大幅簡化SQL不擅長的複雜運算，還具備更高階的能力去解決一些特殊情況。

在多樣性資料方面，esProc支援多種檔案格式和複雜的資料來源，比如Hadoop、MongoDB等。更進一步，只需使用相同的程式碼，資料分析師就能計算來源各異的資料，既包括資料庫，也包括非資料庫。

在大資料量方面，esProc從底層提供了遊標機制，對上層隱藏了內外存交換細節，允許資料分析師用類似處理小資料量的語法，直觀地處理較大的資料量。

比如，庫表orders記錄著電商的大量訂單，全部讀出會超出記憶體，現在需要在庫外計算每個銷售員銷售額最大的3筆訂單。esProc程式碼如下：

A
1 =my.cursor@x(“select sellerid,amount from orders”)
2 =A1.groups(sellerid;top(3; -amount):top3)
3 =A2.conj(top3)

esProc也提供了很多簡單易用的方法進行效能最佳化。比如：orders表每月的資料大致相等，請按月份進行多執行緒並行查詢，從而大幅提高查詢效能。esProc只需如下程式碼：

A B
1 fork to(1,12) /12執行緒並行
2 =connect(“my”)
3 =B2.query@x(“select * from orders where month(orderdate)=?”,A1)
4 =A1.conj() / 合併查詢結果

經過前面的比較我們可以發現，esProc具備豐富的結構化函式，是專業的結構化計算語言，可以大幅簡化SQL不擅長的複雜運算邏輯，是更加理想的SQL後計算指令碼。

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

相關文章