前言:

DataEase 2.0 将 SQL 解析引擎全部交由 Calcite 完成，所以在使用 2.0 书写 SQL 时，需要使用Calcite 的语法以及函数。本文档介绍常见的 SQL 语法以及常用的函数。Calcite 能够通过写 CalCite SQL 语法直接对接其他的数据库，原理是 Calcite 能够通过解析 SQL 为 sqlnode，再将 sqlnode 转化为特定数据库的方言的形式实现 SQL 的统一。

截止本文书写时间，使用测试数据库为 MySQL。

语法官网

1 数据类型

下表 Calcite 列举了部分常见数据结构，连接其它数据库时，其他数据库字段的数据类型映射到 Calcite 中的数据类型由 Calcite 本身解析，在书写 SQL 涉及到类型转换以及使用函数对类型有要求的时候，需要注意数据类型，可以参考下表。

注意：Calcite 时间单位，在使用 Calcite 的时间函数时，涉及到需要填写的 timeUnit 如下列举。

 timeUnit:
  MILLENNIUM | CENTURY | DECADE | YEAR | QUARTER | MONTH | WEEK | DOY | DOW | DAY | HOUR | MINUTE | SECOND | EPOCH

2 数据操作

2.1 运算符优先级

  操作符的优先级从高到低与结合性。

2.2 比较操作

value1 = value2
value1 <> value2
value1 > value2 
value1 >= value2 
value1 < value2 
value1 <= value2 
value1 <=> value2 --两个值是否相等，将空值视为相同
value IS NULL 
value IS NOT NULL 
value1 BETWEEN value2 AND value3
string1 LIKE string2
value IN (value [, value ]*)

2.3 算数操作

+ numeric  --绝对值
- numeric  -- 负数
numeric1 + numeric2
numeric1 - numeric2
numeric1 * numeric2
numeric1 / numeric2

3 Calcite 查询语法基础规则

SQL 书写参考标准 SQL，函数使用 Calcite 函数。

查看官网语法，SQL 规则以及书写顺序如下。[] 表示是可选项，{} 必选项 。中间｜表示或，只能选两边的某一个，函数也作此参考。

select:
      SELECT [ hintComment ] [ STREAM ] [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY [ ALL | DISTINCT ] { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
      [ ORDER BY orderItem [, orderItem ]* ]
      [ LIMIT [ start, ] { count | ALL } ]
      [ OFFSET start { ROW | ROWS } ]
      

--join 操作
[ NATURAL ] [ { LEFT | RIGHT | FULL } [ OUTER ] ] 
JOIN ON booleanExpression


-- union 同标准 SQL，并集
query1 UNION [ ALL | DISTINCT ] query2

4 Calcite 通用函数

注： DataEase v2.0 ，计算字段只存在于数据集，不再有视图计算字段（ DataEase v1 ），下文举例函数都可以使用在数据集计算字段。

4.1 字符串函数

1. CONCAT(string [, string ]*)

返回两个字符串拼接后的结果。

2. CHAR_LENGTH(string)

返回字符串长度。

3. UPPER(string)

字符串转换为大写。

4. LOWER(string)

字符串转换为小写。

5. SUBSTRING(string , n)

返回子字符串，从 n 开始返回，string 下标从 1 开始。

6. SUBSTRING(string ,n,n)

返回子字符串，从 n 开始返回 n 位，string 下标从 1 开始。

7. TRIM( { BOTH | LEADING | TRAILING } string1 FROM string2)

从 string2 的 两端/左端/右端 移除只包含 string1 中的字符的最长字符串。

4.2 日期函数

1. LOCALTIME

返回 time(hh:mm:ss) 类型的时间

2. LOCALTIMESTAMP

返回TIMESTAMP(yyyy-mm-dd hh:mm:ss),类型的程序时间。

3. CURRENT_DATE

返回 date（yyyy-mm-dd) 类型的程序时间。

4. YEAR(date)

返回一个整数年, yyyy。

5. QUARTER(date)

返回季度，1到4之间的整数。

6. MONTH(date)

返回月份，1到12之间的整数。

7. WEEK(date)

返回周，1到53之间的整数。

8. HOUR(date)

返回小时，0到23的整数。

9. MINUTE(date)

返回分钟，0到59的整数。

10. LAST_DAY(date)

返回日期字段该月份的最后一天。

4.3 条件函数

1. NULLIF(value1,value2)

value1 = value2 返回 null，否则返回 value1。

2. COALESCE(value1, value2)

value1 为 null ,返回 value2 ，否则返回 value1。

3. CASE WHEN

1. 简单 case

CASE value WHEN value1 [, value11 ]* THEN result1 [ WHEN valueN [, valueN1 ]* THEN resultN ]* [ ELSE resultZ ] END

2. 搜索 case

CASE WHEN condition1 THEN result1 [ WHEN conditionN THEN resultN ]* [ ELSE resultZ ] END

4.4 聚合函数

注：DataEase v2.0 使用计算字段时，可以使用聚合函数。

1. SUM( [ ALL | DISTINCT ]values )

返回选中字段所有值的和。

2. COUNT(*)

返回满足要求的行的数目。

3. ARG_MAX(value1, value2)

返回 value1 分组中，value2 的最大值。

4. ARG_MIN(value1, value2)

返回 value 1分组中 ，value2 的最大值。

5. COLLECT( [ ALL | DISTINCT ] value)

返回分组内，value 的集合值，以 "," 分隔。

6. MAX( [ ALL | DISTINCT ] value)，MIN( [ ALL | DISTINCT ] value)

返回组内最大或最小值 value。

7. AVG( [ ALL | DISTINCT ] value)

返回组内平均值。

4.5 算术函数

1. ABS(numeric)

返回绝对值。

2. CEIL(numeric)

numeric 向上取整，返回大于或者等于 numeric 的最小整数。

3. FLOOR(numeric)

numeric 向上取整，返回小于或者等于 numeric 的最小整数。

4. ROUND(numeric1 [, numeric2])

返回 numeric1 舍入到可选的 numeric2 (numeric2如果未指定则为0)的小数点右边。

4.6 窗口函数

Calcite 支持窗口函数，用法同标准 SQL。

Function over window。

rows between 控制函数的范围，没写默认全部范围。

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用。

UNBOUNDED PRECEDING 边界是分区中的第一行。

UNBOUNDED FOLLOWING 边界是分区中的最后一行。

m PRECEDING 当前行之前的 m 行。

n FOLLOWING 当前行之后的 n 行。

ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

1. COUNT(*) OVER window

返回窗口中的行数

2. AVG(numeric) OVER window

返回窗口中所有值的数值的平均值（算术平均值）

3. SUM(numeric) OVER window

返回窗口中所有值的数字总和

4. MAX(value) OVER window

返回窗口中所有值中值的最大值

5. MIN(value) OVER window

返回窗口中所有值中值的最小值

6. 窗口排序函数

RANK() OVER window

DENSE_RANK() OVER window

ROW_NUMBER() OVER window

7. LEAD(value, offset, default) OVER window

新增一列，value 向上移动 offest 行，default 默认是 null 。

8. LAG(value, offset, default) OVER ( [partioton by value1] [order by value2 ] )

新增一列，向下移动 offest 行，default 默认是 null 。

9. NTH_VALUE(value, n) OVER ( [partioton by value1] [order by value2 ] )

新增一列，返回分组 value1 中，第 n 个 value 的值，填充到新列中，分组 value1 行数不够 n 的，为 null.

10. NTILE(n) OVER [partioton by value1] [order by value2 ] )

新增一列，返回1 到n之间的整数，尽可能平分分组 value1.

4.7 类型转换

1. CAST(value AS dataType)

将value转换为指定的类型。

隐式类型转换

当转换有意义时，CalCite 会自动将值从一种数据类型转换为另一种数据类型。下表是类型转换的矩阵。该表显示了所有可能的转换，而不考虑进行转换的上下文。

i：隐式转换

e：显式转换

x：不允许

隐式转化背景和策略

集合操作（UNION 、EXCEPT、INTERSECT）：比较各分支行数据类型，找出各字段对的公共类型；

二进制算术表达式 ( +, -, &, ^, /, %)：l将字符串操作数提升为另一个数值操作数的数据类型；

二进制比较 ( =, <, <=, <>, >, >=)：如果操作数是 STRING 和 TIMESTAMP，则提升为TIMESTAMP；如果存在数字类型操作数，则找到两个操作数的共同类型，1 = true 和 0 = false 始终为TRUE；

IN 子查询：比较左右类型，找出共同类型；如果是结构体类型，则为每个字段找到更宽的类型；

IN 表达式列表：比较每个表达式以找到共同类型；

CASE WHEN 表达式 or COALESCE ：查找 THEN 和 ELSE 操作数的通用更宽类型;；

字符 + INTERVAL 或字符 - INTERVAL：将字符提升为 TIMESTAMP；

用户定义函数（UDF）：根据方法声明的参数类型进行强制转换eval()

4.8 特定方言函数

1. 字符串函数

1. CONCAT(string [, string ]*)

连接两个字符串,适用于 MySQL，PG，MsSQL，Oracle。

2. CONCAT_WS(separator, str1, str2 [, string ]*)

连接两个字符串,适用于 MySQL，PG，MsSQL，Oracle。

5 DataEase UDF 函数

Calcite 允许用户使用自定义 UDF 函数实现业务需求，目前 DataEase 自定义两个日期相关的 UDF 函数用来处理 Calcite 日期转换函数缺失问题，DataEase 后续会根据社区反馈以及产品规划自定义其他的 UDF 函数。

5.1 日期函数

1. DE_DATE_FORMAT(date,format)

将date以format格式返回（例：yyyy-MM-dd HH:mm:ss.SSS）

2. DE_STR_TO_DATE(date,format)

将date以format格式返回（例：yyyy-MM-dd HH:mm:ss.SSS）

3. UNIX_TIMESTAMP(date)

将标准格式 date 以 13 位时间戳返回

4. FROM_UNIXTIME(timestamp,format)

将 timestamp 转换成 format 格式返回

6 注意

如有 Calcite 官方文档中支持的函数需要在 DataEase 中也支持，请提交 issue 至 github dataease 项目。