大多數情況下，我們用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.*;

publicclass APP {

publicstaticvoid 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 elsecontinue_inc_days+1

max_continue_inc_days=continue_inc_daysifmax_continue_inc_days

print(max_continue_inc_days)

conn.close()

上述程式碼中，Pandas提供了用於結構化計算的資料結構dataFrame，這種資料結構天然帶序號，在有序運算中可以簡化程式碼，比JAVA更容易進行跨行取數。此外，Pandas對SQL取數的封裝也很緊湊，比JAVA程式碼更加簡短。

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

處理前的資料

處理後的資料：

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）

處理後

核心的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，貸款總額、期數、年利率，示意如下：

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

實現上述運算的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行：