Lindorm：SQL文法參考

投影運算元

子句運算式

SELECT expr0, expr1, ...

運算元說明

如SELECT expr1, expr2 FROM tablename，其中expr1,expr2是投影運算式，支援基本的列引用、變換函數、DISTINCT去重運算元等，用於產生查詢結果。支援使用AS colname對投影結果進行重新命名。

樣本

SELECT * FROM nation;

SELECT n_name FROM nation;

SELECT lower(n_name) FROM nation;

SELECT DISTINCT n_regionkey FROM nation;

SELECT n_nationkey + 1 FROM nation;

SELECT n_nationkey + 1 AS colname FROM nation;

過濾運算元

子句運算式

...WHERE expr0 (AND expr1) ? (OR expr2) ? ...

運算元說明

如SELECT * FROM tablename WHERE expr1 AND expr2，其中expr1和expr2是過濾運算式，支援可返回布爾值的運算式，用於過濾出滿足查詢條件的資料行。支援LIKE模糊比對，以及Regex匹配等過濾條件。

樣本

SELECT n_name FROM nation WHERE n_regionkey = 2;

SELECT n_nationkey FROM nation WHERE n_name IN ('CHINA', 'VIETNAM');

SELECT n_name FROM nation WHERE n_nationkey BETWEEN 10 AND 20;

SELECT n_name FROM nation WHERE n_name LIKE 'C%';

SELECT n_name FROM nation WHERE n_name regexp '^(C|U).*';

SELECT n_name FROM nation WHERE n_regionkey IN (SELECT r_regionkey FROM region WHERE r_name = 'ASIA');

排序運算元

子句運算式

...ORDER BY expr0, expr1 (ASC | DESC) ?

運算元說明

ORDER BY子句，使輸出結果按指定列排序展示，支援按升序、降序排列，支援指定NULL值在前還是在後。

樣本

SELECT * FROM nation ORDER BY n_name;

SELECT * FROM nation ORDER BY n_name DESC;

SELECT * FROM nation ORDER BY n_name NULLS FIRST;

SELECT * FROM nation ORDER BY n_name NULLS LAST;

翻頁運算元

子句運算式

... LIMIT (offset, )? limit

運算元說明

LIMIT [offset, ] length子句，也可以寫作LIMIT length [OFFSET offset]，實現輸出指定位移位置指定行數的資料，常和ORDER BY一起使用。

樣本

SELECT * FROM nation LIMIT 5;

SELECT * FROM nation LIMIT 5, 10;

SELECT * FROM nation ORDER BY n_name LIMIT 3, 5;

SELECT * FROM nation ORDER BY n_name LIMIT 5 OFFSET 3;

彙總運算元

子句運算式

SELECT AGG_FUNC0(expr0), AGG_FUNC1(expr1), ... GROUP BY ...

運算元說明

全表彙總或者分組彙總運算元，如GROUP BY子句，可以實現資料的分組彙總，統計每組資料的整體特徵。也支援通過HAVING子句過濾分組後的資料。

樣本

SELECT count(*) FROM nation;

SELECT max(o_totalprice) FROM orders;

SELECT c_nationkey, count(*) FROM customer GROUP BY c_nationkey;

SELECT c_nationkey, count(*) AS user_count FROM customer GROUP BY c_nationkey HAVING user_count > 6000;

通用資料表運算式

子句運算式

WITH name1 AS (SELECT ...) (, name2 AS (SELECT .... ) )?

運算元說明

通用資料表運算式（Common Table Expression）定義一個臨時結果集，可以在SQL語句的範圍內多次引用。使用WITH name AS (SELECT ...) [, name2 AS (SELECT ...)]來表示，可以最佳化SQL的可讀性。

樣本

WITH china_users AS (SELECT c_custkey FROM customer WHERE c_nationkey = 18), canada_users AS (SELECT c_custkey FROM customer WHERE c_nationkey = 3) SELECT * FROM china_users UNION ALL SELECT * FROM canada_users;

並交補運算元

query1 UNION ( ALL | DISTINCT )? query2

SELECT n_regionkey FROM nation UNION SELECT n_regionkey FROM nation;

SELECT n_regionkey FROM nation UNION DISTINCT SELECT n_regionkey FROM nation;

SELECT n_regionkey FROM nation UNION ALL SELECT n_regionkey FROM nation;

query1 INTERSECT query2

SELECT n_name FROM nation INTERSECT SELECT n_name FROM nation;

query1 ( EXCEPT | MINUS ) query2

SELECT n_name FROM nation EXCEPT SELECT n_name FROM nation;

SELECT n_name FROM nation MINUS SELECT n_name FROM nation;

關聯運算元

子句運算式

... FROM table1 (LEFT | RIGHT | FULL)? (OUTER | SEMI | ANTI)? JOIN table2 ( ON ... )?

運算元說明

關聯（JOIN）操作可以按指定規則合并兩個表中的資料，並且對結果進行投影計算。OLAP資源群組支援SELF JOIN、CROSS JOIN、INNER JOIN、OUTER JOIN、SEMI JOIN和ANTI JOIN。其中，OUTER JOIN包括LEFT JOIN、RIGHT JOIN和FULL JOIN。

樣本

SELECT a.n_name, b.n_name  FROM nation a, nation b;

SELECT a.n_name, b.n_name  FROM nation a JOIN nation b;

SELECT a.n_name, b.n_name  FROM nation a CROSS JOIN nation b;

SELECT r_name, n_name  FROM nation JOIN region ON nation.n_regionkey = region.r_regionkey;

SELECT r_name, n_name  FROM nation, region WHERE r_regionkey = n_regionkey;

SELECT r_name, n_name  FROM nation LEFT OUTER JOIN region ON n_regionkey = r_regionkey;

SELECT r_name, n_name  FROM nation RIGHT OUTER JOIN region ON n_regionkey = r_regionkey;

SELECT r_name, n_name  FROM nation FULL OUTER JOIN region ON n_regionkey = r_regionkey;

SELECT * FROM nation LEFT SEMI JOIN region ON n_regionkey = r_regionkey;

SELECT * FROM nation RIGHT SEMI JOIN region ON n_regionkey = r_regionkey;

SELECT * FROM nation LEFT ANTI JOIN region ON n_regionkey = r_regionkey;

SELECT * FROM nation RIGHT ANTI JOIN region ON n_regionkey = r_regionkey;

視窗函數

子句運算式

... window_function() OVER (PARITION BY ... ORDER BY ... ROWS BETWEEN ... AND ...)

運算元說明

視窗函數是內建的特殊函數。和彙總函式類似，視窗函數通過對多行資料計算得到一個資料值。不同的是，視窗函數使用OVER()子句對當前視窗內的資料進行排序和分組，同時對結果集的每一行計算出一個單獨的值，而不是對每個GROUP BY分組計算一個值。這種靈活的方式允許您在SELECT子句中增加額外的列，對結果集重新組織和過濾。

視窗函數在金融和科學計算領域較為常用，常被用來分析趨勢、計算離群值以及對大量資料進行分桶分析等。

樣本

SELECT row_number() OVER (ORDER BY n_regionkey), n_regionkey, n_name FROM nation;

SELECT row_number() OVER (PARTITION BY n_regionkey ORDER BY n_regionkey), n_regionkey, n_name FROM nation;

SELECT o_orderdate, o_totalprice, sum(o_totalprice) OVER (PARTITION BY substr(o_orderdate, 1, 4) ORDER BY o_orderdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum FROM orders WHERE o_custkey = 53978 ORDER BY o_orderdate;

系統資訊

類型轉換與字串函數

JSON相關函數

日期與時間函數

彙總函式

視窗函數