SQL為什麼動不動就N百行以K計？
發明SQL的初衷之一顯然是為了降低人們實施資料查詢計算的難度。SQL中用了不少類英語的詞彙和語法，這是希望非技術人員也能掌握。確實，簡單的SQL可以當作英語閱讀，即使沒有程式設計經驗的人也能運用。
然而，面對稍稍複雜的查詢計算需求，SQL就會顯得力不從心，經常寫出幾百行有多層巢狀的語句。這種SQL，不要說非技術人員難以完成，即使對於專業程式設計師也不是件容易的事，常常成為很多軟體企業應聘考試的重頭戲。三行五行的SQL僅存在教科書和培訓班，現實中用於報表查詢的SQL通常是以“K”計的。
SQL困難的分析探討
這是為什麼呢？我們通過一個很簡單的例子來考察SQL在計算方面的缺點。
設有一個由三個欄位構成的銷售業績表（為了簡化問題，省去日期資訊）：
| | | |-----|-----| |sales_amount| 銷售業績表| |sales | 銷售員姓名，假定無重名| |product| 銷售的產品| |amount | 該銷售員在該產品上的銷售額|
現在我們想知道出空調和電視銷售額都在前10名的銷售員名單。
這個問題並不難，人們會很自然地設計出如下計算過程：
1． 按空調銷售額排序，找出前10名；
2． 按電視銷售額排序，找出前10名；
3． 對1、2的結果取交集，得到答案；
我們現在來用SQL做。
1． 找出空調銷售額前10名，還算簡單：
sql
select top 10 sales from sales_amount where product='AC' order by amount desc
2． 找出電視銷售額前10名。動作一樣：
sql
select top 10 sales from sales_amount where product='TV' order by amount desc
3． 求1、2的交集。這有點麻煩，SQL不支援步驟化，上兩步的計算結果無法儲存，只能再重抄一遍了:
sql
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要寫成這樣，而日常計算中多達十幾步的比比皆是，這顯然超出來許多人的可接受能力。
我們知道了SQL的第一個重要缺點：不支援步驟化。把複雜的計算分步可以在很大程度地降低問題的難度，反過來，把多步計算匯成一步則很大程度地提高了問題的難度。
可以想象，如果老師要求小學生做應用題時只能列一個算式完成，小朋友們會多麼苦惱（當然，不乏一些聰明孩子搞得定）。
SQL查詢不能分步，但用SQL寫出的儲存過程可以分步，那麼用儲存過程是否可以方便地解決這個問題呢？
暫先不管使用儲存過程的技術環境有多麻煩和資料庫的差異性造成的不相容，我們只從理論上來看用分步SQL是否能讓這個計算更簡單捷些。
1． 計算空調銷售額前10名。語句還是那樣，但我們需要把結果存起來供第3步用，而SQL中只能用表儲存集合資料，這樣我們要建一個臨時表：
sql
create temporary table x1 as
select top 10 sales from sales_amount where product='AC' order by amount desc
2． 計算電視銷售額前10名。類似地
sql
create temporary table x2 as
select top 10 sales from sales_amount where product='TV' order by amount desc
3． 求交集，前面麻煩了，這步就簡單些
sql
select * from x1 intersect x2
分步後思路變清晰了，但臨時表的使用仍然繁瑣。在批量結構化資料計算中，作為中間結果的臨時集合是相當普遍的，如果都建立臨時表來儲存，運算效率低，程式碼也不直觀。
而且，SQL不允許某個欄位取值是集合（即臨時表），這樣，有些計算即使容忍了繁瑣也做不到。
如果我們把問題改為計算所有產品銷售額都在前10名的銷售員，試想一下應當如何計算，延用上述的思路很容易想到：
1． 將資料按產品分組，將每組排序，取出前10名；
2． 將所有的前10名取交集；
由於我們事先不知道會有多個產品，這樣需要把分組結果也儲存在一個臨時表中，而這個表有個欄位要儲存對應的分組成員，這是SQL不支援的，辦法就行不通了。
如果有視窗函式的支援，可以轉換思路，按產品分組後，計算每個銷售員在所有分組的前10名中出現的次數，若與產品總數相同，則表示該銷售員在所有產品銷售額中均在前10名內。
sql
select sales
from ( select sales,
from ( select sales,
rank() over (partition by product order by amount desc ) ranking
from sales_amount)
where ranking <=10 )
group by sales
having count(*)=(select count(distinct product) from sales_amount)
這樣的SQL，有多少人會寫呢？
況且，視窗函式在有些資料庫中還不支援。那麼，就只能用儲存過程寫迴圈依次計算每個產品的前10名，與上一次結果做交集。這個過程比用高階語言編寫程式並不簡單多少，而且仍然要面對臨時表的繁瑣。
現在，我們知道了SQL的第二個重要缺點：集合化不徹底。雖然SQL有集合概念，但並未把集合作為一種基礎資料型別提供，這使得大量集合運算在思維和書寫時都需要繞路。
我們在上面的計算中使用了關鍵字top，事實上關係代數理論中沒有這個東西（它可以被別的計算組合出來），這不是SQL的標準寫法。
我們來看一下沒有top時找前10名會有多困難？
大體思路是這樣：找出比自己大的成員個數作為是名次，然後取出名次不超過10的成員，寫出的SQL如下：
sql
select sales
from ( 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
或
sql
select sales
from ( 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語句，專業程式設計師寫出來也未必容易吧！而僅僅是計算了一個前10名。
退一步講，即使有top，那也只是使取出前一部分輕鬆了。如果我們把問題改成取第6至10名，或者找比下一名銷售額超過10%的銷售員，困難仍然存在。
造成這個現象的原因就是SQL的第三個重要缺點：缺乏有序支援。SQL繼承了數學上的無序集合，這直接導致與次序有關的計算相當困難，而可想而知，與次序有關的計算會有多麼普遍（諸如比上月、比去年同期、前20%、排名等）。
SQL2003標準中增加的視窗函式提供了一些與次序有關的計算能力，這使得上述某些問題可以有較簡單的解法，在一定程度上緩解SQL的這個問題。但視窗函式的使用經常伴隨著子查詢，而不能讓使用者直接使用次序訪問集合成員，還是會有許多有序運算難以解決。
我們現在想關注一下上面計算出來的“好”銷售員的性別比例，即男女各有多少。一般情況下，銷售員的性別資訊會記在花名冊上而不是業績表上，簡化如下：
| | | |--- | ---| |employee| 員工表| |name| 員工姓名，假定無重名| |gender| 員工性別|
我們已經計算出“好”銷售員的名單，比較自然的想法，是用名單到花名冊時找出其性別，再計一下數。但在SQL中要跨表獲得資訊需要用表間連線，這樣，接著最初的結果，SQL就會寫成：
sql
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 ) ) A
where A.sales=employee.name
group by employee.gender
僅僅多了一個關聯表就會導致如此繁瑣，而現實中資訊跨表儲存的情況相當多，且經常有多層。比如銷售員有所在部門，部門有經理，現在我們想知道“好”銷售員歸哪些經理管，那就要有三個表連線了，想把這個計算中的where和group寫清楚實在不是個輕鬆的活兒了。
這就是我們要說的SQL的第四個重要困難：缺乏物件引用機制，關係代數中物件之間的關係完全靠相同的外來鍵值來維持，這不僅在尋找時效率很低，而且無法將外來鍵指向的記錄成員直接當作本記錄的屬性對待，試想，上面的句子可否被寫成這樣：
sql
select sales.gender,count(*)
from (…) // …是前面計算“好”銷售員的SQL
group by sales.gender
顯然，這個句子不僅更清晰，同時計算效率也會更高（沒有連線計算）。
我們通過一個簡單的例子分析了SQL的四個重要困難，這也是SQL難寫或要寫得很長的主要原因。基於一種計算體系解決業務問題的過程，也就是將業務問題的解法翻譯成形式化計算語法的過程（類似小學生解應用題，將題目翻譯成形式化的四則運算）。SQL的上述困難會造成問題解法翻譯的極大障礙，極端情況就會發生這樣一種怪現象：將問題解法形式化成計算語法的難度要遠遠大於解決問題本身。
再打個程式設計師易於理解的比方，用SQL做資料計算，類似於用匯編語言完成四則運算。我們很容易寫出3+57這樣的算式，但如果用匯編語言（以X86為例），就要寫成
mov ax,3
mov bx,5
mul bx,7
add ax,bx
這樣的程式碼無論書寫還是閱讀都遠不如3+57了（要是碰到小數就更要命了）。雖然對於熟練的程式設計師也算不了太大的麻煩，但對於大多數人而言，這種寫法還是過於晦澀難懂了，從這個意義上講，FORTRAN確實是個偉大的發明。
為了理解方便，我們舉的例子還是非常簡單的任務。現實中的任務要遠遠比這些例子複雜，過程中會面臨諸多大大小小的困難。這個問題多寫幾行，那個問題多寫幾行，一個稍複雜的任務寫出幾百行多層巢狀的SQL也就不奇怪了。而且這個幾百行常常是一個語句，由於工程上的原因，SQL又很難除錯，這又進一步加劇了複雜查詢分析的難度。
更多例子
我們再舉幾個例子來分別說明這幾個方面的問題。
為了讓例子中的SQL儘量簡捷，這裡大量使用了視窗函式，故而採用了對視窗函式支援較好的ORACLE資料庫語法，採用其它資料庫的語法編寫這些SQL一般將會更復雜。 這些問題本身應該也算不上很複雜，都是在日常資料分析中經常會出現的，但已經很難為SQL了。
計算不分步
把複雜的計算分步可以在很大程度地降低問題的難度，反過來，把多步計算匯成一步完成則會提高問題的複雜度。
任務1 銷售部的人數，其中北京籍人數，再其中女員工人數？
銷售部的人數
sql
select count(*) from employee where department='sales'
其中北京籍的人數
sql
select count(*) from employee where department='sales' and native_place='Beijing'
再其中的女員工人數
sql
select count (*) from employee
where department='sales' and native_place='Beijing' and gender='female'
常規想法：選出銷售部人員計數，再在其中找出其中北京籍人員計數，然後再遞進地找出女員工計數。每次查詢都基於上次已有的結果，不僅書寫簡單而且效率更高。
但是，SQL的計算不分步，回答下一個問題時無法引用前面的成果，只能把相應的查詢條件再抄一遍。
任務2 每個部門挑選一對男女員工組成遊戲小組
with A as
(select name, department,
row_number() over (partition by department order by 1) seq
from employee where gender=‘male’)
B as
(select name, department,
row_number() over(partition by department order by 1) seq
from employee where gender=‘female’)
select name, department from A
where department in ( select distinct department from B ) and seq=1
union all
select name, department from B
where department in (select distinct department from A ) and seq=1
計算不分步有時不僅造成書寫麻煩和計算低效，甚至可能導致思路嚴重變形。
這個任務的直觀想法：針對每個部門迴圈，如果該部門有男女員工則各取一名添進結果集中。但SQL不支援這種逐步完成結果集的寫法（要用儲存過程才能實現此方案），這時必須轉變思路為：從每個部門中選出男員工，從每個部門選出女員工，對兩個結果集分別選出部門出現在另一個結果集的成員，最後再做並集。
好在還有with子句和視窗函式，否則這個SQL語句簡直無法看了。
集合無序
有序計算在批量資料計算中非常普遍（取前3名/第3名、比上期等），但SQL延用了數學上的無序集合概念，有序計算無法直接進行，只能調整思路變換方法。
任務3 公司中年齡居中的員工
sql select name, birthday from (select name, birthday, row_number() over (order by birthday) ranking from employee ) where ranking=(select floor((count(*)+1)/2) from employee)中位數是個常見的計算，本來只要很簡單地在排序後的集合中取出位置居中的成員。但SQL的無序集合機制不提供直接用位置訪問成員的機制，必須人為造出一個序號欄位，再用條件查詢方法將其選出，導致必須採用子查詢才能完成。任務4 某支股票最長連續漲了多少交易日
sql 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)無序的集合也會導致思路變形。
常規的計算連漲日數思路：設定一初始為0的臨時變數記錄連漲日期，然後和上一日比較，如果未漲則將其清0，漲了再加1，迴圈結束看該值出現的最大值。
使用SQL時無法描述此過程，需要轉換思路，計算從初始日期到當日的累計不漲日數，不漲日數相同者即是連續上漲的交易日，針對其分組即可拆出連續上漲的區間，再求其最大計數。這句SQL讀懂已經不易，寫出來則更困難了。
集合化不徹底
毫無疑問，集合是批量資料計算的基礎。SQL雖然有集合概念，但只限於描述簡單的結果集，沒有將集合作為一種基本的資料型別以擴大其應用範圍。
任務5 公司中與其他人生日相同的員工
sql select * from employee where to_char (birthday, ‘MMDD’) in ( select to_char(birthday, 'MMDD') from employee group by to_char(birthday, 'MMDD') having count(*)>1 )分組的本意是將源集合分拆成的多個子集合，其返回值也應當是這些子集。但SQL無法表示這種“由集合構成的集合”，因而強迫進行下一步針對這些子集的彙總計算而形成常規的結果集。
但有時我們想得到的並非針對子集的彙總值而是子集本身。這時就必須從源集合中使用分組得到的條件再次查詢，子查詢又不可避免地出現。
任務6 找出各科成績都在前10名的學生
select name from (select name from (select name, rank() over(partition by subject order by score DESC) ranking from score_table) where ranking<=10) group by name having count(*)=(select count(distinct subject) from score_table)用集合化的思路，針對科目分組後的子集進行排序和過濾選出各個科目的前10名，然後再將這些子集做交集即可完成任務。但SQL無法表達“集合的集合”，也沒有針對不定數量集合的交運算，這時需要改變思路，利用視窗函式找出各科目前10名後再按學生分組找出出現次數等於科目數量的學生，造成理解困難。
缺乏物件引用
在SQL中，資料表之間的引用關係依靠同值外來鍵來維繫，無法將外來鍵指向的記錄直接用作本記錄的屬性，在查詢時需要藉助多表連線或子查詢才能完成，不僅書寫繁瑣而且運算效率低下。
任務7 女經理的男員工們
用多表連線
select A.*
from employee A, department B, employee C
where A.department=B.department and B.manager=C.name and
A.gender='male' and C.gender='female'
用子查詢
select * from employee
where gender='male' and department in
(select department from department
where manager in
(select name from employee where gender='female'))
如果員工表中的部門欄位是指向部門表中的記錄，而部門表中的經理欄位是指向員工表的記錄，那麼這個查詢條件只要簡單地寫成這種直觀高效的形式：
where gender='male' and department.manager.gender='female'
但在SQL中則只能使用多表連線或子查詢，寫出上面那兩種明顯晦澀的語句。
任務8 員工的首份工作公司
用多表連線
select name, company, first_company
from (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_company
from employee A
沒有物件引用機制和徹底集合化的SQL，也不能將子表作主表的屬性（欄位值）處理。針對子表的查詢要麼使用多表連線，增加語句的複雜度，還要將結果集用過濾或分組轉成與主表記錄一一對應的情況（連線後的記錄與子表一一對應）；要麼採用子查詢，每次臨時計算出與主表記錄相關的子表記錄子集，增加整體計算量（子查詢不能用with子句了）和書寫繁瑣度。
SPL的引入
問題說完，該說解決方案了。
其實在分析問題時也就一定程度地指明瞭解決方案，重新設計計算語言，克服掉SQL的這幾個難點，問題也就解決了。
這就是發明SPL的初衷！
SPL是個開源的程式語言，其全名是Structured Process Language，和SQL只差一個詞。目的在於更好的解決結構化資料的運算。SPL中強調了步驟化、支援有序集合和物件引用機制、從而得到徹底的集合化，這些都會大幅降低前面說的“解法翻譯”難度。
這裡的篇幅不合適詳細介紹SPL了，我們只把上一節中的8個例子的SPL程式碼羅列出來感受一下：
任務1
|| A| B| |--- |---|---| |1 |=employee.select(department=="sales") |=A1.len()| |2| =A1.select(native_place=="Beijing") |=A2.len()| |3| =A2.select(gender=="female")| =A3.len()|
SPL可以保持記錄集合用作中間變數，可逐步執行遞進查詢。
任務2
|| A| B| C|
|---|---|---|---|
|1| for employee.group(department)| =A1.group@1(gender)|
|2|| >if B1.len()>1| =@\|B1|
有步驟和程式邏輯支援的SPL能很自然地逐步完成結果。
任務3
|| A|
|--- |---|
|1| =employee.sort(birthday)|
|2 |=A1((A1.len()+1)/2)|
對於以有序集合為基礎的SPL來說，按位置取值是個很簡單的任務。
任務4
|| A|
|--- |---|
|1| =stock_price.sort(trade_date)|
|2| =0|
|3| =A1.max(A2=if(close_price>close_price[-1],A2+1,0))|
SPL按自然的思路過程編寫計算程式碼即可。
任務5
|| A|
|--- |---|
|1| =employee.group(month(birthday),day(birthday))|
|2| =A1.select(\~.len()>1).conj()|
SPL可以儲存分組結果集，繼續處理就和常規集合一樣。
任務6
|| A|
|--- |---|
|1| =score_table.group(subject)|
|2| =A1.(\~.rank(score).pselect@a(\~<=10))|
|3| =A1.(\~(A2(#)).(name)).isect()|
使用SPL只要按思路過程寫出計算程式碼即可。
任務7
|| A|
|--- |---|
|1 |=employee.select(gender=="male" && department.manager.gender=="female")|
支援物件引用的SPL可以簡單地將外來鍵指向記錄的欄位當作自己的屬性訪問。
任務8
|| A|
|--- |---|
|1 |=employee.new(name,resume.minp(start_date).company:first_company)|
SPL支援將子表集合作為主表字段，就如同訪問其它欄位一樣，子表無需重複計算。
SPL有直觀的IDE，提供了方便的除錯功能，可以單步跟蹤程式碼，進一步降低程式碼的編寫複雜度。
對於應用程式中的計算，SPL提供了標準的JDBC驅動，可以像SQL一樣整合到Java應用程式中： ``` … 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(); ...
```
SPL資料
- 快看！這隻貓兩次登上 Github Trending !!!
- 百萬級資料excel匯出功能如何實現？
- SQL為什麼動不動就N百行以K計？
- 一次SpringBoot版本升級，引發的血案
- MYSQL中的14個神仙功能，驚豔到我了！！！
- 瞧瞧別人家的API介面，那叫一個優雅
- 同事吐槽我的介面效能差，原來它是真凶！
- 如何讓JSON跑得更快？
- 糟了，線上服務出現OOM了
- 7000字 24張圖帶你徹底弄懂執行緒池
- 分頁 模糊查詢 有坑！
- 這12款idea外掛，能讓你程式碼飛起來
- 聊聊資料庫建表的15個小技巧
- 明明加了唯一索引，為什麼還是產生了重複資料？
- 明明加了唯一索引，為什麼還是產生了重複資料？
- Java程式碼優化的30個小技巧
- 聊聊Java中程式碼優化的30個小技巧
- 盤點一下，99%的Java程式設計師會踩的坑
- 聊聊spring事務失效的12種場景，太坑了
- 從20s優化到500ms，我用了這三招