【新人入手】Calcite 语法


飞致云 发布于 2024-04-11 / 371 阅读 / 0 评论 /
前言: DataEase 2.0 将 SQL 解析引擎全部交由 Calcite 完成,所以在使用 2.0 书写 SQL 时,需要使用Calcite 的语法以及函数。本文档介绍常见的 SQL 语法以及常用的函数。Calcite 能够通过写 CalCite SQL 语法直接对接其他的数据库,原理是 Ca

前言:

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

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

语法官网

1 数据类型

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

类型

描述

举例

BOOLEAN

逻辑值

值:真、假、UNKNOWN

TINYINT

1 字节有符号整数

范围 -128 到 127

SMALLINT

2 字节有符号整数

范围 -32768 至 32767

INTEGER, INT

4 字节有符号整数

范围 -2147483648 至 2147483647

BIGINT

8 字节有符号整数

范围 -9223372036854775808 至 9223372036854775807

DECIMAL(p, s)

精确小数

示例:123.45 和 DECIMAL '123.45' 是相同的值,并且类型为 DECIMAL(5, 2)

NUMERIC(p, s)

精确小数

同 DECIMAL

REAL

4字节浮点数

6位小数精度

DOUBLE

8字节浮点数

15位小数精度

FLOAT

8字节浮点数

同 DOUBLE

CHAR(n), CHARACTER(n)

定宽字符串

'Hello'

VARCHAR(n)
CHARACTERVARYING(n)

变长字符串

同 CHAR(n)

DATE

日期

示例:日期“1969-07-20”

TIME

一天中的时间

示例:时间“20:17:40”

TIMESTAMP [ WITHOUT TIME ZONE ]

日期和时间

示例:TIMESTAMP '1969-07-20 20:17:40'

TIMESTAMP WITH TIME ZONE

带时区的日期和时间

示例:TIMESTAMP ‘1969-07-20 20:17:40 America/Los Angeles’

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

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

2 数据操作

2.1 运算符优先级

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

操作

结合性

.

::

+ - (一元加减)

* / % ||

+ -

BETWEEN, IN, LIKE, SIMILAR, 等.

-

< ,>, = ,<=, >=, <> ,!=, <=>

IS NULL, IS FALSE, IS NOT TRUE 等.

-

NOT

AND

OR

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 ]*)

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

  1. CHAR_LENGTH(string)

返回字符串长度。

  1. UPPER(string)

字符串转换为大写。

  1. LOWER(string)

字符串转换为小写。

  1. SUBSTRING(string , n)

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

  1. SUBSTRING(string ,n,n)

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

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

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

4.2 日期函数

  1. LOCALTIME

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

  1. LOCALTIMESTAMP

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

  1. CURRENT_DATE

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

  1. YEAR(date)

返回一个整数年, yyyy。

  1. QUARTER(date)

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

  1. MONTH(date)

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

  1. WEEK(date)

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

  1. HOUR(date)

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

  1. MINUTE(date)

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

  1. LAST_DAY(date)

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

4.3 条件函数

  1. NULLIF(value1,value2)

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

  1. COALESCE(value1, value2)

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

  1. CASE WHEN

  1. 简单 case

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

  1. 搜索 case

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

4.4 聚合函数

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

  1. SUM( [ ALL | DISTINCT ]values )

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

  1. COUNT(*)

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

  1. ARG_MAX(value1, value2)

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

  1. ARG_MIN(value1, value2)

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

  1. COLLECT( [ ALL | DISTINCT ] value)

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

  1. MAX( [ ALL | DISTINCT ] value),MIN( [ ALL | DISTINCT ] value)

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

  1. AVG( [ ALL | DISTINCT ] value)

返回组内平均值。

4.5 算术函数

  1. ABS(numeric)

返回绝对值。

  1. CEIL(numeric)

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

  1. FLOOR(numeric)

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

  1. 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

返回窗口中的行数

  1. AVG(numeric) OVER window

返回窗口中所有值的数值的平均值(算术平均值)

  1. SUM(numeric) OVER window

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

  1. MAX(value) OVER window

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

  1. MIN(value) OVER window

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

  1. 窗口排序函数

RANK() OVER window

DENSE_RANK() OVER window

ROW_NUMBER() OVER window

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

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

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

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

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

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

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

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

4.7 类型转换

  1. CAST(value AS dataType)

将value转换为指定的类型。

隐式类型转换

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

FROM - TO

NULL

BOOLEAN

TINYINT

SMALLINT

INT

BIGINT

DECIMAL

FLOAT OR REAL

DOUBLE

DATE

TIME

TIMESTAMP

CHAR OR VARCHAR

NULL

i

i

i

i

i

i

i

i

i

i

i

i

i

BOOLEAN

x

i

x

x

x

x

x

x

x

x

x

x

i

TINYINT

x

e

i

i

i

i

i

i

i

x

x

e

i

SMALLINT

x

e

i

i

i

i

i

i

i

x

x

e

i

INT

x

e

i

i

i

i

i

i

i

x

x

e

i

BIGINT

x

e

i

i

i

i

i

i

i

x

x

e

i

DECIMAL

x

e

i

i

i

i

i

i

i

x

x

e

i

FLOAT/REAL

x

e

i

i

i

i

i

i

i

x

x

e

i

DOUBLE

x

e

i

i

i

i

i

i

i

x

x

e

i

DATE

x

x

x

x

x

x

x

x

x

i

x

i

i

TIME

x

x

x

x

x

x

x

x

x

x

i

e

i

TIMESTAMP

x

x

e

e

e

e

e

e

e

i

e

i

i

CHAR or VARCHAR

x

e

i

i

i

i

i

i

i

i

i

i

i

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。

  1. 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)

  1. DE_STR_TO_DATE(date,format)

将date以format格式返回(例:yyyy-MM-dd HH:mm:ss.SSS)

  1. UNIX_TIMESTAMP(date)

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

  1. FROM_UNIXTIME(timestamp,format)

将 timestamp 转换成 format 格式返回

6 注意

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



是否对你有帮助?