SQL 由 IBM 于上世紀 70 年代創建，如今已經成為了使用最廣泛的數據庫查詢語言。不過，相信很多人對于 SQL 的理解就是關系數據庫，就是增刪改查；實際上，SQL 在經歷了四十而不惑之后就像“姐姐們”一樣成熟而有魅力，同時它又敢于在不斷變化的產業需求和各種非關系模型的沖擊之下實現自我突破。因此，本文就給大家介紹一下最近幾年 SQL 如何在各個領域乘風破浪！
如果你認為 SQL 就是簡單的增刪改查（INSERT、SELECT、UPDATE、DELETE），那么你了解的僅僅是 1992 年的SQL。
如果你了解通用表表達式（CTE）和遞歸查詢、用戶定義類型或者 OLAP 功能，那么你使用的是 1999 年的 SQL。
如果你接觸過窗口函數（分析函數）、MERGE（UPSERT）語句或者 XML 數據類型，應該知道這些不過是 2003 年的 SQL。
2006 年的 SQL 已經定義了 SQL 操作 XML 的規范，支持使用 XQuery 同時訪問 SQL 數據和 XML 文檔。2008 年又增加了 TRUNCATE TABLE 語句、INSTEAD OF 觸發器以及 FETCH 子句等功能。
2011 年 SQL 最主要的新功能之一就是增強了對時態數據庫（Temporal database）的支持，可以用于記錄那些隨著時間而變化的歷史數據值，應用領域包括金融、保險、預訂系統、醫療信息管理系統等。目前，MariaDB、Oracle、PostgreSQL、Microsoft SQL Server 在一定程度上實現了某些時態表功能，國內的騰訊 TDSQL 是一個全時態數據庫系統。
時間來到了 2016 年，SQL 標準又增加了幾個重要的功能，首先就是對 JSON 文檔的支持。
一.SQL 與文檔數據庫
文檔數據庫屬于 NoSQL 的一種，具有模式自由的存儲特性，通常采用 JSON 格式進程數據的存儲。常用的文檔數據庫包括 MongoDB、CouchDB 等。
實際上，2016 年 SQL 標準就已經增加了 JSON 功能的支持，包括：
- JSON 對象的存儲與檢索；
- 將 JSON 對象表示成 SQL 數據；
- 將 SQL 數據表示成 JSON 對象。
這些功能可以表示為以下示意圖：
如今，主流的關系數據庫也都增加了原生 JSON 數據類型和相關函數的支持，包括 Oracle、MySQL、SQL Server、PostgreSQL 等。
我們以 MySQL 為例，演示一下如何使用 SQL 查詢 JSON 數據。
select emp_id,
emp_info,
emp_info->'$.emp_name' emp_name,
emp_info->'$.sex' sex,
emp_info->>'$.income[0].salary' salary
from employee_json
limit 3;
emp_id|emp_info|emp_name|sex|salary |
------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------|---|-------|
1|{"sex": "男", "email": "liubei@shuguo.com", "income": [{"salary": 33000.0}, {"bonus": 10000}], "job_id": 1, "dept_id": 1, "manager": null, "emp_name": "劉備", "hire_date": "2000-01-01"}|"劉備" |"男"|33000.0|
2|{"sex": "男", "email": "guanyu@shuguo.com", "income": [{"salary": 26000}, {"bonus": 10000}], "job_id": 2, "dept_id": 1, "manager": 1, "emp_name": "關羽", "hire_date": "2000-01-01"} |"關羽" |"男"|26000 |
3|{"sex": "男", "email": "zhangfei@shuguo.com", "income": [{"salary": 24000}, {"bonus": 10000}], "job_id": 2, "dept_id": 1, "manager": 1, "emp_name": "張飛", "hire_date": "2000-01-01"} |"張飛" |"男"|24000 |
其中，emp_info 字段類型為 JSON；-> 操作符返回的類型是 JSON，->> 返回的類型是字符串，使用 SQL/JSON 路徑表達式獲取數據中的元素值；$ 代表整個文檔；$.emp_name 表示獲取 JSON 對象的 emp_name 元素；$.income[0].salary 表示獲取 income 數組中的第一個對象的 salary 元素，數組的下標從 0 開始。
除此之外，我們也可以將 JSON 數據轉換為關系型數據。例如：
select emp_id, jt.*
from employee_json,
json_table(emp_info, '$'
columns (emp_name varchar(50) path '$.emp_name',
sex varchar(10) path '$.sex',
dept_id integer path '$.dept_id',
manager integer path '$.manager',
hire_date date path '$.hire_date',
job_id integer path '$.job_id',
salary integer path '$.income[0].salary',
bonus integer path '$.income[1].bonus',
email varchar(100) path '$.email')
) jt
limit 3;
emp_id|emp_name |sex|dept_id|manager|hire_date |job_id|salary|bonus|email |
------|----------|---|-------|-------|----------|------|------|-----|-------------------|
1|劉備 |男 | 1| |2000-01-01| 1| 33000|10000|liubei@shuguo.com |
2|關羽 |男 | 1| 1|2000-01-01| 2| 26000|10000|guanyu@shuguo.com |
3|張飛 |男 | 1| 1|2000-01-01| 2| 24000|10000|zhangfei@shuguo.com|
其中，$ 表示將整個 emp_info 作為數據行的來源；COLUMNS 定義了字段類型及其數據的來源，PATH 同樣使用 SQL/JSON 路徑表達式。
反之，我們也可以通過 SQL 函數將關系型數據轉換為 JSON 數據。例如：
select json_object('emp_name', emp_name,
'sex', sex,
'income', json_array(json_object('salary', salary), json_object('bonus', bonus))
) AS jo
from employee
limit 3;
jo |
-------------------------------------------------------------------------------------|
{"sex": "男", "income": [{"salary": 30000.00}, {"bonus": 10000.00}], "emp_name": "劉備"}
{"sex": "男", "income": [{"salary": 26000.00}, {"bonus": 10000.00}], "emp_name": "關羽"}
{"sex": "男", "income": [{"salary": 24000.00}, {"bonus": 10000.00}], "emp_name": "張飛"}|
其中， JSON_OBJECT 和 JSON_ARRAY 函數可以將表中的數據構造成 JSON 對象和數組。
不僅如此，使用 SQL 語句也可以對 JSON 節點數據進行 DML 操作，不再介紹具體案例。關于 MySQL 文檔存儲的詳細介紹可以參考這篇文章。
總之，關系數據庫對于 JSON 數據類型的支持可以方便我們將 SQL 的強大功能與 NoSQL 的靈活性相結合；當我們需要為應用增加文檔數據支持的時候，除了使用專門的 NoSQL 數據庫之外，也可以考慮直接在現有的數據庫中使用 JSON 數據類型。
2016 年 SQL 標準增加的另一個重要的功能就是行模式識別（Row Pattern Recognition）。
二.SQL 與復雜事件處理
SQL 行模式識別使用 MATCH_RECOGNIZE 子句表示，通過指定一個模式（正則表達式）查找多行數據之間的規律，并且可以對這些匹配的數據進行過濾、分組和聚合操作。行模式識別可以用于分析各種時間序列數據，例如股票行情數據分析、金融欺詐檢測或者系統事件日志分析等。
目前只有 Oracle 12c 實現了該功能，我們可以使用以下語句找出股票曲線中的所有 V 型曲線：
-- Oracle 12c 實現
SELECT *
FROM stock MATCH_RECOGNIZE (
PARTITION BY scode
ORDER BY tradedate
MEASURES STRT.tradedate AS start_date,
LAST(DOWN.tradedate) AS bottom_date,
LAST(UP.tradedate) AS end_date
ONE ROW PER MATCH
AFTER MATCH SKIP TO LAST UP
PATTERN (STRT DOWN+ UP+)
DEFINE
DOWN AS DOWN.price < PREV(DOWN.price),
UP AS UP.price > PREV(UP.price)
) MR
ORDER BY MR.scode, MR.start_date;
其中，MATCH_RECOGNIZE 子句比較復雜，它的執行過程如下：
- PARTITION BY scode 按照股票代碼進行分區，可以同時分析多只股票的數據；如果省略，所有的數據作為一個整體進行分析，這一點與窗口函數類似；
- ORDER BY tradedate 按照交易日期進行排序，用于分析股票價格隨著時間變化的規律；
- MEASURES 定義了三個輸出值，分別代表 V 型曲線的起始日期、最低點日期以及結束日期；其中的 STRT、DOWN 和 UP 都是 DEFINE 選項中定義的變量；LAST(DOWN.tradedate) 表示下降曲線中的最后一個日期，也就是最低點日期；LAST(UP.tradedate) 表示上升曲線中的最后一個日期，也就是結束日期；
- ONE ROW PER MATCH 表示每次匹配只輸出一個匯總結果；每個 V 型曲線輸出一條記錄；如果使用 ALL ROWS PER MATCH 選項，每個 V 型曲線都會輸出構成曲線的所有節點，下文給出了相應的示例；
- AFTER MATCH SKIP TO LAST UP 表示找到匹配的數據后，從當前 V 型曲線的最后一個上升點（UP）重新開始下一次查找；
- PATTERN (STRT DOWN+ UP+) 定義了需要查找的模式，使用正則表達式語法表示。從起點（STRT）開始，先下降一次或多次（DOWN+），再上升一次或多次（UP+），也就是 V 型曲線；
- DEFINE 用于定義模式變量需要滿足的條件。STRT 變量沒有指定任何條件，意味著所有行都可以作為 V 型曲線的開始；DOWN 變量要求它的價格比上一行的價格更小，PREV 函數表示上一行；UP 變量要求它的價格比上一行的價格更大。
該語句返回的結果如下：
SCODE|START_DATE |BOTTOM_DATE |END_DATE |
-----|-------------------|-------------------|-------------------|
S001 |2019-01-01 00:00:00|2019-01-05 00:00:00|2019-01-06 00:00:00|
S001 |2019-01-06 00:00:00|2019-01-07 00:00:00|2019-01-08 00:00:00|
S001 |2019-01-08 00:00:00|2019-01-12 00:00:00|2019-01-13 00:00:00|
S001 |2019-01-18 00:00:00|2019-01-20 00:00:00|2019-01-21 00:00:00|
S001 |2019-01-21 00:00:00|2019-01-22 00:00:00|2019-01-27 00:00:00|
S001 |2019-01-27 00:00:00|2019-01-28 00:00:00|2019-01-30 00:00:00|
查詢返回了 6 條記錄，分別對應了上圖中的 6 個 V 型曲線。MATCH_RECOGNIZE 支持許多選項，尤其是通過 DEFINE 變量定義和 PATTERN 正則表達式模式可以實現各種復雜的趨勢分析。
SQL 行模式識別（MATCH_RECOGNIZE）能夠用于檢測數據流中的復雜模式，具有處理復雜事件（CEP）的強大功能。常見的應用包括偵測異常的安全行為、發現金融交易行為模式、欺詐檢測和傳感器數據分析等。
2019 年 SQL 標準增加了第 15 部分：ISO/IEC 9075-15:2019 多維數組（SQL/MDA）。
三.SQL 與多維數組
多維數組（Multi-Dimensional Arrays）是各種科學和工程數據的核心基礎結構，包括一維傳感器數據、二維衛星和顯微鏡掃描圖像、三維圖像時間序列和地球物理數據、以及四維氣候和海洋數據等。
大部分的編程語言，例如 C/C++、JAVA、Python、R 等，都提供了數組類型和相關操作的支持。早在 1999 年，SQL 就已經對數組提供了一些非常基本的支持；最新的 SQL/MDA 允許存儲、訪問和處理大規模的多維數組，例如 N 通道的衛星圖像。這意味著 SQL 現在可以解碼圖像，并且通過像素坐標直接訪問和處理圖像區域。
其中，MDA 表示在數據庫之外的數組數據，支持格式包括 TIFF、netCDF、HDF5、JSON 等；SQL/MDA 表示數據庫中存儲的數組數據，支持的操作包括：
- 數組數據的攝取和存儲；
- 更新存儲的數組數據；
- 導出數組；
- 數組和關系數據的集成查詢。
以 PostgreSQL 為例，它允許將字段定義為多維數組類型，數組的元素可以是任何內置類型、自定義類型、枚舉類型、復合類型等。例如：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
ARRAY[20000, 25000, 25000, 25000],
ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
sal_emp 表中包含兩個數組字段，pay_by_quarter 是一個一維數組，schedule 是一個二維數組。
以下查詢返回了 Bill 一周中的前兩天計劃里的第一項內容：
select schedule[1:2][1:1] from sal_emp where name = 'bill';
name |
-----|
Carol|
使用下標可以訪問數組的元素，PostgreSQL 中的數組元素從 1 開始編號。以下語句用于修改數組中的數據切片：
update sal_emp
set pay_by_quarter[1:2] = '{27000,27000}'
where name = 'carol';
PostgreSQL 為數組數據提供許多函數和運算符，例如以下查詢使用 && 運算符查找曾經拿過 10000 報酬的員工：
select name from sal_emp where pay_by_quarter && array[10000];
name|
----|
Bill|
unnest 函數可以將數組轉換為關系表，例如：
select name, unnest(pay_by_quarter), unnest(schedule) from sal_emp;
name |unnest|unnest |
-----|------|------------|
Bill | 10000|meeting |
Bill | 10000|lunch |
Bill | 10000|training |
Bill | 10000|presentation|
Carol| 20000|breakfast |
Carol| 25000|consulting |
Carol| 25000|meeting |
Carol| 25000|lunch |
PostgreSQL 還為數組提供了 GiST 和 GIN 類型的索引，可以優化數組數據的查詢。
除此之外，基于 PostgreSQL 的 PostGIS Raster、Oracle GeoRaster 以及 rasdaman 數組數據庫則提供了更加完善的多維數組應用場景支持。
四.SQL 與圖形數據庫
圖形數據庫（graph database）屬于 NoSQL 的一種，使用節點、邊和屬性來表示和存儲數據，使用圖結構進行語義查詢。圖形數據庫非常適合社交網絡、人工智能、欺詐檢測、推薦系統等領域中的復雜關系處理。Neo4j 是目前最著名的圖形數據庫。
2019 年 9 月 17 圖形查詢語言（GQL）成為了繼 SQL 之后另一種新的 ISO 標準數據庫查詢語言。與此同時，SQL 標準將會出現一個新的第 16 部分（SQL/PGQ）（Property Graph Query），在 SQL 中直接提供一些 GQL 功能。
目前，MariaDB（OQGRAPH）、Oracle、Microsoft SQL Server 等關系型數據庫都提供了圖結構存儲支持。上圖是 Oracle 中一個金融交易系統的圖形數據庫示例，其中 Account、Person 和 Company 是頂點，ownerOf、worksFor 和 transaction 是邊；另外，name 和 number 是頂點的屬性，amount 是邊的屬性。它們可以使用以下數據表進行存儲：
基于這些表可以創建以下屬性圖形：
CREATE PROPERTY GRAPH financial_transactions
VERTEX TABLES (
Accounts LABEL Account,
Persons LABEL Person PROPERTIES ( name ),
Companies LABEL Company PROPERTIES ( name )
)
EDGE TABLES (
Transactions
SOURCE KEY ( from_account ) REFERENCES Accounts
DESTINATION KEY ( to_account ) REFERENCES Accounts
LABEL ( transaction ) PROPERTIES ( amount ),
PersonOwnerOfAccount
SOURCE Persons
DESTINATION Accounts
LABEL ownerOf NO PROPERTIES,
CompanyOwnerOfAccount
SOURCE Companies
DESTINATION Accounts
LABEL ownerOf NO PROPERTIES,
PersonWorksForCompany
SOURCE Persons
DESTINATION Companies
LABEL worksFor NO PROPERTIES
);
接下來我們就可以直接使用 SQL 語句查詢圖結構，例如以下語句查找所有和名叫 Nikita 的人有過交易的人員和信息：
SELECT owner.name AS account_holder, SUM(t.amount) AS total_transacted_with_Nikita
FROM MATCH (p:Person) -[:ownerOf]-> (account1:Account)
, MATCH (account1) -[t:transaction]- (account2) /* match both incoming and outgoing transactions */
, MATCH (account2:Account) <-[:ownerOf]- (owner:Person|Company)
WHERE p.name = 'Nikita'
GROUP BY owner
其中，MATCH 子句用于遍歷圖形結構并返回匹配的模式，語法和 Neo4j 的 Cypher 查詢語言中的 MATCH 子句非常類似。以上查詢返回的結果如下：
+----------------+------------------------------+
| account_holder | total_transacted_with_Nikita |
+----------------+------------------------------|
| Camille | 1000.00 |
| Oracle | 4501.00 |
+----------------+------------------------------+
隨著 SQL 標準第 16 部分（SQL/PGQ）即將出現，我們可以在關系數據庫中直接存儲屬性圖結構數據，并且在 SQL 中進行屬性圖模式匹配，例如最短路徑查找和最佳路徑查找；SQL/PGQ 的另一個優勢就是可以支持分組（GROUP BY）、聚合（AVG、SUM、COUNT 等）、排序（ORDER BY）以及許多其他的 SQL 功能。
五.SQL 與流數據處理
流數據是一組順序、大量、快速、連續到達的數據序列，一般情況下可被視為一個隨時間延續而無限增長的動態數據集合。常見的流數據包括應用程序日志文件、網購數據、游戲玩家互動數據、社交網站信息、金融交易實時數據或地理空間服務，以及來自數據中心內所連接設備或儀器的遙測數據等。
目前，常用的大數據流處理平臺 Spark Streaming、Storm、Flink、ksqlDB 等都提供了 SQL 流數據處理功能；同時，一個關于流數據處理的 SQL 標準部分正在準備中。
其中，ksqlDB 是一個基于 Apache Kafka 的事件流數據庫，提供了輕量級的 SQL 語句，大大降低了構建流處理應用程序所需的操作復雜性。
客戶端應用程序可以采用拉取查詢（pull query）和推送查詢（push query）兩種方式查看和訂閱數據流變化。
在 ksqlDB 中創建一個數據流并運行連續查詢的簡單示例如下：
CREATE STREAM riderLocations (profileId VARCHAR, latitude DOUBLE, longitude DOUBLE)
WITH (kafka_topic='locations', value_format='json', partitions=1);
-- Mountain View lat, long: 37.4133, -122.1162
SELECT * FROM riderLocations
WHERE GEO_DISTANCE(latitude, longitude, 37.4133, -122.1162) <= 5 EMIT CHANGES;
首先，通過一個 Kafka 主題創建一個數據流 riderLocations（騎手位置）；消息內容采用 json 格式存儲，例如 {“profileId”: “c2309eec”, “latitude”: 37.7877, “longitude”: -122.4205}。
然后，針對 riderLocations 數據流運行一個連續查詢，返回距離 Mountain View（加州山景城）5 英里之內的騎手。該查詢會一直運行，直到被終止；并且隨著事件被寫入 riderLocations，它會將結果推送到客戶端。
此時，如果我們打開另一個會話連接到 ksqlDB，生成一些數據流：
INSERT INTO riderLocations (profileId, latitude, longitude) VALUES ('c2309eec', 37.7877, -122.4205);
INSERT INTO riderLocations (profileId, latitude, longitude) VALUES ('18f4ea86', 37.3903, -122.0643);
INSERT INTO riderLocations (profileId, latitude, longitude) VALUES ('4ab5cbad', 37.3952, -122.0813);
INSERT INTO riderLocations (profileId, latitude, longitude) VALUES ('8b6eae59', 37.3944, -122.0813);
INSERT INTO riderLocations (profileId, latitude, longitude) VALUES ('4a7c7b41', 37.4049, -122.0822);
INSERT INTO riderLocations (profileId, latitude, longitude) VALUES ('4ddad000', 37.7857, -122.4011);
隨著每次事件的生成，流查詢語句將會在第一個會話中實時輸出匹配的數據行。
六.總結
隨著互聯網和大數據等新技術的發展，SQL 早已不僅僅是當年的關系數據庫查詢語言了；無論是面向對象特性（例如自定義類型）、文檔數據（XML、JSON）的存儲和處理、時態數據的存儲和處理、復雜事件和流數據處理、數據科學中的多維數組以及圖形數據庫等各種 NoSQL 功能已經或者即將成為 SQL 標準中的一部分，One SQL to Rule Them All！
