4.2. 内置函数

本节主要介绍统一SQL支持原生Oracle数据库中的函数

4.2.1. 数字函数

4.2.1.1. ABS

语法: ABS(numeric_expression)
描述: 该函数返回 numeric_expression 的绝对值。ABS 将负值更改为正值，对零或正值没有影响。

参数解释

参数	说明
numeric_expression	精确数值或近似数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）的表达式

示例

-- 转换前Oracle SQL：
SELECT ABS(-1.0), ABS(0), ABS(6.66), ABS(3-6) FROM DUAL;
ABS(-1.0)|ABS(0)|ABS(6.66)|ABS(3-6)|
---------+------+---------+--------+
     1|     0|     6.66|       3|


-- 转换后LightDB-Oracle SQL：
SELECT ABS(-1.0),ABS(0),ABS(6.66),ABS(3-6) FROM DUAL
abs|abs|abs |abs|
---+---+----+---+
1.0|  0|6.66|  3|

4.2.1.2. BITAND

语法: BITAND (expr1,expr2)
描述: 该函数将其输入参数进行二进制按位与操作

参数解释

参数	说明
expr1	参数1	NUMBER 类型表达式
expr2	参数2	NUMBER 类型表达式

示例

-- 转换前Oracle SQL：
SELECT BITAND(0,0),BITAND(0,-1),BITAND(0,NULL),BITAND(1,2) FROM DUAL;
BITAND(0,0)|BITAND(0,-1)|BITAND(0,NULL)|BITAND(1,2)|
-----------+------------+--------------+-----------+
          0|           0|              |          0|

-- 转换后LightDB-Oracle SQL：
SELECT BITAND(0, 0),BITAND(0, -1),BITAND(0, NULL),BITAND(1, 2) FROM DUAL
bitand|bitand|bitand|bitand|
------+------+------+------+
     0|     0|      |     0|

4.2.1.3. CEIL

语法: CEIL (numeric_expression)
描述: 该函数返回大于等于 numeric_expression 的最小整数

参数解释

参数	说明
numeric_expression	精确数值或近似数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）的表达式

示例

-- 转换前Oracle SQL：
SELECT CEIL(-1.5),CEIL(1.5),CEIL(2),CEIL(6-9.5) FROM DUAL;
CEIL(-1.5)|CEIL(1.5)|CEIL(2)|CEIL(6-9.5)|
----------+---------+-------+-----------+
        -1|        2|      2|         -3|

-- 转换后LightDB-Oracle SQL：
SELECT CEIL(-1.5),CEIL(1.5),CEIL(2),CEIL(6-9.5) FROM DUAL
ceil|ceil|ceil|ceil|
----+----+----+----+
  -1|   2| 2.0|  -3|

4.2.1.4. DBMS_RANDOM.VALUE

语法: DBMS_RANDOM.VALUE
描述: 随机生成 [0,1) 范围内的数字，精度为 38 位

示例

-- 转换前Oracle SQL：
 SELECT DBMS_RANDOM.VALUE from dual;
 VALUE                                   |
----------------------------------------+
0.90603062118926722027812535155007101231|

-- 转换后LightDB-Oracle SQL：
SELECT DBMS_RANDOM.VALUE FROM DUAL
value              |
-------------------+
0.39212693460285664|

4.2.1.5. DBMS_RANDOM.RANDOM

语法: DBMS_RANDOM.RANDOM
描述: 随机生成 [-2^31，2^31）范围内的整数。

-- 转换前Oracle SQL：
 SELECT DBMS_RANDOM.RANDOM from dual;
 RANDOM    |
 ----------+
 -886930169|

-- 转换后LightDB-Oracle SQL：
SELECT DBMS_RANDOM.RANDOM FROM DUAL
random    |
----------+
-720293376|

4.2.1.6. EXP

语法: EXP (numeric_expression)
描述: 该函数是以自然常数 e 为底的指数函数，用于返回 e 的 numeric_expression 次方，其中 e = 2.71828183…。

参数解释

参数	说明
numeric_expression	是数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数

示例

-- 转换前Oracle SQL：
SELECT EXP(1),EXP(0),EXP(NULL),EXP(2) FROM DUAL;
EXP(1)                                  |EXP(0)|EXP(NULL)|EXP(2)                                 |
----------------------------------------+------+---------+---------------------------------------+
2.71828182845904523536028747135266249776|     1|         |7.3890560989306502272304274605750078132|


-- 转换后LightDB-Oracle SQL：
SELECT EXP(1),EXP(0),EXP(NULL),EXP(2) FROM DUAL
exp              |exp|exp|exp             |
-----------------+---+---+----------------+
2.718281828459045|1.0|   |7.38905609893065|

4.2.1.7. FLOOR

语法: FLOOR (numeric_expression)
描述: 该函数返回小于等于数值 numeric_expression 的最大整数。

参数解释

参数	说明
numeric_expression	是数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数

示例

-- 转换前Oracle SQL：
SELECT floor(0) AS "floor(0)",floor(10) AS "floor(10)",floor(10.11) AS "floor(10.11)",floor(-10.11) AS "floor(-10.11)" from dual;
floor(0)|floor(10)|floor(10.11)|floor(-10.11)|
--------+---------+------------+-------------+
      0|       10|          10|          -11|

-- 转换后LightDB-Oracle SQL：
SELECT floor(0) AS "floor(0)",floor(10) AS "floor(10)",floor(10.11) AS "floor(10.11)",floor(-10.11) AS "floor(-10.11)" FROM dual
floor(0)|floor(10)|floor(10.11)|floor(-10.11)|
--------+---------+------------+-------------+
     0.0|     10.0|          10|          -11|

4.2.1.8. LN

语法: LN (numeric_expression)
描述: 该函数返回以 e 为底 numeric_expression 的对数。

参数解释

参数	说明
numeric_expression	是数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数

示例

-- 转换前Oracle SQL：
SELECT LN(3.0) AS "Natural Logarithm" from dual;
Natural Logarithm                       |
----------------------------------------+
1.09861228866810969139524523692252570465|

-- 转换后LightDB-Oracle SQL：
SELECT LN(3.0) AS "Natural Logarithm" from dual;
Natural Logarithm |
------------------+
1.0986122886681097|

4.2.1.9. LOG

语法: LOG (x,y)
描述: 该函数返回以 x 为底的 y 的对数。

参数解释

参数	说明
x	表示对数的底，数值型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。 x 大于 0 且不等于 1
y	表示真数，数值型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。 y 大于 0。

警告

如示例所示，该函数的结果可能存在不一致的情况。

示例

-- 转换前Oracle SQL：
SELECT LOG(10,100),LOG(3,27) AS "LOG3",LOG(2,1024) AS "LOG(2,1024)" from dual;
LOG(10,100)|LOG3                                    |LOG(2,1024)                             |
-----------+----------------------------------------+----------------------------------------+
          2|2.99999999999999999999999999999999999998|9.99999999999999999999999999999999999995|

-- 转换后LightDB-Oracle SQL：
SELECT LOG(10, 100),LOG(3, 27) AS "LOG3",LOG(2, 1024) AS "LOG(2,1024)" FROM dual
log               |LOG3              |LOG(2,1024)        |
------------------+------------------+-------------------+
2.0000000000000000|3.0000000000000000|10.0000000000000000|

4.2.1.10. MOD

语法: MOD (x,y)
描述: 该函数是一个求余函数，即是两个数值表达式作除法运算后的余数。返回结果的符号与被除数的符号相同。

参数解释

参数	说明
x	表示被除数，属于数值型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。
y	表示除数，属于数值型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。

示例

-- 转换前Oracle SQL：
SELECT MOD(11,4) "Modulus",MOD(11,4),MOD(11,-4),MOD(-11,4),MOD(-11,-4) FROM DUAL;
Modulus  | mod | mod | mod | mod
---------+-----+-----+-----+-----
       3 |   3 |   3 |  -3 |  -3

-- 转换后LightDB-Oracle SQL：
SELECT MOD(11, 4) AS "Modulus",MOD(11, 4),MOD(11, -4),MOD(-11, 4),MOD(-11, -4) FROM DUAL
Modulus|mod|mod|mod|mod|
-------+---+---+---+---+
      3|  3|  3| -3| -3|

4.2.1.11. POWER

语法: POWER (x, y)
描述: 该函数作用是返回指定数字的乘幂。

参数解释

参数	说明
x	表示底数，属于数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式
y	表示指数，属于数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。如果 x 是负数，则 y 必须是整数。

示例

-- 转换前Oracle SQL：
select power(2,3),POWER(3,2) "Raised" from dual;
POWER(2,3)|Raised|
----------+------+
         8|     9|

-- 转换后LightDB-Oracle SQL：
SELECT power(2, 3),POWER(3, 2) AS "Raised" FROM dual
power|Raised|
-----+------+
8.0|   9.0|

4.2.1.12. SIGN

语法: SIGN(numeric_expression)
描述: 该函数返回输入数值的符号。符号为 1、-1 和 0。

参数解释

参数	说明
numeric_expression	是数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数。

示例

-- 转换前Oracle SQL：
select sign(20),SIGN(0),SIGN(-1),SIGN(-15) AS "sign" from dual;
sign  | sign | sign | sign
------+------+------+------
    1 |    0 |   -1 |   -1

-- 转换后LightDB-Oracle SQL：
SELECT sign(20),SIGN(0),SIGN(-1),SIGN(-15) AS "sign" FROM dual
sign|sign|sign|sign|
----+----+----+----+
 1.0| 0.0|-1.0|-1.0|

4.2.1.13. SQRT

语法: SQRT(numeric_expression)
描述: 该函数返回参数 numeric_expression 的平方根。

参数解释

参数	说明
numeric_expression	是数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或可以隐式转换为数值数据类型的参数。

示例

-- 转换前Oracle SQL：
SELECT sqrt(20),sqrt(0),sqrt(1),sqrt(2),sqrt(NULL) FROM DUAL;
SQRT(20)                                |SQRT(0)|SQRT(1)|SQRT(2)                                 |SQRT(NULL)|
----------------------------------------+-------+-------+----------------------------------------+----------+
4.47213595499957939281834733746255247088|      0|      1|1.41421356237309504880168872420969807857|          |


-- 转换后LightDB-Oracle SQL：
SELECT sqrt(20),sqrt(0),sqrt(1),sqrt(2),sqrt(NULL) FROM DUAL
sqrt            |sqrt|sqrt|sqrt              |sqrt|
----------------+----+----+------------------+----+
4.47213595499958| 0.0| 1.0|1.4142135623730951|    |

4.2.1.14. ROUND

语法: ROUND (numeric[,decimal])
描述: 该函数返回参数 numeric 四舍五入后的值。

参数解释

参数	说明
numeric	数值类型的（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）表达式。
y	如果 decimal 大于等于 0 则将 numeric 四舍五入到 decimal 位小数。如果 decimal 小于 0 则四舍五入到小数点向左第 decimal 位。当 decimal 不为整数时，截取 decimal 的整数部分。不指定 decimal 时，将 numeric 四舍五入到整数位。

示例

-- 转换前Oracle SQL：
SELECT ROUND(3, 10),ROUND(15.193,1),ROUND(15.193,-1),ROUND(0,10),ROUND(-3,10),ROUND(10) from dual;
ROUND(3,10)|ROUND(15.193,1)|ROUND(15.193,-1)|ROUND(0,10)|ROUND(-3,10)|ROUND(10)|
-----------+---------------+----------------+-----------+------------+---------+
          3|           15.2|              20|          0|          -3|       10|


-- 转换后LightDB-Oracle SQL：
SELECT ROUND(3, 10),ROUND(15.193, 1),ROUND(15.193, -1),ROUND(0, 10),ROUND(-3, 10),ROUND(10) FROM dual
round|round|round|round|round|round|
-----+-----+-----+-----+-----+-----+
   3| 15.2|   20|    0|   -3| 10.0|

4.2.1.15. TRUNC

语法: TRUNC (numeric[,precision])
描述: 该函数返回参数 numeric 按精度 precision 截取后的值

参数解释

参数	说明
numeric	表示被截取的数字
precision	表示精度，为可选项，默认值为 0

示例

-- 转换前Oracle SQL：
SELECT TRUNC(555.666,2.2),TRUNC(555.666,-2),TRUNC(555.666) FROM DUAL;
------------------+-----------------+--------------+
TRUNC(555.666,2.2)|TRUNC(555.666,-2)|TRUNC(555.666)|
------------------+-----------------+--------------+
            555.66|              500|           555|


-- 转换后LightDB-Oracle SQL：
SELECT TRUNC(555.666, 2.2),TRUNC(555.666, -2),TRUNC(555.666) FROM DUAL
trunc |trunc|trunc|
------+-----+-----+
555.66|  500|  555|

4.2.2. 字符串函数

4.2.2.1. ASCII

语法: ASCII(str)
描述: 该函数返回字符串 str 最左侧字符的 ASCII 码

参数解释

参数	说明
str	字符串.如果 str 为空字符串，则该函数返回 0。如果 str 为 NULL，则该函数返回 NULL

示例

-- 转换前Oracle SQL：
SELECT ASCII('a'),ASCII('ab'),ASCII('d'),ASCII('1'),ASCII(''),ASCII(NULL) FROM DUAL;
+-----------+-----------+------------+------------+-----------------------+
| ASCII('a')| ASCII('ab')| ASCII('d')| ASCII('1')| ASCII('')| ASCII(NULL)|
----------+-----------+----------+----------+---------+-----------+
|        97|         97|       100|        49|        0|           |
+-----------+-----------+------------+------------+-----------------------+

-- 转换后LightDB-Oracle SQL：
SELECT ASCII('a'),ASCII('ab'),ASCII('d'),ASCII('1'),ASCII(''),ASCII(NULL) FROM DUAL
ascii|ascii|ascii|ascii|ascii|ascii|
-----+-----+-----+-----+-----+-----+
   97|   97|  100|   49|     |     |

4.2.2.2. CONCAT

语法: CONCAT(char1,char2)
描述: 该函数用于连接两个字符串

参数解释

参数	说明
char1	字符串，字符串类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB
char2	字符串，字符串类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB

示例

-- 转换前Oracle SQL：
SELECT CONCAT('Hello',', world!') FROM DUAL;
CONCAT('HELLO',',WORLD!')|
-------------------------+
Hello, world!            |


SELECT CONCAT('Hello', ', world!') FROM DUAL
concat       |
-------------+
Hello, world!|

4.2.2.3. INSTR

语法: INSTR(str,substr)
描述: 返回字符串 str 中子字符串的第一个出现位置

参数解释

参数	说明
str	要操作的字符串
substr	子字符串

示例

-- 转换前Oracle SQL：
SELECT INSTR('Unisql','U') i1,INSTR('Unisql','u') i2,INSTR('Unisql','n') i3,INSTR('Unisql','a') i4,INSTR('Unisql','') i5,INSTR('Unisql',NULL) i6,INSTR('foobarbar', 'bar') i7, INSTR('xbar', 'foobar') i8,INSTR('北京欢迎你','北') i9  FROM DUAL;
--+--+--+--+--+--+--+--+--+
I1|I2|I3|I4|I5|I6|I7|I8|I9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0|  |  | 4| 0| 1|


-- 转换后LightDB-Oracle SQL：
SELECT INSTR('Unisql', 'U') AS i1,INSTR('Unisql', 'u') AS i2,INSTR('Unisql', 'n') AS i3,INSTR('Unisql', 'a') AS i4,INSTR('Unisql', '') AS i5,INSTR('Unisql', NULL) AS i6,INSTR('foobarbar', 'bar') AS i7,INSTR('xbar', 'foobar') AS i8,INSTR('▒▒ЙЕ╗ХМГ─с', '▒▒') AS i9 FROM DUAL
i1|i2|i3|i4|i5|i6|i7|i8|i9|
--+--+--+--+--+--+--+--+--+
1| 0| 2| 0|  |  | 4| 0| 1|

4.2.2.4. LOWER

语法: LOWER(char)
描述: 该函数将字符串中英文字母全部转为小写

参数解释

参数	说明
char	字符串，字符串类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB

示例

-- 转换前Oracle SQL：
SELECT  LOWER('AaBbCcDd') Lowercase FROM DUAL;
LOWERCASE|
---------+
aabbccdd |


SELECT LOWER('AaBbCcDd') AS Lowercase FROM DUAL
lowercase|
---------+
aabbccdd |

4.2.2.5. LENGTH

语法: LENGTH(str)
描述: 该函数返回 str 的字符长度

参数解释

参数	说明
str	要操作的字符串

示例

-- 转换前Oracle SQL：
SELECT LENGTH('中国'), LENGTH('hello') FROM DUAL;
LENGTH('中国')|LENGTH('HELLO')|
------------+---------------+
           2|              5|

-- 转换后LightDB-Oracle SQL：
SELECT LENGTH('中国'),LENGTH('hello') FROM DUAL
length|length|
------+------+
     2|     5|

4.2.2.6. LENGTHB

语法: LENGTHB(str)
描述: 该函数返回 str 的字节长度，与字符集的设置有关

参数解释

参数	说明
str	要操作的字符串

示例

-- 转换前Oracle SQL：
SELECT LENGTHB('中国'), LENGTHB('hello') FROM DUAL;
LENGTHB('中国')|LENGTHB('HELLO')|
-------------+----------------+
            6|               5|


-- 转换后LightDB-Oracle SQL：
SELECT LENGTHB('中国'),LENGTHB('hello') FROM DUAL
lengthb|lengthb|
-------+-------+
      6|      5|

4.2.2.7. LTRIM

语法: LTRIM(char,[set])
描述: 从 char 的左端删除集合 set 中包含的所有字符

参数解释

参数	说明
char	要操作的字符串
set	要删除的字符集合

示例

-- 转换前Oracle SQL：
SELECT LTRIM(' 2023-09-21 ', ' 0123'),LTRIM('  2023-09-21') FROM DUAL;
--------------------------+-------------------+
LTRIM('2023-09-21','0123')|LTRIM('2023-09-21')|
--------------------------+-------------------+
-09-21                    |2023-09-21         |


-- 转换后LightDB-Oracle SQL：
SELECT LTRIM(' 2023-09-21 ', ' 0123'),LTRIM('  2023-09-21') FROM DUAL
ltrim  |ltrim     |
-------+----------+
-09-21 |2023-09-21|

4.2.2.8. LPAD

语法: LPAD(str,len,padstr)
描述: 使用字符串 padstr 从左侧填充字符串 str，直到长度为 len 时为止

参数解释

参数	说明
str	要操作的字符串
len	要填充长度
padstr	要填充的字符串

示例

-- 转换前Oracle SQL：
SELECT
     LPAD('1223', 3),
     LPAD('1223', 4),
     LPAD('1223', 5),
     LPAD('1223', 6, '2+1'),
     LPAD('1223', 7, ''),
     LPAD('1223', 0),
     LPAD('1223',-1)
FROM dual;
LPAD('1223',3)|LPAD('1223',4)|LPAD('1223',5)|LPAD('1223',6,'2+1')|LPAD('1223',7,'')|LPAD('1223',0)|LPAD('1223',-1)|
--------------+--------------+--------------+--------------------+-----------------+--------------+---------------+
122           |1223          | 1223         |2+1223              |                 |              |               |


-- 转换后LightDB-Oracle SQL：
SELECT LPAD('1223', 3),LPAD('1223', 4),LPAD('1223', 5),LPAD('1223', 6, '2+1'),LPAD('1223', 7, ''),LPAD('1223', 0),LPAD('1223', -1) FROM dual
lpad|lpad|lpad |lpad  |lpad|lpad|lpad|
----+----+-----+------+----+----+----+
122 |1223| 1223|2+1223|    |    |    |

4.2.2.9. REPLACE

语法: REPLACE(char1,char2[,char3])
描述: 该函数将字符串 char1 中与 char2 匹配的子字符串替换为 char3

参数解释

参数	说明
char1	指定等待替换的字符串。数据类型可以是 CHAR 、VARCHAR2、NCHAR、NVARCHAR2 和 CLOB
char2	指定需要替换的字符串。数据类型可以是 CHAR 、VARCHAR2、NCHAR、NVARCHAR2 和 CLOB
char3	指定替换字符串，默认为空，表示删除，不是空格.说明如果 char3 缺省或者为 NULL，那么所有 char1 中出现的 char2 都将被移除。如果 char2 为空值，那么结果就是 char1

示例

-- 转换前Oracle SQL：
SELECT REPLACE('unisql','u','U') "replace" FROM DUAL;
replace|
-------+
Unisql |

-- 转换后LightDB-Oracle SQL：
SELECT REPLACE('unisql', 'u', 'U') AS "replace" FROM DUAL
replace|
-------+
Unisql |

4.2.2.10. REVERSE

语法: REVERSE(str)
描述: 该函数将字符串进行倒序

参数解释

参数	说明
str	要倒序的字符串

示例

-- 转换前Oracle SQL：
SELECT REVERSE('unIsql') FROM DUAL;
REVERSE('UNISQL')|
-----------------+
lqsInu           |

-- 转换后LightDB-Oracle SQL：
SELECT REVERSE('unIsql') FROM DUAL
reverse|
-------+
lqsInu |

4.2.2.11. REGEXP_INSTR

语法

REGEXP_INSTR (source_char, pattern
             [, position[, occurrence[, return_opt[, match_param[, subexpr] ] ] ] ]
          )

描述: 该函数作用是返回正则表达式匹配值在源字符串中的位置

参数解释

参数	说明
source_char	指定用作搜索值的字符表达式，数据类型可为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB。
pattern	指定正则表达式截取规则。它通常是一个文本文字，字符类型可为 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2。
position	指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1，表示从第一个字符开始搜索 source_char。
occurrence	指定 source_char 中第几个正则表达式匹配位置，为可选项。默认值为 1。如果 occurrence 大于 1，则从第一次匹配后的第一个字符开始搜索第二次正则表达式匹配，依此类推。
return_opt	指定返回出现匹配值位置选项，为可选项。默认值为 0。如果指定 0，则返回匹配的第一个字符的位置。如果指定 1，则返回匹配的最后字符后一位的位置。
match_param	指定更改正则表达式默认匹配方式，为可选项。是数据类型 VARCHAR2 或 CHAR 的字符表达式。
subexpr	指示 pattern 要返回的子表达式，是一个从 0 到 9 的非负整数，为可选项。默认值为 0，表示返回第一个子表达式出现的位置。

match_param取值	说明
i	表示大小写不敏感。
c	表示大小写敏感。
n	表示句点 . 可以匹配换行符。
m	表示多行模式。
x	表示忽略空格字符，默认情况下，空格字符会相互匹配。

示例

-- 转换前Oracle SQL：
SELECT
REGEXP_INSTR('http://www.example.com/products','http://([[:alnum:]]+\.?){3,4}/?') "regexp_str",
REGEXP_INSTR('1234567890','(123)(4(56)(78))'),
REGEXP_INSTR('Anderson', 'a|e|i|o|u')
FROM DUAL;
----------+---------------------------------------------+------------------------------------+
regexp_str|REGEXP_INSTR('1234567890','(123)(4(56)(78))')|REGEXP_INSTR('ANDERSON','A|E|I|O|U')|
----------+---------------------------------------------+------------------------------------+
         1|                                            1|                                   4|

-- 转换后LightDB-Oracle SQL：
SELECT REGEXP_INSTR('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') AS "regexp_str",REGEXP_INSTR('1234567890', '(123)(4(56)(78))'),REGEXP_INSTR('Anderson', 'a|e|i|o|u') FROM DUAL
regexp_str|regexp_instr|regexp_instr|
----------+------------+------------+
         1|           1|           4|

4.2.2.12. REGEXP_REPLACE

语法

REGEXP_REPLACE(source_char, pattern
              [, replace_string
                 [, position
                   [, occurrence
                     [, match_param ]
                   ]
                 ]
              ]
            )

描述: 该函数将字符串 source_char 中与正则表达式相匹配的字符替换为 replace_string 中的字符。

参数解释

参数	说明
source_char	指定用作搜索值的字符表达式。它通常是一种字符列。数据类型可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB 。
pattern	指定正则表达式，它通常是一个文本文字，数据类型可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2。
replace_string	表示替换的字符，可以是 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB 类型。
position	指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1，表示从第一个字符开始搜索 source_char。
occurrence	指定替换操作的第几个匹配项，是一个非负整数。
match_param	数据类型 VARCHAR2 或 CHAR 的字符表达式，它允许您更改函数的默认匹配行为。

match_param取值	说明
i	表示大小写不敏感。
c	表示大小写敏感。
n	表示句点 . 可以匹配换行符。
m	表示多行模式。
x	表示忽略空格字符，默认情况下，空格字符会相互匹配。

警告

PostgreSQL 最多只支持三个参数。

示例

-- 转换前Oracle SQL：
SELECT REGEXP_REPLACE('china', '(.)', '\1 '),REGEXP_REPLACE('Jane Doe','Jane', 'John'),REGEXP_REPLACE('515.123.4444','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','(\1) \2-\3') "REGEXP_REPLACE",REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA','( ){2,}', ' ') FROM DUAL;
REGEXP_REPLACE('CHINA','(.)','\1')|REGEXP_REPLACE('JANEDOE','JANE','JOHN')|REGEXP_REPLACE|REGEXP_REPLACE('500ORACLEPARKWAY,REDWOODSHORES,CA','(){2,}','')|
----------------------------------+---------------------------------------+--------------+---------------------------------------------------------------+
c h i n a                         |John Doe                               |(515) 123-4444|500 Oracle Parkway, Redwood Shores, CA                         |

-- 转换后LightDB-Oracle SQL：
SELECT REGEXP_REPLACE('china', '(.)', '\1 '),REGEXP_REPLACE('Jane Doe', 'Jane', 'John'),REGEXP_REPLACE('515.123.4444', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') AS "REGEXP_REPLACE",REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA', '( ){2,}', ' ') FROM DUAL
regexp_replace|regexp_replace|REGEXP_REPLACE|regexp_replace                        |
--------------+--------------+--------------+--------------------------------------+
c h i n a     |John Doe      |(515) 123-4444|500 Oracle Parkway, Redwood Shores, CA|

4.2.2.13. REGEXP_SUBSTR

语法

REGEXP_SUBSTR(source_char, pattern
         [, position [, occurrence ] ])

描述: 该函数允许使用正则表达式搜索字符串并返回匹配字符串

参数解释

参数	说明
source_char	指定用作搜索值的字符表达式
pattern	指定正则表达式截取规则。它通常是一个文本字面量
position	指定开始正则表达式匹配的起始位置，取值是一个正整数，为可选项。默认值是 1，表示从第一个字符开始搜索
occurrence	指定 source_char 中第几个正则表达式匹配位置，为可选项。默认值为 1

示例

-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+') from dual;
---------------------------------------------+
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+')|
---------------------------------------------+
a11                                          |

-- 转换后LightDB-Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+') FROM dual
regexp_substr|
-------------+
a11          |

-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1) from dual;
-----------------------------------------------+
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1)|
-----------------------------------------------+
a11                                            |

-- 转换后LightDB-Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1) FROM dual
regexp_substr|
-------------+
a11          |

-- 转换前Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL;
-------------------------------------------------+
REGEXP_SUBSTR('A11A22A33A44','A[[:DIGIT:]]+',1,2)|
-------------------------------------------------+
a22                                              |

-- 转换后LightDB-Oracle SQL：
SELECT regexp_substr('a11a22a33a44', 'a[[:digit:]]+', 1, 2) FROM DUAL
regexp_substr|
-------------+
a22          |

4.2.2.14. REGEXP_LIKE condition

语法: REGEXP_LIKE(source_char,pattern)
描述: 根据正则表达式进行匹配搜索

参数解释

参数	说明
source_char	是一个字符表达式，可作为搜索值
pattern	正则表达式

示例

-- 建表语句
CREATE TABLE unisql_regexp_like_test(id int,name varchar(50),hiredate varchar(50),salary int);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RAPHAELY', '2017-07-01', 1700);
INSERT INTO unisql_regexp_like_test VALUES(100, 'DE HAAN', '2018-05-01',11000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'ERRAZURIZ', '2017-07-21', 1400);
INSERT INTO unisql_regexp_like_test VALUES(100, 'HARTSTEIN', '2019-05-01',14000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RAPHAELY', '2017-07-22', 1700);
INSERT INTO unisql_regexp_like_test VALUES(100, 'WEISS',  '2019-07-11',13500);
INSERT INTO unisql_regexp_like_test VALUES(100, 'RUSSELL', '2019-10-05', 13000);
INSERT INTO unisql_regexp_like_test VALUES(100, 'PARTNERS',  '2018-12-01',14000);
INSERT INTO unisql_regexp_like_test VALUES(200, 'ROSS',  '2019-06-11',13500);
INSERT INTO unisql_regexp_like_test VALUES(200, 'BELL', '2019-05-25', 13000);
INSERT INTO unisql_regexp_like_test VALUES(200, 'PART',  '2018-08-11',14000);

-- 转换前Oracle SQL：
SELECT * FROM unisql_regexp_like_test WHERE regexp_like(name, '^R')
ID |NAME    |HIREDATE  |SALARY|
---+--------+----------+------+
100|RAPHAELY|2017-07-01|  1700|
100|RAPHAELY|2017-07-22|  1700|
100|RUSSELL |2019-10-05| 13000|
200|ROSS    |2019-06-11| 13500|


-- 转换后LightDB-Oracle SQL：
SELECT * FROM unisql_regexp_like_test WHERE regexp_like(name, '^R')
id |name    |hiredate  |salary|
---+--------+----------+------+
100|RAPHAELY|2017-07-01|  1700|
100|RAPHAELY|2017-07-22|  1700|
100|RUSSELL |2019-10-05| 13000|
200|ROSS    |2019-06-11| 13500|

4.2.2.15. RPAD

语法: RPAD(str,len,padstr)
描述: 使用字符串 padstr 从右侧填充字符串 str，直到长度为 len 时为止

参数解释

参数	说明
str	要操作的字符串
len	要填充长度
padstr	要填充的字符串

示例

-- 转换前Oracle SQL：
SELECT
     RPAD('1223', 3),
     RPAD('1223', 4),
     RPAD('1223', 5),
     RPAD('1223', 6, '2+1'),
     RPAD('1223', 7, ''),
     RPAD('1223', 0),
     RPAD('1223',-1)
FROM dual;
RPAD('1223',3)|RPAD('1223',4)|RPAD('1223',5)|RPAD('1223',6,'2+1')|RPAD('1223',7,'')|RPAD('1223',0)|RPAD('1223',-1)|
--------------+--------------+--------------+--------------------+-----------------+--------------+---------------+
122           |1223          |1223          |12232+              |                 |              |               |


-- 转换后LightDB-Oracle SQL：
SELECT RPAD('1223', 3),RPAD('1223', 4),RPAD('1223', 5),RPAD('1223', 6, '2+1'),RPAD('1223', 7, ''),RPAD('1223', 0),RPAD('1223', -1) FROM dual
rpad|rpad|rpad |rpad  |rpad|rpad|rpad|
----+----+-----+------+----+----+----+
122 |1223|1223 |12232+|    |    |    |

4.2.2.16. RTRIM

语法: RTRIM(char,[set])
描述: 从 char 的右端删除集合 set 中包含的所有字符

参数解释

参数	说明
char	要操作的字符串
set	要删除的字符集合

示例

-- 转换前Oracle SQL：
SELECT RTRIM(' 2023-09-21 ', ' 0123'),RTRIM('  2023-09-21') FROM DUAL;
RTRIM('2023-09-21','0123')|RTRIM('2023-09-21')|
--------------------------+-------------------+
2023-09-                 |  2023-09-21       |


-- 转换后LightDB-Oracle SQL：
SELECT RTRIM(' 2023-09-21 ', ' 0123'),RTRIM('  2023-09-21') FROM DUAL
rtrim    |rtrim       |
---------+------------+
2023-09-|  2023-09-21|

4.2.2.17. SUBSTR

语法

SUBSTR(str, pos)
SUBSTR(str, pos, len)

描述: 返回 str 的子字符串，起始位置为 pos，长度为 len。参数中包含 NULL 时，返回 NULL

参数解释

参数	说明
str	要操作的字符串
pos	子字符串的起始位置
len	子字符串的长度

示例

-- 转换前Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL;
-------------------+---------------------+--------------------+----------------------+
SUBSTR('ABCDEFG',3)|SUBSTR('ABCDEFG',3,2)|SUBSTR('ABCDEFG',-3)|SUBSTR('ABCDEFG',3,-2)|
-------------------+---------------------+--------------------+----------------------+
cdefg              |cd                   |efg                 |                      |


-- 转换后LightDB-Oracle SQL：
SELECT SUBSTR('abcdefg', 3),SUBSTR('abcdefg', 3, 2),SUBSTR('abcdefg', -3),SUBSTR('abcdefg', 3, -2) FROM DUAL
substr|substr|substr|substr|
------+------+------+------+
cdefg |cd    |efg   |      |

4.2.2.18. TRIM

语法: TRIM([[{BOTH | LEADING | TRAILING}] FROM] str
描述: 删除字符串所有前缀和/或后缀，默认为 BOTH。参数中包含 NULL 时，返回 NULL

参数解释

参数	说明
BOTH	删除字符串所有前缀和后缀
LEADING	删除字符串前缀
TRAILING	删除字符串后缀
str	要操作的字符串

示例

-- 转换前Oracle SQL：
SELECT TRIM(' bar '),TRIM(LEADING 'x' FROM 'xxxbarxxx'), TRIM(BOTH 'x' FROM 'xxxbarxxx'),TRIM(TRAILING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM NULL),TRIM(NULL) FROM DUAL;
TRIM('BAR')|TRIM(LEADING'X'FROM'XXXBARXXX')|TRIM(BOTH'X'FROM'XXXBARXXX')|TRIM(TRAILING'X'FROM'XXXBARXXX')|TRIM(BOTH'X'FROMNULL)|TRIM(NULL)|
-----------+-------------------------------+----------------------------+--------------------------------+---------------------+----------+
bar        |barxxx                         |bar                         |xxxbar                          |                     |          |

-- 转换后LightDB-Oracle SQL：
SELECT TRIM(' bar '),TRIM(LEADING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM 'xxxbarxxx'),TRIM(TRAILING 'x' FROM 'xxxbarxxx'),TRIM(BOTH 'x' FROM NULL),TRIM(NULL) FROM DUAL
btrim|ltrim |btrim|rtrim |btrim|btrim|
-----+------+-----+------+-----+-----+
bar  |barxxx|bar  |xxxbar|     |     |

4.2.2.19. UPPER

语法: UPPER(char)
描述: 该函数将字符串中英文字母全部转为大写

参数解释

参数	说明
char	要转换的字符串，数据类型可以为 CHAR、VARCHAR2、NCHAR、NVARCHAR2 或 CLOB.

示例

-- 转换前Oracle SQL：
SELECT UPPER('unisql') "UPPER" FROM DUAL;
UPPER |
------+
UNISQL|

-- 转换后LightDB-Oracle SQL：
SELECT UPPER('unisql') AS "UPPER" FROM DUAL
UPPER |
------+
UNISQL|

4.2.3. 时间日期函数

4.2.3.1. ADD_MONTHS

语法: ADD_MONTHS(date, n)
描述: 该函数功能是返回日期 date 加上 n 个月后的日期值。

参数解释

参数	说明
date	指定日期。该参数为 DATE 数据类型。
n	整数或可以转换为一个整数的任意值。NUMBER 数据类型。

示例

-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD') ,-3) AS a1,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 3)AS a2,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.1)AS a3,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.5)AS a4,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 1.8)AS a5,
ADD_MONTHS(TO_DATE('2023-12-21','YYYY-MM-DD'), 0)AS a6
FROM DUAL;
A1                     |A2                     |A3                     |A4                     |A5                     |A6                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2024-01-21 00:00:00.000|2023-12-21 00:00:00.000|

-- 转换后LightDB-Oracle SQL：
SELECT ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), -3) AS a1,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 3) AS a2,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 1.1) AS a3,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 1.5) AS a4,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 1.8) AS a5,ADD_MONTHS(TO_DATE('2023-12-21', 'YYYY-MM-DD'), 0) AS a6 FROM DUAL
a1                     |a2                     |a3                     |a4                     |a5                     |a6                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-21 00:00:00.000|2024-03-21 00:00:00.000|2024-01-21 00:00:00.000|2024-02-21 00:00:00.000|2024-02-21 00:00:00.000|2023-12-21 00:00:00.000|

4.2.3.2. CURRENT_DATE

语法: CURRENT_DATE
描述: 该函数返回当前会话时区的当前日期

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE           |
-----------------------+
2023-12-20 09:34:47.000|

-- 转换后LightDB-Oracle SQL：
SELECT localtimestamp(0) FROM DUAL
localtimestamp         |
-----------------------+
2023-12-20 09:29:07.000|

4.2.3.3. CURRENT_TIMESTAMP

语法: CURRENT_TIMESTAMP
描述: 该函数返回当前会话时区中的当前日期

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP            |
-----------------------------+
2023-12-20 09:36:55.612 +0800|


-- 转换后LightDB-Oracle SQL：
SELECT CURRENT_TIMESTAMP(0) FROM DUAL
current_timestamp      |
-----------------------+
2023-12-20 09:30:35.000|

4.2.3.4. TRUNC(date)

语法: TRUNC(date,[fmt])
描述: 该函数返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值，并且返回的日期值在 date 之前

参数解释

参数	说明
date	DATE 数据类型
fmt	指定了函数返回值与 date 的距离单位

fmt的取值如下：

fmt 参数的取值	说明
J	默认值，最近 0 点日期。
DAY、DY、D、DDD、DD	返回离指定日期最近的星期日。
MONTH、MON、MM、RM	返回离指定日期最近的月的第一天日期。
Q	返回离指定日期最近的季的日期。
YYYY、YYY、YY、Y	多个 y 表示不同的精度，返回离指定日期最近的年的第一个日期。
CC、SCC	返回离指定日期最近的世纪的初日期。

示例

-- 转换前Oracle SQL：
SELECT TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'YEAR'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'MONTH'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'DDD')FROM DUAL;
TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'YEAR')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'MONTH')|TRUNC(TO_DATE('2022/04/1910:32:34','YYYY/MM/DDHH24:MI:SS'),'DDD')|
------------------------------------------------------------------+-------------------------------------------------------------------+-----------------------------------------------------------------+
                                           2022-01-01 00:00:00.000|                                            2022-04-01 00:00:00.000|                                          2022-04-19 00:00:00.000|

-- 转换后LightDB-Oracle SQL：
SELECT TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'YEAR'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'MONTH'),TRUNC(TO_DATE('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS'), 'DDD') FROM DUAL
trunc                  |trunc                  |trunc                  |
-----------------------+-----------------------+-----------------------+
2022-01-01 00:00:00.000|2022-04-01 00:00:00.000|2022-04-19 00:00:00.000|

4.2.3.5. EXTRACT

语法: EXTRACT(unit FROM date)
描述: 以整数类型返回 date 的指定部分值

参数解释

参数	说明
date	日期类型参数

示例

-- 转换前Oracle SQL：
SELECT  EXTRACT(HOUR FROM TIMESTAMP '2023-09-06 12:38:40') "HOUR",
            EXTRACT(MINUTE FROM TIMESTAMP '2023-09-06 12:38:40') "MINUTE",
            EXTRACT(SECOND FROM TIMESTAMP '2023-09-06 12:38:40') "SECOND",
            EXTRACT(DAY FROM TIMESTAMP '2023-09-06 12:38:40') "DAY",
            EXTRACT(MONTH FROM TIMESTAMP '2023-09-06 12:38:40') "MONTH",
            EXTRACT(YEAR FROM TIMESTAMP '2023-09-06 12:38:40') "YEAR"
         FROM DUAL;
HOUR|MINUTE|SECOND|DAY|MONTH|YEAR|
----+------+------+---+-----+----+
  12|    38|    40|  6|    9|2023|

-- 转换后LightDB-Oracle SQL：
SELECT EXTRACT(HOUR FROM TIMESTAMP '2023-09-06 12:38:40') AS "HOUR",EXTRACT(MINUTE FROM TIMESTAMP '2023-09-06 12:38:40') AS "MINUTE",EXTRACT(SECOND FROM TIMESTAMP '2023-09-06 12:38:40') AS "SECOND",EXTRACT(DAY FROM TIMESTAMP '2023-09-06 12:38:40') AS "DAY",EXTRACT(MONTH FROM TIMESTAMP '2023-09-06 12:38:40') AS "MONTH",EXTRACT(YEAR FROM TIMESTAMP '2023-09-06 12:38:40') AS "YEAR" FROM DUAL
HOUR|MINUTE|SECOND|DAY|MONTH|YEAR  |
----+------+------+---+-----+------+
12.0|  38.0|  40.0|6.0|  9.0|2023.0|

4.2.3.6. LAST_DAY

语法: LAST_DAY(date)
描述: 返回 date 当月最后一天的日期值

参数解释

参数	说明
date	日期类型参数

示例

-- 转换前Oracle SQL：
SELECT LAST_DAY(SYSDATE),LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD')) FROM DUAL;
LAST_DAY(SYSDATE)      |LAST_DAY(TO_DATE('2023/09/21','YYYY/MM/DD'))|
-----------------------+--------------------------------------------+
2023-12-31 14:05:54.000|                     2023-09-30 00:00:00.000|


-- 转换后LightDB-Oracle SQL：
SELECT LAST_DAY(sysdate),LAST_DAY(TO_DATE('2023/09/21', 'YYYY/MM/DD')) FROM DUAL
last_day               |last_day               |
-----------------------+-----------------------+
2023-12-31 06:07:14.000|2023-09-30 00:00:00.000|

4.2.3.7. SYSDATE

语法: SYSDATE
描述: 返回当前日期和时间
参数解释: 无

示例

-- 转换前Oracle SQL：
SELECT SYSDATE FROM DUAL;
SYSDATE                |
-----------------------+
2023-12-20 09:51:46.000|

-- 转换后LightDB-Oracle SQL：
SELECT sysdate FROM DUAL
sysdate                |
-----------------------+
2023-12-20 01:46:46.000|

4.2.3.8. ROUND(date)

ROUND(date,[fmt])

该函数以参数 fmt 为单位距离返回离指定日期 date 最近的时间值，根据公历的规则运算。

-- 转换前Oracle SQL：
SELECT ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'YYYY') AS yyyy,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'Q') AS q,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MONTH') AS mont,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'DDD') AS ddd,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'HH') AS hh,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MI') AS mi from dual;
YYYY                   |Q                      |MONT                   |DDD                    |HH                     |MI                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-01-01 00:00:00.000|2023-04-01 00:00:00.000|2023-04-01 00:00:00.000|2023-03-21 00:00:00.000|2023-03-20 12:00:00.000|2023-03-20 12:12:00.000|

-- 转换后LightDB-Oracle SQL：
SELECT ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'YYYY') AS yyyy,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'Q') AS q,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MONTH') AS mont,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'DDD') AS ddd,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'HH') AS hh,ROUND(TO_DATE('2023-03-20 12:12:12', 'yyyy-MM-dd hh24:mi:ss'), 'MI') AS mi FROM dual
   yyyy                   |q                      |mont                   |ddd                    |hh                     |mi                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-01-01 00:00:00.000|2023-04-01 00:00:00.000|2023-04-01 00:00:00.000|2023-03-21 00:00:00.000|2023-03-20 12:00:00.000|2023-03-20 12:12:00.000|

4.2.3.9. SYSTIMESTAMP

语法: SYSTIMESTAMP
描述: 该函数返回系统当前日期和时间，返回值的秒的小数位包含 3 位精度，包含当前时区信息,依赖于当前数据库服务器所在操作系统的时区
参数解释: 无

示例

-- 转换前Oracle SQL：
SELECT SYSTIMESTAMP,SYSTIMESTAMP(0),SYSTIMESTAMP(1),SYSTIMESTAMP(2),SYSTIMESTAMP(3),SYSTIMESTAMP(4),SYSTIMESTAMP(5),SYSTIMESTAMP(6),SYSTIMESTAMP(7),SYSTIMESTAMP(8),SYSTIMESTAMP(9) FROM DUAL;
SYSTIMESTAMP                 |SYSTIMESTAMP(0)              |SYSTIMESTAMP(1)              |SYSTIMESTAMP(2)              |SYSTIMESTAMP(3)              |SYSTIMESTAMP(4)              |SYSTIMESTAMP(5)              |SYSTIMESTAMP(6)              |SYSTIMESTAMP(7)              |SYSTIMESTAMP(8)              |SYSTIMESTAMP(9)              |
-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+
2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:35.000 +0800|2023-12-20 09:56:34.900 +0800|2023-12-20 09:56:34.930 +0800|2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:34.932 +0800|2023-12-20 09:56:34.932 +0800|

-- 转换后LightDB-Oracle SQL：
SELECT systimestamp,SYSTIMESTAMP(0),SYSTIMESTAMP(1),SYSTIMESTAMP(2),SYSTIMESTAMP(3),SYSTIMESTAMP(4),SYSTIMESTAMP(5),SYSTIMESTAMP(6),SYSTIMESTAMP(7),SYSTIMESTAMP(8),SYSTIMESTAMP(9) FROM DUAL
systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |systimestamp           |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-12-20 10:50:31.293|2023-12-20 10:50:31.000|2023-12-20 10:50:31.300|2023-12-20 10:50:31.290|2023-12-20 10:50:31.293|2023-12-20 10:50:31.293|2023-12-20 10:50:31.293|2023-12-20 10:50:31.293|2023-12-20 10:50:31.293|2023-12-20 10:50:31.293|2023-12-20 10:50:31.293|

4.2.4. 通用比较函数

4.2.4.1. GREATEST

语法: GREATEST(value1, ...)
描述: 返回参数的最大值，和函数 LEAST() 相反

参数解释

参数	说明
value1, …	参数至少为两个（如果只提供一个参数，则会报错）。如果参数中有 NULL，则该函数返回为 NULL

示例

-- 转换前Oracle SQL：
SELECT GREATEST(2,1), GREATEST('2',1,0), GREATEST('a','b','c'), GREATEST('a', NULL, 'c'), GREATEST('2023-05-15','2023-09-06') from dual;
-------------+-----------------+---------------------+----------------------+-----------------------------------+
GREATEST(2,1)|GREATEST('2',1,0)|GREATEST('A','B','C')|GREATEST('A',NULL,'C')|GREATEST('2023-05-15','2023-09-06')|
-------------+-----------------+---------------------+----------------------+-----------------------------------+
            2|2                |c                    |                      |2023-09-06                         |

-- 转换后LightDB-Oracle SQL：
SELECT GREATEST(2, 1),GREATEST('2', 1, 0),GREATEST('a', 'b', 'c'),GREATEST('a', NULL, 'c'),GREATEST('2023-05-15', '2023-09-06') FROM dual
greatest|greatest|greatest|greatest|greatest  |
--------+--------+--------+--------+----------+
       2|2       |c       |        |2023-09-06|

4.2.4.2. LEAST

语法: LEAST(value1, ...)
描述: 返回参数的最小值，和 GREATEST() 函数相反

参数解释

参数	说明
value1, …	参数至少为两个；如果参数中有 NULL，返回值为 NULL

示例

-- 转换前Oracle SQL：
SELECT LEAST(2, null), LEAST('2',4,9), LEAST('a','b','c'), LEAST('a',NULL,'c'), LEAST('2014-05-15','2014-06-01') FROM dual;
-------------+--------------+------------------+-------------------+--------------------------------+
LEAST(2,NULL)|LEAST('2',4,9)|LEAST('A','B','C')|LEAST('A',NULL,'C')|LEAST('2014-05-15','2014-06-01')|
-------------+--------------+------------------+-------------------+--------------------------------+
            |2             |a                 |                   |2014-05-15                      |

-- 转换后LightDB-Oracle SQL：
SELECT LEAST(2, NULL),LEAST('2', 4, 9),LEAST('a', 'b', 'c'),LEAST('a', NULL, 'c'),LEAST('2014-05-15', '2014-06-01') FROM dual
least|least|least|least|least     |
-----+-----+-----+-----+----------+
     |2    |a    |     |2014-05-15|

4.2.5. 转换函数

4.2.5.1. CAST

语法: CAST (expr AS type_name )
描述: 该函数用于将一种内置数据类型转换为另一种内置数据类型

参数解释

参数	说明
expr	列名或者表达式。
AS	用于分隔两个参数，在 AS 之前的是要处理的数据，在 AS 之后是要转换的数据类型。
type_name	数据库的内建数据类型名称。

下表显示了哪些数据类型可以转换为其他内置数据类型

	from BINARY_FLOAT / BINARY_DOUBLE	from CHAR / VARCHAR2	from NUMBER	from DATETIME/INTERVAL	from RAW	from NCHAR/ NVARCHAR2
to BINARY_FLOAT/ BINARY_DOUBLE	yes	yes	yes	no	no	yes
to CHAR/ VARCHAR2	yes	yes	yes	yes	yes	no
to NUMBER	yes	yes	yes	no	no	yes
to DATETIME/INTERVAL	no	yes	no	yes	no	no
to RAW	yes	yes	yes	no	yes	no
to NCHAR/NVARCHAR2	yes	no	yes	yes	yes	yes

警告

使用 CAST 函数转换数据类型时，需要注意以下内容：

对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
注意在转换成RAW类型时，Oracle和LightDB-Oracle的实现有一定的差异，会出现执行结果不一致的情况。
对于类似cast(‘6983140795139873811265107033372908879759234567’ AS number)这种超过38位精度的数字，转换后的SQL在目标库执行会存在报错或者精度错误的情况。

示例

-- 转换前Oracle SQL：
select
cast(123 as BINARY_FLOAT),
cast(123 as BINARY_DOUBLE),
cast(1 as CHAR),
cast(123 as VARCHAR2(10)),
cast('123' as NUMBER),
cast('2023-12-20 10:07:55.222' as TIMESTAMP),
cast('2-6' AS INTERVAL YEAR TO MONTH),
cast('1 10:22:22' AS INTERVAL DAY TO SECOND),
cast('313233' AS RAW(10)),
cast(123 as NCHAR(3)),
cast(123 as NVARCHAR2(3))
from dual;
CAST(123ASBINARY_FLOAT)|CAST(123ASBINARY_DOUBLE)|CAST(1ASCHAR)|CAST(123ASVARCHAR2(10))|CAST('123'ASNUMBER)|CAST('2023-12-2010:07:55.222'ASTIMESTAMP)|CAST('2-6'ASINTERVALYEARTOMONTH)|CAST('110:22:22'ASINTERVALDAYTOSECOND)|CAST('313233'ASRAW(10))|CAST(123ASNCHAR(3))|CAST(123ASNVARCHAR2(3))|
-----------------------+------------------------+-------------+-----------------------+-------------------+-----------------------------------------+--------------------------------+--------------------------------------+-----------------------+-------------------+-----------------------+
                  123.0|                   123.0|1            |123                    |                123|                  2023-12-20 10:07:55.222|2-6                             |1 10:22:22.0                          |123                    |123                |123                    |


-- 转换后LightDB-Oracle SQL：
SELECT CAST(123 AS binary_float),CAST(123 AS binary_double),CAST(1 AS char),CAST(123 AS varchar2(10)),CAST('123' AS number),CAST('2023-12-20 10:07:55.222' AS timestamp),CAST('2-6' AS interval year to month),CAST('1 10:22:22' AS interval day to second(6)),CAST('313233' AS raw(10)),CAST(123 AS nchar(3)),CAST(123 AS nvarchar2(3)) FROM dual
binary_float|binary_double|bpchar|varchar2|numeric|timestamp              |interval                                     |interval                                        |raw   |bpchar|nvarchar2|
------------+-------------+------+--------+-------+-----------------------+---------------------------------------------+------------------------------------------------+------+------+---------+
       123.0|        123.0|1     |123     |    123|2023-12-20 10:07:55.222|2 years 6 mons 0 days 0 hours 0 mins 0.0 secs|0 years 0 mons 1 days 10 hours 22 mins 22.0 secs|313233|123   |123      |


-- 转换前Oracle SQL:
SELECT
     CAST('100.2345' AS NUMBER) AS to_nu,
     CAST('100.2345' AS NUMBER(*)) AS to_nu,
     CAST('100.2345' AS NUMBER(*,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(*,2)) AS to_nu,
     CAST('100.2345' AS NUMBER(4)) AS to_nu,
     CAST('100.2345' AS NUMBER(4,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(9)) AS to_nu,
     CAST('100.2345' AS NUMBER(9,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(18)) AS to_nu,
     CAST('100.2345' AS NUMBER(18,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(38)) AS to_nu,
     CAST('100.2345' AS NUMBER(38,0)) AS to_nu,
     CAST('100.2345' AS NUMBER(10,2)) AS to_nu,
     cast('100.2345' AS decimal) AS to_dec,
     cast('100.2345' AS decimal(*)) AS to_dec,
     cast('100.2345' AS decimal(*,0)) AS to_dec,
     cast('100.2345' AS decimal(*,2)) AS to_dec,
     cast('100.2345' AS decimal(4)) AS to_dec,
     cast('100.2345' AS decimal(4,0)) AS to_dec,
     cast('100.2345' AS decimal(9)) AS to_dec,
     cast('100.2345' AS decimal(9,0)) AS to_dec,
     CAST('100.2345' AS decimal(18)) AS to_dec,
     CAST('100.2345' AS decimal(18,0)) AS to_dec,
     CAST('100.2345' AS decimal(38)) AS to_dec,
     CAST('100.2345' AS decimal(38,0)) AS to_dec,
     CAST('100.2345' AS decimal(10,2)) AS to_dec,
     cast('100.2345' AS dec) AS to_dec,
     cast('100.2345' AS dec(*)) AS to_dec,
     cast('100.2345' AS dec(*,0)) AS to_dec,
     cast('100.2345' AS dec(*,2)) AS to_dec,
     cast('100.2345' AS dec(4)) AS to_dec,
     cast('100.2345' AS dec(4,0)) AS to_dec,
     cast('100.2345' AS dec(9)) AS to_dec,
     cast('100.2345' AS dec(9,0)) AS to_dec,
     CAST('100.2345' AS dec(18)) AS to_dec,
     CAST('100.2345' AS dec(18,0)) AS to_dec,
     CAST('100.2345' AS dec(38)) AS to_dec,
     CAST('100.2345' AS dec(38,0)) AS to_dec,
     CAST('100.2345' AS dec(10,2)) AS to_dec,
     cast('100.2345' AS numeric) AS to_numr,
     cast('100.2345' AS numeric(*)) AS to_numr,
     cast('100.2345' AS numeric(*,0)) AS to_numr,
     cast('100.2345' AS numeric(*,2)) AS to_numr,
     cast('100.2345' AS numeric(4)) AS to_numr,
     cast('100.2345' AS numeric(4,0)) AS to_numr,
     cast('100.2345' AS numeric(9)) AS to_numr,
     cast('100.2345' AS numeric(9,0)) AS to_numr,
     CAST('100.2345' AS numeric(18)) AS to_numr,
     CAST('100.2345' AS numeric(18,0)) AS to_numr,
     CAST('100.2345' AS numeric(38)) AS to_numr,
     CAST('100.2345' AS numeric(38,0)) AS to_numr,
     CAST('100.2345' AS numeric(10,2)) AS to_numr
FROM dual
TO_NU   |TO_NU   |TO_NU|TO_NU |TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU|TO_NU |TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_DEC|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|TO_NUMR|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345|  100|100.23|  100|  100|  100|  100|  100|  100|  100|  100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|    100|    100|    100| 100.23|    100|    100|    100|    100|    100|    100|    100|    100| 100.23|

-- 转换后LightDB-Oracle SQL：
SELECT CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number) AS to_nu,CAST('100.2345' AS number(38,0)) AS to_nu,CAST('100.2345' AS number(38,2)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(4)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(9)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(18)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(38)) AS to_nu,CAST('100.2345' AS number(10,2)) AS to_nu,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,0)) AS to_dec,CAST('100.2345' AS decimal(38,2)) AS to_dec,CAST('100.2345' AS decimal(4)) AS to_dec,CAST('100.2345' AS decimal(4)) AS to_dec,CAST('100.2345' AS decimal(9)) AS to_dec,CAST('100.2345' AS decimal(9)) AS to_dec,CAST('100.2345' AS decimal(18)) AS to_dec,CAST('100.2345' AS decimal(18)) AS to_dec,CAST('100.2345' AS decimal(38)) AS to_dec,CAST('100.2345' AS decimal(38)) AS to_dec,CAST('100.2345' AS decimal(10,2)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,0)) AS to_dec,CAST('100.2345' AS dec(38,2)) AS to_dec,CAST('100.2345' AS dec(4)) AS to_dec,CAST('100.2345' AS dec(4)) AS to_dec,CAST('100.2345' AS dec(9)) AS to_dec,CAST('100.2345' AS dec(9)) AS to_dec,CAST('100.2345' AS dec(18)) AS to_dec,CAST('100.2345' AS dec(18)) AS to_dec,CAST('100.2345' AS dec(38)) AS to_dec,CAST('100.2345' AS dec(38)) AS to_dec,CAST('100.2345' AS dec(10,2)) AS to_dec,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,0)) AS to_numr,CAST('100.2345' AS numeric(38,2)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(4)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(9)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(18)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(38)) AS to_numr,CAST('100.2345' AS numeric(10,2)) AS to_numr FROM dual
to_nu   |to_nu   |to_nu|to_nu |to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu|to_nu |to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_dec|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|to_numr|
--------+--------+-----+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
100.2345|100.2345|  100|100.23|  100|  100|  100|  100|  100|  100|  100|  100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|   100|   100|   100|100.23|   100|   100|   100|   100|   100|   100|   100|   100|100.23|    100|    100|    100| 100.23|    100|    100|    100|    100|    100|    100|    100|    100| 100.23|

4.2.5.2. NUMTODSINTERVAL

语法: NUMTODSINTERVAL(number, 'interval_unit')
描述: 该函数作用是将一个数值表达式加时间间隔单位转换为一个 INTERVAL DAY TO SECOND 数据类型的值。可以用来对一个日期时间值进行加减计算。

参数解释

参数	说明
number	指定间隔数量，是一个 NUMBER 值或可以隐式转换为 NUMBER 值的表达式。
interval_unit	指定间隔单位，可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的值，不区分大小写。默认情况下，返回的精度为 9

interval_unit的取值如下：

DAY, 表示天。
HOUR, 表示小时。
MINUTE ,表示分钟。
SECOND, 表示秒。

示例

-- 转换前Oracle SQL：
SELECT NUMTODSINTERVAL(10,'day'),
NUMTODSINTERVAL(10,'HOUR'),
NUMTODSINTERVAL(10,'MINUTE'),
NUMTODSINTERVAL(10,'SECOND')
FROM DUAL;
NUMTODSINTERVAL(10,'DAY')|NUMTODSINTERVAL(10,'HOUR')|NUMTODSINTERVAL(10,'MINUTE')|NUMTODSINTERVAL(10,'SECOND')|
-------------------------+--------------------------+----------------------------+----------------------------+
10 0:0:0.0               |0 10:0:0.0                |0 0:10:0.0                  |0 0:0:10.0                  |


-- 转换后LightDB-Oracle SQL：
SELECT NUMTODSINTERVAL(10, 'day'),NUMTODSINTERVAL(10, 'HOUR'),NUMTODSINTERVAL(10, 'MINUTE'),NUMTODSINTERVAL(10, 'SECOND') FROM DUAL
numtodsinterval                               |numtodsinterval                               |numtodsinterval                               |numtodsinterval                               |
----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+
0 years 0 mons 10 days 0 hours 0 mins 0.0 secs|0 years 0 mons 0 days 10 hours 0 mins 0.0 secs|0 years 0 mons 0 days 0 hours 10 mins 0.0 secs|0 years 0 mons 0 days 0 hours 0 mins 10.0 secs|

4.2.5.3. NUMTOYMINTERVAL

语法: NUMTOYMINTERVAL(number, 'interval_unit')
描述: 该函数作用是将一个数值表达式加时间间隔单位转换为一个 INTERVAL DAY TO SECOND 数据类型的值。可以用来对一个日期时间值进行加减计算。

参数解释

参数	说明
number	指定间隔数量，是一个 NUMBER 值或可以隐式转换为 NUMBER 值的表达式。
interval_unit	指定间隔单位，可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的值，不区分大小写。默认情况下，返回的精度为 9。间隔单位：YEAR 表示年。MONTH 表示月。

示例

-- 转换前Oracle SQL：
SELECT NUMTOYMINTERVAL(1,'YEAR'),
NUMTOYMINTERVAL(1,'MONTH'),
SYSDATE+NUMTOYMINTERVAL(1,'YEAR'),
SYSDATE+NUMTOYMINTERVAL(1,'MONTH')
FROM DUAL;
NUMTOYMINTERVAL(1,'YEAR')|NUMTOYMINTERVAL(1,'MONTH')|SYSDATE+NUMTOYMINTERVAL(1,'YEAR')|SYSDATE+NUMTOYMINTERVAL(1,'MONTH')|
-------------------------+--------------------------+---------------------------------+----------------------------------+
1-0                      |0-1                       |          2024-12-20 13:40:28.000|           2024-01-20 13:40:28.000|


-- 转换后LightDB-Oracle SQL：
SELECT NUMTOYMINTERVAL(1, 'YEAR'),NUMTOYMINTERVAL(1, 'MONTH'),sysdate+NUMTOYMINTERVAL(1, 'YEAR'),sysdate+NUMTOYMINTERVAL(1, 'MONTH') FROM DUAL
numtoyminterval                              |numtoyminterval                              |?column?               |?column?               |
---------------------------------------------+---------------------------------------------+-----------------------+-----------------------+
1 years 0 mons 0 days 0 hours 0 mins 0.0 secs|0 years 1 mons 0 days 0 hours 0 mins 0.0 secs|2024-12-21 07:16:53.000|2024-01-21 07:16:53.000|

4.2.5.4. TO_BLOB

语法: TO_BLOB( raw_value )
描述: 该函数将 LONG RAW 值和 RAW 值转换为 BLOB 值

参数解释

参数	说明
raw_value	LONG RAW 或 RAW 类型的值

示例

-- create table
CREATE TABLE unisql_test_blob (c1 RAW(100),c2 LONG RAW);

INSERT INTO unisql_test_blob (c1, c2) VALUES (HEXTORAW('FFAABB'), EMPTY_BLOB());
INSERT INTO unisql_test_blob (c1, c2) VALUES (HEXTORAW('112233'), EMPTY_BLOB());

-- 转换前Oracle SQL：
SELECT LENGTH(c1) "LENGTH_RAW",LENGTH(TO_BLOB(c1)) "LENGTH_BLOB" FROM unisql_test_blob
LENGTH_RAW|LENGTH_BLOB|
----------+-----------+
         6|          3|
         6|          3|

-- 转换后LightDB-Oracle SQL：
SELECT LENGTH(c1) AS "LENGTH_RAW",LENGTH(TO_BLOB(c1)) AS "LENGTH_BLOB" FROM unisql_test_blob
LENGTH_RAW|LENGTH_BLOB|
----------+-----------+
         3|          3|
         3|          3|

4.2.5.5. TO_CHAR (character)

语法: TO_CHAR(character)
描述: 该函数将 NCHAR、NVARCHAR2 或 CLOB 类型的数据转换为 VARCHAR2 数据类型。

参数解释

参数	说明
character	指定要转换为 VARCHAR2 数据类型表达式，数据类型可以是 NCHAR、NVARCHAR2 或 CLOB

示例

-- 转换前Oracle SQL：
SELECT TO_CHAR('010101') FROM DUAL;
TO_CHAR('010101')|
-----------------+
010101           |


-- 转换后LightDB-Oracle SQL：
SELECT TO_CHAR('010101') FROM DUAL
to_char|
-------+
010101 |

4.2.5.6. TO_CHAR(datetime)

语法: TO_CHAR(datetime [, fmt [, 'nlsparam' ] ])
描述: 该函数将一个日期时间或间隔数据转换为一个字符串类型的值，使用指定的格式字符串fmt来格式化输出字符串。

参数解释

参数	说明
datetime	datetime 属于日期时间， DATE、TIMESTAMP数据类型
fmt	指定输出格式

示例

-- 转换前Oracle SQL：
SELECT
TO_CHAR(TO_TIMESTAMP('2023-04-20 17:31:12.66', 'YYYY-MM-DD HH24:MI:SS.FF'),'HH12:MI:SS') AS a1,
TO_CHAR(TO_DATE('2023-04-20', 'YYYY-MM-DD'),'YYYY-MM-DD') AS a2
FROM DUAL;
A1      |A1        |
--------+----------+
05:31:12|2023-04-20|

-- 转换后LightDB-Oracle SQL：
SELECT TO_CHAR(TO_TIMESTAMP('2023-04-20 17:31:12.66', 'YYYY-MM-DD HH24:MI:SS.FF'), 'HH12:MI:SS') AS a1,TO_CHAR(TO_DATE('2023-04-20', 'YYYY-MM-DD'), 'YYYY-MM-DD') AS a2 FROM DUAL
a1      |a2        |
--------+----------+
05:31:12|2023-04-20|

4.2.5.7. TO_CHAR (number)

语法: TO_CHAR(n [, fmt [, 'nlsparam' ] ])
描述: 该函数将 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型的数值按照指定数值格式转换为 varchar2 数据类型的值。

参数解释

参数	说明
n	属于 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。
fmt	输出格式参数，为可选项。如果省略 fmt，则 n 被转换为 VARCHAR2 值。取值信息请参见 fmt 参数列表。
nlsparam	定义十进制字符、组分隔符、当地货币符号和国际货币符号，为可选项。

fmt参数列表:

fmt 参数取值	说明
9	返回指定位数的值。
0	它返回前导 0，它返回尾随 0。
,（逗号）	返回指定位置的逗号。可以在数字格式模型中指定多个逗号。限制条件：格式模型数值不能以逗号开头，且逗号不能出现在小数字符或句点的右边。
.（小数点）	返回一个小数，且小数点在指定位置。限制条件：在数字格式模型中，只能指定一个小数点。

示例

-- 转换前Oracle SQL：
SELECT TO_CHAR(123456789.123,'999,999,999.909') FROM DUAL;
TO_CHAR(123456789.123,'999,999,999.909')|
----------------------------------------+
123,456,789.123                        |

-- 转换后LightDB-Oracle SQL：
SELECT TO_CHAR(123456789.123, '999,999,999.909') FROM DUAL
to_char         |
----------------+
 123,456,789.123|

4.2.5.8. TO_CLOB

语法: TO_CLOB(lob_column | char)
描述: 该函数将 LOB 列或其他字符串中的 NCLOB 值转换为 CLOB 值。

参数解释

参数	说明
lob_column	属于 LOB 列或其他字符串中的 NCLOB 值。
char	属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB 或 NCLOB 类型的值。

示例

CREATE TABLE unisql_test_clob (c1 clob,c2 varchar2(10));
INSERT INTO unisql_test_clob VALUES (TO_CLOB('1'),'orale');

-- 转换前Oracle SQL：
SELECT TO_CLOB(c1),TO_CLOB(c2),TO_CLOB(10)  FROM unisql_test_clob;
TO_CLOB(C1)|TO_CLOB(C2)|TO_CLOB(10)|
-----------+-----------+-----------+
1          |orale      |10         |


-- 转换后LightDB-Oracle SQL：
SELECT CAST(c1 AS text),CAST(c2 AS text),CAST(10 AS text) FROM unisql_test_clob
c1|c2   |text|
--+-----+----+
1 |orale|10  |

4.2.5.9. TO_DATE

语法: TO_DATE(char [, fmt])
描述: 该函数将 CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的字符转换为日期数据类型的值

参数解释

参数	说明
char	CHAR、VARCHAR、NCHAR 或 NVARCHAR2 数据类型的值
fmt	指定 char 的时间格式

示例

-- 转换前Oracle SQL：
select to_date('2012/12/12', 'yyyy-MM-dd'),
to_date('2023-12-02', 'yyyy-mm-dd'),
to_date('2023-12-02 11:23:33', 'yyyy-mm-dd HH24:MI:SS')
FROM DUAL;
TO_DATE('2012/12/12','YYYY-MM-DD')|TO_DATE('2023-12-02','YYYY-MM-DD')|TO_DATE('2023-12-0211:23:33','YYYY-MM-DDHH24:MI:SS')|
----------------------------------+----------------------------------+----------------------------------------------------+
           2012-12-12 00:00:00.000|           2023-12-02 00:00:00.000|                             2023-12-02 11:23:33.000|

-- 转换后LightDB-Oracle SQL：
SELECT to_date('2012/12/12', 'yyyy-MM-dd'),to_date('2023-12-02', 'yyyy-mm-dd'),to_date('2023-12-02 11:23:33', 'yyyy-mm-dd HH24:MI:SS') FROM DUAL
to_date                |to_date                |to_date                |
-----------------------+-----------------------+-----------------------+
2012-12-12 00:00:00.000|2023-12-02 00:00:00.000|2023-12-02 11:23:33.000|

4.2.5.10. TO_NUMBER

语法: TO_NUMBER(expr)
描述: 该函数功能是将 CHAR、VARCHAR2等类型的字符串转换为 NUMBER 数值数据类型的值

参数解释

参数	说明
expr	属于 CHAR、VARCHAR2、NCHAR、NVARCHAR2、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。

示例

-- 转换前Oracle SQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL;
TO_NUMBER('0123456')|TO_NUMBER('1.2')|TO_NUMBER('0')|TO_NUMBER(1.2)|TO_NUMBER('-1')|TO_NUMBER(-1)|
--------------------+----------------+--------------+--------------+---------------+-------------+
              123456|             1.2|             0|           1.2|             -1|           -1|

-- 转换后LightDB-Oracle SQL：
SELECT TO_NUMBER('0123456'),TO_NUMBER('1.2'),TO_NUMBER('0'),TO_NUMBER(1.2),TO_NUMBER('-1'),TO_NUMBER(-1) FROM DUAL
to_number|to_number|to_number|to_number|to_number|to_number|
---------+---------+---------+---------+---------+---------+
   123456|      1.2|        0|      1.2|       -1|       -1|

4.2.5.11. TO_TIMESTAMP

语法: TO_TIMESTAMP (char，[fmt])
描述: 该函数将字符串转换为 TIMESTAMP 数据类型

参数解释

参数	说明
datetime	datetime 属于日期时间， DATE、TIMESTAMP数据类型
fmt	指定输出格式

示例

-- 转换前Oracle SQL：
SELECT TO_TIMESTAMP ('2023-03-17 14:10:10', 'YYYY-MM-DD HH24:MI:SS') AS t1, to_timestamp('2023-12-20', 'yyyy-mm-dd') AS t2,to_timestamp('2023-12-20 02', 'yyyy-mm-dd hh24') AS t3 FROM DUAL;
T1                     |T2                     |T3                     |
-----------------------+-----------------------+-----------------------+
2023-03-17 14:10:10.000|2023-12-20 00:00:00.000|2023-12-20 02:00:00.000|

-- 转换后LightDB-Oracle SQL：
SELECT TO_TIMESTAMP('2023-03-17 14:10:10', 'YYYY-MM-DD HH24:MI:SS') AS t1,to_timestamp('2023-12-20', 'yyyy-mm-dd') AS t2,to_timestamp('2023-12-20 02', 'yyyy-mm-dd hh24') AS t3 FROM DUAL
t1                     |t2                     |t3                     |
-----------------------+-----------------------+-----------------------+
2023-03-17 14:10:10.000|2023-12-20 00:00:00.000|2023-12-20 02:00:00.000|

4.2.5.12. TO_NCHAR (character)

语法: TO_NCHAR(character)
描述: 该函数将 CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据转换为国家字符集，返回 NVARCHAR2 数据类型。

参数解释

参数	说明
character	CHAR、VARCHAR2、CLOB 或 NCLOB 类型的数据

示例

CREATE TABLE unisql_test_nchar(col1 INT,col2 VARCHAR2(20));
INSERT INTO unisql_test_nchar VALUES(1,'unisql tool');

-- 转换前Oracle SQL：
SELECT TO_NCHAR(col1),TO_NCHAR(col2) FROM unisql_test_nchar;
TO_NCHAR(COL1)|TO_NCHAR(COL2)|
--------------+--------------+
1             |unisql tool   |

-- 转换后LightDB-Oracle SQL：
SELECT CAST(col1 AS text),CAST(col2 AS text) FROM unisql_test_nchar
col1|col2       |
----+-----------+
1   |unisql tool|

4.2.5.13. TO_NCHAR (datetime)

语法: TO_NCHAR({ datetime | interval }[, fmt [, 'nlsparam' ] ])
描述: 该函数将 DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL YEAR TO MONTH 或 INTERVAL DAY TO SECOND 等数据类型的值从数据库字符集转换为国家字符集的数据类型

参数解释

参数	说明
datetime	DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 等数据类型的值。
interval	INTERVAL DAY TO SECOND 和 INTERVAL YEAR TO MONTH 数据类型的值
fmt	指定输出格式
nlsparam	用来控制返回的月份和日份所使用的语言，为可选项。

interval取值	说明
YEAR	表示年。
MONTH	表示月。
DAY	表示天。
HOUR	表示小时。
MINUTE	表示分钟。
SECOND	表示秒数。

4.2.5.14. TO_NCHAR (number)

语法: TO_NCHAR(n)
描述: 该函数将 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 类型的数值转换为国家字符集中的字符串。

参数解释

参数	说明
n	属于 NUMBER、BINARY_FLOAT 或 BINARY_DOUBLE 数据类型的数值。

示例

-- 转换前Oracle SQL：
SELECT TO_NCHAR(123456789.123) FROM DUAL;
TO_NCHAR(123456789.123)|
-----------------------+
123456789.123          |

-- 转换后LightDB-Oracle SQL：
SELECT CAST(123456789.123 AS text) FROM DUAL
text         |
-------------+
123456789.123|

4.2.5.15. RAWTOHEX

该函数将一个字节数组转换为十六进制字符串。

-- 转换前Oracle SQL：
SELECT RAWTOHEX(SYS_GUID()) FROM DUAL;
RAWTOHEX(SYS_GUID())            |
--------------------------------+
0D003A97D52E7EB7E0632989140A93CC|

-- 转换后LightDB-Oracle SQL：
SELECT replace(SYS_GUID(), '-', '') FROM DUAL
replace                         |
--------------------------------+
874cb2d1c2f24023a5f16d1525043d6b|

4.2.5.16. ROWIDTOCHAR

该函数将 rowid 值转换为 VARCHAR2 数据类型的值参考示例如下：

CREATE TABLE unisql_rowidtochar_test (
 id NUMBER,
 name VARCHAR(50)
);

INSERT INTO unisql_rowidtochar_test (id, name) VALUES (1, 'Alice');
INSERT INTO unisql_rowidtochar_test (id, name) VALUES (2, 'Bob');

-- 转换前Oracle SQL：
SELECT ROWIDTOCHAR(ROWID) AS char_rowid FROM unisql_rowidtochar_test;
CHAR_ROWID        |
------------------+
AAA2i1AAMAAIbEjAAA|
AAA2i1AAMAAIbEjAAB|

-- 转换后LightDB-Oracle SQL：
SELECT ROWIDTOCHAR(ROWID) AS char_rowid FROM unisql_rowidtochar_test
char_rowid|
----------+
(0,1)     |
(0,2)     |

4.2.6. 编码解码函数

4.2.6.1. DECODE

语法: DECODE (condition, search, result [, search, result ...][, default])
描述: 该函数功能是将 condition 与每个 search 依次做比较，并返回对比结果

参数解释

参数	说明
condition	数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式
search	数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式
result	数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式
default	数值数据类型（NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE）或字符数据类型（ CHAR、VARCHAR2、NCHAR 或 NVARCHAR2）的值或表达式

示例

-- 转换前Oracle SQL：
SELECT DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大') FROM DUAL;
DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大')|
------------------------------------------------------------+
(5*3-2)大                                                    |

-- 转换后LightDB-Oracle SQL：
SELECT DECODE(SIGN((5*3-2)-(3*4-1)), 0, '相等', 1, '(5*3-2)┤з', '(3*4-1)┤з') FROM DUAL
decode  |
--------+
(5*3-2)大|

4.2.7. 空值函数

4.2.7.1. NVL

语法: NVL(expr1, expr2)
描述: 该函数从两个表达式返回一个非空值。如果 expr1 与 expr2 的结果都为空值，则 NVL 函数返回 NULL

参数解释

参数	说明
expr1	指定第一个参数，数据类型可以是数据库内建数据类型中的任何数据类型。
expr2	指定第二个参数，数据类型可以是数据库内建数据类型中的任何数据类型。

示例

-- 转换前Oracle SQL：
SELECT NVL(10,'1'),NVL(NULL,1),NVL(0/1,1) FROM DUAL;
NVL(10,'1')|NVL(NULL,1)|NVL(0/1,1)|
-----------+-----------+----------+
         10|          1|         0|

-- 转换后LightDB-Oracle SQL：
SELECT NVL(10, '1'),NVL(NULL, 1),NVL(0/1, 1) FROM DUAL
nvl|nvl|nvl|
---+---+---+
10|1  |  0|

4.2.7.2. NULLIF

语法: NULLIF(expr1, expr2)
描述: 该函数用于比较两个参数表达式是否相等。如果 expr1 与 expr2 相等，则返回 NULL。如果 expr1 与 expr2 不相等，则返回 expr1。

参数解释

参数	说明
expr1	指定第一个参数，可以是任意数据类型的表达式。
expr2	指定第二个参数，如果 expr1 不是数值数据类型，则 expr2 的数据类型必须与 expr1 相同，否则数据库将返回错误。

示例

-- 转换前Oracle SQL：
SELECT NULLIF(3+4,6+1),NULLIF(3+4,6+2),NULLIF('3+4','6+1') FROM DUAL;
NULLIF(3+4,6+1)|NULLIF(3+4,6+2)|NULLIF('3+4','6+1')|
---------------+---------------+-------------------+
               |              7|3+4                |

-- 转换后LightDB-Oracle SQL：
SELECT NULLIF(3+4, 6+1),NULLIF(3+4, 6+2),NULLIF('3+4', '6+1') FROM DUAL
nullif|nullif|nullif|
------+------+------+
      |     7|3+4   |

4.2.7.3. IS NULL

空值比较

-- 转换前Oracle SQL：
SELECT * FROM  unisql_employee WHERE employee_id IS NULL;
EMPLOYEE_ID|NAME|SALARY|DEPARTMENT_ID|HIRE_DATE|COMMISSION_PCT|
-----------+----+------+-------------+---------+--------------+

-- 转换后LightDB-Oracle SQL：
SELECT * FROM unisql_employee WHERE employee_id IS NULL
employee_id|name|salary|department_id|hire_date|commission_pct|
-----------+----+------+-------------+---------+--------------+

4.2.7.4. EMPTY_BLOB, EMPTY_CLOB

empty_blob和empty_clob返回一个空的LOB定位器。空意味着LOB是初始化的，但没有填充数据。

DROP TABLE unisql_blob_test;
CREATE TABLE unisql_blob_test (id number,blob_data blob);

-- 在insert语句中的使用
INSERT INTO unisql_blob_test (id,blob_data) VALUES (1,EMPTY_BLOB());
INSERT INTO unisql_blob_test (id,blob_data) VALUES (2,EMPTY_BLOB());
INSERT INTO unisql_blob_test (id,blob_data) VALUES (3,EMPTY_BLOB());
INSERT INTO unisql_blob_test (id,blob_data) VALUES (3,'blob data');

SELECT * FROM unisql_blob_test;

-- 在update语句中的使用
UPDATE unisql_blob_test SET blob_data = EMPTY_BLOB() WHERE id = 4;

4.2.8. 环境和标识符函数

4.2.8.1. SYS_GUID

语法: SYS_GUID()
描述: 该函数生成并返回由 16 个字节组成的全局唯一标识符，即生成一个全局唯一序列号

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT SYS_GUID() FROM DUAL;
SYS_GUID()      |
----------------+
íx OH xàc)   çt|

-- 转换后LightDB-Oracle SQL：
SELECT SYS_GUID() FROM DUAL;
------------------------------------+
SYS_GUID                            |
------------------------------------+
c0f0d0f9-4ca1-11ee-b63c-78ac443a561e|

4.2.8.2. SYS_CONTEXT

该函数返回当前时刻与上下文命名空间关联的参数的值。

-- 转换前Oracle SQL：
SELECT SYS_CONTEXT ('USERENV', 'SID') FROM DUAL;
SYS_CONTEXT('USERENV','SID')|
----------------------------+
13168                       |

-- 转换后LightDB-Oracle SQL：
SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL
sys_context|
-----------+
18337      |

4.2.8.3. USER

语法: USER
描述: 返回当前的用户名与主机名
参数解释: 无

示例

-- 转换前Oracle SQL：
SELECT USER FROM DUAL;
USER  |
------+
SYSTEM|

-- 转换后LightDB-Oracle SQL：
SELECT USER FROM DUAL
user   |
-------+
lightdb|

4.2.9. 层次函数

4.2.9.1. SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH(column,'char')

该函数返回从根到节点的列值的路径，由 CONNECT BY 条件返回的每一行的列值用指定分隔符号分隔。

CREATE TABLE unisql_sys_connect_by_path_test(z_id int,name varchar(20),n_id int);
INSERT INTO unisql_sys_connect_by_path_test VALUES(0,'ABC','');
INSERT INTO unisql_sys_connect_by_path_test VALUES(1,'An',0);
INSERT INTO unisql_sys_connect_by_path_test VALUES(2,'Bn',0);
INSERT INTO unisql_sys_connect_by_path_test VALUES(3,'Cn',0);
INSERT INTO unisql_sys_connect_by_path_test VALUES(4,'A1',1);
INSERT INTO unisql_sys_connect_by_path_test VALUES(5,'B1',2);
INSERT INTO unisql_sys_connect_by_path_test VALUES(6,'C1',3);
INSERT INTO unisql_sys_connect_by_path_test VALUES(7,'C2',6);
INSERT INTO unisql_sys_connect_by_path_test VALUES(8,'A2',4);
INSERT INTO unisql_sys_connect_by_path_test VALUES(9,'B2',5);
INSERT INTO unisql_sys_connect_by_path_test VALUES(10,'A3',8);
INSERT INTO unisql_sys_connect_by_path_test VALUES(11,'A4',10);
INSERT INTO unisql_sys_connect_by_path_test VALUES(12,'B3',9);

-- 转换前Oracle SQL：
SELECT z_id,name,n_id,SYS_CONNECT_BY_PATH(name, '/') "Path"
FROM unisql_sys_connect_by_path_test
WHERE n_id in(0,1,2)
START WITH n_id IS NOT NULL
CONNECT BY PRIOR n_id = z_id
ORDER BY z_id;
Z_ID|NAME|N_ID|Path           |
----+----+----+---------------+
   1|An  |   0|/A4/A3/A2/A1/An|
   1|An  |   0|/A2/A1/An      |
   1|An  |   0|/A3/A2/A1/An   |
   1|An  |   0|/A1/An         |
   1|An  |   0|/An            |
   2|Bn  |   0|/B3/B2/B1/Bn   |
   2|Bn  |   0|/B1/Bn         |
   2|Bn  |   0|/B2/B1/Bn      |
   2|Bn  |   0|/Bn            |
   3|Cn  |   0|/C2/C1/Cn      |
   3|Cn  |   0|/C1/Cn         |
   3|Cn  |   0|/Cn            |
   4|A1  |   1|/A3/A2/A1      |
   4|A1  |   1|/A1            |
   4|A1  |   1|/A2/A1         |
   4|A1  |   1|/A4/A3/A2/A1   |
   5|B1  |   2|/B1            |
   5|B1  |   2|/B3/B2/B1      |
   5|B1  |   2|/B2/B1         |

-- 转换后LightDB-Oracle SQL：
SELECT z_id,name,n_id,SYS_CONNECT_BY_PATH(name, '/') AS "Path" FROM unisql_sys_connect_by_path_test WHERE n_id IN (0,1,2) START WITH n_id IS NOT NULL CONNECT BY PRIOR n_id=z_id ORDER BY z_id
z_id|name|n_id|Path           |
----+----+----+---------------+
   1|An  |   0|/An            |
   1|An  |   0|/A1/An         |
   1|An  |   0|/A2/A1/An      |
   1|An  |   0|/A3/A2/A1/An   |
   1|An  |   0|/A4/A3/A2/A1/An|
   2|Bn  |   0|/B1/Bn         |
   2|Bn  |   0|/Bn            |
   2|Bn  |   0|/B3/B2/B1/Bn   |
   2|Bn  |   0|/B2/B1/Bn      |
   3|Cn  |   0|/C1/Cn         |
   3|Cn  |   0|/Cn            |
   3|Cn  |   0|/C2/C1/Cn      |
   4|A1  |   1|/A4/A3/A2/A1   |
   4|A1  |   1|/A2/A1         |
   4|A1  |   1|/A3/A2/A1      |
   4|A1  |   1|/A1            |
   5|B1  |   2|/B2/B1         |
   5|B1  |   2|/B1            |
   5|B1  |   2|/B3/B2/B1      |

4.2.10. 聚合函数

4.2.10.1. AVG

语法: AVG([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数将数值类型或者可以转换成数值类型的表达式作为参数求平均值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT 或 ALL	查询时是否去重。为可选项，默认值 ALL。ALL：查询时不去除数据中的重复值，且忽略数据中的 NULL 值。DISTINCT：查询时去除数据中的重复值，且忽略数据中的 NULL 值。
expr	指定要计算的列名。列的数据类型是数值类型或者可以转换成数值类型的表达式。数值类型可以为 NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE。
OVER	使用 OVER 子句定义窗口进行计算

示例

CREATE TABLE unisql_employee(employee_id int,name varchar(30),salary int,department_id int,hire_date varchar(10),commission_pct number);
INSERT INTO unisql_employee values(1,'JACK',5000,1,'2023-01-01',0.05);
INSERT INTO unisql_employee values(2,'TOM',10000,1,'2023-02-01',0.15);
INSERT INTO unisql_employee values(3,'LINDA',15000,1,'2023-03-01',0.20);
INSERT INTO unisql_employee values(4,'ADA',20000,2,'2023-04-01',0.10);
INSERT INTO unisql_employee values(5,'TINA',30000,2,'2023-05-01',0.20);
INSERT INTO unisql_employee values(6,'KATE',50000,3,'2023-06-01',0.30);

-- 转换前Oracle SQL：
SELECT AVG(salary) FROM unisql_employee;
AVG(SALARY)                             |
----------------------------------------+
21666.6666666666666666666666666666666667|

-- 转换后LightDB-Oracle SQL：
SELECT AVG(salary) FROM unisql_employee
avg               |
------------------+
21666.666666666667|

4.2.10.2. CORR

语法: CORR(expr1, expr2) [ OVER (analytic_clause) ]
描述: 该函数用于计算一组数值对的相关系数

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr1	指定第一个参数，属于数值数据类型或可以隐式转换为数值数据类型的值。
expr2	指定第二个参数，属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算

示例

CREATE TABLE unisql_students (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   age INTEGER,
   grade INTEGER
);

INSERT INTO unisql_students (id,name, age, grade) VALUES(1,'Alice', 20, 85);
INSERT INTO unisql_students (id,name, age, grade) VALUES(2,'Bob', 22, 78);
INSERT INTO unisql_students (id,name, age, grade) VALUES(3,'Charlie', 21, 92);
INSERT INTO unisql_students (id,name, age, grade) VALUES(4,'David', 23, 75);
INSERT INTO unisql_students (id,name, age, grade) VALUES(5,'Emma', 22, 88);
INSERT INTO unisql_students (id,name, age, grade) VALUES(6,'Frank', 24, 79);
INSERT INTO unisql_students (id,name, age, grade) VALUES(7,'Grace', 20, 90);
INSERT INTO unisql_students (id,name, age, grade) VALUES(8,'Harry', 22, 84);
INSERT INTO unisql_students (id,name, age, grade) VALUES(9,'Isabella', 21, 90);
INSERT INTO unisql_students (id,name, age, grade) VALUES(10,'Jack', 23, 87);
INSERT INTO unisql_students (id,name, age, grade) VALUES(11,'Katherine', 24, 78);
INSERT INTO unisql_students (id,name, age, grade) VALUES(12,'Liam', 20, 95);
INSERT INTO unisql_students (id,name, age, grade) VALUES(13,'Mia', 22, 82);

-- 计算年龄和成绩之间的相关系数
-- 转换前Oracle SQL：
SELECT CORR(age, grade) AS age_grade_corr FROM unisql_students;
AGE_GRADE_CORR                             |
-------------------------------------------+
-0.7343934402070001857892159538585929477569|

-- 转换后LightDB-Oracle SQL：
SELECT CORR(age, grade) AS age_grade_corr FROM unisql_students
age_grade_corr     |
-------------------+
-0.7343934402070003|

4.2.10.3. COUNT

语法: COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
描述: 该函数用于返回查询 expr 的行数。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
星号(*)	表示返回满足条件的所有行，且包含重复行和空行。
DISTINCT ALL	查询时是否去重。为可选项，默认值 ALL。ALL：返回所有值，包含重复行，且忽略空行。DISTINCT：返回的行中去除重复行，且忽略空行。
expr	指定参与计算的列名。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT count(1),count(*),count(employee_id),count(DISTINCT(department_id)) FROM unisql_employee ke;
COUNT(1)|COUNT(*)|COUNT(EMPLOYEE_ID)|COUNT(DISTINCT(DEPARTMENT_ID))|
--------+--------+------------------+------------------------------+
      6|       6|                 6|                             3|


-- 转换后LightDB-Oracle SQL：
SELECT count(1),count(1),count(employee_id),count(DISTINCT (department_id)) FROM unisql_employee AS ke
count|count|count|count|
-----+-----+-----+-----+
    6|    6|    6|    3|

4.2.10.4. COVAR_POP

语法: COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
描述: 该函数用于计算一组数值对的样本协方差。可以将该函数用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr1	指定第一个数值表达式，属于数值数据类型或可以隐式转换为数值数据类型的值。
expr2	指定第二个数值表达式，属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 计算总体协方差
-- 转换前Oracle SQL：
SELECT COVAR_POP(age, grade) AS pop_covariance FROM unisql_students;
POP_COVARIANCE                           |
-----------------------------------------+
-5.86982248520710059171597633136094674615|

-- 转换后LightDB-Oracle SQL：
SELECT COVAR_POP(age, grade) AS pop_covariance FROM unisql_students
pop_covariance    |
------------------+
-5.869822485207101|

4.2.10.5. COVAR_SAMP

语法: COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
描述: 该函数用于计算一组数值对的样本协方差。可以将该函数用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr1	指定第一个数值表达式，属于数值数据类型或可以隐式转换为数值数据类型的值。
expr2	指定第二个数值表达式，属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 计算样本协方差
-- 转换前Oracle SQL：
SELECT COVAR_SAMP(age, grade) AS samp_covariance FROM unisql_students;
SAMP_COVARIANCE                        |
---------------------------------------+
-6.358974358974358974358974358974358975|

-- 转换后LightDB-Oracle SQL：
SELECT COVAR_SAMP(age, grade) AS samp_covariance FROM unisql_students
samp_covariance  |
-----------------+
-6.35897435897436|

4.2.10.6. CUME_DIST

语法

/*聚合语法*/
CUME_DIST( expr[,expr]...)
WITHIN GROUP (ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...)

/*分析语法*/
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)

描述: 该函数用于计算一组值中的某个值的累积分布，返回值的范围为 (0,1]。可以将此函数用作聚合或分析函数。

说明

作为分析函数，该函数计算指定值在一组值中的相对位置。对于行 row，假设按升序排列，row 的 cume_dist 是值低于或等于 row 值的行数除以被计算的行数（整个查询结果集或分区）。
作为聚合函数，该函数的参数标识每个聚合组中的单个假设行。因此，它们必须全部计算为每个聚合组内的常量表达式。常量参数表达式和 ORDER BY 聚合子句中的表达式按位置匹配。因此，参数的数量必须相同，并且它们的类型必须兼容。如果指定值与分组序列中某值重复，则将两个重复的值视为一个值处理。

参数解释

参数	说明
expr	指定要查询的值。
expr_col	指定要查询的值对应的列名。
DESC	ASC	指定列表的排序方式，为可选项。 * ASC 为升序排序，为默认值。 * DESC 为降序排序。
NULLS { FIRST	LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。 * NULLS FIRST 表示 NULL 值排在非空值的前面。 * NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 20岁 在列 age 中的位置
-- 转换前Oracle SQL：
SELECT CUME_DIST(20) WITHIN GROUP (ORDER BY age) FROM unisql_students;
CUME_DIST(20)WITHINGROUP(ORDERBYAGE)      |
------------------------------------------+
0.2857142857142857142857142857142857142857|

-- 转换后LightDB-Oracle SQL：
SELECT CUME_DIST(20) WITHIN GROUP (ORDER BY age) FROM unisql_students
cume_dist         |
------------------+
0.2857142857142857|

4.2.10.7. DENSE_RANK

语法

/*聚合语法*/
DENSE_RANK(expr [, expr ...]) WITHIN GROUP ( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]... )

/*分析语法*/
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

描述: 该函数功能是计算有序行组中行的秩。秩的值是从 1 开始的连续整数，最大的秩值是符合查询结果的数值。具有相同值的排序标准的行接收相同的秩，相同的行数不会被记录到下个排名中。可以将其用作聚合或分析函数。

说明

作为聚合函数，DENSE_RANK 根据指定的排序规范计算由函数的参数标识的假设行的密集等级。常量参数表达式 expr 和 order_by_clause 聚合中的表达式按位置匹配。因此，参数的数量必须相同并且类型必须兼容。
作为一个分析函数，DENSE_RANK 根据 order_by_clause 中 value_exprs 的值，计算从查询返回的每一行相对于其他行的秩。

参数解释

参数	说明
expr	对应列中数据类型的值。
expr_col	指定要查询的值对应的列名。
DESC \| ASC	指定列表的排序方式，为可选项。ASC 为升序排序，默认值。DESC 为降序排序。
NULLS { FIRST \| LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。NULLS FIRST 表示 NULL 值排在非空值的前面。NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT DENSE_RANK(15500, .05) WITHIN GROUP (ORDER BY salary DESC, commission_pct) "Dense Rank" FROM unisql_employee;
Dense Rank|
----------+
         4|

-- 转换后LightDB-Oracle SQL：
SELECT DENSE_RANK(15500, 0.05) WITHIN GROUP (ORDER BY salary DESC,commission_pct) AS "Dense Rank" FROM unisql_employee
Dense Rank|
----------+
         4|

-- 转换前Oracle SQL：
SELECT DENSE_RANK(30000,0.06) WITHIN GROUP (ORDER BY salary desc,commission_pct) AS denseRank FROM unisql_employee ke;
DENSERANK|
---------+
      2|

-- 转换后LightDB-Oracle SQL：
SELECT DENSE_RANK(30000, 0.06) WITHIN GROUP (ORDER BY salary DESC,commission_pct) AS denseRank FROM unisql_employee AS ke
denserank|
---------+
      2|

4.2.10.8. LISTAGG

语法: LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
描述: 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值

参数解释

参数	说明
measure_expr	可以是任何表达式。度量列中的空值将被忽略。
delimiter	指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。

示例

-- 转换前Oracle SQL：
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1;
RK              |
----------------+
JACK; TOM; LINDA|

-- 转换后LightDB-Oracle SQL：
SELECT LISTAGG(name, '; ') WITHIN GROUP (ORDER BY employee_id,name) AS rk FROM unisql_employee WHERE department_id=1
   rk              |
----------------+
JACK; TOM; LINDA|


-- 使用语法词ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR
CREATE TABLE listagg_test_employees (
emp_id int,
emp_name VARCHAR(100),
department_id int
);

-- 插入数据
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (1, 'John Doe', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (2, 'Jane Smith', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (3, 'Mark Johnson', 2);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (4, 'Emily Davis', 2);

SELECT LISTAGG(emp_name ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW TRUNCATE) over(partition by department_id) rk FROM listagg_test_employees;

SELECT LISTAGG(emp_name ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW ERROR) over(partition by department_id) rk FROM listagg_test_employees;

4.2.10.9. MAX

语法: MAX ([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数返回参数中指定的列中的最大值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT 或 ALL	查询时是否去重。为可选项，默认值 ALL。ALL：返回所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT：返回的行中去除重复行，且忽略值为 NULL 的行。
expr	可为数值、字符、日期型或其它类型的数据列或表达式。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 转换前Oracle SQL：
SELECT MAX(salary),MAX(1),MAX(DISTINCT department_id) FROM unisql_employee ke;
MAX(SALARY)|MAX(1)|MAX(DISTINCTDEPARTMENT_ID)|
-----------+------+--------------------------+
      50000|     1|                         3|

-- 转换后LightDB-Oracle SQL：
SELECT MAX(salary),MAX(1),MAX(DISTINCT department_id) FROM unisql_employee AS ke
max  |max|max|
-----+---+---+
50000|  1|  3|

4.2.10.10. MEDIAN

语法: MEDIAN(expr) [ OVER (query_partition_clause) ]
描述: 该函数用于返回一组数值的中值，即将一组数值排序后返回居于中间的数值。如果参数集合中包含偶数个数值，该函数将返回位于中间的两个数的平均值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定要求中值的数组名称，参数类型属于数值数据类型或可以隐式转换为数字数据类型。
OVER	使用 OVER 子句定义窗口进行计算。详细信息请参见分析函数说明。

示例

-- 转换前Oracle SQL：
SELECT department_id, MEDIAN(salary) FROM unisql_employee ke GROUP BY department_id;
DEPARTMENT_ID|MEDIAN(SALARY)|
-------------+--------------+
            1|         10000|
            2|         25000|
            3|         50000|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,MEDIAN(salary) FROM unisql_employee AS ke GROUP BY department_id
department_id|median |
-------------+-------+
            3|50000.0|
            2|25000.0|
            1|10000.0|

4.2.10.11. MIN

语法: MIN([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数返回参数中指定列的最小值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT \| UNIQUE \| ALL	查询时是否去重，为可选项。默认值 ALL。ALL：返回所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT：返回的行中去除重复行，且忽略值为 NULL 的行。
expr	可为数值、字符、日期型或其它数据类型的列或表达式。
OVER	使用 OVER 子句定义窗口进行计算。详细信息请参见分析函数说明。

示例

-- 转换前Oracle SQL：
SELECT MIN(SALARY),MIN(1),MIN(DISTINCT department_id) FROM unisql_employee;
MIN(SALARY)|MIN(1)|MIN(DISTINCTDEPARTMENT_ID)|
-----------+------+--------------------------+
      5000|     1|                         1|


-- 转换后LightDB-Oracle SQL：
SELECT MIN(SALARY),MIN(1),MIN(DISTINCT department_id) FROM unisql_employee
min |min|min|
----+---+---+
5000|  1|  1|

4.2.10.12. PERCENT_RANK

语法

/* 聚合语法 */
PERCENT_RANK(expr [, expr ...]) WITHIN GROUP ( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]  [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]] ...)

/* 分析语法 */
PERCENT_RANK( ) OVER ([query_partition_clause] order_by_clause)

警告

统一SQL暂未支持聚合语法的解析

4.2.10.13. PERCENTILE_CONT

语法

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expr [ DESC | ASC ])
[ OVER (query_partition_clause) ]

描述: 该函数是一个假定连续分布模型的逆分布函数。根据指定百分比值和排序规范，返回一个在该排序规范的给定百分比值的内插值。在计算中忽略空值。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
percentile	指定百分位的值，是一个数值数据类型的常量，取值范围为 [0 1]。说明 MEDIAN 函数相当于是 percentile 为 0.5 的特殊情况。
expr	指定排序规范的表达式，数据类型属于数值类型或日期时间类型。注意 expr 必须为涉及列引用的单个表达式，不允许使用多个表达式。
DESC	ASC	指定列表的排序方式，为可选项。ASC 为升序排序，为默认值。DESC 为降序排序。
OVER	使用 OVER 子句定义窗口进行计算

示例

CREATE TABLE unisql_test_data (
 id int PRIMARY KEY,
 value INTEGER
);

INSERT INTO unisql_test_data(id,value) VALUES (1,10);
INSERT INTO unisql_test_data(id,value) VALUES (2,20);
INSERT INTO unisql_test_data(id,value) VALUES (3,30);
INSERT INTO unisql_test_data(id,value) VALUES (4,40);
INSERT INTO unisql_test_data(id,value) VALUES (5,50);
INSERT INTO unisql_test_data(id,value) VALUES (6,60);
INSERT INTO unisql_test_data(id,value) VALUES (7,70);
INSERT INTO unisql_test_data(id,value) VALUES (8,80);
INSERT INTO unisql_test_data(id,value) VALUES (9,90);
INSERT INTO unisql_test_data(id,value) VALUES (10,100);

-- 计算 value 列的给定百分位数的连续值
-- 转换前Oracle SQL：
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) FROM unisql_test_data;
PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYVALUE)|
---------------------------------------------+
                                           55|

-- 转换后LightDB-Oracle SQL：
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) FROM unisql_test_data
percentile_cont|
---------------+
           55.0|

4.2.10.14. PERCENTILE_DISC

语法

PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]

描述: 该函数根据一个百分位值和指定排序规范，返回大于或等于百分位值的最小累积分布值（相对于同一排序规范）的值。在计算中忽略空值。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr1	指定百分位的值，是一个数值数据类型的常量，取值范围为 [0	1]。
expr2	指定用于排序和计算百分比的表达式，数据类型属于数值类型或日期时间类型。
DESC \| ASC	指定列表的排序方式，为可选项。ASC 为升序排序，为默认值。DESC 为降序排序。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 计算指定百分位数的值
-- 转换前Oracle SQL：
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY value) AS median_value FROM unisql_test_data;
MEDIAN_VALUE|
------------+
         50|

-- 转换后LightDB-Oracle SQL：
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY value) AS median_value FROM unisql_test_data
median_value|
------------+
         50|

4.2.10.15. RANK

语法

/*聚合语法*/
RANK(expr [, expr ]...) WITHIN GROUP( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [, expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...)

/*分析语法*/
RANK() OVER ([ query_partition_clause ] order_by_clause)

描述: 该函数用于计算一个值在一组值的排名。当有相同排序值时，将会有相同的排名，并且值相同的行数会被记录到下个排名中。可以将此函数用作聚合或分析函数

说明

作为聚合函数，RANK 计算由参数 expr 标识的假设行相对于指定排序规范的排名。参数表达式 expr 和 ORDER BY 聚合子句中的表达式按位置匹配。因此，参数的数量必须相同，并且它们的数据类型必须兼容。
作为分析函数，RANK 计算从查询相对于由查询返回的其他行返回的每一行的秩，基于所述值 value_exprs 中 order_by_clause。

参数解释

参数	说明
expr	指定要查询的值。
expr_col	指定要查询的值对应的列名。
DESC \| ASC	指定列表的排序方式，为可选项。ASC 为升序排序，默认值。DESC 为降序排序。
NULLS { FIRST \| LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。NULLS FIRST 表示 NULL 值排在非空值的前面。NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT RANK(40) WITHIN GROUP (ORDER BY id DESC) "Rank of 40" FROM unisql_test_data;
Rank of 40|
----------+
         1|

-- 转换后LightDB-Oracle SQL：
SELECT RANK(40) WITHIN GROUP (ORDER BY id DESC) AS "Rank of 40" FROM unisql_test_data
Rank of 40|
----------+
         1|

4.2.10.16. STDDEV

语法

STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]

描述: 该函数用于计算一组数值型数据标准差

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT \| ALL	查询时是否去重。为可选项，默认值 ALL。ALL：全部数值列。DISTINCT：去重关键字，表示计算唯一值的总体标准差。
expr	数值类型或者可以转换成数值类型的值。
OVER	使用 OVER 子句定义窗口进行计算

示例

CREATE TABLE unisql_test_data (
 id int PRIMARY KEY,
 value INTEGER
);

INSERT INTO unisql_test_data(id,value) VALUES (1,10);
INSERT INTO unisql_test_data(id,value) VALUES (2,20);
INSERT INTO unisql_test_data(id,value) VALUES (3,30);
INSERT INTO unisql_test_data(id,value) VALUES (4,40);
INSERT INTO unisql_test_data(id,value) VALUES (5,50);
INSERT INTO unisql_test_data(id,value) VALUES (6,60);
INSERT INTO unisql_test_data(id,value) VALUES (7,70);
INSERT INTO unisql_test_data(id,value) VALUES (8,80);
INSERT INTO unisql_test_data(id,value) VALUES (9,90);
INSERT INTO unisql_test_data(id,value) VALUES (10,100);

-- 样本标准偏差
-- 转换前Oracle SQL：
SELECT STDDEV(value) AS sample_stddev FROM unisql_test_data;
SAMPLE_STDDEV                            |
-----------------------------------------+
30.27650354097491665422532809718193699195|

-- 转换后LightDB-Oracle SQL：
SELECT STDDEV(value) AS sample_stddev FROM unisql_test_data
sample_stddev      |
-------------------+
30.2765035409749167|

4.2.10.17. STDDEV_POP

语法

STDDEV_POP( [ALL] expr) [ OVER (analytic_clause) ]

描述: 该函数将数值型数据作为参数计算总体标准差。总体标准差是总体方差的算术平方根。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
ALL	指定全部数值列，为可选项。默认值为 ALL。
expr	数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或者可以转换成数值类型的表达式。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 总体标准偏差
-- 转换前Oracle SQL：
SELECT STDDEV_POP(value) AS pop_stddev FROM unisql_test_data;
POP_STDDEV                              |
----------------------------------------+
28.7228132326901432992530573410946465911|

-- 转换后LightDB-Oracle SQL：
SELECT stddev_pop(value) AS pop_stddev FROM unisql_test_data
pop_stddev         |
-------------------+
28.7228132326901433|

4.2.10.18. STDDEV_SAMP

语法

STDDEV_SAMP([ALL] expr) [ OVER (analytic_clause) ]

描述: 该函数将数值型数据作为参数计算样本标准差，样本标准差是样本方差的算术平方根。STDDEV_SAMP 与函数 STDDEV 的不同之处在于，STDDEV 只有一行输入数据时返回 0，而 STDDEV_SAMP 返回 NULL。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
ALL	全部数值列。为可选项，默认值 ALL。
expr	数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或者可以转换成数值类型的表达式。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 样本标准偏差
-- 转换前Oracle SQL：
SELECT STDDEV_SAMP(value) AS samp_stddev FROM unisql_test_data;
SAMP_STDDEV                              |
-----------------------------------------+
30.27650354097491665422532809718193699195|

-- 转换后LightDB-Oracle SQL：
SELECT STDDEV_SAMP(value) AS samp_stddev FROM unisql_test_data
samp_stddev        |
-------------------+
30.2765035409749167|

4.2.10.19. SUM

语法: SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数返回指定参数的总和，可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定参与计算的数值表达式。属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT sum(salary),sum(1),sum(DISTINCT department_id) FROM unisql_employee ke;
SUM(SALARY)|SUM(1)|SUM(DISTINCTDEPARTMENT_ID)|
-----------+------+--------------------------+
     130000|     6|                         6|

-- 转换后LightDB-Oracle SQL：
SELECT sum(salary),sum(1),sum(DISTINCT department_id) FROM unisql_employee AS ke
sum   |sum|sum|
------+---+---+
130000|  6|  6|

4.2.10.20. VAR_POP

语法: VAR_POP(expr) [ OVER (analytic_clause) ]
描述: 该函数返回一组数值集合的总体方差（忽略 NULL）。可以将该函数用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定参与计算的数值表达式。属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 总体方差
-- 转换前Oracle SQL：
SELECT VAR_POP(value) AS pop_variance FROM unisql_test_data;
POP_VARIANCE|
------------+
         825|

-- 转换后LightDB-Oracle SQL：
SELECT var_pop(value) AS pop_variance FROM unisql_test_data
pop_variance|
------------+
         825|

4.2.10.21. VAR_SAMP

语法: VAR_SAMP(expr) [ OVER (analytic_clause) ]
描述: 该函数返回一组数值集合的样本方差（忽略 NULL）。可以将该函数用作聚合或分析函数

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定参与计算的数值表达式。属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 样本方差
-- 转换前Oracle SQL：
SELECT VAR_SAMP(value) AS samp_variance FROM unisql_test_data;
SAMP_VARIANCE                           |
----------------------------------------+
916.666666666666666666666666666666666667|

-- 转换后LightDB-Oracle SQL：
SELECT VAR_SAMP(value) AS samp_variance FROM unisql_test_data
samp_variance       |
--------------------+
916.6666666666666667|

4.2.10.22. VARIANCE

语法: VARIANCE([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数功能是返回指定列的方差。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT \| ALL	查询时是否去重。为可选项，默认值 ALL。ALL：查询时不去除数据中的重复值，且忽略数据中的 NULL 值。DISTINCT：查询时去除数据中的重复值，且忽略数据中的 NULL 值。
expr	数值数据类型或任何可以隐式转换为数值数据类型的表达式。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 计算年龄的样本方差
-- 转换前Oracle SQL：
SELECT VARIANCE(age) AS age_variance FROM unisql_students;
AGE_VARIANCE                          |
--------------------------------------+
1.974358974358974358974358974358974359|


-- 转换后LightDB-Oracle SQL：
SELECT VARIANCE(age) AS age_variance FROM unisql_students
age_variance      |
------------------+
1.9743589743589744|

4.2.11. 分析函数

4.2.11.1. AVG

语法: AVG([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数将数值类型或者可以转换成数值类型的表达式作为参数求平均值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT 或 ALL	查询时是否去重。为可选项，默认值 ALL。ALL：查询时不去除数据中的重复值，且忽略数据中的 NULL 值。DISTINCT：查询时去除数据中的重复值，且忽略数据中的 NULL 值。
expr	指定要计算的列名。列的数据类型是数值类型或者可以转换成数值类型的表达式。数值类型可以为 NUMBER、FLOAT、BINARY_FLOAT 或 BINARY_DOUBLE。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT employee_id,salary,department_id,AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS avgRes FROM unisql_employee ke GROUP BY department_id,salary ,employee_id;
EMPLOYEE_ID|SALARY|DEPARTMENT_ID|AVGRES|
-----------+------+-------------+------+
         1|  5000|            1|  5000|
         2| 10000|            1|  7500|
         3| 15000|            1| 10000|
         4| 20000|            2| 20000|
         5| 30000|            2| 25000|
         6| 50000|            3| 50000|

-- 转换后LightDB-Oracle SQL：
SELECT employee_id,salary,department_id,AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS avgRes FROM unisql_employee AS ke GROUP BY department_id,salary,employee_id
employee_id|salary|department_id|avgres|
-----------+------+-------------+------+
         1|  5000|            1|  5000|
         2| 10000|            1|  7500|
         3| 15000|            1| 10000|
         4| 20000|            2| 20000|
         5| 30000|            2| 25000|
         6| 50000|            3| 50000|

4.2.11.2. CORR

语法: CORR(expr1, expr2) [ OVER (analytic_clause) ]

警告

统一SQL暂未支持分析函数用法的解析

4.2.11.3. COUNT

语法: COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
描述: 该函数用于返回查询 expr 的行数。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
星号(*)	表示返回满足条件的所有行，且包含重复行和空行。
DISTINCT ALL	查询时是否去重。为可选项，默认值 ALL。ALL：返回所有值，包含重复行，且忽略空行。DISTINCT：返回的行中去除重复行，且忽略空行。
expr	指定参与计算的列名。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT id,value,count(id) OVER (PARTITION BY value) FROM unisql_test_data;
ID|VALUE|COUNT(ID)OVER(PARTITIONBYVALUE)|
--+-----+-------------------------------+
  10|                              1|
  20|                              1|
  30|                              1|
  40|                              1|
  50|                              1|
  60|                              1|
  70|                              1|
  80|                              1|
  90|                              1|
 100|                              1|

-- 转换后LightDB-Oracle SQL：
SELECT id,value,count(id) OVER (PARTITION BY value) FROM unisql_test_data
id|value|count|
--+-----+-----+
  10|    1|
  20|    1|
  30|    1|
  40|    1|
  50|    1|
  60|    1|
  70|    1|
  80|    1|
  90|    1|
 100|    1|

4.2.11.4. COVAR_POP

语法: COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ]

警告

统一SQL暂未支持分析函数用法的解析

4.2.11.5. COVAR_SAMP

语法: COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]

警告

统一SQL暂未支持解析

4.2.11.6. CUME_DIST

语法

/*聚合语法*/
CUME_DIST( expr[,expr]...)
WITHIN GROUP (ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...)

/*分析语法*/
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)

描述: 该函数用于计算一组值中的某个值的累积分布，返回值的范围为 (0,1]。可以将此函数用作聚合或分析函数。

说明

作为分析函数，该函数计算指定值在一组值中的相对位置。对于行 row，假设按升序排列，row 的 cume_dist 是值低于或等于 row 值的行数除以被计算的行数（整个查询结果集或分区）。
作为聚合函数，该函数的参数标识每个聚合组中的单个假设行。因此，它们必须全部计算为每个聚合组内的常量表达式。常量参数表达式和 ORDER BY 聚合子句中的表达式按位置匹配。因此，参数的数量必须相同，并且它们的类型必须兼容。如果指定值与分组序列中某值重复，则将两个重复的值视为一个值处理。

参数解释

参数	说明
expr	指定要查询的值。
expr_col	指定要查询的值对应的列名。
DESC \| ASC	指定列表的排序方式，为可选项。 * ASC 为升序排序，为默认值。 * DESC 为降序排序。
NULLS { FIRST \| LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。 * NULLS FIRST 表示 NULL 值排在非空值的前面。 * NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 计算当前行的累积分布
-- 转换前Oracle SQL：
SELECT CUME_DIST() OVER (ORDER BY grade) AS cum_dist FROM unisql_students;
CUM_DIST                                  |
------------------------------------------+
0.0769230769230769230769230769230769230769|
0.2307692307692307692307692307692307692308|
0.2307692307692307692307692307692307692308|
0.3076923076923076923076923076923076923077|
0.3846153846153846153846153846153846153846|
0.4615384615384615384615384615384615384615|
0.5384615384615384615384615384615384615385|
0.6153846153846153846153846153846153846154|
0.6923076923076923076923076923076923076923|
0.8461538461538461538461538461538461538462|
0.8461538461538461538461538461538461538462|
0.9230769230769230769230769230769230769231|
                                         1|

-- 转换后LightDB-Oracle SQL：
SELECT CUME_DIST() OVER (ORDER BY grade) AS cum_dist FROM unisql_students
cum_dist           |
-------------------+
0.07692307692307693|
0.23076923076923078|
0.23076923076923078|
 0.3076923076923077|
0.38461538461538464|
0.46153846153846156|
 0.5384615384615384|
 0.6153846153846154|
 0.6923076923076923|
 0.8461538461538461|
 0.8461538461538461|
 0.9230769230769231|
                1.0|

4.2.11.7. DENSE_RANK

语法

/*聚合语法*/
DENSE_RANK(expr [, expr ...]) WITHIN GROUP ( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]... )

/*分析语法*/
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)

描述: 该函数功能是计算有序行组中行的秩。秩的值是从 1 开始的连续整数，最大的秩值是符合查询结果的数值。具有相同值的排序标准的行接收相同的秩，相同的行数不会被记录到下个排名中。可以将其用作聚合或分析函数。

说明

作为聚合函数，DENSE_RANK 根据指定的排序规范计算由函数的参数标识的假设行的密集等级。常量参数表达式 expr 和 order_by_clause 聚合中的表达式按位置匹配。因此，参数的数量必须相同并且类型必须兼容。
作为一个分析函数，DENSE_RANK 根据 order_by_clause 中 value_exprs 的值，计算从查询返回的每一行相对于其他行的秩。

参数解释

参数	说明
expr	对应列中数据类型的值。
expr_col	指定要查询的值对应的列名。
DESC \| ASC	指定列表的排序方式，为可选项。ASC 为升序排序，默认值。DESC 为降序排序。
NULLS { FIRST \| LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。NULLS FIRST 表示 NULL 值排在非空值的前面。NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 计算值的密集排名
-- 转换前Oracle SQL：
SELECT name, DENSE_RANK() OVER (ORDER BY grade) AS dense_rank FROM unisql_students;
NAME     |DENSE_RANK|
---------+----------+
David    |         1|
Bob      |         2|
Katherine|         2|
Frank    |         3|
Mia      |         4|
Harry    |         5|
Alice    |         6|
Jack     |         7|
Emma     |         8|
Isabella |         9|
Grace    |         9|
Charlie  |        10|
Liam     |        11|

-- 转换后LightDB-Oracle SQL：
SELECT name,DENSE_RANK() OVER (ORDER BY grade) AS dense_rank FROM unisql_students
name     |dense_rank|
---------+----------+
David    |         1|
Bob      |         2|
Katherine|         2|
Frank    |         3|
Mia      |         4|
Harry    |         5|
Alice    |         6|
Jack     |         7|
Emma     |         8|
Grace    |         9|
Isabella |         9|
Charlie  |        10|
Liam     |        11|


-- 转换前Oracle SQL：
SELECT department_id,salary,DENSE_RANK() over(PARTITION BY department_id ORDER BY salary) dr FROM unisql_employee ke  ORDER BY dr;
DEPARTMENT_ID|SALARY|DR|
-------------+------+--+
            1|  5000| 1|
            3| 50000| 1|
            2| 20000| 1|
            1| 10000| 2|
            2| 30000| 2|
            1| 15000| 3|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,salary,DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS dr FROM unisql_employee AS ke ORDER BY dr
department_id|salary|dr|
-------------+------+--+
            1|  5000| 1|
            2| 20000| 1|
            3| 50000| 1|
            1| 10000| 2|
            2| 30000| 2|
            1| 15000| 3|

4.2.11.8. FIRST_VALUE

语法

FIRST_VALUE { (expr) [{RESPECT | IGNORE} NULLS ]  | (expr [{RESPECT | IGNORE} NULLS ]) } OVER (analytic_clause)

描述: 该函数是一个分析函数，功能是返回有序值中的第一个值。如果集合中的第一个值为 NULL，则函数返回NULL，除非您指定 IGNORE NULLS，该设置对于数据致密化很有用。expr 列中 NULL 值行对应的返回值是 NULL

参数解释

参数	说明
expr	指定要返回值的列名。注意不能将 FIRST_VALUE 或其他分析函数用于 expr 来嵌套分析函数。
{RESPECT \| IGNORE} NULLS	表示是否忽略 NULL 值，为可选项。默认值为 RESPECT NULLS。RESPECT NULLS 表示不忽略 NULL 值。IGNORE NULLS 表示忽略 NULL 值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 返回年龄最小的学生名字
-- 转换前Oracle SQL：
SELECT id,name,age,grade,FIRST_VALUE(name) OVER (ORDER BY age) AS youngest_student FROM unisql_students;
ID|NAME     |AGE|GRADE|YOUNGEST_STUDENT|
--+---------+---+-----+----------------+
1|Alice    | 20|   85|Alice           |
7|Grace    | 20|   90|Alice           |
12|Liam     | 20|   95|Alice           |
3|Charlie  | 21|   92|Alice           |
9|Isabella | 21|   90|Alice           |
2|Bob      | 22|   78|Alice           |
5|Emma     | 22|   88|Alice           |
8|Harry    | 22|   84|Alice           |
13|Mia      | 22|   82|Alice           |
4|David    | 23|   75|Alice           |
10|Jack     | 23|   87|Alice           |
6|Frank    | 24|   79|Alice           |
11|Katherine| 24|   78|Alice           |

-- 转换后LightDB-Oracle SQL：
SELECT id,name,age,grade,FIRST_VALUE(name) OVER (ORDER BY age) AS youngest_student FROM unisql_students
id|name     |age|grade|youngest_student|
--+---------+---+-----+----------------+
1|Alice    | 20|   85|Alice           |
7|Grace    | 20|   90|Alice           |
12|Liam     | 20|   95|Alice           |
3|Charlie  | 21|   92|Alice           |
9|Isabella | 21|   90|Alice           |
5|Emma     | 22|   88|Alice           |
2|Bob      | 22|   78|Alice           |
8|Harry    | 22|   84|Alice           |
13|Mia      | 22|   82|Alice           |
10|Jack     | 23|   87|Alice           |
4|David    | 23|   75|Alice           |
11|Katherine| 24|   78|Alice           |
6|Frank    | 24|   79|Alice           |


-- 转换前Oracle SQL：
SELECT employee_id, name, salary, FIRST_VALUE(name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv FROM unisql_employee;
EMPLOYEE_ID|NAME |SALARY|FV  |
-----------+-----+------+----+
         1|JACK |  5000|JACK|
         2|TOM  | 10000|JACK|
         3|LINDA| 15000|JACK|
         4|ADA  | 20000|JACK|
         5|TINA | 30000|JACK|
         6|KATE | 50000|JACK|

-- 转换后LightDB-Oracle SQL：
SELECT employee_id, name, salary, FIRST_VALUE(name) OVER (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS fv FROM unisql_employee
employee_id|name |salary|fv  |
-----------+-----+------+----+
         1|JACK |  5000|JACK|
         2|TOM  | 10000|JACK|
         3|LINDA| 15000|JACK|
         4|ADA  | 20000|JACK|
         5|TINA | 30000|JACK|
         6|KATE | 50000|JACK|

4.2.11.9. LAG

语法

LAG { (value_expr [,offset [,default]]) [{ RESPECT|IGNORE } NULLS ] | (value_expr [{ RESPECT|IGNORE } NULLS ] [,offset [,default] ]) } OVER([query_partition_clause] order_by_clause)

描述: 该函数是一个分析函数，功能是在查询中取出同一字段的前 offset 行的数据作为独立的列存在表中。这种操作可以代替表的自联接。

参数解释

参数	说明
value_expr	指定需要查询的字段。注意不能使用 LAG 函数或其他分析函数来嵌套 value_expr。
offset	指定查询 value_expr 的偏移量，一个大于零的整数，为可选项。默认值为 1。
default	指定没有符合条件的默认值。如果偏移量超出窗口的范围，则返回 default 的值，为可选项。默认值为 NULL。
{RESPECT \| IGNORE} NULLS	表示是否忽略 NULL 值，为可选项。默认值为 RESPECT NULLS。RESPECT NULLS 表示不忽略 NULL 值。IGNORE NULLS 表示忽略 NULL 值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 返回每个学生的上一位学生的年龄
-- 转换前Oracle SQL：
SELECT name, age, LAG(age) OVER (ORDER BY age) AS previous_age FROM unisql_students;
NAME     |AGE|PREVIOUS_AGE|
---------+---+------------+
Alice    | 20|            |
Grace    | 20|          20|
Liam     | 20|          20|
Isabella | 21|          20|
Charlie  | 21|          21|
Bob      | 22|          21|
Mia      | 22|          22|
Emma     | 22|          22|
Harry    | 22|          22|
Jack     | 23|          22|
David    | 23|          23|
Katherine| 24|          23|
Frank    | 24|          24|

-- 转换后LightDB-Oracle SQL：
SELECT name,age,LAG(age) OVER (ORDER BY age) AS previous_age FROM unisql_students
name     |age|previous_age|
---------+---+------------+
Alice    | 20|            |
Grace    | 20|          20|
Liam     | 20|          20|
Charlie  | 21|          20|
Isabella | 21|          21|
Emma     | 22|          21|
Bob      | 22|          22|
Harry    | 22|          22|
Mia      | 22|          22|
Jack     | 23|          22|
David    | 23|          23|
Katherine| 24|          23|
Frank    | 24|          24|


-- 转换前Oracle SQL：
SELECT name, age, LAG(age,3) OVER (ORDER BY age) AS previous_age FROM unisql_students;
NAME     |AGE|PREVIOUS_AGE|
---------+---+------------+
Alice    | 20|            |
Grace    | 20|            |
Liam     | 20|            |
Isabella | 21|          20|
Charlie  | 21|          20|
Bob      | 22|          20|
Mia      | 22|          21|
Emma     | 22|          21|
Harry    | 22|          22|
Jack     | 23|          22|
David    | 23|          22|
Katherine| 24|          22|
Frank    | 24|          23|

-- 转换后LightDB-Oracle SQL：
SELECT name,age,LAG(age, 3) OVER (ORDER BY age) AS previous_age FROM unisql_students
name     |age|previous_age|
---------+---+------------+
Alice    | 20|            |
Grace    | 20|            |
Liam     | 20|            |
Charlie  | 21|          20|
Isabella | 21|          20|
Emma     | 22|          20|
Bob      | 22|          21|
Harry    | 22|          21|
Mia      | 22|          22|
Jack     | 23|          22|
David    | 23|          22|
Katherine| 24|          22|
Frank    | 24|          23|

4.2.11.10. LAST_VALUE

语法

LAST_VALUE { (expr) [{RESPECT | IGNORE} NULLS ] | (expr [{RESPECT | IGNORE} NULLS ]) } OVER (analytic_clause)

描述: 该函数是一个分析函数，用于返回一组有序值中的最后一个值。如果集合中的最后一个值为 NULL，则该函数将返回 NULL，除非您指定 IGNORE NULLS

参数解释

参数	说明
expr	指定要返回值的列名。注意不能将 LAST_VALUE 或其他分析函数用于 expr 来嵌套分析函数。
{RESPECT / IGNORE} NULLS	表示是否忽略 NULL 值为可选项。默认值为 RESPECT NULLS。RESPECT NULLS 表示不忽略 NULL 值。IGNORE NULLS 表示忽略 NULL 值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 返回每个学生的最后一位学生的年龄
-- 转换前Oracle SQL：
SELECT name, age, LAST_VALUE(age) OVER (ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_age FROM unisql_students;
NAME     |AGE|LAST_AGE|
---------+---+--------+
Alice    | 20|      24|
Grace    | 20|      24|
Liam     | 20|      24|
Isabella | 21|      24|
Charlie  | 21|      24|
Bob      | 22|      24|
Mia      | 22|      24|
Emma     | 22|      24|
Harry    | 22|      24|
Jack     | 23|      24|
David    | 23|      24|
Katherine| 24|      24|
Frank    | 24|      24|

-- 转换后LightDB-Oracle SQL：
SELECT name,age,LAST_VALUE(age) OVER (ORDER BY age) AS last_age FROM unisql_students
name     |age|last_age|
---------+---+--------+
Alice    | 20|      20|
Grace    | 20|      20|
Liam     | 20|      20|
Charlie  | 21|      21|
Isabella | 21|      21|
Emma     | 22|      22|
Bob      | 22|      22|
Harry    | 22|      22|
Mia      | 22|      22|
Jack     | 23|      23|
David    | 23|      23|
Katherine| 24|      24|
Frank    | 24|      24|

4.2.11.11. LEAD

语法

LEAD { (value_expr [,offset [,default]]) [{ RESPECT|IGNORE } NULLS ] | (value_expr [{ RESPECT|IGNORE } NULLS ] [,offset [,default] ])  } OVER([query_partition_clause] order_by_clause)

描述: 该函数是一个分析函数，功能是在查询中取出同一字段的后 offset 行的数据作为独立的列存在表中。这种操作可以代替表的自联接。

参数解释

参数	说明
value_expr	指定需要查询的字段。注意不能使用 LEAD 函数或其他分析函数来嵌套 value_expr。
offset	指定查询 value_expr 的偏移量，一个大于零的整数。为可选项，默认值为 1。
default	指定没有符合条件的默认值。如果偏移量超出窗口的范围，则返回 default 的值。为可选项，默认值为 NULL。
{RESPECT \| IGNORE} NULLS	表示是否忽略 NULL 值，为可选项。默认值为 RESPECT NULLS。RESPECT NULLS 表示不忽略 NULL 值。IGNORE NULLS 表示忽略 NULL 值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 返回每个学生的下一位学生的年龄
-- 转换前Oracle SQL：
SELECT name, age, LEAD(age) OVER (ORDER BY age) AS next_age FROM unisql_students;
NAME     |AGE|NEXT_AGE|
---------+---+--------+
Alice    | 20|      20|
Grace    | 20|      20|
Liam     | 20|      21|
Isabella | 21|      21|
Charlie  | 21|      22|
Bob      | 22|      22|
Mia      | 22|      22|
Emma     | 22|      22|
Harry    | 22|      23|
Jack     | 23|      23|
David    | 23|      24|
Katherine| 24|      24|
Frank    | 24|        |

-- 转换后LightDB-Oracle SQL：
SELECT name,age,LEAD(age) OVER (ORDER BY age) AS next_age FROM unisql_students
name     |age|next_age|
---------+---+--------+
Alice    | 20|      20|
Grace    | 20|      20|
Liam     | 20|      21|
Charlie  | 21|      21|
Isabella | 21|      22|
Emma     | 22|      22|
Bob      | 22|      22|
Harry    | 22|      22|
Mia      | 22|      23|
Jack     | 23|      23|
David    | 23|      24|
Katherine| 24|      24|
Frank    | 24|        |


-- 转换前Oracle SQL：
SELECT name, age, LEAD(age,3) OVER (ORDER BY age) AS next_age FROM unisql_students;
NAME     |AGE|NEXT_AGE|
---------+---+--------+
Alice    | 20|      21|
Grace    | 20|      21|
Liam     | 20|      22|
Isabella | 21|      22|
Charlie  | 21|      22|
Bob      | 22|      22|
Mia      | 22|      23|
Emma     | 22|      23|
Harry    | 22|      24|
Jack     | 23|      24|
David    | 23|        |
Katherine| 24|        |
Frank    | 24|        |

-- 转换后LightDB-Oracle SQL：
SELECT name, age, LEAD(age,3) OVER (ORDER BY age) AS next_age FROM unisql_students;
name     |age|next_age|
---------+---+--------+
Alice    | 20|      21|
Grace    | 20|      21|
Liam     | 20|      22|
Charlie  | 21|      22|
Isabella | 21|      22|
Emma     | 22|      22|
Bob      | 22|      23|
Harry    | 22|      23|
Mia      | 22|      24|
Jack     | 23|      24|
David    | 23|        |
Katherine| 24|        |
Frank    | 24|        |

4.2.11.12. LISTAGG

语法: LISTAGG (measure_expr [,'delimiter']) [ WITHIN GROUP ] (order_by_clause)
描述: 该函数用于列转行，LISTAGG 对 ORDER BY 子句中指定的每个组内的数据进行排序，然后合并度量列的值

参数解释

参数	说明
measure_expr	可以是任何表达式。度量列中的空值将被忽略。
delimiter	指定用于分隔度量值的字符串。此子句是可选的，默认为 NULL。

示例

-- 转换前Oracle SQL：
SELECT LISTAGG(name, '; ') over(partition by department_id) rk FROM unisql_employee ke;
RK              |
----------------+
JACK; TOM; LINDA|
JACK; TOM; LINDA|
JACK; TOM; LINDA|
ADA; TINA       |
ADA; TINA       |
KATE            |

-- 转换后LightDB-Oracle SQL：
SELECT LISTAGG(name, '; ') OVER (PARTITION BY department_id) AS rk FROM unisql_employee AS ke
rk              |
----------------+
JACK; TOM; LINDA|
JACK; TOM; LINDA|
JACK; TOM; LINDA|
ADA; TINA       |
ADA; TINA       |
KATE            |


-- 使用语法词ON OVERFLOW TRUNCATE/ON OVERFLOW ERROR
CREATE TABLE listagg_test_employees (
emp_id int,
emp_name VARCHAR(100),
department_id int
);

-- 插入数据
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (1, 'John Doe', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (2, 'Jane Smith', 1);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (3, 'Mark Johnson', 2);
INSERT INTO listagg_test_employees (emp_id, emp_name, department_id) VALUES (4, 'Emily Davis', 2);

SELECT LISTAGG(emp_name ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW TRUNCATE) over(partition by department_id) rk FROM listagg_test_employees;

SELECT LISTAGG(emp_name ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name, ', ' ON OVERFLOW ERROR) WITHIN GROUP (ORDER BY department_id) rk FROM listagg_test_employees;
SELECT LISTAGG(emp_name,', ' ON OVERFLOW ERROR) over(partition by department_id) rk FROM listagg_test_employees;

4.2.11.13. MAX

语法: MAX ([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数返回参数中指定的列中的最大值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT 或 ALL	查询时是否去重。为可选项，默认值 ALL。ALL：返回所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT：返回的行中去除重复行，且忽略值为 NULL 的行。
expr	可为数值、字符、日期型或其它类型的数据列或表达式。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 转换前Oracle SQL：
SELECT department_id, name, salary,MAX(salary) OVER (PARTITION BY department_id) AS mgr_max FROM unisql_employee ORDER BY department_id, name, salary;
DEPARTMENT_ID|NAME |SALARY|MGR_MAX|
-------------+-----+------+-------+
            1|JACK |  5000|  15000|
            1|LINDA| 15000|  15000|
            1|TOM  | 10000|  15000|
            2|ADA  | 20000|  30000|
            2|TINA | 30000|  30000|
            3|KATE | 50000|  50000|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,name,salary,MAX(salary) OVER (PARTITION BY department_id) AS mgr_max FROM unisql_employee ORDER BY department_id,name,salary
department_id|name |salary|mgr_max|
-------------+-----+------+-------+
            1|JACK |  5000|  15000|
            1|LINDA| 15000|  15000|
            1|TOM  | 10000|  15000|
            2|ADA  | 20000|  30000|
            2|TINA | 30000|  30000|
            3|KATE | 50000|  50000|

4.2.11.14. MEDIAN

语法: MEDIAN(expr) [ OVER (query_partition_clause) ]
描述: 该函数用于返回一组数值的中值，即将一组数值排序后返回居于中间的数值。如果参数集合中包含偶数个数值，该函数将返回位于中间的两个数的平均值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定要求中值的数组名称，参数类型属于数值数据类型或可以隐式转换为数字数据类型。
OVER	使用 OVER 子句定义窗口进行计算。详细信息请参见分析函数说明。

示例

-- 转换前Oracle SQL：
SELECT department_id, employee_id, salary,MEDIAN(salary) OVER(PARTITION BY department_id) FROM unisql_employee ORDER BY department_id, employee_id;
DEPARTMENT_ID|EMPLOYEE_ID|SALARY|MEDIAN(SALARY)OVER(PARTITIONBYDEPARTMENT_ID)|
-------------+-----------+------+--------------------------------------------+
            1|          1|  5000|                                       10000|
            1|          2| 10000|                                       10000|
            1|          3| 15000|                                       10000|
            2|          4| 20000|                                       25000|
            2|          5| 30000|                                       25000|
            3|          6| 50000|                                       50000|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,employee_id,salary,MEDIAN(salary) OVER (PARTITION BY department_id) FROM unisql_employee ORDER BY department_id,employee_id
department_id|employee_id|salary|median |
-------------+-----------+------+-------+
            1|          1|  5000|10000.0|
            1|          2| 10000|10000.0|
            1|          3| 15000|10000.0|
            2|          4| 20000|25000.0|
            2|          5| 30000|25000.0|
            3|          6| 50000|50000.0|

4.2.11.15. MIN

语法: MIN([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数返回参数中指定列的最小值。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT \| UNIQUE \| ALL	查询时是否去重，为可选项。默认值 ALL。ALL：返回所有值，包含重复行，且忽略值为 NULL 的行。DISTINCT：返回的行中去除重复行，且忽略值为 NULL 的行。
expr	可为数值、字符、日期型或其它数据类型的列或表达式。
OVER	使用 OVER 子句定义窗口进行计算。详细信息请参见分析函数说明。

示例

-- 转换前Oracle SQL：
SELECT department_id, name, salary,
MIN(salary) OVER (PARTITION BY department_id) AS mgr_max
FROM unisql_employee
ORDER BY department_id, name, salary;
DEPARTMENT_ID|NAME |SALARY|MGR_MAX|
-------------+-----+------+-------+
            1|JACK |  5000|   5000|
            1|LINDA| 15000|   5000|
            1|TOM  | 10000|   5000|
            2|ADA  | 20000|  20000|
            2|TINA | 30000|  20000|
            3|KATE | 50000|  50000|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,name,salary,MIN(salary) OVER (PARTITION BY department_id) AS mgr_max FROM unisql_employee ORDER BY department_id,name,salary
department_id|name |salary|mgr_max|
-------------+-----+------+-------+
            1|JACK |  5000|   5000|
            1|LINDA| 15000|   5000|
            1|TOM  | 10000|   5000|
            2|ADA  | 20000|  20000|
            2|TINA | 30000|  20000|
            3|KATE | 50000|  50000|

4.2.11.16. NTILE

语法: NTILE (expr) OVER ([ query_partition_clause ] order_by_clause)
描述: 该函数是一个分析函数，功能是将有序数据集划分为若干个组，并为每一行分配适当的组号。组编号为 1 到 expr。

参数解释

参数	说明
expr	指定划分的组数。expr 值必须解析为大于等于 1 的正数，如果 expr 是一个非整数常量，则数据库将会该值截断为整数。如果 expr 大于行数，则将填充与行数相等的多个组，其余组为空。注意不能通过使用 NTILE 或任何其他分析函数来嵌套分析函数。但是您可以在 expr 中使用其他内置函数表达式。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 将学生按照年龄排序并分成两桶
-- 转换前Oracle SQL：
SELECT name, age, NTILE(2) OVER (ORDER BY age) AS age_bucket FROM unisql_students;
NAME     |AGE|AGE_BUCKET|
---------+---+----------+
Alice    | 20|         1|
Grace    | 20|         1|
Liam     | 20|         1|
Isabella | 21|         1|
Charlie  | 21|         1|
Bob      | 22|         1|
Mia      | 22|         1|
Emma     | 22|         2|
Harry    | 22|         2|
Jack     | 23|         2|
David    | 23|         2|
Katherine| 24|         2|
Frank    | 24|         2|

-- 转换后LightDB-Oracle SQL：
SELECT name,age,NTILE(2) OVER (ORDER BY age) AS age_bucket FROM unisql_students
name     |age|age_bucket|
---------+---+----------+
Alice    | 20|         1|
Grace    | 20|         1|
Liam     | 20|         1|
Charlie  | 21|         1|
Isabella | 21|         1|
Emma     | 22|         1|
Bob      | 22|         1|
Harry    | 22|         2|
Mia      | 22|         2|
Jack     | 23|         2|
David    | 23|         2|
Katherine| 24|         2|
Frank    | 24|         2|

4.2.11.17. NTH_VALUE

语法

NTH_VALUE (measure_expr, n) [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER (analytic_clause)

描述: 该函数功能是返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值

参数解释

参数	说明
measure_expr	指定返回数据的字段。
n	指定返回测量值的第 n 行。n 取值为正整数。注意如果 n 是 NULL，函数将返回错误。如果 n 大于窗口内所有的行数，函数返回 NULL。
FROM { FIRST \| LAST }	指定计算方向。为可选项，默认值为 FROM FIRST。FROM FIRST 表示从窗口的第一行开始计算。FROM LAST 表示最后一行开始计算。
{RESPECT \| IGNORE} NULLS	表示是否忽略 NULL 值，为可选项。默认值为 RESPECT NULLS。RESPECT NULLS 表示不忽略 NULL 值。IGNORE NULLS 表示忽略 NULL 值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 返回成绩第三名的学生名字
-- 转换前Oracle SQL：
SELECT name, grade, NTH_VALUE(name, 3) OVER (ORDER BY grade DESC) AS third_highest_grade_student FROM unisql_students;
NAME     |GRADE|THIRD_HIGHEST_GRADE_STUDENT|
---------+-----+---------------------------+
Liam     |   95|                           |
Charlie  |   92|                           |
Grace    |   90|Grace                      |
Isabella |   90|Grace                      |
Emma     |   88|Grace                      |
Jack     |   87|Grace                      |
Alice    |   85|Grace                      |
Harry    |   84|Grace                      |
Mia      |   82|Grace                      |
Frank    |   79|Grace                      |
Bob      |   78|Grace                      |
Katherine|   78|Grace                      |
David    |   75|Grace                      |

-- 转换后LightDB-Oracle SQL：
SELECT name,grade,NTH_VALUE(name, 3) OVER (ORDER BY grade DESC) AS third_highest_grade_student FROM unisql_students
name     |grade|third_highest_grade_student|
---------+-----+---------------------------+
Liam     |   95|                           |
Charlie  |   92|                           |
Grace    |   90|Grace                      |
Isabella |   90|Grace                      |
Emma     |   88|Grace                      |
Jack     |   87|Grace                      |
Alice    |   85|Grace                      |
Harry    |   84|Grace                      |
Mia      |   82|Grace                      |
Frank    |   79|Grace                      |
Katherine|   78|Grace                      |
Bob      |   78|Grace                      |
David    |   75|Grace                      |

4.2.11.18. PERCENT_RANK

语法

/* 聚合语法 */
PERCENT_RANK(expr [, expr ...]) WITHIN GROUP ( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]  [,expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]] ...)

/* 分析语法 */
PERCENT_RANK( ) OVER ([query_partition_clause] order_by_clause)

描述: 该函数用来计算一组值中的某个值的累积分布

说明

作为聚合函数，PERCENT_RANK 计算某一个数在一个集合中的对应位置百分比，它的返回值范围为 (0, 1]。如果有 N 行数据，expr 的值，大于第二行的值而小于第三行的值，那么位置百分比等于 2/N。常量参数表达式和 ORDER BY 聚合子句中的表达式按位置匹配。因此，参数的数量必须相同，并且它们的类型必须兼容。如果指定值与分组序列中某值重复，则将两个相同的值视为一个值处理。
作为分析函数，PERCENT_RANK 计算某列或某列组合后每行的百分比排序，它的返回值范围为 [0, 1]。当有相同排序值时，将会有相同的排名，并且值相同的行数会被记录到下个排名中。任何集合中的第一行的 PERCENT_RANK 函数为 0，位置百分比的计算公式为：位置百分比=序号/最大序号，具体示例请参见如下表格：

N	序号	位置百分比
A	0	0
B	1	0.25
C	2	0.5
D	3	0.75
E	4	1

参数解释

参数	说明
expr	指定要查询的值。
expr_col	指定要查询的值对应的列名。
DESC \| ASC	指定列表的排序方式，为可选项。ASC 为升序排序，为默认值。DESC 为降序排序。
NULLS { FIRST \| LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。NULLS FIRST 表示 NULL 值排在非空值的前面。NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 计算给定值的排名百分比
-- 转换前Oracle SQL：
SELECT id,value,PERCENT_RANK() OVER (ORDER BY value) AS percentile_rank FROM unisql_test_data;
ID|VALUE|PERCENTILE_RANK                           |
--+-----+------------------------------------------+
1|   10|                                         0|
2|   20|0.1111111111111111111111111111111111111111|
3|   30|0.2222222222222222222222222222222222222222|
4|   40|0.3333333333333333333333333333333333333333|
5|   50|0.4444444444444444444444444444444444444444|
6|   60|0.5555555555555555555555555555555555555556|
7|   70|0.6666666666666666666666666666666666666667|
8|   80|0.7777777777777777777777777777777777777778|
9|   90|0.8888888888888888888888888888888888888889|
10|  100|                                         1|

-- 转换后LightDB-Oracle SQL：
SELECT id,value,PERCENT_RANK() OVER (ORDER BY value) AS percentile_rank FROM unisql_test_data
id|value|percentile_rank   |
--+-----+------------------+
1|   10|               0.0|
2|   20|0.1111111111111111|
3|   30|0.2222222222222222|
4|   40|0.3333333333333333|
5|   50|0.4444444444444444|
6|   60|0.5555555555555556|
7|   70|0.6666666666666666|
8|   80|0.7777777777777778|
9|   90|0.8888888888888888|
10|  100|               1.0|

4.2.11.19. PERCENTILE_CONT

语法

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY expr [ DESC | ASC ])
[ OVER (query_partition_clause) ]

描述: 该函数用于计算给定百分位数，它根据指定的百分比插值计算出连续型列的值。

警告

统一SQL暂未支持分析函数用法的解析

4.2.11.20. PERCENTILE_DISC

语法

PERCENTILE_DISC(expr1) WITHIN GROUP (ORDER BY expr2 [ DESC | ASC ])
[ OVER (query_partition_clause) ]

描述: 该函数用于计算给定百分位数，它返回在排序后的数据集中，指定百分位数位置的实际值。

警告

统一SQL暂未支持分析函数用法的解析

4.2.11.21. RANK

语法

/*聚合语法*/
RANK(expr [, expr ]...) WITHIN GROUP( ORDER BY expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ] [, expr_col [ DESC | ASC ][ NULLS { FIRST | LAST } ]]...)

/*分析语法*/
RANK() OVER ([ query_partition_clause ] order_by_clause)

描述: 该函数用于计算一个值在一组值的排名。当有相同排序值时，将会有相同的排名，并且值相同的行数会被记录到下个排名中。可以将此函数用作聚合或分析函数

说明

作为聚合函数，RANK 计算由参数 expr 标识的假设行相对于指定排序规范的排名。参数表达式 expr 和 ORDER BY 聚合子句中的表达式按位置匹配。因此，参数的数量必须相同，并且它们的数据类型必须兼容。
作为分析函数，RANK 计算从查询相对于由查询返回的其他行返回的每一行的秩，基于所述值 value_exprs 中 order_by_clause。

参数解释

参数	说明
expr	指定要查询的值。
expr_col	指定要查询的值对应的列名。
DESC \| ASC	指定列表的排序方式，为可选项。ASC 为升序排序，默认值。DESC 为降序排序。
NULLS { FIRST \| LAST }	排序后 expr_col 中 NULL 值的位置，为可选项。NULLS FIRST 表示 NULL 值排在非空值的前面。NULLS LAST 表示 NULL 值排在非空值的后面，为默认值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 计算值的排名
-- 转换前Oracle SQL：
SELECT value, RANK() OVER (ORDER BY value) AS value_rank FROM unisql_test_data;
VALUE|VALUE_RANK|
-----+----------+
   10|         1|
   20|         2|
   30|         3|
   40|         4|
   50|         5|
   60|         6|
   70|         7|
   80|         8|
   90|         9|
  100|        10|


-- 转换后LightDB-Oracle SQL：
SELECT value,RANK() OVER (ORDER BY value) AS value_rank FROM unisql_test_data
value|value_rank|
-----+----------+
   10|         1|
   20|         2|
   30|         3|
   40|         4|
   50|         5|
   60|         6|
   70|         7|
   80|         8|
   90|         9|
  100|        10|


-- 转换前Oracle SQL：
SELECT department_id, name, salary,RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK FROM unisql_employee ORDER BY RANK, name;
DEPARTMENT_ID|NAME |SALARY|RANK|
-------------+-----+------+----+
            2|ADA  | 20000|   1|
            1|JACK |  5000|   1|
            3|KATE | 50000|   1|
            2|TINA | 30000|   2|
            1|TOM  | 10000|   2|
            1|LINDA| 15000|   3|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,name,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary) AS RANK FROM unisql_employee ORDER BY RANK,name
department_id|name |salary|rank|
-------------+-----+------+----+
            2|ADA  | 20000|   1|
            1|JACK |  5000|   1|
            3|KATE | 50000|   1|
            2|TINA | 30000|   2|
            1|TOM  | 10000|   2|
            1|LINDA| 15000|   3|

4.2.11.22. STDDEV

语法

STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]

描述: 该函数用于计算一组数值型数据标准差

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT \| ALL	查询时是否去重。为可选项，默认值 ALL。ALL：全部数值列。DISTINCT：去重关键字，表示计算唯一值的总体标准差。
expr	数值类型或者可以转换成数值类型的值。
OVER	使用 OVER 子句定义窗口进行计算

示例

CREATE TABLE unisql_test_data (
 id int PRIMARY KEY,
 value INTEGER
);

INSERT INTO unisql_test_data(id,value) VALUES (1,10);
INSERT INTO unisql_test_data(id,value) VALUES (2,20);
INSERT INTO unisql_test_data(id,value) VALUES (3,30);
INSERT INTO unisql_test_data(id,value) VALUES (4,40);
INSERT INTO unisql_test_data(id,value) VALUES (5,50);
INSERT INTO unisql_test_data(id,value) VALUES (6,60);
INSERT INTO unisql_test_data(id,value) VALUES (7,70);
INSERT INTO unisql_test_data(id,value) VALUES (8,80);
INSERT INTO unisql_test_data(id,value) VALUES (9,90);
INSERT INTO unisql_test_data(id,value) VALUES (10,100);

-- 样本标准偏差：计算 value 列的标准偏差
-- 转换前Oracle SQL：
SELECT STDDEV(value) OVER () AS standard_deviation_value FROM unisql_test_data;
STANDARD_DEVIATION_VALUE                 |
-----------------------------------------+
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|

-- 转换后LightDB-Oracle SQL：
SELECT STDDEV(value) OVER () AS standard_deviation_value FROM unisql_test_data
standard_deviation_value|
------------------------+
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|
      30.2765035409749167|

4.2.11.23. STDDEV_POP

语法

STDDEV_POP( [ALL] expr) [ OVER (analytic_clause) ]

描述: 该函数将数值型数据作为参数计算总体标准差。总体标准差是总体方差的算术平方根。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
ALL	指定全部数值列，为可选项。默认值为 ALL。
expr	数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或者可以转换成数值类型的表达式。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 总体标准偏差：计算 value 列的总体标准偏差
-- 转换前Oracle SQL：
SELECT STDDEV_POP(value) OVER () AS population_standard_deviation_value FROM unisql_test_data;
POPULATION_STANDARD_DEVIATION_VALUE     |
----------------------------------------+
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|
28.7228132326901432992530573410946465911|

-- 转换后LightDB-Oracle SQL：
SELECT stddev_pop(value) OVER () AS population_standard_deviation_value FROM unisql_test_data
population_standard_deviation_value|
-----------------------------------+
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|
               28.7228132326901433|

4.2.11.24. STDDEV_SAMP

语法

STDDEV_SAMP([ALL] expr) [ OVER (analytic_clause) ]

描述: 该函数将数值型数据作为参数计算样本标准差，样本标准差是样本方差的算术平方根。STDDEV_SAMP 与函数 STDDEV 的不同之处在于，STDDEV 只有一行输入数据时返回 0，而 STDDEV_SAMP 返回 NULL。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
ALL	全部数值列。为可选项，默认值 ALL。
expr	数值类型（NUMBER、FLOAT、BINARY_FLOAT 和 BINARY_DOUBLE）或者可以转换成数值类型的表达式。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 样本标准偏差
-- 转换前Oracle SQL：
SELECT STDDEV_SAMP(value) OVER () AS sample_standard_deviation_value FROM unisql_test_data;
SAMPLE_STANDARD_DEVIATION_VALUE          |
-----------------------------------------+
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|
30.27650354097491665422532809718193699195|

-- 转换后LightDB-Oracle SQL：
SELECT STDDEV_SAMP(value) OVER () AS sample_standard_deviation_value FROM unisql_test_data
sample_standard_deviation_value|
-------------------------------+
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|
            30.2765035409749167|

4.2.11.25. SUM

语法: SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数返回指定参数的总和，可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定参与计算的数值表达式。属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 转换前Oracle SQL：
SELECT department_id, name, salary,SUM(salary) OVER (PARTITION BY department_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM unisql_employee ORDER BY department_id, name, salary, l_csum;
DEPARTMENT_ID|NAME |SALARY|L_CSUM|
-------------+-----+------+------+
            1|JACK |  5000|  5000|
            1|LINDA| 15000| 30000|
            1|TOM  | 10000| 15000|
            2|ADA  | 20000| 20000|
            2|TINA | 30000| 50000|
            3|KATE | 50000| 50000|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,name,salary,SUM(salary) OVER (PARTITION BY department_id ORDER BY salary) AS l_csum FROM unisql_employee ORDER BY department_id,name,salary,l_csum
department_id|name |salary|l_csum|
-------------+-----+------+------+
            1|JACK |  5000|  5000|
            1|LINDA| 15000| 30000|
            1|TOM  | 10000| 15000|
            2|ADA  | 20000| 20000|
            2|TINA | 30000| 50000|
            3|KATE | 50000| 50000|

4.2.11.26. VAR_POP

语法: VAR_POP(expr) [ OVER (analytic_clause) ]
描述: 该函数返回一组数值集合的总体方差（忽略 NULL）。可以将该函数用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定参与计算的数值表达式。属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 转换前Oracle SQL：
SELECT department_id, name, salary,
VAR_POP(salary) OVER (PARTITION BY department_id)
FROM unisql_employee GROUP BY department_id,name,salary;
DEPARTMENT_ID|NAME |SALARY|VAR_POP(SALARY)OVER(PARTITIONBYDEPARTMENT_ID)|
-------------+-----+------+---------------------------------------------+
            1|JACK |  5000|    16666666.66666666666666666666666666666667|
            1|LINDA| 15000|    16666666.66666666666666666666666666666667|
            1|TOM  | 10000|    16666666.66666666666666666666666666666667|
            2|ADA  | 20000|                                     25000000|
            2|TINA | 30000|                                     25000000|
            3|KATE | 50000|                                            0|

-- 转换后LightDB-Oracle SQL：
SELECT department_id,name,salary,var_pop(salary) OVER (PARTITION BY department_id) FROM unisql_employee GROUP BY department_id,name,salary
department_id|name |salary|var_pop              |
-------------+-----+------+---------------------+
            1|TOM  | 10000|16666666.666666666667|
            1|JACK |  5000|16666666.666666666667|
            1|LINDA| 15000|16666666.666666666667|
            2|TINA | 30000|             25000000|
            2|ADA  | 20000|             25000000|
            3|KATE | 50000|                    0|

4.2.11.27. VAR_SAMP

语法: VAR_SAMP(expr) [ OVER (analytic_clause) ]
描述: 该函数返回一组数值集合的样本方差（忽略 NULL）。可以将该函数用作聚合或分析函数

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
expr	指定参与计算的数值表达式。属于数值数据类型或可以隐式转换为数值数据类型的值。
OVER	使用 OVER 子句定义窗口进行计算。

示例

-- 转换前Oracle SQL：
SELECT department_id, name, salary,
VAR_SAMP(SUM(SALARY)) OVER (PARTITION BY department_id)
FROM unisql_employee GROUP BY department_id,name,salary;
department_id | last_name | salary |       var_samp
DEPARTMENT_ID|NAME |SALARY|VAR_SAMP(SUM(SALARY))OVER(PARTITIONBYDEPARTMENT_ID)|
-------------+-----+------+---------------------------------------------------+
            1|JACK |  5000|                                           25000000|
            1|LINDA| 15000|                                           25000000|
            1|TOM  | 10000|                                           25000000|
            2|ADA  | 20000|                                           50000000|
            2|TINA | 30000|                                           50000000|
            3|KATE | 50000|                                                   |

-- 转换后LightDB-Oracle SQL：
SELECT department_id,name,salary,VAR_SAMP(SUM(SALARY)) OVER (PARTITION BY department_id) FROM unisql_employee GROUP BY department_id,name,salary
department_id|name |salary|var_samp|
-------------+-----+------+--------+
            1|TOM  | 10000|25000000|
            1|JACK |  5000|25000000|
            1|LINDA| 15000|25000000|
            2|TINA | 30000|50000000|
            2|ADA  | 20000|50000000|
            3|KATE | 50000|        |

4.2.11.28. VARIANCE

语法: VARIANCE([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
描述: 该函数功能是返回指定列的方差。可以将其用作聚合或分析函数。

说明

作为分析函数使用时，需要使用 OVER 子句定义窗口进行计算。它对一组行的集合进行计算并返回多个值。
作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 OVER 子句。

参数解释

参数	说明
DISTINCT \| ALL	查询时是否去重。为可选项，默认值 ALL。ALL：查询时不去除数据中的重复值，且忽略数据中的 NULL 值。DISTINCT：查询时去除数据中的重复值，且忽略数据中的 NULL 值。
expr	数值数据类型或任何可以隐式转换为数值数据类型的表达式。
OVER	使用 OVER 子句定义窗口进行计算

示例

-- 计算年龄的样本方差
-- 转换前Oracle SQL：
SELECT VARIANCE(value) OVER () AS population_variance_value FROM unisql_test_data;
POPULATION_VARIANCE_VALUE               |
----------------------------------------+
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|
916.666666666666666666666666666666666667|


-- 转换后LightDB-Oracle SQL：
SELECT VARIANCE(value) OVER () AS population_variance_value FROM unisql_test_data;
population_variance_value|
-------------------------+
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|
   916.6666666666666667|

4.2.11.29. ROW_NUMBER

语法

ROW_NUMBER() OVER ([ query_partition_clause ] order_by_clause)

描述: 该函数是一个分析函数，作用是为每一条行（分组）记录返回一个唯一的序号，该序号按照 order_by_clause 中指定列进行的排序，从 1 开始。当遇到相同的数据时，排名按照记录集中记录的顺序依次递增，对于不同数据进行依次排名。

-- 返回每个学生的行号
-- 转换前Oracle SQL：
SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS row_num FROM unisql_students;
NAME     |ROW_NUM|
---------+-------+
Alice    |      1|
Bob      |      2|
Charlie  |      3|
David    |      4|
Emma     |      5|
Frank    |      6|
Grace    |      7|
Harry    |      8|
Isabella |      9|
Jack     |     10|
Katherine|     11|
Liam     |     12|
Mia      |     13|

-- 转换后LightDB-Oracle SQL：
SELECT name,ROW_NUMBER() OVER (ORDER BY name) AS row_num FROM unisql_students
name     |row_num|
---------+-------+
Alice    |      1|
Bob      |      2|
Charlie  |      3|
David    |      4|
Emma     |      5|
Frank    |      6|
Grace    |      7|
Harry    |      8|
Isabella |      9|
Jack     |     10|
Katherine|     11|
Liam     |     12|
Mia      |     13|

4.2.12. 序列、伪列

4.2.12.1. seq_name.nextval

该函数用于获取序列号的下一个sequence的值

-- 创建sequence
create sequence uni_seq increment by 1 start with 1;

-- 转换前Oracle SQL：
-- 获取下一个sequence的值
SELECT uni_seq.nextval FROM unisql_employee ke;
NEXTVAL|
-------+
      1|
      2|
      3|
      4|
      5|
      6|

-- 转换后LightDB-Oracle 创建sequence SQL：
CREATE SEQUENCE uni_seq INCREMENT BY 1 START WITH 1
-- 转换后LightDB-Oracle SQL：
SELECT uni_seq.nextval FROM unisql_employee AS ke
nextval|
-------+
      1|
      2|
      3|
      4|
      5|
      6|

4.2.12.2. ROWID

伪列

-- 转换前Oracle SQL：
SELECT ROWID,ke.* FROM unisql_employee ke;
ROWID             |EMPLOYEE_ID|NAME |SALARY|DEPARTMENT_ID|HIRE_DATE |COMMISSION_PCT|
------------------+-----------+-----+------+-------------+----------+--------------+
AAA2fQAAMAAIbD0AAA|          1|JACK |  5000|            1|2023-01-01|          0.05|
AAA2fQAAMAAIbD0AAB|          2|TOM  | 10000|            1|2023-02-01|          0.15|
AAA2fQAAMAAIbD0AAC|          3|LINDA| 15000|            1|2023-03-01|           0.2|
AAA2fQAAMAAIbD0AAD|          4|ADA  | 20000|            2|2023-04-01|           0.1|
AAA2fQAAMAAIbD0AAE|          5|TINA | 30000|            2|2023-05-01|           0.2|
AAA2fQAAMAAIbD0AAF|          6|KATE | 50000|            3|2023-06-01|           0.3|


-- 转换后LightDB-Oracle SQL：
SELECT ROWID,ke.* FROM unisql_employee AS ke
rowid|employee_id|name |salary|department_id|hire_date |commission_pct|
-----+-----------+-----+------+-------------+----------+--------------+
(0,1)|          1|JACK |  5000|            1|2023-01-01|          0.05|
(0,2)|          2|TOM  | 10000|            1|2023-02-01|          0.15|
(0,3)|          3|LINDA| 15000|            1|2023-03-01|           0.2|
(0,4)|          4|ADA  | 20000|            2|2023-04-01|           0.1|
(0,5)|          5|TINA | 30000|            2|2023-05-01|           0.2|
(0,6)|          6|KATE | 50000|            3|2023-06-01|           0.3|