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是資料分析的事實標準。資料模型（DataModel） 用於描述資料在資料庫中的組織形式。常見的模型有關係模型（Relational），鍵值模型（Key/Value），圖模型（Graph），文件模型（Document），列簇模型（Column-family）等。關係型資料庫採用關係模型。Redis採用鍵值模型。圖資料庫採用圖模型。MongolDB採用文件模型。關係模型中的關係有點繞口，在英文中是Relational，硬翻譯成了關係，我的理解，關係指的是一些相互之間有關係的屬性組成的一個實體，因為各個列屬性之間存在關聯關係，而被稱為一個關係，其實指的是屬性之間的相關性，這種相關性體現在：屬於同一行；滿足列之間的約束條件；滿足行之間的約束條件；滿足不同關係之間的約束條件。透過不同的約束條件，是全部的資料形成一種有組織的存在。資料庫透過關係模型，定義出一個個關係實體，確保內容之間滿足一定的約束標間，並且提供程式設計介面去讀寫資料庫內容。一個資料庫包含一堆關係，每個關係是一個多行多列的表格。每一行的各個列之間是相關的，也可能會定義一些約束條件。行與行之間，也可能透過定義唯一鍵（Primary Key），定義排序方式來約束行之間的關係。關係與關係之間，可以透過外部鍵來實現。這種列之間和行之間的約束關係，在OLTP場景中比較實用，因為OLTP關注的資料本身，因此在儲存資料時，更多關注資料的儲存形式。而OLAP關注的資料的分析，所以在數倉中，這些約束條件是弱化的，因此，在數倉中，我們只需關注一張多行多列的表格即可，像PK、排序這類約束屬性，更多隻是用來做資料加速的手段。關係模型用來作為一種嚴密的理論，給執行器的最佳化提供理論基礎。但是這個名字畢竟太繞口，在後續文章中，除非涉及到關係模型相關的理論，會使用關係這個詞，一般情況下，會用表來指代一個關係。關係模型和關係代數是SQL的理論基礎。代數不僅是我們所熟知的簡單的加減乘除等數學計算。在計算機行業，我們見到過多種algebra，在神經網路中常用的線性代數（linear algebra），在電路中用到的布林代數（boolean algebra），夏農把布林代數帶入到了邏輯電路設計中，為計算機二進位制計算提供了理論依據。此外還有N多種algebra，這裡不一一列舉。關係代數，源自於集合代數，講述集合之間的變換關係。關係代數中的一系列操作，接受一個或兩個關係作為輸入，產生一個新的關係作為結果。由於輸入和輸出都是一個關係，我們可以串聯多個運算元，形成更加複雜的運算元。關係代數中包含的運算元有：σ (select，從一個關係中篩選出部分行，形成一個新的關係),Π(projection，從一個關係中篩選出部分列，形成一個新的關係),∪(Union，合併兩個關係), ∩(Intersection，取兩個關係的交集部分), –(difference，取兩個關係的差集部分), ×(Product，兩個關係的笛卡爾積),⋈(Join，兩個關係在滿足某些條件下的連線),ρ（Rename，重新命名關係中的列）, ←(Assignments，把一個臨時的查詢命名成一個新的關係), δ(Duplicate Eliminating，去重), γ(Aggregation，對部分列做聚合計算，結果形成一個新關係), τ(Sorting，排序結果形成一個新關係)。這裡定義了常用的關係操作，名字已經表示出了其操作的含義，在這裡不再介紹每個操作的明細了。在語法解析和最佳化器階段我們會再次接觸到關係代數，並且藉助於關係代數的理論依據，來做一些語法樹上的轉換。在這裡我們只需要知道在關係代數上有這些操作，並且在之後的SQL語法上看到如何用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標準。Data Manipulation Language（DML）：資料操作語言，用於增刪改查資料。Data Definition Language（DDL）：資料定義語言，用於定義表的格式。Data Control Language（DCL）:資料控制語言，用於控制許可權等。雖然DML和DCL是SQL系統的基礎功能，本文的關注重點更多是資料處理的技術，以及如何加快資料處理的技術，因此更多關注DDL。在DDL中，也有增刪改查，在這幾項中，本文更多關注查的部分內容，即如何加快資料的讀取和計算。而資料的寫入、儲存部分的最佳化手段，也是為了滿足加速資料計算的目的。SQL全稱Structed Query Language(結構化查詢語言)。SQL語法簡單，易學易用，是資料分析領域最通用的語言。SQL是資料分析的操作工具，對於使用者而言SQL代表浙使用者的操作語義，但是對於程式而言，只是接收到一串字串。程式需要理解SQL的意義，要經過詞法分析、語法分析、語義分析、構造成抽象語法樹。詞法分析、語法分析是非常基礎的操作。大學的計算機的編譯原理課程應該包含了本部分內容，詞法分析和語法分析的模式是固定的，玩不出花樣，無助於提升計算速度。不過作為OLAP引擎中必不可少的第一環，還是有必要對詞法分析和語法分析做出簡單的介紹，有助於瞭解後續章節中的查詢計劃和最佳化器，但是本章不會佔用太多篇幅，本文的重點是關於計算速度的內容。開發者也可以研發自定義的分析語言，只要語言符合一定的規則，沒有歧義，在語義上完整，也能過稱為一種語言。不過開發一個新的語言非常困難，大多數的新語言採用程式式程式設計，每一個短語表示一個簡單的操作；或者採用管道式宣告語法，每一部分代表輸入，計算和輸出，但是要定義一種能夠無限擴充套件而沒有歧義的語法是很難的。在語義完整程度上是不能和SQL相比較的。無論是開發一門新的語言，還是採用SQL，流程都和下圖類似。OLAP引擎解析SQL，生成抽象語法樹，再轉化成邏輯執行計劃，經過最佳化後，生成高效能的運算元組合。這就是編譯和最佳化的過程。
在瞭解編譯之前，我們首先了解一下SQL的結構定義。SQL是圍繞著關係進行的。可以在關係上定義各種操作，也可以定義多個關係的操作。SQL操作的物件是結構化資料。SQL語法的基礎語法以及巢狀擴充套件，都是圍繞著“關係”進行的。“關係”可以想象成資料庫中的表，由多行多列組成。一個SQL，接受一個或多個“關係”的輸入，並輸出一個“關係”。在巢狀查詢時，內部查詢輸出一箇中間“關係”，並作為外層查詢的輸入“關係”，類似於Linux命令列中的管道語法。在下文中，用“表”來表示“關係”。在一個表上，可以進行過濾(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的語法可以無限的巢狀。對於巢狀查詢，除了用括號把子查詢包含起來作為子表，另一種做法是用with語句定義子查詢。下文予以詳細介紹。最簡單的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聚合計算只輸出一行結果，所以是不能在同級別混用的。如上文所說，轉換函式對每一行輸入，經過計算後，輸出對應一行的結果，即，轉換函式不會改變輸入資料的行數。scalar function的scalar就代表是對原有資料的線性伸縮，不改變原資料的維度空間。轉換函式的輸入引數可以是0個或者多個；輸出只有1個，即無論輸入多少列引數，輸出只有一列。如果希望輸出多列，則需要把輸出結果整合到一個複雜型別裡，例如陣列array或者字典map，再透過巢狀查詢展開結果。由於轉換函式不改變結果的行數，因此可以無限巢狀呼叫轉換函式，例如fun1(fun2(fun3(fun4(fun5(key)))))，儘管大多數情況下無限層次的巢狀並不是必要的，一到兩層的巢狀是常見的場景。轉換函式定義好了輸入輸出模式，函式實現並不屬於執行框架的內容，執行框架無需關注函式的內部實現，只需要呼叫該函式，並且把對應的引數傳入函式，然後獲得輸出結果並傳遞給後續的運算元即可。基於這套機制，使用者可以開發更多自定義的UDF，並且註冊到執行引擎中。開發者在開發UDF的過程中，只需要關心UDF的格式定義，而無需關注執行引擎內部複雜的實現邏輯。SELECT round(key,1) FROM table 
聚合函式和轉換函式的不同點在於：聚合函式無論接受多少行輸入資料，輸出資料都只有一個值，即一行一列；如果是按照視窗聚合（group by某些列），那麼每個視窗內的輸入資料只會產生一個輸出資料。例如求均值的函式avg，無論輸入的資料有多少行，最終都只輸出一個均值。另一個不同點在於，轉換函式沒有內部狀態，輸入資料後可以立馬得到輸出結果；而聚合函式，要在記憶體中儲存一個狀態，直到全部資料都輸入結束後，才能拿到最終的結果。例如avg函式，在記憶體中儲存一個sum和一個count這兩個值作為狀態，分別表示輸入資料的求和值以及輸入行數，每輸入一個新的資料，都更新狀態，最終輸出時才把兩者相除，獲得均值。聚合函式也是一種UDAF(使用者自定義聚合函式)。使用者可以開發自己的UDAF，並且註冊到執行引擎中供呼叫。SELECT status，avg(dValue) FROM accesslog group by status按照status劃分視窗，分別有200和500兩個視窗，每個視窗內的資料分別計算avg這個集合函式，產生一個聚合結果。
選擇性聚合
如果在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表示式的內容才會參與對應的聚合。在同一層的的各個聚合函式，可以指定各自的過濾條件，也可以不指定過濾條件，每個聚合函式對應的過濾條件之間沒有任何關係，可以相同，也可以不同。這就是選擇性聚合，在語法層面給多樣化的聚合提供了方便。Distinct 聚合
在聚合函式中，所有的輸入都會參與到聚合中。但是有一種場景，先把資料做去重，再做聚合。最常見的使用場景是count(distinct key)，先對key做去重，在計算count值。除了count，其他的聚合函式也可以使用該語法，例如avg(distinct key)，先去重再聚合。aggregate_function(all key)aggregate_function(distinct key)
第一種語法不做去重，全部資料參與計算。第二種語法先做去重，再做聚合計算。預設是第一種語法，因此all關鍵字不是必須的。聚合中的Null值
在聚合函式的輸入引數中，如果引數值是null，那麼不參與計算。例如sum(key)，只統計非null值的和。count(key)只統計非null的個數。此處有個例外，就是count(*)，因為*並不是具體的列，不存在null或非null的差別，因此所有的行都會統計在內。如果聚合函式的所有輸入，排除掉null值後，只有0行有效資料，那麼聚合函式的返回結果是null，因為沒有任何有效資料參與計算。以sum為例，如果全都是null，或者只有0行輸入，返回0或者其他特殊值是不合適的，因為沒有特殊值可以唯一代表這種場景，只有返回null才合適。在所有的聚合函式中，除了count之外，都符合這一定義，count 0行輸入的結果是0。只有聚合函式的場景，所有的輸入都聚合成一個結果。如果要把輸入分到多個分組中，每個分組分別生成聚合結果，則需要用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操作，在下文詳細介紹。上文介紹的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),())。轉換函式輸入一行資料，輸出一行資料。聚合函式把多行資料聚合成一行。有沒有一種聚合函式，實現聚合，但是不改變輸入行數呢？答案是視窗函式。視窗函式在表達結果上類似於轉換函式，針對每一行輸入，都會產生一行輸出，不會改變結果的行數。但在使用上，在視窗函式內部，可以使用聚合計算函式。視窗函式根據某些列分成多個桶，把每一行資料分發到對應的桶中去，然後在每一個桶上執行聚合函式，並且把結果寫回到每一行。因此，相當於視窗函式把聚合函式當成了轉換函式來使用。轉換函式是把一行輸入轉換成一行輸出；視窗函式是把視窗內的若干行聚合後生成一個結果，但是每一行都會有一個結果。視窗函式的邏輯如圖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內的資料聚合生成一個結果。 
圖2-6視窗函式的輸出
在視窗函式中，除了普通的聚合函式，還有一些特殊的、專門用於視窗運算的聚合函式。例如：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開始累積。在內部實現和表達效果上中，運算子和函式是相同的。兩者的區別在於語法形式不同，函式有明確的函式名，包含0個或者多個引數的引數列表；運算子則是透過常見的符號來表達意義，例如+-*/等符號。運算子包含1個或者2個引數。雙目運算子包含兩個引數，例如+運算子，需要左右引數。單目運算子包含一個引數，例如-運算子，代表符號的取反操作。運算子需要在語法檔案中顯式定義語法形式。而函式名是不需要定義在語法檔案中的，在語法檔案中只是一個普通的字串而已，直到語意檢查階段才需要檢查函式是否存在。表示式是一種有一個或多個函式、運算子、連線符組成的一個完整表示式（Expression）。表示式的作用等同於轉換函式，輸入0個或多個欄位，輸出一行一列結果。常見的表示式有bool表示式，邏輯表示式，比較表示式，函式呼叫，lambda表示式等。比較表示式
比較表示式透過比較運算子>,>=,<,<=,=,<>等連線兩個表示式，用於判定兩個表示式的大小關係。左右的表示式不一定是基礎型別，也可能是複雜的表示式，例如函式呼叫表示式。基礎型別的資料包括integer、bigint等數值型別，也可能是varchar，char等字串型別。除了上述比較演算法，還有between關鍵字，key between x to y，等價於key >=x and key <=y，是一個閉區間。Bool表示式
bool表示式指的是返回結果為bool型別的一類表示式。Bool表示式廣泛的應用於各種過濾條件中，例如WHERE，HAVING，ON等。一些轉換函式可以返回bool型別結果，還有一些比較運算子可以返回bool結果。例如>,< 等比較運算子。 邏輯表示式
函式可以代表一個簡單的表示式，如果要表達複雜的邏輯，除了函式巢狀函式，還可以用邏輯連結符號組合多個表示式，形成一個複雜的bool表示式。邏輯表示式由邏輯運算子AND、OR、NOT連線1個或者2個bool表示式，並且返回bool結果。其中AND和OR是雙目運算子，NOT是單目運算子。Lambda表示式
Lambda表示式又稱為是匿名函式，沒有函式名稱，只有引數列表和計算表示式。Lambda表示式可以用於讓使用者自定義處理邏輯，相當於一種UDF。通常在使用中，lambda表達也可以作為函式的引數傳入函式，然後在函式內呼叫該lambda表示式迭代處理資料。一個簡單的lambda表示式如：x -> x + 1，表示接受一個引數x，返回x+1。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’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) > 10having子句的執行發生在group by之後，order by之前。順序參考圖2-2。Order by子句包含一個或多個表示式，用於排序輸出的結果。在order by中可以指定多個表示式，每個表示式指定排序方式，可以升序，也可以降序，預設是升序排列。排序時多個表示式從左到右依次評估。當左側表示式評估出來的多個行結果一樣時，會評估右側表示式的值用於排序。例如order by key1 asc, key2 desc 表示按照key1升序排列，當key1相同時，按照key2降序排列。Order by子句的一個樣例，學生按照分數排序：Select * from student order by score ascLimit子句用於限制返回結果的行數。當之前的運算元輸出行數超出了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的查詢一般是事務性查詢，另一方面資料量比較小，翻頁的代價不大。在一層SQL查詢中，資料來源可以是一個表，也可以是多個表。對多個表進行操作併產出一個新的表。表之前的操作包括連線操作（join），集合操作（set）。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運算元。
Set操作是一種集合操作，集合的元素是行，用於把多個表前後拼接成一個表。拼接後不改變列的個數，原表中的一行，原樣輸出到結果中，參與set操作的左右表的列個數和型別必須保持一致。set操作和join操作的差別在於，join是左右表列與列按照連線條件拼接成一行，set操作是行與行拼接成更多行，不改變原始一行的內容。Set操作包括Union、Intersect、Except。分別代表並集、交集、差集。集合的理論基礎是集合代數，預設場景下，集合是不包含重複元素的。集合運算子後可以新增distinct或者all關鍵字，分別表示結果去重和不去重。預設是去重的結果。例如table1 union table2，輸出兩個表去重後的結果。在一個最簡單的查詢中，from語句指定了要從什麼表中讀取資料。在from中，最簡單的情況是指定一個表，從這一個表中讀取資料出來；稍微複雜的情況是from多張表的join結果；再複雜一點，from的來源，根本不是一張表，而是另一個查詢的輸出結果。我們都知道，一個SQL查詢的結果也能成為一個新的表，這個新的表可以作為另一個查詢的輸入。這就是關係模型的優秀之處，任何關係經過計算後，形成第二個關係，再經過第二次計算，則形成了第三個關係。理論上，表活著關係可以經過無數輪計算，組成一個單向流動的連結串列。這就是巢狀查詢。巢狀查詢的結果，可以像一張普通的表一樣，參與下游的計算、join、union等。在SQL中，寫巢狀查詢有兩種形式，第一種，最直觀的就是from 後寫一個子查詢，並且把子查詢用()包含起來，形成一個完整的整體，例如:select abc from ( select abc from table）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查詢中定義一個檢視，在後續可以引用多次該檢視。檢視並不是物化檢視，如果引用多次檢視，會展開執行多次。子查詢除了作為一種關係在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型別。
標量子查詢的結果只有一行一列一個值。針對這個特性，可以有很多最佳化手段。在後續的最佳化器章節會給出介紹。理論上來說，對於外層查詢的每一行資料，都需要去執行一次子查詢表示式。但是這裡還有些不同點，對於相關子查詢和不相關子查詢的處理是不同的。對於不相關子查詢，子查詢沒有引用外部的任何列，因此對於外部的每一行資料，子查詢的執行結果都是相同的，因此執行一次即可。這種場景下，子查詢只會執行一次。標量子查詢可以用於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。基本上，在需要單個值的地方就可以使用標量子查詢。子查詢用於判斷集合從屬關係
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）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查詢中，子查詢是不相關查詢，因此，子查詢只需要執行一次即可，因而查詢效率較高。子查詢用於比較級和數值大小關係
外層查詢可以和子查詢的結果進行對比，對比的運算子包括<,>, <=, >=, =, <>。子查詢的結果可以包含修飾符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表中存在比之小的值，則該行資料滿足條件，會輸出到下游運算元中。量化子查詢會在最佳化器章節進行深入的介紹其最佳化方法。子查詢用於判定集合是否包含重複值
和exists類似，還有一個unique關鍵字，用於判斷子查詢的所有行是否包含重複值，如果包含重複值，那麼返回false；如果不包含重複值，則返回true。select * from log where unique (select projectName from log)子查詢的實際執行方式
一般來說，上述幾種子查詢，如果是非關聯子查詢，每一行判定結果都一樣，意義不是很大。所以，通常上邊的這些子查詢都會是關聯子查詢，這樣才會每一行結果不一樣。而關聯子查詢一般會在plan最佳化階段，轉化為join計算。子查詢是一種語法表示形式，在物化plan中，是沒有子查詢這種執行方式的，一般需要需要轉化為等價的關係代數表達形式。除了常規的幾種join（left，right，full，cross），還有兩種特殊的join形式，semijon和antijoin。semijoin用於in或exists查詢，表示只要滿足條件，就輸出左表的資料，每行資料只輸出一次。antijoin用於not in或not exists，表示只要不滿足條件，就輸出左表的資料，每行資料只輸出一次。雖然semejoin和antijoin有等價的表示形式，但是這兩種特化的表達形式可以獲得更好的執行效能。對於常規的資料處理是很簡單的，但是往往有一些非法的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。在SQL中，生成新的資料依賴於表示式或者函式，在上文中提到，函式分成兩種型別，分別是標量轉換函式，另一種是聚合計算函式。標量計算函式把一行輸入資料轉換成一行一列輸出結果；聚合計算函式把多行輸入資料轉換成一行一列輸出結果。如果我們要輸出一列轉換成多列，那麼可以透過多個表示式實現。如果我們需要把一行轉化成多行，該怎麼處理呢？在這種需求場景下，就用到了Unnest語法。Unnest語法可以把一行資料轉換成多行資料。例如輸入資料是一個陣列型別，那麼可以把陣列中的每一個元素作為一行結果輸出。語法如：SELECT element FROM (VALUES ( ARRAY[1,2,3]) ) as t(element)。輸出結果為3行，分別是1、2、3.。除了SELECT語法，還有其他的語法例如INSERT/CREATE 等DDL語句。本文介紹了SQL和查詢相關的一些核心語法規則，有助於讀者瞭解SQL能夠完成哪些方面的計算。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024922/viewspace-2933588/，如需轉載，請註明出處，否則將追究法律責任。