3.2. 内置函数

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

3.2.1. 数字函数

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

-- 转换后PostgreSQL SQL：
SELECT ABS(-1.0),ABS(0),ABS(6.66),ABS(3-6)
abs|abs|abs |abs|
---+---+----+---+
1.0|  0|6.66|  3|

3.2.1.2. BITAND

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

参数解释

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

警告

参数2为负的情况需要包一层括号，例如：BITAND(0,(-1))。

示例

-- 转换前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|              |           2|

-- 转换后PostgreSQL SQL：
SELECT 0&0,0&(-1),0&NULL,-1&2
?column?|?column?|?column?|?column?|
--------+--------+--------+--------+
       0|       0|        |       2|

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

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

3.2.1.4. DBMS_RANDOM.VALUE

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT random()
random            |
------------------+
0.6059267559984995|

3.2.1.5. DBMS_RANDOM.RANDOM

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

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

-- 转换后PostgreSQL SQL：
SELECT CAST(CAST(random() AS numeric)*power(2, 32)-power(2, 31) AS numeric(38,0))
numeric    |
-----------+
-1109521446|

3.2.1.6. EXP

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

参数解释

参数	说明
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|

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

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

-- 转换后PostgreSQL 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)"
floor(0)|floor(10)|floor(10.11)|floor(-10.11)|
--------+---------+------------+-------------+
     0.0|     10.0|          10|          -11|

3.2.1.8. LN

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT LN(3.0) AS "Natural Logarithm".
Natural Logarithm |
------------------+
1.0986122886681097|

3.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和 targetDB 对数据类型默认值(比如数字类型的精度)存在一定的差异。
数据库函数在处理传入的参数时也存在隐式转换的情况。
如果对数据完全一致要求较高，建议根据函数参数类型进行更精确的控制。

-- 转换前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|

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

3.2.1.10. SIGN

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

参数解释

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

返回类型

如果 numeric_expression > 0，则返回 1
如果 numeric_expression < 0，则返回 -1
如果 numeric_expression = 0，则返回 0

示例

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

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

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

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

3.2.1.12. MOD

语法: MOD(N,M)
描述: 取余函数

参数解释

参数	说明
N	被除数
M	除数

示例

-- 转换前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

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

3.2.1.13. POWER

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

参数解释

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

示例

-- 转换前Oracle SQL：
SELECT POWER(3,2),POWER(-3,3),POWER(4,-2) FROM DUAL;
POWER(3,2)|POWER(-3,3)|POWER(4,-2)|
----------+-----------+-----------+
         9|        -27|     0.0625|

-- 转换后PostgreSQL SQL：
SELECT POWER(3, 2),POWER(-3, 3),POWER(4, -2)
power|power|power |
-----+-----+------+
  9.0|-27.0|0.0625|

3.2.1.14. ROUND

语法

ROUND(X)
ROUND(X,D)

描述

返回一个数值，四舍五入到指定的长度或精度

参数解释

参数	说明
X	要操作的数值
D	返回的长度或精度

示例

-- 转换前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|

-- 转换后PostgreSQL SQL：
SELECT round(3, 10),round(15.193, 1),round(15.193, -1),round(0, 10),round(-3, 10),ROUND(10)
round       |round|round|round       |round        |round|
------------+-----+-----+------------+-------------+-----+
3.0000000000| 15.2|   20|0.0000000000|-3.0000000000| 10.0|

3.2.1.15. TRUNC(number)

PostgreSQL不支持

3.2.2. 字符串函数

3.2.2.1. CHR

语法: CHR(n)
描述: 该函数根据数字参数返回字符，返回值与当前系统的字符集相关

参数解释

参数	说明
n	字符的数字代码,是一个整数值，范围为 [0，4294967295]

示例

-- 转换前Oracle SQL：
select CHR(67),CHR(68) from dual;
CHR(67)|CHR(68)|
-------+-------+
C      |D      |

-- 转换后PostgreSQL SQL：
SELECT CHR(67),CHR(68)
chr|chr|
---+---+
C  |D  |

3.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!            |

-- 转换后PostgreSQL SQL：
SELECT CONCAT('Hello', ', world!')
concat       |
-------------+
Hello, world!|

3.2.2.3. LOWER

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT LOWER('AaBbCcDd') AS Lowercase
lowercase|
---------+
aabbccdd |

3.2.2.4. LPAD

PostgreSQL不支持

3.2.2.5. LTRIM

语法: LTRIM(char,[set])
描述: 从 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         |

-- 转换后PostgreSQL SQL：
SELECT LTRIM(' 2023-09-21 ', ' 0123'),LTRIM('  2023-09-21')
ltrim  |ltrim     |
-------+----------+
-09-21 |2023-09-21|

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

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

3.2.2.7. REGEXP_SUBSTR

语法

REGEXP_SUBSTR(source_char, pattern
            [, position [, occurrence [, 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，则从第一次匹配后的第一个字符开始搜索第二次正则表达式匹配，依此类推。
match_param	指定更改正则表达式默认匹配方式，为可选项。是数据类型 VARCHAR2 或 CHAR 的字符表达式。
subexpr	指示 pattern 要返回的子表达式，是一个从 0 到 9 的非负整数，为可选项。默认值为 0，表示返回第一个子表达式。

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

示例

-- 转换前Oracle SQL：
SELECT
REGEXP_SUBSTR('500 oracle parkway, redwood shores, ca',',[^,]+,') AS a,
REGEXP_SUBSTR('http://www.example.com/products','http://([[:alnum:]]+\.?){3,4}/?') AS b,
REGEXP_SUBSTR('1234567890','(123)(4(56)(78))') AS c,
REGEXP_SUBSTR ('Anderson', 'a|e|i|o|u') AS d,
REGEXP_SUBSTR('FIRST_NAME','[^A]+') AS e,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1) AS a1,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1) AS a2,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i') AS a3,
REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) AS a4
FROM DUAL;
-----------------+-----------------------+--------+-+-------+--------+--------+--------+---+
A                |B                      |C       |D|E      |A1      |A2      |A3      |A4 |
-----------------+-----------------------+--------+-+-------+--------+--------+--------+---+
, redwood shores,|http://www.example.com/|12345678|e|FIRST_N|12345678|12345678|12345678|123|

-- 转换后PostgreSQL SQL：
SELECT unisql.regexp_substr('500 oracle parkway, redwood shores, ca', ',[^,]+,') AS a,unisql.regexp_substr('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') AS b,unisql.regexp_substr('1234567890', '(123)(4(56)(78))') AS c,unisql.regexp_substr('Anderson', 'a|e|i|o|u') AS d,unisql.regexp_substr('FIRST_NAME', '[^A]+') AS e,unisql.regexp_substr('1234567890', '(123)(4(56)(78))', 1) AS a1,unisql.regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1) AS a2,unisql.regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i') AS a3,unisql.regexp_substr('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) AS a4
a                |b                      |c       |d|e      |a1      |a2      |a3      |a4 |
-----------------+-----------------------+--------+-+-------+--------+--------+--------+---+
, redwood shores,|http://www.example.com/|12345678|e|FIRST_N|12345678|12345678|12345678|123|

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

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

3.2.2.9. RPAD

PostgreSQL不支持

3.2.2.10. RTRIM

语法: RTRIM(char,[set])
描述: 从 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       |

-- 转换后PostgreSQL SQL：
SELECT RTRIM(' 2023-09-21 ', ' 0123'),RTRIM('  2023-09-21')
rtrim    |rtrim       |
---------+------------+
 2023-09-|  2023-09-21|

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

-- 转换后PostgreSQL SQL：
SELECT unisql.substr('abcdefg', 3),unisql.substr('abcdefg', 3, 2),unisql.substr('abcdefg', -3),unisql.substr('abcdefg', 3, -2)
substr|substr|substr|substr|
------+------+------+------+
cdefg |cd    |efg   |      |

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

-- 转换后PostgreSQL SQL：
SELECT trim(CAST(' bar ' AS text)),ltrim(CAST('xxxbarxxx' AS text), CAST('x' AS text)),trim(CAST('x' AS text) FROM CAST('xxxbarxxx' AS text)),rtrim(CAST('xxxbarxxx' AS text), CAST('x' AS text)),trim(CAST('x' AS text) FROM CAST(NULL AS text)),trim(CAST(NULL AS text))
btrim|ltrim |btrim|rtrim |btrim|btrim|
-----+------+-----+------+-----+-----+
bar  |barxxx|bar  |xxxbar|     |     |

3.2.2.13. UPPER

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT UPPER('unisql') AS "UPPER"
UPPER |
------+
UNISQL|

3.2.2.14. REVERSE

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT REVERSE('unIsql')
reverse|
-------+
lqsInu |

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

-- 转换后PostgreSQL SQL：
SELECT ASCII('a'),ASCII('ab'),ASCII('d'),ASCII('1'),ASCII(''),ASCII(NULL)
ascii|ascii|ascii|ascii|ascii|ascii|
-----+-----+-----+-----+-----+-----+
   97|   97|  100|   49|    0|     |

3.2.2.16. INSTR

PostgreSQL不支持

3.2.2.17. LENGTH

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT LENGTH(CAST('中国' AS text)),LENGTH(CAST('hello' AS text))
length|length|
------+------+
     2|     5|

3.2.2.18. LENGTHB

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT octet_length(CAST('中国' AS text)),octet_length(CAST('hello' AS text))
octet_length|octet_length|
------------+------------+
           6|           5|

3.2.2.19. REGEXP_INSTR

PostgreSQL不支持

3.2.3. 时间日期函数

3.2.3.1. ADD_MONTHS

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

参数解释

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

示例

-- 转换前Oracle SQL：
SELECT
ADD_MONTHS(CURRENT_DATE,-3) AS a1,
ADD_MONTHS(CURRENT_DATE, 3)AS a2,
ADD_MONTHS(CURRENT_DATE, 1.1)AS a3,
ADD_MONTHS(CURRENT_DATE, 1.5)AS a4,
ADD_MONTHS(CURRENT_DATE, 1.8)AS a5,
ADD_MONTHS(CURRENT_DATE, 0)AS a6
FROM DUAL;
A1                     |A2                     |A3                     |A4                     |A5                     |A6                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-22 15:25:08.000|2024-03-22 15:25:08.000|2024-01-22 15:25:08.000|2024-01-22 15:25:08.000|2024-01-22 15:25:08.000|2023-12-22 15:25:08.000|

-- 转换后PostgreSQL SQL：
SELECT localtimestamp(0)+make_interval(0, CAST(trunc(-3) AS int), 0, 0, 0, 0, 0) AS a1,localtimestamp(0)+make_interval(0, CAST(trunc(3) AS int), 0, 0, 0, 0, 0) AS a2,localtimestamp(0)+make_interval(0, CAST(trunc(1.1) AS int), 0, 0, 0, 0, 0) AS a3,localtimestamp(0)+make_interval(0, CAST(trunc(1.5) AS int), 0, 0, 0, 0, 0) AS a4,localtimestamp(0)+make_interval(0, CAST(trunc(1.8) AS int), 0, 0, 0, 0, 0) AS a5,localtimestamp(0)+make_interval(0, CAST(trunc(0) AS int), 0, 0, 0, 0, 0) AS a6
a1                     |a2                     |a3                     |a4                     |a5                     |a6                     |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-09-22 15:26:16.000|2024-03-22 15:26:16.000|2024-01-22 15:26:16.000|2024-01-22 15:26:16.000|2024-01-22 15:26:16.000|2023-12-22 15:26:16.000|

3.2.3.2. CURRENT_DATE

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

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT CURRENT_DATE FROM DUAL;
CURRENT_DATE           |
-----------------------+
2023-12-22 15:27:15.000|

-- 转换后PostgreSQL SQL：
SELECT localtimestamp(0)
localtimestamp         |
-----------------------+
2023-12-22 15:28:04.000|

3.2.3.3. CURRENT_TIMESTAMP

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

参数解释

无

示例

-- 转换前Oracle SQL：
SELECT CURRENT_TIMESTAMP FROM DUAL;
CURRENT_TIMESTAMP            |
-----------------------------+
2023-12-22 15:28:21.307 +0800|

-- 转换后PostgreSQL SQL：
SELECT CURRENT_TIMESTAMP(0)
current_timestamp      |
-----------------------+
2023-12-22 15:28:45.000|

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

-- 转换后PostgreSQL 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"
HOUR|MINUTE|SECOND|DAY|MONTH|YEAR  |
----+------+------+---+-----+------+
12.0|  38.0|  40.0|6.0|  9.0|2023.0|

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

-- 转换后PostgreSQL SQL：
SELECT unisql.last_day(statement_timestamp()),unisql.last_day(CAST(to_timestamp('2023/09/21', 'YYYY/MM/DD') AS timestamp))
last_day               |last_day               |
-----------------------+-----------------------+
2023-12-31 09:44:53.000|2023-09-30 00:00:00.000|

3.2.3.6. MONTHS_BETWEEN

语法: MONTHS_BETWEEN (date1，date2)
描述: 该函数是返回参数 date1 到 date2 之间的月数

参数解释

参数	说明
date1	DATE 数据类型的值。
date2	DATE 数据类型的值。

示例

-- 转换前Oracle SQL：
SELECT MONTHS_BETWEEN (TO_DATE('05-02-2023 13:24:52','MM-DD-YYYY HH24:MI:SS'), TO_DATE('03-04-2023 19:24:52','MM-DD-YYYY HH24:MI:SS') ) FROM dual;
MONTHS_BETWEEN(TO_DATE('05-02-202313:24:52','MM-DD-YYYYHH24:MI:SS'),TO_DATE('03-04-202319:24:52','MM-DD-YYYYHH24:MI:SS'))|
-------------------------------------------------------------------------------------------------------------------------+
                                                                                 1.92741935483870967741935483870967741935|

-- 转换后PostgreSQL SQL：
SELECT unisql.months_between(CAST(to_timestamp('05-02-2023 13:24:52', 'MM-DD-YYYY HH24:MI:SS') AS timestamp), CAST(to_timestamp('03-04-2023 19:24:52', 'MM-DD-YYYY HH24:MI:SS') AS timestamp))
months_between    |
------------------+
1.9274193548387097|

3.2.3.7. NUMTODSINTERVAL

语法: NUMTODSINTERVAL (n,interval_unit)
描述: 该函数是把参数 n 转为以参数 interval_unit 为单位的 INTERVAL DAY TO SECOND 数据类型的值

参数解释

参数	说明
n	NUMBER 数据类型或可以转换为 NUMBER 数据类型的表达式。
interval_unit	单位值。取值为 DAY（天）、HOUR（小时）、MINUTE（分钟）、SECOND（秒），不区分大小写。

示例

警告

结果格式或表现存在不一致的情况。如果对数据完全一致要求较高，建议不使用该特性或对结果进一步处理。

-- 转换前Oracle SQL：
SELECT SYSDATE+NUMTODSINTERVAL(3,'DAY'),NUMTODSINTERVAL(2,'day') FROM dual;
SYSDATE+NUMTODSINTERVAL(3,'DAY')|NUMTODSINTERVAL(2,'DAY')|
--------------------------------+------------------------+
         2023-12-24 09:54:11.000|2 0:0:0.0               |

-- 转换后PostgreSQL SQL：
?column?               |make_interval                                |
-----------------------+---------------------------------------------+
2023-12-24 09:54:11.000|0 years 0 mons 2 days 0 hours 0 mins 0.0 secs|

3.2.3.8. NUMTOYMINTERVAL

PostgreSQL不支持

3.2.3.9. SYSDATE

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

示例

-- 转换前Oracle SQL：
SELECT SYSDATE FROM DUAL;
SYSDATE                |
-----------------------+
2023-12-21 09:56:26.240|

-- 转换后PostgreSQL SQL：
SELECT statement_timestamp()
statement_timestamp    |
-----------------------+
2023-12-21 09:56:26.240|

3.2.3.10. SYSTIMESTAMP

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

示例

-- 转换前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-21 10:04:30.543 +0800|2023-12-21 10:04:31.000 +0800|2023-12-21 10:04:30.500 +0800|2023-12-21 10:04:30.540 +0800|2023-12-21 10:04:30.544 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|2023-12-21 10:04:30.543 +0800|

-- 转换后PostgreSQL SQL：
SELECT statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp(),statement_timestamp()
statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |statement_timestamp    |
-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|2023-12-21 09:58:47.767|

3.2.3.11. TRUNC

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

参数解释

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

fmt的取值如下：

fmt 参数的取值	说明
j	默认值，最近 0 点日期。
day、dy、d	返回离指定日期最近的星期日。
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|

-- 转换后PostgreSQL SQL：
SELECT date_trunc('YEAR', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp)),date_trunc('MONTH', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp)),date_trunc('DAY', CAST(to_timestamp('2022/04/19 10:32:34', 'YYYY/MM/DD HH24:MI:SS') AS timestamp))
date_trunc             |date_trunc             |date_trunc             |
-----------------------+-----------------------+-----------------------+
2022-01-01 00:00:00.000|2022-04-01 00:00:00.000|2022-04-19 00:00:00.000|

3.2.3.12. TO_CHAR (datetime)

语法: TO_CHAR({ datetime} [, fmt])
描述: 该函数将日期时间按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值

参数解释

参数	说明
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      |A2        |
--------+----------+
05:31:12|2023-04-20|

-- 转换后PostgreSQL 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(CAST(to_timestamp('2023-04-20', 'YYYY-MM-DD') AS timestamp), 'YYYY-MM-DD') AS a2
a1      |a2        |
--------+----------+
05:31:12|2023-04-20|

3.2.3.13. TO_DSINTERVAL

语法

/*SQL 日期格式*/
TO_DSINTERVAL ('[+ | -] days hours:minutes:seconds[.frac_secs]')

/*ISO 日期格式*/
TO_DSINTERVAL ('[-] P[days D]
[T[hours H][minutes M][seconds[.frac_secs]S]]')

描述: 该函数将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值

参数解释

参数	说明
[+ \| -]days hours：minutes：seconds[.frac_secs]	符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。
[-] P[days D] [T[hours H][minutes M][seconds[.frac_secs]S]	符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。注意值中不允许有空格。
frac_secs	表示秒的小数部分，取整数值，范围为 [0 999999999]。 \|

days 表示天，取整数值，范围为 [0,999999999]。
hours 表示小时，取整数值，范围为 [0,23]。
minutes 表示分钟，取整数值，范围为 [0,59]。
seconds 表示秒，取整数值，范围为 [0,59]。

示例

-- 转换前Oracle SQL：
SELECT TO_DSINTERVAL('100 00:00:00'),TO_DSINTERVAL('P100DT05H') FROM DUAL;
TO_DSINTERVAL('10000:00:00')|TO_DSINTERVAL('P100DT05H')|
----------------------------+--------------------------+
100 0:0:0.0                 |100 5:0:0.0               |

-- 转换后PostgreSQL SQL：
SELECT CAST('100 00:00:00' AS INTERVAL),CAST('P100DT05H' AS INTERVAL)
interval                                       |interval                                       |
-----------------------------------------------+-----------------------------------------------+
0 years 0 mons 100 days 0 hours 0 mins 0.0 secs|0 years 0 mons 100 days 5 hours 0 mins 0.0 secs|

3.2.3.14. TO_TIMESTAMP

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

参数解释

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

示例

-- 转换前Oracle SQL：
SELECT to_timestamp('2020-02-02', 'yyyy-mm-dd'),
to_timestamp('2020-02-02 02', 'yyyy-mm-dd hh24'),
to_timestamp('2020-02-02 02:23:59.231', 'yyyy-mm-dd hh24:mi:ss.ff')
FROM dual;
TO_TIMESTAMP('2020-02-02','YYYY-MM-DD')|TO_TIMESTAMP('2020-02-0202','YYYY-MM-DDHH24')|TO_TIMESTAMP('2020-02-0202:23:59.231','YYYY-MM-DDHH24:MI:SS.FF')|
---------------------------------------+---------------------------------------------+----------------------------------------------------------------+
                2020-02-02 00:00:00.000|                      2020-02-02 02:00:00.000|                                         2020-02-02 02:23:59.231|

-- 转换后PostgreSQL SQL：
SELECT to_timestamp('2020-02-02', 'yyyy-mm-dd'),to_timestamp('2020-02-02 02', 'yyyy-mm-dd hh24'),to_timestamp('2020-02-02 02:23:59.231', 'yyyy-mm-dd hh24:mi:ss.ff')
to_timestamp           |to_timestamp           |to_timestamp           |
-----------------------+-----------------------+-----------------------+
2020-02-02 00:00:00.000|2020-02-02 02:00:00.000|2020-02-02 02:23:59.000|

3.2.3.15. TO_YMINTERVAL

语法

/*SQL 日期格式*/
TO_YMINTERVAL([+|-] years-months)

/*ISO 日期格式*/
TO_YMINTERVAL([-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]])

描述: 该函数将一个 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串转换为一个 INTERVAL YEAR TO MONTH 数据类型的值

参数解释

参数	说明
[+\|-] years-months	符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。years 表示年，取整数值，范围为 [0 999999999]。months 表示月，取整数值，范围为 [0 \| 11]。 \|
[-]P[ years Y][months M][days D][T[hours H][minutes M][seconds[.frac_secs]S]]	符合该参数格式的 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型的字符串。 frac_secs 表示秒的小数部分，取整数值，范围是[0 999999999]。注意值中不允许有空格。 \|

示例

-- 转换前Oracle SQL：
SELECT SYSDATE,SYSDATE+TO_YMINTERVAL('01-02') FROM DUAL;
SYSDATE                |SYSDATE+TO_YMINTERVAL('01-02')|
-----------------------+------------------------------+
2023-12-21 10:15:15.000|       2025-02-21 10:15:15.000|

-- 转换后PostgreSQL SQL：
SELECT statement_timestamp(),statement_timestamp()+CAST('01-02' AS INTERVAL)
statement_timestamp    |?column?               |
-----------------------+-----------------------+
2023-12-21 10:09:45.154|2025-02-21 10:09:45.154|

3.2.4. 通用比较函数

3.2.4.1. GREATEST

PostgreSQL不支持

3.2.4.2. LEAST

PostgreSQL不支持

3.2.5. 转换函数

3.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 函数转换数据类型时，需要注意以下内容：

对于以上列表中的转换，如果目标库对转换后的目标数据类型不支持，则会在目标库中执行时报错。
对于类似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' as DATE),
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-20'ASDATE)|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 00:00:00.000|                  2023-12-20 10:07:55.222|2-6                             |1 10:22:22.0                          |123                    |123                |123                    |

-- 转换后PostgreSQL SQL：
float4|float8|bpchar|varchar|numeric|timestamp              |timestamp              |interval                                     |interval                                        |bytea |bpchar|varchar|
------+------+------+-------+-------+-----------------------+-----------------------+---------------------------------------------+------------------------------------------------+------+------+-------+
 123.0| 123.0|1     |123    |    123|2023-12-20 00:00:00.000|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|

-- 转换后PostgreSQL SQL：
SELECT CAST('100.2345' AS decimal) AS to_nu,CAST('100.2345' AS decimal) AS to_nu,CAST('100.2345' AS decimal(38,0)) AS to_nu,CAST('100.2345' AS decimal(38,2)) AS to_nu,CAST('100.2345' AS decimal(4)) AS to_nu,CAST('100.2345' AS decimal(4)) AS to_nu,CAST('100.2345' AS decimal(9)) AS to_nu,CAST('100.2345' AS decimal(9)) AS to_nu,CAST('100.2345' AS decimal(18)) AS to_nu,CAST('100.2345' AS decimal(18)) AS to_nu,CAST('100.2345' AS decimal(38)) AS to_nu,CAST('100.2345' AS decimal(38)) AS to_nu,CAST('100.2345' AS decimal(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
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|

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

-- 转换后PostgreSQL SQL：
SELECT make_interval(0, 0, 0, 10, 0, 0, 0),make_interval(0, 0, 0, 0, 10, 0, 0),make_interval(0, 0, 0, 0, 0, 10, 0),make_interval(0, 0, 0, 0, 0, 0, 10)
make_interval                                 |make_interval                                 |make_interval                                 |make_interval                                 |
----------------------------------------------+----------------------------------------------+----------------------------------------------+----------------------------------------------+
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|

3.2.5.3. TO_BLOB

PostgreSQL不支持

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

-- 转换后PostgreSQL SQL：
SELECT CAST('010101' AS text)
text  |
------+
010101|

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

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

3.2.5.6. TO_CLOB

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

参数解释

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

警告

不支持2个及2个以上参数的用法。

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_test_clob (c1 clob,c2 varchar2(10));
INSERT INTO unisql_test_clob VALUES (TO_CLOB('1'),'orale');
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         |

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

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

-- 转换后PostgreSQL SQL：
SELECT CAST(to_timestamp('2012/12/12', 'yyyy-MM-dd') AS timestamp),CAST(to_timestamp('2023-12-02', 'yyyy-mm-dd') AS timestamp),CAST(to_timestamp('2023-12-02 11:23:33', 'yyyy-mm-dd HH24:MI:SS') AS timestamp)
to_timestamp           |to_timestamp           |to_timestamp           |
-----------------------+-----------------------+-----------------------+
2012-12-12 00:00:00.000|2023-12-02 00:00:00.000|2023-12-02 11:23:33.000|

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

-- 转换后PostgreSQL SQL：
SELECT CAST('0123456' AS numeric),CAST('1.2' AS numeric),CAST('0' AS numeric),CAST(1.2 AS numeric),CAST('-1' AS numeric),CAST(-1 AS numeric)
numeric|numeric|numeric|numeric|numeric|numeric|
-------+-------+-------+-------+-------+-------+
 123456|    1.2|      0|    1.2|     -1|     -1|

3.2.5.9. TO_NCHAR (character)

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

参数解释

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

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_test_nchar(col1 INT,col2 VARCHAR2(20));
INSERT INTO unisql_test_nchar VALUES(1,'unisql tool');
SELECT TO_NCHAR(col1),TO_NCHAR(col2) FROM unisql_test_nchar;
TO_NCHAR(COL1)|TO_NCHAR(COL2)|
--------------+--------------+
1             |unisql tool   |

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

3.2.5.10. TO_NCHAR (datetime)

PostgreSQL不支持

3.2.5.11. TO_NCHAR (number)

PostgreSQL不支持

3.2.6. 编码解码函数

3.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)大'),
      DECODE(INSTR('CLARK','S'), 0, '不含有 S', '含有 S') "CLARK",
 DECODE(INSTR('KING','S'), 0, '不含有 S', '含有 S') "KING",
 DECODE(INSTR('MILLER','S'), 0, '不含有 S', '含有 S') "MILLER",
 DECODE(INSTR('ADAMS','S'), 0, '不含有 S', '含有 S') "ADAMS",
 DECODE(INSTR('FORD','S'), 0, '不含有 S', '含有 S') "FORD",
 DECODE(INSTR('JONES','S'), 0, '不含有 S', '含有 S') "JONES"
 FROM DUAL;
DECODE(SIGN((5*3-2)-(3*4-1)),0,'相等',1,'(5*3-2)大','(3*4-1)大')|CLARK|KING |MILLER|ADAMS|FORD |JONES|
------------------------------------------------------------+-----+-----+------+-----+-----+-----+
(5*3-2)大                                                    |不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |

-- 转换后PostgreSQL SQL：
SELECT CASE WHEN SIGN((5*3-2)-(3*4-1))=0 THEN '相等' WHEN SIGN((5*3-2)-(3*4-1))=1 THEN '(5*3-2)大' ELSE '(3*4-1)大' END,CASE WHEN strpos('CLARK', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "CLARK",CASE WHEN strpos('KING', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "KING",CASE WHEN strpos('MILLER', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "MILLER",CASE WHEN strpos('ADAMS', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "ADAMS",CASE WHEN strpos('FORD', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "FORD",CASE WHEN strpos('JONES', 'S')=0 THEN '不含有 S' ELSE '含有 S' END AS "JONES"
case    |CLARK|KING |MILLER|ADAMS|FORD |JONES|
--------+-----+-----+------+-----+-----+-----+
(5*3-2)大|不含有 S|不含有 S|不含有 S |含有 S |不含有 S|含有 S |

3.2.7. 空值函数

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

-- 转换后PostgreSQL SQL：
SELECT coalesce(10, '1'),coalesce(NULL, 1),coalesce(0/1, 1)
coalesce|coalesce|coalesce|
--------+--------+--------+
      10|       1|       0|

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

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

3.2.7.3. IS NULL

空值比较

示例

-- 转换前Oracle SQL：
CREATE TABLE unisql_employee(employee_id int,name varchar(30),salary int,department_id int,hire_date varchar(10),commission_pct number);
SELECT * FROM  unisql_employee WHERE employee_id IS NULL
EMPLOYEE_ID|NAME|SALARY|DEPARTMENT_ID|HIRE_DATE|COMMISSION_PCT|
-----------+----+------+-------------+---------+--------------+

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

3.2.8. 环境和标识符函数

3.2.8.1. SYS_GUID

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

参数解释

无

示例

警告

结果格式或表现存在不一致的情况。如果对数据完全一致要求较高，建议不使用该特性或对结果进一步处理。

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

-- 转换后PostgreSQL SQL：
SELECT gen_random_uuid()
gen_random_uuid                     |
------------------------------------+
6b846107-4402-46da-832f-c9738a6e029e|

3.2.8.2. USER

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT USER
user   |
-------+
lightdb|

3.2.8.3. SYS_CONTEXT

PostgreSQL不支持

3.2.9. 层次函数

3.2.9.1. SYS_CONNECT_BY_PATH

语法: SYS_CONNECT_BY_PATH(column,'char')
描述: 该函数返回从根到节点的列值的路径，由 CONNECT BY 条件返回的每一行的列值用指定分隔符号分隔。该函数仅在层次查询中有效

参数解释

参数	说明
column	指定返回数据的列名。可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型。
char	指定分隔符号。可以是 CHAR、VARCHAR2、NCHAR 或 NVARCHAR2 数据类型。

示例

-- 建表语句：
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',null);
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         |

-- 转换后PostgreSQL SQL：
WITH RECURSIVE tmp AS (SELECT z_id AS "$z_id",name AS "$name",n_id AS "$n_id",concat('/', name) AS "Path",NULL AS "prior$n_id" FROM unisql_sys_connect_by_path_test WHERE n_id IS NOT NULL UNION ALL SELECT unisql_sys_connect_by_path_test.z_id AS "$z_id",unisql_sys_connect_by_path_test.name AS "$name",unisql_sys_connect_by_path_test.n_id AS "$n_id",concat(concat("Path", '/'), unisql_sys_connect_by_path_test.name) AS "Path",CAST("$n_id" AS text) AS "prior$n_id" FROM unisql_sys_connect_by_path_test , tmp WHERE tmp."$n_id"=unisql_sys_connect_by_path_test.z_id) SELECT tmp."$z_id" AS z_id,tmp."$name" AS name,tmp."$n_id" AS n_id,tmp."Path" AS "Path" FROM tmp WHERE tmp."$n_id" IN (0,1,2) ORDER BY tmp."$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      |

3.2.10. 聚合函数

3.2.10.1. AVG

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.2.10.2. CORR

聚合函数用法 PostgreSQL不支持

3.2.10.3. COUNT

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

-- 转换后PostgreSQL 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|

3.2.10.4. COVAR_POP

聚合函数用法 PostgreSQL不支持

3.2.10.5. COVAR_SAMP

聚合函数用法 PostgreSQL不支持

3.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 } ]]...)

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

说明

作为聚合函数，该函数的参数标识每个聚合组中的单个假设行。因此，它们必须全部计算为每个聚合组内的常量表达式。常量参数表达式和 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|

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

3.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 } ]]... )

描述: 该函数功能是计算有序行组中行的秩。秩的值是从 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|

-- 转换后PostgreSQL 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|
-- 转换后PostgreSQL SQL：
denserank|
---------+
        2|

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

-- 转换后PostgreSQL SQL：
SELECT STRING_AGG(name, '; ' 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;

3.2.10.9. MAX

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.2.10.10. MEDIAN

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

-- 转换后PostgreSQL SQL：
SELECT department_id,percentile_cont(5e-01) WITHIN GROUP (ORDER BY salary) FROM unisql_employee AS ke GROUP BY department_id
department_id|percentile_cont|
-------------+---------------+
            1|        10000.0|
            2|        25000.0|
            3|        50000.0|

3.2.10.11. MIN

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.2.10.12. PERCENT_RANK

聚合函数用法 PostgreSQL不支持

3.2.10.13. PERCENTILE_CONT

语法

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

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.2.10.14. PERCENTILE_DISC

语法

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

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.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 计算由参数 expr 标识的假设行相对于指定排序规范的排名。参数表达式 expr 和 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 RANK(40) WITHIN GROUP (ORDER BY id DESC) "Rank of 40" FROM unisql_test_data;
Rank of 40|
----------+
         1|

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

3.2.10.16. STDDEV

语法

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

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

说明

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

参数解释

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

示例

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

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

3.2.10.17. STDDEV_POP

语法

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

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

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

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

3.2.10.19. SUM

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.2.10.20. VAR_POP

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

说明

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

参数解释

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

示例

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

-- 转换后PostgreSQL SQL：
SELECT var_pop(value) AS pop_variance FROM unisql_test_data
pop_variance        |
--------------------+
825.0000000000000000|

3.2.10.21. VAR_SAMP

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

说明

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

参数解释

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

示例

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

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

3.2.10.22. VARIANCE

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

说明

作为聚合函数使用时，该函数对一组行的集合进行聚合计算，结果只能返回一个值，此时不需要加 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|

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

3.2.11. 分析函数

3.2.11.1. AVG

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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.0000000000000000|
          2| 10000|            1| 7500.0000000000000000|
          3| 15000|            1|10000.0000000000000000|
          4| 20000|            2|    20000.000000000000|
          5| 30000|            2|    25000.000000000000|
          6| 50000|            3|    50000.000000000000|

3.2.11.2. CORR

分析函数用法 PostgreSQL不支持

3.2.11.3. COUNT

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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|

3.2.11.4. COVAR_POP

分析函数用法 PostgreSQL不支持

3.2.11.5. COVAR_SAMP

分析函数用法 PostgreSQL不支持

3.2.11.6. CUME_DIST

语法

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

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

说明

作为分析函数，该函数计算指定值在一组值中的相对位置。对于行 row，假设按升序排列，row 的 cume_dist 是值低于或等于 row 值的行数除以被计算的行数（整个查询结果集或分区）。

参数解释

参数	说明
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|

-- 转换后PostgreSQL SQL：
SELECT CUME_DIST() OVER (ORDER BY age) AS cumulative_distribution 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|

3.2.11.7. DENSE_RANK

语法

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

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

说明

作为一个分析函数，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|

-- 转换后PostgreSQL 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|

-- 转换后PostgreSQL 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|

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

-- 转换后PostgreSQL SQL：
SELECT string_agg(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            |

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

-- 转换后PostgreSQL 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|

-- 转换后PostgreSQL SQL：
SELECT employee_id,name,salary,FIRST_VALUE(name) OVER (ORDER BY salary) 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|

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

-- 转换后PostgreSQL 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|            |
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|

-- 转换后PostgreSQL SQL：
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|

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

-- 转换后PostgreSQL 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|

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

-- 转换后PostgreSQL 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|        |

-- 转换后PostgreSQL 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|        |

3.2.11.13. MAX

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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|

3.2.11.14. MEDIAN

分析函数用法 PostgreSQL不支持

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

-- 转换后PostgreSQL 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|

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

-- 转换后PostgreSQL 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|

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

-- 转换后PostgreSQL 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                      |

3.2.11.18. PERCENT_RANK

语法

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

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

说明

作为分析函数，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|

-- 转换后PostgreSQL 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|

3.2.11.19. PERCENTILE_CONT

分析函数用法 PostgreSQL不支持

3.2.11.20. PERCENTILE_DISC

分析函数用法 PostgreSQL不支持

3.2.11.21. RANK

语法

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

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

说明

作为分析函数，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|

-- 转换后PostgreSQL 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|

-- 转换后PostgreSQL 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|

3.2.11.22. STDDEV

语法

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

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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|

3.2.11.23. STDDEV_POP

语法

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

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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|

3.2.11.24. STDDEV_SAMP

语法

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

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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|

3.2.11.25. SUM

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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|

3.2.11.26. VAR_POP

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

说明

作为分析函数使用时，需要使用 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|

-- 转换后PostgreSQL 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.000000000000|
            2|ADA  | 20000|25000000.000000000000|
            3|KATE | 50000|                    0|

3.2.11.27. VAR_SAMP

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

说明

作为分析函数使用时，需要使用 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|                                                   |

-- 转换后PostgreSQL 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.000000000000|
            1|JACK |  5000|25000000.000000000000|
            1|LINDA| 15000|25000000.000000000000|
            2|TINA | 30000|50000000.000000000000|
            2|ADA  | 20000|50000000.000000000000|
            3|KATE | 50000|                     |

3.2.11.28. VARIANCE

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

说明

作为分析函数使用时，需要使用 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|


-- 转换后PostgreSQL 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|

3.2.12. 序列伪列

3.2.12.1. seq_name.nextval

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

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

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

-- 转换后PostgreSQL SQL：
SELECT nextval('uni_seq') FROM unisql_employee AS ke.
nextval|
-------+
      1|
      2|
      3|
      4|
      5|
      6|

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

-- 转换后PostgreSQL SQL：
SELECT CTID,ke.* FROM unisql_employee AS ke
ctid |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.20|
(0,4)|          4|ADA  | 20000|            2|2023-04-01|          0.10|
(0,5)|          5|TINA | 30000|            2|2023-05-01|          0.20|
(0,6)|          6|KATE | 50000|            3|2023-06-01|          0.30|

3.2.13. 其他

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

-- 转换后PostgreSQL SQL：
SELECT * FROM unisql_regexp_like_test WHERE unisql.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|