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模糊匹配，以及正则表达式匹配等过滤条件。

示例

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相关函数

日期与时间函数

聚合函数

窗口函数