發(fā)明 SQL 的初衷之一顯然是為了降低人們實(shí)施數(shù)據(jù)查詢計(jì)算的難度。SQL 中用了不少類英語的詞匯和語法，這是希望非技術(shù)人員也能掌握。確實(shí)，簡(jiǎn)單的 SQL 可以當(dāng)作英語閱讀，即使沒有程序設(shè)計(jì)經(jīng)驗(yàn)的人也能運(yùn)用。
然而，面對(duì)稍稍復(fù)雜的查詢計(jì)算需求，SQL 就會(huì)顯得力不從心，經(jīng)常寫出幾百行有多層嵌套的語句。這種 SQL，不要說非技術(shù)人員難以完成，即使對(duì)于專業(yè)程序員也不是件容易的事，常常成為很多軟件企業(yè)應(yīng)聘考試的重頭戲。三行五行的 SQL 僅存在教科書和培訓(xùn)班，現(xiàn)實(shí)中用于報(bào)表查詢的 SQL 通常是以“K”計(jì)的。
這是為什么呢？我們通過一個(gè)很簡(jiǎn)單的例子來考察 SQL 在計(jì)算方面的缺點(diǎn)。
設(shè)有一個(gè)由三個(gè)字段構(gòu)成的銷售業(yè)績表（為了簡(jiǎn)化問題，省去日期信息）：
| sales_amount |
銷售業(yè)績表 |
| sales |
銷售員姓名，假定無重名 |
| product |
銷售的產(chǎn)品 |
| amount |
該銷售員在該產(chǎn)品上的銷售額 |
現(xiàn)在我們想知道出空調(diào)和電視銷售額都在前 10 名的銷售員名單。
這個(gè)問題并不難，人們會(huì)很自然地設(shè)計(jì)出如下計(jì)算過程：
1． 按空調(diào)銷售額排序，找出前 10 名；
2． 按電視銷售額排序，找出前 10 名；
3． 對(duì) 1、2 的結(jié)果取交集，得到答案；
早期的 SQL 不支持步驟化，要把前兩步寫進(jìn)子查詢，顯得有點(diǎn)繁瑣:
select * from ( select top 10 sales from sales_amount where product='AC' order by amount desc )intersect ( select top 10 sales from sales_amount where product='TV' order by amount desc )
后來，SQL 也發(fā)現(xiàn)了不分步會(huì)很麻煩，就提供了 CTE 語法，可以用 with 關(guān)鍵字，可以把前面步驟中的查詢結(jié)果命名后在后面的步驟中使用：
with A as select top 10 sales from sales_amount where product='AC' order by amount desc B as select top 10 sales from sales_amount where product='TV' order by amount descselect * from A intersect B
句子沒有更短，但分步后思路確實(shí)變清晰了。
現(xiàn)在，我們把問題稍復(fù)雜化一點(diǎn)，改為計(jì)算所有產(chǎn)品銷售額都在前 10 名的銷售員，試想一下應(yīng)當(dāng)如何計(jì)算，延用上述的思路很容易想到：
1. 列出所有產(chǎn)品；
2．將每種產(chǎn)品的前 10 名取出，分別保存；
但是，使用 CTE 語法只能針對(duì)確定個(gè)數(shù)的中間結(jié)果做進(jìn)一步的計(jì)算。而我們事先不知道總共有多個(gè)產(chǎn)品，這會(huì)導(dǎo)致 WITH 中子句個(gè)數(shù)是不確定的，這就寫不出來了。
1．將數(shù)據(jù)按產(chǎn)品分組，將每組排序，取出前 10 名；
但這樣需要把第一步的分組結(jié)果保存起來，而這個(gè)中間結(jié)果是一個(gè)表，其中有個(gè)字段要存儲(chǔ)對(duì)應(yīng)的分組成員中的前 10 名，也就是字段的取值將是個(gè)集合，SQL 不支持這種數(shù)據(jù)類型，還是寫不出來。
如果有窗口函數(shù)的支持，可以再轉(zhuǎn)換思路，按產(chǎn)品分組后，計(jì)算每個(gè)銷售員在所有分組的前 10 名中出現(xiàn)的次數(shù)，若與產(chǎn)品總數(shù)相同，則表示該銷售員在所有產(chǎn)品銷售額中均在前 10 名內(nèi)。
select salesfrom ( select sales, from ( select sales, rank() over (partition by product order by amount desc ) ranking from sales_amount) where ranking <=10 )group by saleshaving count(*)=(select count(distinct product) from sales_amount)
這是能寫出來，但這樣復(fù)雜的 SQL，有多少人會(huì)寫呢？
前兩種簡(jiǎn)單的思路無法用 SQL 實(shí)現(xiàn)，只能采用第三種迂回的思路。這里的原因在于 SQL 的一個(gè)重要缺點(diǎn)：集合化不徹底。
雖然 SQL 有集合概念，但并未把集合作為一種基礎(chǔ)數(shù)據(jù)類型提供，不能讓變量或字段的取值是個(gè)集合，除了表之外也沒有其它集合形式的數(shù)據(jù)類型，這使得大量集合運(yùn)算在思維和書寫時(shí)都需要繞路。
我們?cè)谏厦娴挠?jì)算中使用了關(guān)鍵字 top，事實(shí)上關(guān)系代數(shù)理論中沒有這個(gè)東西（它可以被別的計(jì)算組合出來），這不是 SQL 的標(biāo)準(zhǔn)寫法。
我們來看一下沒有 top 時(shí)找前 10 名會(huì)有多困難？
大體思路是這樣：找出比自己大的成員個(gè)數(shù)作為是名次，然后取出名次不超過 10 的成員，寫出的 SQL 如下：
select salesfrom ( select A.sales sales, A.product product, (select count(*)+1 from sales_amount where A.product=product AND A.amount<=amount) ranking from sales_amount A )where product='AC' AND ranking<=10
select salesfrom ( select A.sales sales, A.product product, count(*)+1 ranking from sales_amount A, sales_amount B where A.sales=B.sales and A.product=B.product AND A.amount<=B.amount group by A.sales,A.product )where product='AC' AND ranking<=10
這樣的 SQL 語句，專業(yè)程序員寫出來也未必容易吧！而僅僅是計(jì)算了一個(gè)前 10 名。
退一步講，即使有 top，那也只是使取出前一部分輕松了。如果我們把問題改成取第 6 至 10 名，或者找比下一名銷售額超過 10% 的銷售員，這些困難仍然存在，還是要采用迂回的思路才能用 SQL 完成。
造成這個(gè)現(xiàn)象的原因就是 SQL 的另一個(gè)重要缺點(diǎn)：缺乏有序支持。SQL 繼承了數(shù)學(xué)上的無序集合，這直接導(dǎo)致與次序有關(guān)的計(jì)算相當(dāng)困難，而可想而知，與次序有關(guān)的計(jì)算會(huì)有多么普遍（諸如比上月、比去年同期、前 20%、排名等）。
SQL2003 標(biāo)準(zhǔn)中增加的窗口函數(shù)提供了一些與次序有關(guān)的計(jì)算能力，這使得上述某些問題可以有較簡(jiǎn)單的解法，在一定程度上緩解 SQL 的這個(gè)問題。但窗口函數(shù)的使用經(jīng)常伴隨著子查詢，而不能讓用戶直接使用次序訪問集合成員，還是會(huì)有許多有序運(yùn)算難以解決。
我們現(xiàn)在想關(guān)注一下上面計(jì)算出來的“好”銷售員的性別比例，即男女各有多少。一般情況下，銷售員的性別信息會(huì)記在花名冊(cè)上而不是業(yè)績表上，簡(jiǎn)化如下：
| employee |
員工表 |
| name |
員工姓名，假定無重名 |
| gender |
員工性別 |
我們已經(jīng)計(jì)算出“好”銷售員的名單，比較自然的想法，是用名單到花名冊(cè)時(shí)找出其性別，再計(jì)一下數(shù)。但在 SQL 中要跨表獲得信息需要用表間連接，這樣，接著最初的結(jié)果，SQL 就會(huì)寫成：
select employee.gender,count(*)from employee, ( ( select top 10 sales from sales_amount where product='AC' order by amount desc ) intersect ( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) Awhere A.sales=employee.namegroup by employee.gender
僅僅多了一個(gè)關(guān)聯(lián)表就會(huì)導(dǎo)致如此繁瑣，而現(xiàn)實(shí)中信息跨表存儲(chǔ)的情況相當(dāng)多，且經(jīng)常有多層。比如銷售員有所在部門，部門有經(jīng)理，現(xiàn)在我們想知道“好”銷售員歸哪些經(jīng)理管，那就要有三個(gè)表連接了，想把這個(gè)計(jì)算中的 where 和 group 寫清楚實(shí)在不是個(gè)輕松的活兒了。
這就是我們要說的 SQL 的下一個(gè)重要困難：缺乏對(duì)象引用機(jī)制，關(guān)系代數(shù)中對(duì)象之間的關(guān)系完全靠相同的外鍵值來維持，這不僅在尋找時(shí)效率很低，而且無法將外鍵指向的記錄成員直接當(dāng)作本記錄的屬性對(duì)待，試想，上面的句子可否被寫成這樣：
select sales.gender,count(*)from (…) // …是前面計(jì)算“好”銷售員的SQLgroup by sales.gender
顯然，這個(gè)句子不僅更清晰，同時(shí)計(jì)算效率也會(huì)更高（沒有連接計(jì)算）。
我們通過一個(gè)簡(jiǎn)單的例子分析了 SQL 的幾個(gè)重要困難，這也是 SQL 難寫或要寫得很長的主要原因?；谝环N計(jì)算體系解決業(yè)務(wù)問題的過程，也就是將業(yè)務(wù)問題的解法翻譯成形式化計(jì)算語法的過程（類似小學(xué)生解應(yīng)用題，將題目翻譯成形式化的四則運(yùn)算）。SQL 的上述困難會(huì)造成問題解法翻譯的極大障礙，極端情況就會(huì)發(fā)生這樣一種怪現(xiàn)象：將問題解法形式化成計(jì)算語法的難度要遠(yuǎn)遠(yuǎn)大于解決問題本身。
再打個(gè)程序員易于理解的比方，用 SQL 做數(shù)據(jù)計(jì)算，類似于用匯編語言完成四則運(yùn)算。我們很容易寫出 3+5*7 這樣的算式，但如果用匯編語言（以 X86 為例），就要寫成
mov ax,3mov bx,5mul bx,7add ax,bx
這樣的代碼無論書寫還是閱讀都遠(yuǎn)不如 3+5*7 了（要是碰到小數(shù)就更要命了）。雖然對(duì)于熟練的程序員也算不了太大的麻煩，但對(duì)于大多數(shù)人而言，這種寫法還是過于晦澀難懂了，從這個(gè)意義上講，F(xiàn)ORTRAN 確實(shí)是個(gè)偉大的發(fā)明。
為了理解方便，我們舉的例子還是非常簡(jiǎn)單的任務(wù)?，F(xiàn)實(shí)中的任務(wù)要遠(yuǎn)遠(yuǎn)比這些例子復(fù)雜，過程中會(huì)面臨諸多大大小小的困難。這個(gè)問題多寫幾行，那個(gè)問題多寫幾行，一個(gè)稍復(fù)雜的任務(wù)寫出幾百行多層嵌套的 SQL 也就不奇怪了。而且這個(gè)幾百行常常是一個(gè)語句，由于工程上的原因，SQL 又很難調(diào)試，這又進(jìn)一步加劇了復(fù)雜查詢分析的難度。
我們?cè)倥e幾個(gè)例子來分別說明這幾個(gè)方面的問題。
為了讓例子中的 SQL 盡量簡(jiǎn)捷，這里大量使用了窗口函數(shù)，故而采用了對(duì)窗口函數(shù)支持較好的 ORACLE 數(shù)據(jù)庫語法，采用其它數(shù)據(jù)庫的語法編寫這些 SQL 一般將會(huì)更復(fù)雜。
這些問題本身應(yīng)該也算不上很復(fù)雜，都是在日常數(shù)據(jù)分析中經(jīng)常會(huì)出現(xiàn)的，但已經(jīng)很難為 SQL 了。
集合無序
有序計(jì)算在批量數(shù)據(jù)計(jì)算中非常普遍（取前 3 名 / 第 3 名、比上期等），但 SQL 延用了數(shù)學(xué)上的無序集合概念，有序計(jì)算無法直接進(jìn)行，只能調(diào)整思路變換方法。
select name, birthdayfrom (select name, birthday, row_number() over (order by birthday) ranking from employee )where ranking=(select floor((count(*)+1)/2) from employee)
中位數(shù)是個(gè)常見的計(jì)算，本來只要很簡(jiǎn)單地在排序后的集合中取出位置居中的成員。但 SQL 的無序集合機(jī)制不提供直接用位置訪問成員的機(jī)制，必須人為造出一個(gè)序號(hào)字段，再用條件查詢方法將其選出，導(dǎo)致必須采用子查詢才能完成。
任務(wù) 2 某支股票最長連續(xù)漲了多少交易日
select max (consecutive_day)from (select count(*) (consecutive_day from (select sum(rise_mark) over(order by trade_date) days_no_gain from (select trade_date, case when closing_price>lag(closing_price) over(order by trade_date) then 0 else 1 END rise_mark from stock_price) ) group by days_no_gain)
常規(guī)的計(jì)算連漲日數(shù)思路：設(shè)定一初始為 0 的臨時(shí)變量記錄連漲日期，然后和上一日比較，如果未漲則將其清 0，漲了再加 1，循環(huán)結(jié)束看該值出現(xiàn)的最大值。
使用 SQL 時(shí)無法描述此過程，需要轉(zhuǎn)換思路，計(jì)算從初始日期到當(dāng)日的累計(jì)不漲日數(shù)，不漲日數(shù)相同者即是連續(xù)上漲的交易日，針對(duì)其分組即可拆出連續(xù)上漲的區(qū)間，再求其最大計(jì)數(shù)。這句 SQL 讀懂已經(jīng)不易，寫出來則更困難了。
集合化不徹底
毫無疑問，集合是批量數(shù)據(jù)計(jì)算的基礎(chǔ)。SQL 雖然有集合概念，但只限于描述簡(jiǎn)單的結(jié)果集，沒有將集合作為一種基本的數(shù)據(jù)類型以擴(kuò)大其應(yīng)用范圍。
select * from employeewhere to_char (birthday, ‘MMDD’) in ( select to_char(birthday, 'MMDD') from employee group by to_char(birthday, 'MMDD') having count(*)>1 )
分組的本意是將源集合分拆成的多個(gè)子集合，其返回值也應(yīng)當(dāng)是這些子集。但 SQL 無法表示這種“由集合構(gòu)成的集合”，因而強(qiáng)迫進(jìn)行下一步針對(duì)這些子集的匯總計(jì)算而形成常規(guī)的結(jié)果集。
但有時(shí)我們想得到的并非針對(duì)子集的匯總值而是子集本身。這時(shí)就必須從源集合中使用分組得到的條件再次查詢，子查詢又不可避免地出現(xiàn)。
任務(wù) 4 找出各科成績都在前 10 名的學(xué)生
select namefrom (select name from (select name, rank() over(partition by subject order by score DESC) ranking from score_table) where ranking<=10)group by namehaving count(*)=(select count(distinct subject) from score_table)
用集合化的思路，針對(duì)科目分組后的子集進(jìn)行排序和過濾選出各個(gè)科目的前 10 名，然后再將這些子集做交集即可完成任務(wù)。但 SQL 無法表達(dá)“集合的集合”，也沒有針對(duì)不定數(shù)量集合的交運(yùn)算，這時(shí)需要改變思路，利用窗口函數(shù)找出各科目前 10 名后再按學(xué)生分組找出出現(xiàn)次數(shù)等于科目數(shù)量的學(xué)生，造成理解困難。
缺乏對(duì)象引用
在 SQL 中，數(shù)據(jù)表之間的引用關(guān)系依靠同值外鍵來維系，無法將外鍵指向的記錄直接用作本記錄的屬性，在查詢時(shí)需要借助多表連接或子查詢才能完成，不僅書寫繁瑣而且運(yùn)算效率低下。
select A.*from employee A, department B, employee Cwhere A.department=B.department and B.manager=C.name and A.gender='male' and C.gender='female'
select * from employeewhere gender='male' and department in (select department from department where manager in (select name from employee where gender='female'))
如果員工表中的部門字段是指向部門表中的記錄，而部門表中的經(jīng)理字段是指向員工表的記錄，那么這個(gè)查詢條件只要簡(jiǎn)單地寫成這種直觀高效的形式：
where gender='male' and department.manager.gender='female'
但在 SQL 中則只能使用多表連接或子查詢，寫出上面那兩種明顯晦澀的語句。
select name, company, first_companyfrom (select employee.name name, resume.company company, row_number() over(partition by resume. name order by resume.start_date) work_seq from employee, resume where employee.name = resume.name)where work_seq=1
select name, (select company from resume where name=A.name and start date=(select min(start_date) from resume where name=A.name)) first_companyfrom employee A
沒有對(duì)象引用機(jī)制和徹底集合化的 SQL，也不能將子表作主表的屬性（字段值）處理。針對(duì)子表的查詢要么使用多表連接，增加語句的復(fù)雜度，還要將結(jié)果集用過濾或分組轉(zhuǎn)成與主表記錄一一對(duì)應(yīng)的情況（連接后的記錄與子表一一對(duì)應(yīng)）；要么采用子查詢，每次臨時(shí)計(jì)算出與主表記錄相關(guān)的子表記錄子集，增加整體計(jì)算量（子查詢不能用 with 子句了）和書寫繁瑣度。
其實(shí)在分析問題時(shí)也就一定程度地指明了解決方案，重新設(shè)計(jì)計(jì)算語言，克服掉 SQL 的這幾個(gè)難點(diǎn)，問題也就解決了。
SPL 是個(gè)開源的程序語言，其全名是 Structured Process Language，和 SQL 只差一個(gè)詞。目的在于更好的解決結(jié)構(gòu)化數(shù)據(jù)的運(yùn)算。SPL 中強(qiáng)調(diào)了有序性、支持對(duì)象引用機(jī)制、從而得到徹底的集合化，這些都會(huì)大幅降低前面說的“解法翻譯”難度。
這里的篇幅不合適詳細(xì)介紹 SPL 了，我們只把上一節(jié)中的例子的 SPL 代碼羅列出來感受一下：
|
A |
| 1 |
=employee.sort(birthday) |
| 2 |
=A1((A1.len()+1)/2) |
對(duì)于以有序集合為基礎(chǔ)的 SPL 來說，按位置取值是個(gè)很簡(jiǎn)單的任務(wù)。
任務(wù) 2
|
A |
| 1 |
=stock_price.sort(trade_date) |
| 2 |
=0 |
| 3 |
=A1.max(A2=if(close_price>close_price[-1],A2+1,0)) |
SPL 按自然的思路過程編寫計(jì)算代碼即可。
任務(wù) 3
|
A |
| 1 |
=employee.group(month(birthday),day(birthday)) |
| 2 |
=A1.select(~.len()>1).conj() |
SPL 可以保存分組結(jié)果集，繼續(xù)處理就和常規(guī)集合一樣。
任務(wù) 4
|
A |
| 1 |
=score_table.group(subject) |
| 2 |
=A1.(~.rank(score).pselect@a(~<=10)) |
| 3 |
=A1.(~(A2(#)).(name)).isect() |
使用 SPL 只要按思路過程寫出計(jì)算代碼即可。
任務(wù) 5
|
A |
| 1 |
=employee.select(gender=="male" && department.manager.gender=="female") |
支持對(duì)象引用的 SPL 可以簡(jiǎn)單地將外鍵指向記錄的字段當(dāng)作自己的屬性訪問。
任務(wù) 6
|
A |
| 1 |
=employee.new(name,resume.minp(start_date).company:first_company) |
SPL 支持將子表集合作為主表字段，就如同訪問其它字段一樣，子表無需重復(fù)計(jì)算。
SPL 有直觀的 IDE，提供了方便的調(diào)試功能，可以單步跟蹤代碼，進(jìn)一步降低代碼的編寫復(fù)雜度。
對(duì)于應(yīng)用程序中的計(jì)算，SPL 提供了標(biāo)準(zhǔn)的 JDBC 驅(qū)動(dòng)，可以像 SQL 一樣集成到 Java 應(yīng)用程序中：
…Class.forName("com.esproc.jdbc.InternalDriver");Connection conn =DriverManager.getConnection("jdbc:esproc:local://");Statement st = connection.();CallableStatement st = conn.prepareCall("{call xxxx(?,?)}");st.setObject(1, 3000);st.setObject(2, 5000);ResultSet result=st.execute();...
GitHub：https://github.com/SPLWare/esProc
簡(jiǎn)單好用的SPL開源啦！
為了給感興趣的小伙伴們提供一個(gè)相互交流的平臺(tái)，
特地開通了交流群（群完全免費(fèi)，不廣告不賣課）
本文感興趣的朋友，請(qǐng)到閱讀原文去收藏 ^_^
