OLAP計算引擎是一架機器，而操作這架機器的是程式設計語言。使用者通過特定語言告訴計算引擎，需要讀取哪些資料、以及需要進行什麼樣的計算。程式設計語言有很多種，任何人都可以設計出一門程式設計語言，然後設計對應的編譯器做解析。SQL語言從發明出來到現在已經經曆了近半個世紀，是什麼讓SQL持續的煥發青春呢？本文将介紹SQL的理論基礎和各種高端玩法。歡迎閱讀~
一、資料分析的語言接口
OLAP計算引擎是一架機器，而操作這架機器的是程式設計語言。使用者通過特定語言告訴計算引擎，需要讀取哪些資料、以及需要進行什麼樣的計算。程式設計語言有很多種，任何人都可以設計出一門程式設計語言，然後設計對應的編譯器做解析。程式設計語言從分類上來說，可以分為指令式，聲明式。
指令式程式設計語言是我們最常見的程式設計語言，C/C++/Java等都是指令式程式設計語言，這類語言明确的告訴機器應該執行什麼樣的指令，留給編譯器優化的空間很小了。
聲明式程式設計描述程式應該獲得什麼結果，至于如何做到，并不關注細節。SQL就是一種聲明式程式設計語言。例如SQL語句select count(1) from department where kpi =3.25，指明計算kpi=3.25的人數，但不會具體指定如何完成計算。這給後續的優化器留下了很大的操作空間，優化器可以根據SQL的需求和實際的資料做各種各樣的探索，尋找到最佳的執行方式。
一個優秀的分析語言應該具有以下幾個特征：
- 語言簡單，門檻低
- 語意明确，無歧義
- 資料豐富，友善學習
- 生态豐富，工具多
- 友善擴充，可編排複雜的邏輯
SQL是一種曆史悠久的，使用比較廣泛的分析語言。在關系型資料庫時代就廣泛使用的一種語言。
在21世紀初，出現了MapReduce算法，資料分析師需要編寫MapReduce程式來分析資料。MapReduce程式是一種指令式語言，編寫過程非常麻煩，堪比寫程式，這就需要資料分析師不僅具備算法能力，還要具備工程能力，使用體驗非常糟糕。
這就需要兩個團隊來合作，BI團隊把分析需求傳遞給開發團隊，由開發團隊去開發分析程式。為了改善分析體驗，出現了SQL on Hadoop的解決方案，典型的如Hive，提供SQL接口，并把使用者輸入的SQL轉寫成MapReduce執行計劃，因而極大的提升了資料分析的體驗，實作了BI團隊的自主分析，降低了資料分析的門檻，大大增加了閱聽人範圍。
是以，SQL的影響力是非常大的。從Hive開始，大資料的主要使用接口就轉移到了SQL上。而工程師們可以在SQL這張皮之下，專心的優化性能，無縫的更新計算引擎，保持使用接口的一緻性。
SQL的文法簡單，邏輯清晰，了解了最簡單的查詢語句之後，就可以嵌套多層表達很複雜的邏輯。SQL基于關系代數，有理論基礎，保證語意明确沒有歧義。SQL的發展曆史非常久遠，因而學習資料也比較多，友善新入門者學習。同時圍繞SQL的生态也比較豐富，有很多工具使用SQL做分析。
除了SQL之外，也有一些軟體推出自定義的語言，例如Elasticsearch使用Lucene文法，Prometheus推出了自定義的PromQL，而Splunk推出了SPL。每一種新的文法，對于新使用者而言，都存在一定的學習門檻。因而都不如SQL使用廣泛。可以說SQL是資料分析的事實标準。
1.1 資料模型
資料模型（DataModel） 用于描述資料在資料庫中的組織形式。常見的模型有關系模型（Relational），鍵值模型（Key/Value），圖模型（Graph），文檔模型（Document），列簇模型（Column-family）等。 關系型資料庫采用關系模型。Redis采用鍵值模型。圖資料庫采用圖模型。MongolDB采用文檔模型。
關系模型中的關系有點繞口，在英文中是Relational，硬翻譯成了關系，我的了解，關系指的是一些互相之間有關系的屬性組成的一個實體，因為各個列屬性之間存在關聯關系，而被稱為一個關系，其實指的是屬性之間的相關性，這種相關性展現在：屬于同一行；滿足列之間的限制條件；滿足行之間的限制條件；滿足不同關系之間的限制條件。通過不同的限制條件，是全部的資料形成一種有組織的存在。
資料庫通過關系模型，定義出一個個關系實體，確定内容之間滿足一定的限制标間，并且提供程式設計接口去讀寫資料庫内容。一個資料庫包含一堆關系，每個關系是一個多行多列的表格。每一行的各個列之間是相關的，也可能會定義一些限制條件。行與行之間，也可能通過定義唯一鍵（Primary Key），定義排序方式來限制行之間的關系。關系與關系之間，可以通過外部鍵來實作。
這種列之間和行之間的限制關系，在OLTP場景中比較實用，因為OLTP關注的資料本身，是以在存儲資料時，更多關注資料的存儲形式。而OLAP關注的資料的分析，是以在數倉中，這些限制條件是弱化的。是以，在數倉中，我們隻需關注一張多行多列的表格即可，像PK、排序這類限制屬性，更多隻是用來做資料加速的手段。關系模型用來作為一種嚴密的理論，給執行器的優化提供理論基礎。但是這個名字畢竟太繞口，在後續文章中，除非涉及到關系模型相關的理論，會使用關系這個詞，一般情況下，會用表來指代一個關系。
1.2 關系代數(Relational Algebra)
關系模型和關系代數是SQL的理論基礎。代數不僅是我們所熟知的簡單的加減乘除等數學計算。在計算機行業，我們見到過多種algebra，在神經網絡中常用的線性代數（linear algebra），在電路中用到的布爾代數（boolean algebra），香農把布爾代數帶入到了邏輯電路設計中，為計算機二進制計算提供了理論依據。此外還有N多種algebra，這裡不一一列舉。
關系代數，源自于集合代數，講述集合之間的變換關系。關系代數中的一系列操作，接受一個或兩個關系作為輸入，産生一個新的關系作為結果。由于輸入和輸出都是一個關系，我們可以串聯多個算子，形成更加複雜的算子。
關系代數中包含的算子有：
- σ (select，從一個關系中篩選出部分行，形成一個新的關系)
- Π(projection，從一個關系中篩選出部分列，形成一個新的關系)
- ∪(Union，合并兩個關系), ∩(Intersection，取兩個關系的交集部分)
- –(difference，取兩個關系的差集部分)
- ×(Product，兩個關系的笛卡爾積)
- ⋈(Join，兩個關系在滿足某些條件下的連接配接)
- ρ（Rename，重命名關系中的列)
- ←(Assignments，把一個臨時的查詢命名成一個新的關系)
- δ(Duplicate Eliminating，去重)
- γ(Aggregation，對部分列做聚合計算，結果形成一個新關系)
- τ(Sorting，排序結果形成一個新關系)
這裡定義了常用的關系操作，名字已經表示出了其操作的含義，在這裡不再介紹每個操作的明細了。在文法解析和優化器階段我們會再次接觸到關系代數，并且借助于關系代數的理論依據，來做一些文法樹上的轉換。在這裡我們隻需要知道在關系代數上有這些操作，并且在之後的SQL文法上看到如何用SQL文法來表達這些操作。
二、SQL
2.1 SQL語言的發展曆史
SQL的發展曆史，可以追溯到機械化資料分析的曆史。在20世紀初，IBM主要的業務是打孔卡業務，也就是使用卡上的孔來記錄資訊，然後利用電路的通斷判斷是否有孔，并通過電路驅動機械裝置，累計計算結果。
打孔卡類似我們現代使用的答題卡，答題卡的每一個題目，都提供了四個選項，然後用鉛筆塗黑對應的選項；打孔卡不同的地方在于，選中的部分穿透成孔，當放置到電路闆上時，有孔的部分會有電流通過，進而觸發之後的動作。這是在當時是一項非常先進的資料分析方法，相較于古老的依賴人去計數，也讓大資料的自動化分析成為可能。
在20世紀初，要統計數千萬人口的資訊，需要投入大量的人力資源，而打孔卡這種創世紀的發明，帶來了資料分析行業的快速發展。是以可以說IBM的業務主要是提供資料分析的機器，主要的客戶場景是聯邦政府的人口普查，以及業機構做商業分析。這個時候，資料存儲以來打孔卡，而資料計算是機械裝置，計算結果輸出到列印機。
到20世紀50年代，随着電氣化的發展，錄音帶取代打孔卡成為新的儲存設備，電氣裝置取代機械裝置做計數。計算結果可以繼續存儲到錄音帶上。錄音帶的存儲空間很大，不過錄音帶的缺點是隻能順序讀寫，這導緻資料處理的程式不得不适應這種特性按照順序處理。
到了60、70年代，磁盤被發明出來，磁盤可以被随機讀寫，這極大的改變了資料處理方式。資料結構無需考慮資料之間的順序，一些更加複雜的資料模型被發明出來，例如網狀模型或者階層化模型。1970年，Edgar Codd定義了關系模型，給出了非過程式的查詢資料的方法，關系型資料庫誕生了。關系模型非常簡潔，并且提供了理論基礎。非過程式的查詢方法，屏蔽了實作的細節，使用者隻需要聲明所需要的結果即可，實作的過程則交給優化器給出最優的執行計劃，這極大的降低了使用門檻。關系模型的發明者也是以獲得了圖靈獎。盡管關系模型在學術上非常吸引人，但是在現實中，在性能上還比不上已經存在的資料庫。
到了70年代後期、80年代，IBM推出了一個突破性的項目System R，在項目中研發了至關重要的能夠使關系型資料庫非常高效的技術。在System R中，IBM推出了SQL的最早期版本，稱為Sequal，後來演化成了SQL（Structed Query Language結構化查詢語言）。這個項目雖然是個原型，但是它促進了之後IBM推出了第一個商用的關系模型的資料庫産品System/38(1979),SQL/DS(1981),DB2(1983)。其中DB2目前還是活躍的商用資料庫，在大學中也有DB2的使用課程。至此，SQL語言出現了，并且被其他的商用資料庫系統所采用，比如Oracle的資料庫。在數十年内，SQL語言憑借着其易用性，擊敗了其他需要關心底層實作的資料庫産品，成為了事實上的标準。
1986年ANSI标準推出了SQL标準，稱為SQL86，就是我們常說的ANSI SQL。之後标準經過陸續補充，以添加新的特性，陸續出現了SQL89，SQL92，SQL1999（正則式，觸發器，OO）， SQL2003（XML,視窗函數，Sequence，自增ID），SQL2006， SQL2008（清空表文法，Fancy Sorting）， SQL2011（臨時表，管道式DML）， 最近的是SQL2016（Json，多态表）。
一般來說，一個資料分析系統，不一定完全遵循SQL的标準，這主要是由分析系統的特有特性所決定的，有些特性，在SQL标準裡邊是沒有的，是以一般會在SQL标準上做一些拓展，号稱是相容ANSI SQL。一個系統需要支援的最小功能集合是SQL92标準。
2.2 SQL的功能
SQL文法包含了幾個類别的功能，分别是
Data Manipulation Language（DML）：資料操作語言，用于增删改查資料。
Data Definition Language（DDL）：資料定義語言，用于定義表的格式。
Data Control Language（DCL）:資料控制語言，用于控制權限等。
雖然DML和DCL是SQL系統的基礎功能，本文的關注重點更多是資料處理的技術，以及如何加快資料處理的技術，是以更多關注DDL。 在DDL中，也有增删改查，在這幾項中，本文更多關注查的部分内容，即如何加快資料的讀取和計算。而資料的寫入、存儲部分的優化手段，也是為了滿足加速資料計算的目的。
2.3 SQL的處理過程
SQL全稱Structed Query Language(結構化查詢語言)。SQL文法簡單，易學易用，是資料分析領域最通用的語言。SQL是資料分析的操作工具，對于使用者而言SQL代表浙使用者的操作語義，但是對于程式而言，隻是接收到一串字元串。程式需要了解SQL的意義，要經過詞法分析、文法分析、語義分析、構造成抽象文法樹。詞法分析、文法分析是非常基礎的操作。大學的計算機的編譯原理課程應該包含了本部分内容，詞法分析和文法分析的模式是固定的，玩不出花樣，無助于提升計算速度。不過作為OLAP引擎中必不可少的第一環，還是有必要對詞法分析和文法分析做出簡單的介紹，有助于了解後續章節中的查詢計劃和優化器，但是本章不會占用太多篇幅，本文的重點是關于計算速度的内容。
開發者也可以研發自定義的分析語言，隻要語言符合一定的規則，沒有歧義，在語義上完整，也能過稱為一種語言。不過開發一個新的語言非常困難，大多數的新語言采用程式式程式設計，每一個短語表示一個簡單的操作；或者采用管道式聲明文法，每一部分代表輸入，計算和輸出，但是要定義一種能夠無限擴充而沒有歧義的文法是很難的。在語義完整程度上是不能和SQL相比較的。無論是開發一門新的語言，還是采用SQL，流程都和下圖類似。OLAP引擎解析SQL，生成抽象文法樹，再轉化成邏輯執行計劃，經過優化後，生成高性能的算子組合。這就是編譯和優化的過程。
程式編譯和SQL編譯
在了解編譯之前，我們首先了解一下SQL的結構定義。SQL是圍繞着關系進行的。可以在關系上定義各種操作，也可以定義多個關系的操作。
2.4 關系
SQL操作的對象是結構化資料。SQL文法的基礎文法以及嵌套擴充，都是圍繞着“關系”進行的。“關系”可以想象成資料庫中的表，由多行多列組成。一個SQL，接受一個或多個“關系”的輸入，并輸出一個“關系”。在嵌套查詢時，内部查詢輸出一個中間“關系”，并作為外層查詢的輸入“關系”，類似于Linux指令行中的管道文法。在下文中，用“表”來表示“關系”。
三、SQL 文法
3.1 單表上的操作
在一個表上，可以進行過濾(WHERE)、轉換(scalar函數)、聚合(聚合或分組聚合)、聚合後過濾（HAVING）、排序（ORDER BY）、投影（SELECT）、截斷行數（LIIMIT）等操作。各個操作之間的執行時間存在先後順序。一個典型的SQL文法如：
[WITH with_query [,...]]
SELECT expr
FROM TABLE
WHERE bool_expr
GROUP BY columns
HAVING Condition
ORDER BY expr
LIMIT count 在執行順序上，首先從表中select出需要的列；然後執行WHERE語句；過濾完後，執行GROUP BY聚合計算；聚合後的結果執行HAVING執行二次過濾；然後執行ORDER BY排序結果；最後根據LIMIT限定輸出的行數。
SQL執行順序
經過以上步驟，完成對一個表的操作，并且輸出一個新的表。當需要嵌套查詢時，把内部的結果表用括号包含起來，即可視同内部查詢為一個普通表，然後執行上述相同的操作。因而，SQL的文法可以無限的嵌套。對于嵌套查詢，除了用括号把子查詢包含起來作為子表，另一種做法是用with語句定義子查詢。下文予以詳細介紹。
3.2 SELECT子句
最簡單的SELECT操作是SELECT select_expr from TABLE。表示從表中擷取資料，也允許在資料之上增加一些列的計算。在select可跟的表達式有：
- SELECT 列名.表示從表中讀取列的原始資料。
- SELECT scalar_function(列名)，表示讀取列的原始資料，并且經過scalar_function逐行轉換每一行原始資料，輸出轉換後結果。Scalar Function是轉換函數，表示1行到1行的轉換。經過轉換後的資料行數不會發生改變。一個典型的轉換函數是round函數，表示把原始資料截斷後保留幾個小數位。
- SELECT aggregate_function(列名)，表示讀取原始資料，并且對所有的原始資料做聚合計算，輸出聚合後的結果，結果隻包含一行一列資料。
SELECT後的表達式有可以有1個或者多個，可用逗号來連接配接多個表達式，如果是第1或第2種情況，兩種表達式可以混合使用，例如SELECT column1, scalar_function(column2)，可以并列出現無限多個列名或者轉換函數。對于第3種情況，在沒有group by語句的情況下，聚合函數隻能和其他聚合函數混合使用，例如SELECT aggretate_function1(column1), aggregate_function2(column2)，在同級别不能出現1或者2的情況，當然聚合函數内是可以嵌套轉換函數的，例如SELECT aggregate_function(scalar_function(column))。對于有group by的情況，group by的列名以及列名之上的轉換函數可以出現在select中。原理也很簡單，因為case 1和2不改變結果行數，case 3聚合計算隻輸出一行結果，是以是不能在同級别混用的。
3.3 轉換函數（scalar function）
如上文所說，轉換函數對每一行輸入，經過計算後，輸出對應一行的結果，即，轉換函數不會改變輸入資料的行數。scalar function的scalar就代表是對原有資料的線性伸縮，不改變原資料的次元空間。轉換函數的輸入參數可以是0個或者多個；輸出隻有1個，即無論輸入多少列參數，輸出隻有一列。如果希望輸出多列，則需要把輸出結果整合到一個複雜類型裡，例如數組array或者字典map，再通過嵌套查詢展開結果。
由于轉換函數不改變結果的行數，是以可以無限嵌套調用轉換函數，例如fun1(fun2(fun3(fun4(fun5(key)))))，盡管大多數情況下無限層次的嵌套并不是必要的，一到兩層的嵌套是常見的場景。
轉換函數定義好了輸入輸出模式，函數實作并不屬于執行架構的内容，執行架構無需關注函數的内部實作，隻需要調用該函數，并且把對應的參數傳入函數，然後獲得輸出結果并傳遞給後續的算子即可。
基于這套機制，使用者可以開發更多自定義的UDF，并且注冊到執行引擎中。開發者在開發UDF的過程中，隻需要關心UDF的格式定義，而無需關注執行引擎内部複雜的實作邏輯。
轉換函數的一個樣例，key列取一位小數輸出：
SELECT round(key,1) FROM table
3.4 聚合函數
聚合函數和轉換函數的不同點在于：聚合函數無論接受多少行輸入資料，輸出資料都隻有一個值，即一行一列；如果是按照視窗聚合（group by某些列），那麼每個視窗内的輸入資料隻會産生一個輸出資料。例如求均值的函數avg，無論輸入的資料有多少行，最終都隻輸出一個均值。另一個不同點在于，轉換函數沒有内部狀态，輸入資料後可以立馬得到輸出結果；而聚合函數，要在記憶體中儲存一個狀态，直到全部資料都輸入結束後，才能拿到最終的結果。例如avg函數，在記憶體中儲存一個sum和一個count這兩個值作為狀态，分别表示輸入資料的求和值以及輸入行數，每輸入一個新的資料，都更新狀态，最終輸出時才把兩者相除，獲得均值。
聚合函數也是一種UDAF(使用者自定義聚合函數)。使用者可以開發自己的UDAF，并且注冊到執行引擎中供調用。
聚合函數的一個樣例，求通路日志的平均延時：
SELECT status，avg(dValue) FROM accesslog group by status
按照status劃分視窗，分别有200和500兩個視窗，每個視窗内的資料分别計算avg這個集合函數，産生一個聚合結果。
聚合函數
3.4.1 選擇性聚合
如果在SQL裡邊隻有一個聚合函數，我們隻期望對部分資料做聚合計算，那麼隻需要把過濾條件放在where中，先過濾出自己想要的資料即可。但是，如果有多個聚合函數呢，每個聚合函數需要的過濾條件不一樣呢？對于count算子，有對應的count_if函數可以附加過濾條件。對于其他的聚合函數，也可以使用case when先過濾出來需要的資料，然後再執行聚合計算，例如avg(case when status=200 then latency end)。不過case when并不是專門用來做過濾的，文法使用起來也不叫複雜，也不是所有聚合函數都滿足這種過濾的語意。除了case when，還有一種專門的選擇性聚合算子，可以對每個聚合函數附加一個過濾條件。具體文法如：
SELECT
key,
AGG1(x) FILTER (WHERE condition1),
AGG2(y) FILTER (WHERE condition2),
AGG3(z) FILTER (WHERE condition3),
...
FROM 每個聚合算子後都跟着一個filter( where bool表達式)，滿足bool表達式的内容才會參與對應的聚合。在同一層的的各個聚合函數，可以指定各自的過濾條件，也可以不指定過濾條件，每個聚合函數對應的過濾條件之間沒有任何關系，可以相同，也可以不同。這就是選擇性聚合，在文法層面給多樣化的聚合提供了友善。
3.4.2 Distinct 聚合
在聚合函數中，所有的輸入都會參與到聚合中。但是有一種場景，先把資料做去重，再做聚合。最常見的使用場景是count(distinct key)，先對key做去重，在計算count值。除了count，其他的聚合函數也可以使用該文法，例如avg(distinct key)，先去重再聚合。
聚合函數内的完整文法是:
aggregate_function(all key)
aggregate_function(distinct key) 第一種文法不做去重，全部資料參與計算。第二種文法先做去重，再做聚合計算。預設是第一種文法，是以all關鍵字不是必須的。
3.4.3 聚合中的Null值
在聚合函數的輸入參數中，如果參數值是null，那麼不參與計算。例如sum(key)，隻統計非null值的和。count(key)隻統計非null的個數。此處有個例外，就是count(*)，因為*并不是具體的列，不存在null或非null的差别，是以所有的行都會統計在内。
如果聚合函數的所有輸入，排除掉null值後，隻有0行有效資料，那麼聚合函數的傳回結果是null，因為沒有任何有效資料參與計算。以sum為例，如果全都是null，或者隻有0行輸入，傳回0或者其他特殊值是不合适的，因為沒有特殊值可以唯一代表這種場景，隻有傳回null才合适。在所有的聚合函數中，除了count之外，都符合這一定義，count 0行輸入的結果是0。
3.5 GROUP BY分組聚合
隻有聚合函數的場景，所有的輸入都聚合成一個結果。如果要把輸入分到多個分組中，每個分組分别生成聚合結果，則需要用group by指定分組。 Group by後跟一列或者多列、或者有某些列經過轉換函數計算後的結果。Group by子句是配合聚合算子使用的。沒有group by的情況下，聚合算子接受所有的輸入資料，産生一個計算結果；有group by的情況，稱為分組聚合，各行資料先按照group by中指定的列或列的轉換結果，計算所屬分組，每個分組内無論有多少行資料，都會計算産生一行聚合結果。圖2-4是一個group by分組聚合的樣例，按照status分組，總共有2個分組，每個分組産生一行聚合結果，即共兩行聚合結果。
Group by的一個樣例，求通路日志中每個站點的平均延時：
SELECT avg(latency), host from accesslog GROUP BY host 在一個分組内，可以執行多個聚合函數，每個聚合函數産生一列聚合結果。即分組的數量決定結果行數，聚合函數的數量決定結果的列數。
在有group by的場景下，select中指定的表達式，除了聚合函數外，還可以select某些列，或者某些列經過轉換函數計算後的結果，這些列是有限制條件的，隻能是group by中出現的列。如果是非group by的列，就會出現一個難以抉擇的問題，因為分組是按照group by的列分組的，每個分組隻輸出一行結果，如果select 非group by的列，那麼在分組中，會有多行資料進入同一分組，在輸出時到底選擇哪一行作為解決呢？這沒有明确的答案。有幾種可能性，第一種是随機的選擇一行；第二種是選擇第一行；第三種是選擇最後一行；第四種是全部輸出。可能性太多，如果使用者不明确的告訴SQL選擇哪一種選項，就會造成誤判，輸出結果不一定滿足使用者預期。每一種選項都會有對應的聚合函數實作。當然在mysql系統中，是按照第一種選項輸出的。
對于需要在分組内産生多行聚合結果的使用場景，可以參考視窗函數。
如果要分組的列是null值，則null值會作為一個單獨的分組。
一般的場景下，一個原始資料隻會在一個分組内參與聚合計算，不會同時出現在多個分組中。但也有一些進階用法就是grouping set操作，在下文詳細介紹。
3.6 Grouping sets操作
上文介紹的group by子句，是比較簡單的一種分組聚合操作。全量的資料，會按照分組條件分到不同的組裡邊，每一行資料，都隻會在一個分組中參與聚合。還有一種更加複雜的分組聚合操作是grouping sets操作。相關關鍵字是grouping sets, cube, rollup。該算子可以允許在一次查詢中，按照不同的分組條件，多次分組。每一條資料，都會按照不同的分組條件多次參與聚合。
例如，如果你希望按照多個分組聚合(grade, class), (grade),(class),如果使用group by，那麼要分别執行三次group by操作。使用grouping sets則可以在一次查詢中完成，文法是select grade,class,count(1) from log group by grouping sets((grade, class), (grade),(class))。在輸出的結果中，grade class兩列都會輸出，但是在後兩個集合中，隻group by了一列，另一列以null出現在結果中。
Rollup文法是一種特殊的grouping sets文法，roll up後跟的集合，會按照層級聚合的方式，枚舉出所有的字首集合。例如group by rollup(grade, class),相當于group by grouping sets ((grade, class),(grade),())。最後一個分組條件是空分組，也就是不分組，相當于沒有group by的場景。
Cube文法也是一種特殊的grouping sets文法，cube和roll up不同之處在于，cube會枚舉所有可能的集合。例如group by cube(grade,class)，相當于group by grouping sets((grade,class),(grade),(class),())。
3.7 視窗函數
轉換函數輸入一行資料，輸出一行資料。聚合函數把多行資料聚合成一行。有沒有一種聚合函數，實作聚合，但是不改變輸入行數呢？答案是視窗函數。
視窗函數在表達結果上類似于轉換函數，針對每一行輸入，都會産生一行輸出，不會改變結果的行數。但在使用上，在視窗函數内部，可以使用聚合計算函數。視窗函數根據某些列分成多個桶，把每一行資料分發到對應的桶中去，然後在每一個桶上執行聚合函數，并且把結果寫回到每一行。是以，相當于視窗函數把聚合函數當成了轉換函數來使用。轉換函數是把一行輸入轉換成一行輸出；視窗函數是把視窗内的若幹行聚合後生成一個結果，但是每一行都會有一個結果。
視窗函數的邏輯如圖2-4所示，有視窗列，排序列，參與聚合的列。在每個視窗内對指定的若幹行進行聚合計算，并且寫入到目前行的結果中。輸出的結果行數和輸入的行數相同。
視窗函數示意圖
視窗函數最簡單的場景，例如：avg(key2) over()，表示把所有資料當成一個分組做avg聚合，并且寫回每條資料中，雖然結果中的每行數字都相同，但是沒有改變結果行數。如下圖中的out3的結果所示，所有行的均值為3，3就是每一行對應的結果。
再複雜一點的視窗函數場景，例如：avg(key2) over(partition by key1)，表示按照key1作為分組，每個分組内分别執行avg聚合計算，并且更新到每個分組的每條資料中。如下圖的out1所示，a這個視窗的均值是1.5，視窗内所有的結果都填充為1.5。b這個視窗内均值是4，視窗内所有的結果都填充成4。
更加複雜一點的視窗函數樣例如：avg(key2) over(partition by key1 order by key2)，表示按照key1作為分組，在每個分組内再按照key2排序，計算視窗内從第一行到目前行為止的資料的avg聚合結果，也就是分組内每一行的結果可能是不一樣的。參考下圖中的out2這個結果，a這個視窗，第一行資料是1，均值就是1；第二行資料是2，第二行對應的視窗均值就是第一行和第二行的均值，也就是1.5。是以結果中，第一行的結果是1，第二行的結果是1.5。這個和out1的對比比較明顯，out1的結果中，每個視窗内的結果都是一樣的。
上邊的樣例還不是最複雜的，前2個樣例，都是在分組内的所有資料上執行聚合；加上order by之後，是聚合從第一行到目前行的資料。那有沒有一種方法，隻聚合目前行附近的幾行呢？能否更加靈活的指定視窗内哪些行參與聚合計算呢？答案是可以的。視窗函數可以指定目前行的前後若幹行參與聚合計算，例如avg(key2) over(partition by key1 order by key2 range between unbounded preceding and current row)，表示從第一行到目前行。range between 1 precedingand 2 following，表示包含前一行、目前行、後兩行總共4行組成的資料進行聚合，更新到目前行的結果。參與聚合的行稱為一個frame，一個frame内的資料聚合生成一個結果。
視窗函數的輸出
在視窗函數中，除了普通的聚合函數，還有一些特殊的、專門用于視窗運算的聚合函數。例如：rank()用于視窗内的排序，輸出排序後的序号，相同的值序号相同，但是相同的值會占用計數值，例如100、102、102、103，輸出序号是1、2、2、4，注意最後一個序号是4。如果期望輸出的需要是去重排序後的序号，則應該用dense_rank函數，針對上述例子，輸出序号為1、2、2、3。此外還有row_number輸出行号。cume_dist排序後從視窗第一行開始的累積百分比，和rank類似，相同的值輸出相同的結果，輸出結果為rank()/total。percent_rank輸出(rank()-1)/total-1)。cume_dist和percent_rank的差别在于，後者從0開始累積。
3.8 運算符和函數
在内部實作和表達效果上中，運算符和函數是相同的。兩者的差別在于文法形式不同，函數有明确的函數名，包含0個或者多個參數的參數清單；運算符則是通過常見的符号來表達意義，例如+-*/等符号。運算符包含1個或者2個參數。雙目運算符包含兩個參數，例如+運算符，需要左右參數。單目運算符包含一個參數，例如-運算符，代表符号的取反操作。運算符需要在文法檔案中顯式定義文法形式。而函數名是不需要定義在文法檔案中的，在文法檔案中隻是一個普通的字元串而已，直到語意檢查階段才需要檢查函數是否存在。
3.9 表達式
表達式是一種有一個或多個函數、運算符、連接配接符組成的一個完整表達式（Expression）。表達式的作用等同于轉換函數，輸入0個或多個字段，輸出一行一列結果。常見的表達式有bool表達式，邏輯表達式，比較表達式，函數調用，lambda表達式等。
3.9.1 比較表達式
比較表達式通過比較運算符>,>=,<,<=,=,<>等連接配接兩個表達式，用于判定兩個表達式的大小關系。左右的表達式不一定是基礎類型，也可能是複雜的表達式，例如函數調用表達式。基礎類型的資料包括integer、bigint等數值類型，也可能是varchar，char等字元串類型。除了上述比較算法，還有between關鍵字，key between x to y，等價于key >=x and key <=y，是一個閉區間。
3.9.2 Bool表達式
bool表達式指的是傳回結果為bool類型的一類表達式。Bool表達式廣泛的應用于各種過濾條件中，例如WHERE，HAVING，ON等。一些轉換函數可以傳回bool類型結果，還有一些比較運算符可以傳回bool結果。例如>,< 等比較運算符。
3.9.3 邏輯表達式
函數可以代表一個簡單的表達式，如果要表達複雜的邏輯，除了函數嵌套函數，還可以用邏輯連結符号組合多個表達式，形成一個複雜的bool表達式。邏輯表達式由邏輯運算符AND、OR、NOT連接配接1個或者2個bool表達式，并且傳回bool結果。其中AND和OR是雙目運算符，NOT是單目運算符。
3.9.4 Lambda表達式
Lambda表達式又稱為是匿名函數，沒有函數名稱，隻有參數清單和計算表達式。Lambda表達式可以用于讓使用者自定義處理邏輯，相當于一種UDF。通常在使用中，lambda表達也可以作為函數的參數傳入函數，然後在函數内調用該lambda表達式疊代處理資料。
一個簡單的lambda表達式如：x -> x + 1，表示接受一個參數x，傳回x+1。
3.10 WHERE子句
Where子句後跟一個bool表達式，表示從表中讀取資料後，會對每一行資料評估該bool表達式的結果。如果表達式評估結果為true，則該行資料就會傳遞後給後續的算子做進一步計算；如果評估結果為false或者位置狀态，則丢棄改行資料，不再參與後續計算。
Bool表達式可以是一個簡單的表達式，例如a=1；也可以是嵌套多層轉換函數組成的bool表達式，例如a%10=1；或者由邏輯運算符連接配接起來的邏輯表達式，例如 a AND b。Bool表達式中的函數都是轉換函數，不能是聚合函數。
Where子句的操作發生在聚合計算之前。Where 子句非常重要，可以幫助減少讀取和計算的資料量，常常用于加速計算。在優化器中，有一些規則幫助把過濾條件盡可能的下推到葉子結點。filter下推是一種非常常用且有效的加速手段。
Where子句的一個樣例，擷取學生中所有的男生資訊：
SELECT * FROM student where gender = ‘male’ 3.11 HAVING子句
Having子句常常跟随group by子句出現。Having子句類似于where，是一個bool表達式。但Having應用于group by聚合計算之後，每個分組的計算結果會用來繼續評估Having表達式的結果，隻有滿足having子句為true的分組，才能輸出到後續的算子。
Having和where的差別在于：1， where在group by之前完成，having 在group by之後執行；2，where應用于每條原始資料上，having應用于group by分組聚合結果上。
理論上而言，即便沒有group by計算，隻有一個全局聚合操作，能夠使用having，但是全局聚合的結果隻有一樣，那麼這個時候having的作用就是判斷這一行結果是否滿足條件。例如select avg(latency) as avg_latency from log having avg_latency > 100
即便沒有group by沒有任何聚合函數，select中隻有原始列或者轉換函數的結果時，也可以用having，但這時候having就沒有意義了，因為having中的條件是可以合并到where中的。例如
select * from log where latency > 10000000 having status>200 完全可以寫成
select * from log where latency > 10000000 and status>200 總而言之，having子句一般和group by語句聯合使用，用于過濾分組聚合後的結果，篩選出分組聚合結果滿足特定條件的某些分組。
Having子句的一個樣例，求通路日志中平均延時大于10秒的站點及其延時：
SELECT avg(latency), host from accesslog GROUP BY host HAVING avg(latency) > 10 having子句的執行發生在group by之後，order by之前。
3.12 Order By子句
Order by子句包含一個或多個表達式，用于排序輸出的結果。在order by中可以指定多個表達式，每個表達式指定排序方式，可以升序，也可以降序，預設是升序排列。排序時多個表達式從左到右依次評估。當左側表達式評估出來的多個行結果一樣時，會評估右側表達式的值用于排序。例如order by key1 asc, key2 desc 表示按照key1升序排列，當key1相同時，按照key2降序排列。
Order by子句的一個樣例，學生按照分數排序
Select * from student order by score asc 3.13 Limit 子句
Limit子句用于限制傳回結果的行數。當之前的算子輸出行數超出了limit指定的行數時，會丢棄超出的部分。由于Limit算子可以減少傳遞給下遊的資料量。因而在優化中非常有用。例如order by和limit算子合并，在排序階段就大大減少用于排序的資料量；把limit算子盡可能向葉子結點的方向下推。通常而言，limit算子會和order by聯合使用。如果單獨使用limit算子，輸出結果不保證順序，也就是說每次執行會獲得不同的結果。和order by聯合使用時，才能保證每次查詢結果的一緻性。
一個查詢樣例
SELECT * FROM student limit 100 表示擷取100個學生資訊。
通常而言，limit限定最多的傳回行數。在MySQL中，還可以通過limit offset,line這個翻頁文法，從第offset行開始，讀取line行結果。而對于OLAP引擎而言，支援翻頁并不現實，因為每次送出翻頁請求，都是要計算一遍SQL，獲得結果後再翻頁傳回，因而代價非常大。除非OLAP引擎把計算結果緩存在記憶體中，等待下次翻頁擷取。MySQL之是以能夠支援翻頁，一方面是因為MySQL的查詢一般是事務性查詢，另一方面資料量比較小，翻頁的代價不大。
3.14 多個表間操作
在一層SQL查詢中，資料源可以是一個表，也可以是多個表。對多個表進行操作并産出一個新的表。表之前的操作包括連接配接操作（join），集合操作（set）。
3.14.1 Join
Join可以把多個表（左右）連接配接成一個表，根據一定的條件讓多個表對應的行輸出到同一行，左表的一行資料和右表的一行資料連接配接成一行，左表和右表的列都會出現在結果中。Join的操作類型包括Inner Join、Left Join、Right Join、Full Join、Cross Join。各種Join的政策參考下圖所示，Inner Join輸出左右兩表的交集，即滿足連接配接條件的行，輸出結果中，左表和右表的列都不為null。Left Join不管左表是否滿足條件都輸出，而右表隻輸出滿足條件的行，其他行以null輸出。Right Join和Left Join相反。Full Join同時輸出左右表，對于滿足條件的行，輸出對應的左右表連接配接後的結果，對于不滿足的行，輸出左表(右表)的行，同時右表(左表)以null輸出，相當于集合了Left Join和Right Join的特性。Cross Join沒有連結條件，輸出兩個表的笛卡爾積。
Join操作是SQL所有算子中，計算複雜度最高的算子之一。針對Join的優化是SQL中一個非常重要的課題，Join的執行方式、執行順序，左右表的大小等因素都會影響Join的性能。在後續章節中，會介紹基于規則的優化器和基于代價的優化器來優化Join算子。
不同的Join類型
3.14.2 Set
Set操作是一種集合操作，集合的元素是行，用于把多個表前後拼接成一個表。拼接後不改變列的個數，原表中的一行，原樣輸出到結果中，參與set操作的左右表的列個數和類型必須保持一緻。set操作和join操作的差别在于，join是左右表列與列按照連接配接條件拼接成一行，set操作是行與行拼接成更多行，不改變原始一行的内容。Set操作包括Union、Intersect、Except。分别代表并集、交集、差集。
集合的理論基礎是集合代數，預設場景下，集合是不包含重複元素的。集合運算符後可以添加distinct或者all關鍵字，分别表示結果去重和不去重。預設是去重的結果。例如table1 union table2，輸出兩個表去重後的結果。
3.15 嵌套查詢
在一個最簡單的查詢中，from語句指定了要從什麼表中讀取資料。在from中，最簡單的情況是指定一個表，從這一個表中讀取資料出來；稍微複雜的情況是from多張表的join結果；再複雜一點，from的來源，根本不是一張表，而是另一個查詢的輸出結果。我們都知道，一個SQL查詢的結果也能成為一個新的表，這個新的表可以作為另一個查詢的輸入。這就是關系模型的優秀之處，任何關系經過計算後，形成第二個關系，再經過第二次計算，則形成了第三個關系。理論上，表活着關系可以經過無數輪計算，組成一個單向流動的連結清單。這就是嵌套查詢。嵌套查詢的結果，可以像一張普通的表一樣，參與下遊的計算、join、union等。
在SQL中，寫嵌套查詢有兩種形式，第一種，最直覺的就是from 後寫一個子查詢，并且把子查詢用()包含起來，形成一個完整的整體，例如:
select abc from ( select abc from table） ()内部的即為一個完整的子查詢。
第二種是with文法：
with temp_table1 as (select abc from table),
temp_table2 as (select abc from temp_table1)
select * from temp_table2 通過with文法，可以定義多個視圖，視圖用括号左右包含起來。多個臨時表之間，用逗号分隔。with語句的最後不需要加逗号，直接跟select語句。
with語句比較簡潔，可以做到每一行隻定義一個視圖，視圖的定義和使用可以分開到不同的地方，在使用時隻需要引用視圖的表名即可。定義一次視圖甚至可以多次引用。而嵌套式查詢，臨時表的定義和使用放在一起，每使用一次就需要定義一次。外層的查詢語句内部是一個子查詢，from關鍵字在整個SQL語句的中間位置，導緻外層查詢的前半部分在子查詢前邊，後半部分在子查詢後邊，同一層查詢語意被一個複雜的字查詢分隔開，不利于對SQL語意了解。是以在使用前套查詢時，推薦使用with文法。
with查詢中定義一個視圖，在後續可以引用多次該視圖。視圖并不是物化視圖，如果引用多次視圖，會展開執行多次。
3.16 子查詢表達式
子查詢除了作為一種關系在from語句中被引用。還有一種用途是作為表達式被引用，例如where語句中的引用子查詢結果作為一個集合，判斷某個值和這個集合的關系。子查詢表達式和嵌套查詢的差別在于，子查詢表達式在plan中扮演一個表達式，而嵌套查詢扮演一個視圖。在子查詢中，可以引用外層查詢的屬性，而外層查詢中，不能引用子查詢的屬性。
除了from後，嵌套子查詢可以出現在SQL的幾乎每個位置。
- 出現在select輸出結果中，select (select 1) as one from student。
- 出現在where中，select name from student where id in (select id from applied)。
對于判斷外層查詢屬性和内層子查詢結果之間關系的判定方式，有幾種方式：
- ALL 表示外層表達式要滿足子查詢的所有結果。
- ANY表示外層表達式需要滿足子查詢的至少一個結果。
- IN 等同于ANY。
- EXISTS表示至少有一行結果傳回。
按照輸出結果，子查詢包括三種類型：
- 标量子查詢（scalar subquery）：隻傳回一行一列結果。
- 多行輸出子查詢：輸出多行一列，或多行多列。
- exists子查詢：輸出結果是bool類型。
按是否引用外層查詢的屬性，分為：
- 關聯子查詢：子查詢中引用到了外層查詢的屬性。
- 無關聯子查詢：子查詢沒有引用外層查詢的屬性。
3.17 标量子查詢表達式
标量子查詢的結果隻有一行一列一個值。針對這個特性，可以有很多優化手段。在後續的優化器章節會給出介紹。理論上來說，對于外層查詢的每一行資料，都需要去執行一次子查詢表達式。但是這裡還有些不同點，對于相關子查詢和不相關子查詢的處理是不同的。對于不相關子查詢，子查詢沒有引用外部的任何列，是以對于外部的每一行資料，子查詢的執行結果都是相同的，是以執行一次即可。這種場景下，子查詢隻會執行一次。
标量子查詢可以用于case表達式、select子句、where子句、order by子句、函數的參數等。由于标量子查詢隻傳回一行一列，是以可以當成單個值使用。
scalar 子查詢在被使用之處，隻能要求出現一個結果，但并未在文法上限制子查詢傳回一個結果。使用者可以寫一個聚合子查詢隻傳回一個結果，或者用limit 1限定傳回一個結果；也可以寫一個可能傳回多行資料的SQL，隻有在執行時，如果實際傳回多行結果則會報錯。
例如select count(1) from log where latency >= (select avg(latency) from log)，子查詢中時聚合函數，一定會傳回一行結果，因而可以正常執行。但加入使用者寫這樣一個子查詢select count(1) from log where latency >= (select (latency) from log)，則存在三種可能，傳回0行結果，傳回1行結果，傳回大于1行結果。如果傳回0行結果，則以null作為子查詢的輸出，如果傳回大于1行結果，則運作報錯。因為标量子查詢的外層需要一行一列輸入。或者說，标量子查詢之是以稱為是标量子查詢，是因為外層查詢要求子查詢輸出一行一列，而不是子查詢本身通過文法或者實際運作隻能得到一行一列結果。
除了where中，還可以在select中，例如select *, (select max(latency) from log )from log，在每一行都輸出最大的latency值。如果寫成select *, (select latency from log )from log則會報錯。
也可以作為函數參數:select *, abs((select max(latency) from log) )from log。基本上，在需要單個值的地方就可以使用标量子查詢。
3.17.1 子查詢用于判斷集合從屬關系
in和not in用于判定外層查詢的屬性是否屬于内層子查詢結果的集合内。例如：
select * from course where student_id in (select student_id from student where apply_year='2018') in和not in除了用于子查詢，還可以指定一個list常量，例如：
select * from course where student_id in（1，2，3） 3.17.2 Exists子查詢用于判定是否是空集合
Exists子查詢檢查子查詢是否有輸出結果，如果有至少一行結果，則判定為true，否則判定為false。通常Exists子查詢被用于關聯子查詢，也就是說針對外層查詢的每一行資料，判定Exists子查詢的結果。 如果是非關聯子查詢，則對于外層查詢的每一行資料，Exists的結果都是一行的結果，這樣做沒有意義。
例如，
SELECT name FROM websites WHERE EXISTS ( select count from access_log WHERE websites.id = access_log.site_id and count > 100) 表示輸出通路日志中count > 100的網站的名字。
not exists是相反的語意，表示子查詢的結果為空集合。
Exists查詢也可以用in文法來表達，in文法表示判斷某一列的每一行是否在子查詢的輸出結果中。例如上述的邏輯，可以用in文法來表達：SELECT name FROM websites WHERE id in ( SELECT site_id from access_log where count > 100)。顯然，在in查詢中，子查詢是不相關查詢，是以，子查詢隻需要執行一次即可，因而查詢效率較高。
3.17.3 子查詢用于比較級和數值大小關系
外層查詢可以和子查詢的結果進行對比，對比的運算符包括<,>, <=, >=, =, <>。子查詢的結果可以包含修飾符SOME，ANY，ALL。外層表的每一行會逐個和子查詢結果的每一行進行對比，傳回true或者false。如果是SOME或者ANY修飾符，那麼隻需要至少1行對比為true即可。如果是ALL修飾符，那麼就需要所有的行對比結果都為true才行。=ANY的語義和IN相同。<>ALL的意義和NOT IN相同。
一個樣例：
SELECT Num FROM Test2 WHERE Num > ANY (SELECT Num FROM Test1) 表示Test2這張表中的Num，如果在Test1表中存在比之小的值，則該行資料滿足條件，會輸出到下遊算子中。
量化子查詢會在優化器章節進行深入的介紹其優化方法。
3.17.4 子查詢用于判定集合是否包含重複值
和exists類似，還有一個unique關鍵字，用于判斷子查詢的所有行是否包含重複值，如果包含重複值，那麼傳回false；如果不包含重複值，則傳回true。
例如：
select * from log where unique (select projectName from log) 3.17.5 子查詢的實際運作方式
一般來說，上述幾種子查詢，如果是非關聯子查詢，每一行判定結果都一樣，意義不是很大。是以，通常上邊的這些子查詢都會是關聯子查詢，這樣才會每一行結果不一樣。而關聯子查詢一般會在plan優化階段，轉化為join計算。
子查詢是一種文法表示形式，在物化plan中，是沒有子查詢這種執行方式的，一般需要需要轉化為等價的關系代數表達形式。
除了正常的幾種join（left，right，full，cross），還有兩種特殊的join形式，semijon和antijoin。semijoin用于in或exists查詢，表示隻要滿足條件，就輸出左表的資料，每行資料隻輸出一次。antijoin用于not in或not exists，表示隻要不滿足條件，就輸出左表的資料，每行資料隻輸出一次。雖然semejoin和antijoin有等價的表示形式，但是這兩種特化的表達形式可以獲得更好的執行性能。
3.18 Null 處理
對于正常的資料處理是很簡單的，但是往往有一些非法的case需要處理。null就是一個典型的場景。一個非法值，或者不知道具體值的值就用null表示。
在聚合函數中，輸入null值的處理在上文已經描述過了。在這個章節中，主要考慮轉換函數輸入null的情況。
對于一個轉換函數或者轉換表達式，如果傳回值是非boolean的情況，例如代數運算，如果輸入是null，那麼輸出也是null。
如果轉換函數或者轉換表達式傳回值是boolean的情況，例如一個比較表達式，正常情況輸出隻有true、false兩種場景，如果輸入的一個參數是null，無法明确判定是true還是false的情況下，則需要第三種狀态，即unkonwn狀态用于判斷。為什麼簡單粗暴的輸出null呢？這是因為，unknown代表的資訊量要大于null。在後續的計算中，即便存在unkonwn狀态，也能過推斷出結果。
針對and、or、not邏輯表達式，當出現unkonwn時，甚至可以借助短路求值的思想，獲得最終結果，無需關心unknown到底是true還是false。
AND： 如果是true and unknown，結果取決于unkonwn，那麼結果就是unkonwn；如果是false and unkonwn，無論unkonwn是true還是false，結果都是false。
OR：如果是true or unkonwn，無論unknown是true還是false，結果都是true；如果是false or unknown，結果取決于unknown，結果仍為unknown。
NOT： not unknown，結果仍是unknown。
Is null文法和is not null文法：is null可以判斷一個表達式是否是null，is not null正好相反。同時在SQL标準中，還有is unknown文法和is not unknown文法，不過這兩個關于unknown的文法并不是所有的SQL引擎都支援。
在用于分組操作時，例如group by，distinct，union等， 如果指定的列中包含null，那麼所有對應null的行都會作為一個分組。這一點和計算表達式中的表現是不同的，例如判斷null=null，那麼輸出将是unknown，而不是true。
3.19 Unnest文法
在SQL中，生成新的資料依賴于表達式或者函數，在上文中提到，函數分成兩種類型，分别是标量轉換函數，另一種是聚合計算函數。标量計算函數把一行輸入資料轉換成一行一列輸出結果；聚合計算函數把多行輸入資料轉換成一行一列輸出結果。如果我們要輸出一列轉換成多列，那麼可以通過多個表達式實作。如果我們需要把一行轉化成多行，該怎麼處理呢？在這種需求場景下，就用到了Unnest文法。
Unnest文法可以把一行資料轉換成多行資料。例如輸入資料是一個數組類型，那麼可以把數組中的每一個元素作為一行結果輸出。文法如：
SELECT element FROM (VALUES ( ARRAY[1,2,3]) ) as t(element)。輸出結果為3行，分别是1、2、3.。
3.20 其他SQL文法
除了SELECT文法，還有其他的文法例如INSERT/CREATE 等DDL語句。
小結
本文介紹了SQL和查詢相關的一些核心文法規則，有助于讀者了解SQL能夠完成哪些方面的計算。
文章來源：https://developer.aliyun.com/article/1142817