函数

概述

AntDB 为操作内置数据类型的值提供了大量函数和操作符。

数学函数

下面的表显示了 AntDB 兼容的 Oracle 数学函数。AntDB 支持大部分的隐式类型转换，通过使用不同的参数类型，以多种方式使用这些函数。除非特别说明，任何形式的函数都返回和它的参数类型相同的值。

函数名	参数个数	描述	举例
ABS	1	绝对值	ABS(-15)
BITAND	2	按位与	BITAND(6,3)
CEIL	1	向上取整	CEIL(5.2)
EXP	1	e 的 n 次方	EXP(4)
FLOOR	1	向下取整	FLOOR(15.7)
LN	1	LN(x),x 的自然对数	LN(95)
LOG	2	log(x,y) 返回以 x 为底的数值 y 的对数	LOG(10,100)
MOD	2	mod(x,y) 返回 x 除以 y 的余数	MOD(11,4)
POWER	2	power(x, y)，x 的 y 次方	POWER(3,2)
REMAINDER	2	remainder(x,y)，返回 x 除以 y 的余数	REMAINDER(bin_float, bin_double)
ROUND (number)	1~2	ROUND(n [, integer ]) 四舍五入	round(4.5455, 2) round(4.4)
SIGN	1	返回一个数字的正负标志	SIGN(-15)
SQRT	1	平方根	SQRT(26)
TRUNC (number)	1~2	TRUNC(n1 [, n2 ]) 按照指定的精度截断	trunc(4.567,2)
WIDTH_BUCKET	4	WIDTH_BUCKET(x, min, max, num_buckets)，范围 MIN 到 MAX 被分为 bainum_buckets 节，每节有相同 du 的大小。返回 x 所在的那一节。如果 x 小于 MIN，将返回 0，如果 x 大于或等于 MAX，将返回 num_buckets+1.MIN 和 MAX 都不能为 NULL，num_buckets 必须是一个正整数。如果 x 是 NULL，则返回 NULL。	WIDTH_BUCKET(credit_limit, 100, 5000, 10)

下面的表显示了允许使用的三角函数。

函数名	参数个数	描述	举例
ATAN	1	反正切	ATAN(.3)
ATAN2	2	反正切	ATAN2(.3, .2)
COS	1	余弦	COS(180 * 3.14159265359/180)
COSH	1	双曲余弦	COSH(0)
ACOS	1	反余弦，参数取值范围 (-1, 1)	ACOS(.3)
SIN	1	正弦	SIN(30 * 3.14159265359/180)
SINH	1	返回x的双曲正弦值	SINH(1)
ASIN	1	反正弦，参数取值范围 (-1, 1)	ASIN(.3)
TAN	1	正切	TAN(135 * 3.14159265359/180)
TANH	1	双曲正切	TANH(.5)

字符串函数

在这个章节中介绍了用于检测和操作字符串值的函数。AntDB 支持隐式类型转换，会将函数参数转化为合理的参数类型，但是并非支持全部的类型转换。所以用户使用函数时，最好按照每个函数的参数类型要求去使用。

返回字符类型的字符串函数

名称	参数个数	功能	举例
CHR	1	将 ASCII 转换为字符。	CHR(56) CHR (196 USING NCHAR_CS)
CONCAT	2	CONCAT(char1, char2) 字符串连接。	concat('Ant', 'DB')
INITCAP	1	将每个单词的第一个字母大写,其它字母变为小写返回。	INITCAP('the soap')
LOWER	1	字符串转换为小写。	LOWER('MR. SCOTT MCMILLAN')
UPPER	1	将字母转成大写。	UPPER(last_name)
LPAD	2~3	LPAD(string, length INTEGER [, fill ]) 通过使用参数 fill 指定的字符（缺省是空格）从左边填充参数 string，使其达到长度 length。如果字符串的长度超过参数 length 指定的长度,那么就会从右边截断 字符串。	LPAD('Page 1',15,'*.')
RPAD	2~3	RPAD(string, length INTEGER [, fill ]) 通过使用字符 fill（缺省是空格）从右边填充字符串 string，使其达到参数 length 指定的长度。如果字符 string 的长度超过参数 length 的值，那么将把字符串 string 截断为 length 指定的长度。
LTRIM	1~2	去除字符串的左边的空格或者字符。	LTRIM('<=====>BROWNING<=====>', '<>=')
RTRIM	1~2	去除字符串的右边的空格或者字符。	RTRIM('<=====>BROWNING<=====>', '<>=')
NLS_INITCAP	1	NLS_INITCAP（string）功能：返回字符串每个单词第一个字母大写而单词中的其他字母小写的 string。Oracle 中此函数支持 2 个参数，AntDB 只做了部分支持。	SELECT nls_initcap('a c b d e') FROM dual;
NLS_LOWER	1	NLS_LOWER（string）返回字符串中的所有字母都是小写形式的 string。Oracle 中此函数支持 2 个参数，AntDB 只做了部分支持。	SELECT nls_lower('A c B d e') FROM dual;
NLS_UPPER	1	nls_upper（string）返回字符串中的所有字母都是大写的形式的 string。Oracle 中此函数支持 2 个参数，AntDB 只做了部分支持。	SELECT nls_upper('A c B d e') FROM dual;
REGEXP_REPLACE	2~6	REGEXP_REPLACE ( source_char, pattern [, replace_string [, position [, occurrence [, match_param ]]] ] ) 用于通过正则表达式来进行匹配替换，默认情况下，每次匹配到的正则，都替换为 replace_string。	SELECT regexp_replace('13,453,5', '([0-9]{2,3})', '''\1''') FROM dual;
REGEXP_SUBSTR	2~6	REGEXP_SUBSTR(String, pattern, position, occurrence, modifier) 在 source_char 中搜索与指定的模式匹配的字符串。	REGEXP_SUBSTR('11a22A33a','[^A]+',1,1,'i')
REGEXP_COUNT	2~4	REGEXP_COUNT ( source_char, pattern [, position [, match_param]]) pattern 在source_char 串中出现的次数。如果未找到匹配，则函数返回 0。	REGEXP_COUNT('reinitializing', 'i', 1)
REPLACE	2~3	REPLACE(char, search_string [, replacement_string ]) 字符串替换。	SELECT replace ('asd','sd','aa') FROM dual;
TRANSLATE	3	TRANSLATE ( 'char' , 'from_string' , 'to_string' ) 返回将 from_string 中的每个字符替换为 to_string 中的相应字符以后的 string。	TRANSLATE('SQL*Plus User''s Guide', ' */''', '___')
SOUNDEX	1	返回 string 的声音表示形式。这对于比较两个拼写不同但是发音类似的单词而言很有帮助。	SOUNDEX(last_name) = SOUNDEX('SMYTHE')
SUBSTR	2~3	SUBSTR (char, position [, substring_length ]) 从字符串中的参数 position 指定位置，开始获取参数 substring_length 指定的字符个数，作为子字符串从参数 string中 取出。如果没有指定 count, 那么子字符串的长度就是从字符串的开始位置到字符串的结尾。	SELECT substr('Hello World', 1,3) FROM dual;

返回数值类型的字符串函数

名称	参数个数	功能	举例
ASCII	1	字符对应的 ASCII 码的数字	ASCII(SUBSTR(last_name, 1, 1))
INSTR	2~4	INSTR(string , substring [, position [, occurrence ] ])要截取的字符串在源字符串中的位置	INSTR('CORPORATE FLOOR','OR', 3, 2)
LENGTHB	1	LENGTHB 给出该字符串的 byte	SELECT lengthb('你好') FROM dual;
LENGTH	1	length(string)string 返回字符串的字符个数	SELECT length('你好') FROM dual;
REGEXP_COUNT	2~4	REGEXP_COUNT ( source_char, pattern [, position [, match_param]]) 返回 pattern 在 source_char 串中出现的次数。如果未找到匹配，则函数返回 0。position 变量告诉 Oracle 在源串的什么位置开始搜索。在开始位置之后每出现一次模式，都会使计数结果增加 1。	SELECT REGEXP_COUNT('GEORGE','GE',1,'i') FROM DUAL;
REGEXP_INSTR	2~7	正则表达式搜索字符串，返回的字符串中的匹配内容的位置信息。	REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6)

日期和时间函数

下表显示了所有可以用于处理日期/时间类型值的函数，在进行本节的内容学习前，应该参考前面数据类型章节中的日期/时间类型的背景信息。

名称	参数个数	功能	举例
ADD_MONTHS	2	ADD_MONTHS(DATE, NUMBER) 返回参数 DATE 指定日期加上参数 NUMBER 指定的月数后的结果。	ADD_MONTHS(hire_date, 1)
EXTRACT (datetime)	2	EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } FROM { expr }) 找出日期或间隔值的字段值。	SELECT EXTRACT(MONTH FROM SYSDATE) "MONTH" FROM DUAL;
LAST_DAY	1	LAST_DAY(DATE) 返回日期代表月份的最后一天，如果日期包含时间部分，这部内容对于结果没有影响。	LAST_DAY(SYSDATE)
MONTHS_BETWEEN	2	MONTHS_BETWEEN(date1, date2) 返回两个日期间之间的月数。	SELECT months_between(to_date('2018-11-12', 'yyyy-mm-dd'), to_date('2017-11-12', 'yyyy-mm-dd')) AS zs FROM dual;
NEW_TIME	3	NEW_TIME(date, timezone1, timezone2) 将一个日期和时间值转换成另一种时区的日期和时间值。	new_time(sysdate,'PDT','GMT')
NEXT_DAY	2	NEXT_DAY(DATE, dayofweek) 参数 2 是个枚举值，在指定日期后参数 dayofweek 代表的日期。	SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL;
ROUND (date)	1~2	ROUND（r[,f]）将日期 r 按 f 的格式进行四舍五入。如果 f不填，则四舍五入到最近的一天。 ROUND (date)参数 2 是个枚举值： CC,SCCSYYYY,YYYY,YEAR,SYEAR,YYY,YY,Y IYYY,IY,IY,I Q MONTH,MON,MM,RM WW IW W DDD,DD,J,Day DAY,DY,D HH,HH12,HH24,Hour MI	ROUND (TO_DATE ('27-OCT-00'),'YEAR')
SYS_EXTRACT_UTC	1	SYS_EXTRACT_UTC(datetime_with_timezone) 返回与指定时间相对应的标准 UTC 时间。	SELECT SYS_EXTRACT_UTC(TIMESTAMP '2020-03-28 11:30:00.00 -08:00') FROM DUAL;
TRUNC (date)	1~2	TRUNC（date[,fmt]）按照指定格式截取日期。	TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
CURRENT_DATE	0	调用不需要括号和参数，返回系统当前日期。	CURRENT_DATE
CURRENT_TIMESTAMP	0~1	CURRENT_TIMESTAMP [ (timestamp_precision) ] 可以带参数，也可以不带参数，以 timestamp with time zone 数据类型返回当前会话时区中的当前日期。	CURRENT_TIMESTAMP
DBTIMEZONE	0	调用不需要括号和参数。	DBTIMEZONE
SYSDATE	-1	返回目前系统的时间。	SYSDATE
SYSTIMESTAMP	-1	返回本机数据库上当前系统日期和时间(包括微秒和时区)。	SYSTIMESTAMP
SESSIONTIMEZONE	-1	返回会话时区。	SESSIONTIMEZONE
LOCALTIMESTAMP	0~1	可以带参数，也可以不带参数。	LOCALTIMESTAMP [ (timestamp_precision) ]

ADD_MONTH

函数 ADD_MONTHS 在指定的日期上加上（如果第二个参数是负数，那么就是减）指定月数。

在执行结果中日期和在给定日期中月份的日期是相同的，除非指定日期是月份的最后一天，在这种情况下，所得到的结果日期是执行结果中月份的最后一天。

示例：

SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;

ADD_MONTHS('13-JUN-07',4)  
----------------------------
 2007-10-13 00:00:00
(1 row)

SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL;

ADD_MONTHS('31-DEC-06',2)  
----------------------------
 2007-02-28 00:00:00
(1 row)


SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL;

ADD_MONTHS('31-MAY-04',-3)  
-----------------------------
 2004-02-29 00:00:00
(1 row)

EXTRACT

函数 EXTRACT 用于从日期/时间字段中获取像年份或小时这样的子字段。函数 EXTRACT 返回值的类型是 number。下面是有效字段名称：

• YEAR

表示年的字段。

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40')  
-------------------------------------------------------
                                                2001
(1 row)

• MONTH

表示一年中的月份（1-12）。

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40')  
----------------------------------------------------------
                                                    2
(1 row)

• DAY

表示一个月中的日期（1-31）。

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40')  
-------------------------------------------------------
                                                 16
(1 row)

• HOUR

表示小时字段（0-23）。

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40')  
-----------------------------------------------------
                                                  20
(1 row)

• MINUTE

表示分钟字段（0-59）。

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40')  
---------------------------------------------------------
                                                    38
(1 row)

• SECOND

表示秒字段，包含小数部分（0-59）。

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40.45') FROM DUAL;

 EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40.45')  
-----------------------------------------------------------
                                                    40.45
(1 row)

SYS_EXTRACT_UTC

SYS_EXTRACT_UTC 函数是返回与指定时间相对应的标准 UTC 时间。

语法：

SYS_EXTRACT_UTC (datetime_with_timezone)

示例：

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2020-03-28 11:30:00.00 -08:00') FROM DUAL;

SYS_EXTRACT_UTC(TIMESTAMP '2020-03-28 11:30:00.00 -08:00')  
-------------------------------------------------------------
 2020-03-28 03:30:00
(1 row)

MONTHS_BETWEEN

函数 MONTHS_BETWEEN 返回 2 个日期之间的月数。如果第一个日期大于第二个日期，那么返回结果是一个正数数值，反之，返回结果则是一个负数。

如果所有日期参数中月份的日期相同，或者所有日期参数分别是月份中的最后一天，那么结果就是整的月数。

示例：

SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL;

MONTHS_BETWEEN('15-DEC-06','15-OCT-06')  
------------------------------------------
                                        2
(1 row)


SELECT MONTHS_BETWEEN('15-OCT-06','15-DEC-06') FROM DUAL;

MONTHS_BETWEEN('15-OCT-06','15-DEC-06')  
------------------------------------------
                                       -2
(1 row)


SELECT MONTHS_BETWEEN('31-JUL-00','01-JUL-00') FROM DUAL;

MONTHS_BETWEEN('31-JUL-00','01-JUL-00')  
------------------------------------------
                        0.967741935483871
(1 row)


SELECT MONTHS_BETWEEN('01-JAN-07','01-JAN-06') FROM DUAL;

MONTHS_BETWEEN('01-JAN-07','01-JAN-06')  
------------------------------------------
                                       12
(1 row)

NEXT_DAY

函数 NEXT_DAY 返回大于指定日期参数的第一个周日期。我们应该至少指定周日期的前三个字母，例如 SAT，如果日期中包括时间部分，对结果没有影响。

下面是函数 NEXT_DAY 的示例。

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY') FROM DUAL;

NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY')  
-----------------------------------------------------
 2007-08-19 00:00:00
(1 row)

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON') FROM DUAL;

NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON')  
---------------------------------------------------
 2007-08-20 00:00:00
(1 row)

SELECT NEXT_DAY(current_timestamp,'SUNDAY') FROM DUAL;

 NEXT_DAY(CURRENT_TIMESTAMP,'SUNDAY')  
---------------------------------------
 2020-10-04 17:08:05
(1 row)

LAST_DAY

日期 date 所在月份的最后一天的日期。

语法：

LAST_DAY (date)

举例：

SELECT SYSDATE, LAST_DAY(SYSDATE)Last, LAST_DAY(SYSDATE)-SYSDATE "Left" FROM DUAL;
     ORA_SYS_NOW          |        Last          | Left 
--------------------------+----------------------+------
   2020-09-29 17:12:29    | 2020-09-30 17:12:29  |   1
(1 row)

NEW_TIME

函数 NEW_TIME 用于将一个日期和时间值从一个时区转换到另外一个时区。这个函数返回值类型为 DATE。

语法：

NEW_TIME(DATE, time_zone1, time_zone2)

其中参数 time_zone1 和 time_zone2 必须是从下面列表中列 Time Zone 中取出的字符串类型值。

时区	以UTC为基准的偏移时区	描述
AST	UTC+4	大西洋(Atlantic)标准时间
ADT	UTC+3	大西洋(Atlantic)夏令时间
BST	UTC+11	白令海(Bering)标准时间
BDT	UTC+10	白令海(Bering)夏令时间
CST	UTC+6	中央(Central)标准时间
CDT	UTC+5	中央(Central)夏令时间
EST	UTC+5	东部(Eastern)标准时间
EDT	UTC+4	东部((Eastern)夏令时间
GMT	UTC	格林尼治(Greenwich)标准时间
HST	UTC+10	阿拉斯加-夏威夷(Alaska-Hawaii)标准时间
HDT	UTC+9	阿拉斯加-夏威夷(Alaska-Hawaii)夏令时间
MST	UTC+7	山地(Mountain)标准时间
MDT	UTC+6	山地(Mountain)夏令时间
NST	UTC+3:30	纽芬兰(Newfoundland)标准时间
PST	UTC+8	太平洋(Pacific)标准时间
PDT	UTC+7	太平洋(Pacific)夏令时间
YST	UTC+9	育空河(Yukon)标准时间
YDT	UTC+8	育空河(Yukon)夏令时间

下面是一个关于函数 NEW_TIME 的示例：

SELECT NEW_TIME(TO_DATE('08-13-07 10:35:15','MM-DD-YY HH24:MI:SS'),'AST', 'PST') "Pacific Standard Time" FROM DUAL;

Pacific Standard Time 
-----------------------
 2007-08-13 06:35:15
(1 row)

ROUND

函数 ROUND 根据指定的模板模式返回一个经过取整的日期类型值。如果没有指定模板模式，那么就把日期取整到最近的一天。下面的表显示了函数 ROUND 所使用的模板模式。

模式	描述
CC, SCC	如果最后两位数字小于等于 50，返回 1 月 1 号，在 cc01 中 cc 是给定年份的头两位数字。而如果最后 2 位数字大于 50，就要对给定的年份的前两位数字加上 1；（对于公元年份来说）
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	返回 1 月 1 日，在 yyyy 中，yyyy 被取整到与之最为接近的年，向前取整到 6 月 30 号，向后可取整到 7 月 1 号
IYYY, IYY, IY, I	将日期取整到 ISO 年份的开始，如果月份和日期在 6 月 30 号前，那么是向前取整， 如果月份和日期在 7 月 1 日后，则向后取整。
Q	如果月份和日期在每个季度第二个月的 15 号前，以向前取整的方式决定季度的第一天，如果月份和日期在每个季度的第二个月 16 日或者更往后，则向后取整。
MONTH, MON, MM, RM	如果月份的日期在每月的 15 日或者之前，那么返回指定月份的第一天；如果月份 的日子在 16 号或者之后，那么返回下一个月的第一天。
WW	取整到和一年中第一天相同的周日期对应的最近日期。
IW	取整到和 ISO 年份中第一天相同周的日期对应的最近日期。
W	取整到和月中第一天相同的周日期对应的最近日期。
DDD, DD, J	取整到最近日期的开始；如果日期是 11:59:59AM 或者更早，那么就取整到同一天的 开始时间，而如果日期是 12:00PM 或更往后，则将日期取整到第二天的开始时间。
DAY, DY, D	取整到最近的周日期为星期天的日期。
HH, HH12, HH24	取整到最近的小时数。
MI	取整到最近的分钟数。

下面是函数 ROUND 的使用示例。

下面的示例将日期取整到与之最接近的世纪的开始年份。

SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century   
------------------
 01-JAN-2001
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century   
-------------------
 01-JAN-2001
(1 row)

下面的示例将日期取整到与之最近年份的开始日期。

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year     
-------------
 01-JAN-1999
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

Year
-------------
01-JAN-2000
(1 row)

下面的示例将日期取整到与之最接近 ISO 年份的开始日期。第一个示例把日期取整为 2004 年，对于 2004 年的 ISO 年份来说，实际上是在 2003 年 12 月 29 号开始。第二个示例把日期取整到 2005 年。对于 2005 年的 ISO 年份来说，实际上是在 2005 年的 1 月 3 号开始。（ISO 年份是以 7 天为跨度从第一个星期一开始，从星期一到星期日，包含新的一年里至少 4 天的日期。因此，可以从前一年的 12 月开始一个 ISO 年份）

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

ISO Year
-------------
29-DEC-2003
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

ISO Year
-------------
03-JAN-2005
(1 row)

下面的示例把日期取整到与之最接近的季度。

SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

       Quarter       
---------------------
 2007-01-01 00:00:00
(1 row)


SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

       Quarter       
---------------------
 2007-04-01 00:00:00
(1 row)

下面的示例将日期取整与之到最近的月份。

SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

        Month        
---------------------
 2007-12-01 00:00:00
(1 row)

SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

        Month        
---------------------
 2008-01-01 00:00:00
(1 row)

下面的示例将日期取整与之最接近的星期。所以在第一个示例中，与 2007 年 1 月 18 日最接近的星期一是 2007 年 1 月 15 日。在第二个示例中，与 2007 年 1 月 19 日最接近的星期一是 2007 年 1 月 22 日。

SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week         
---------------------
 2007-01-15 00:00:00
(1 row)

SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week         
---------------------
 2007-01-22 00:00:00
(1 row)

下面的示例把日期取整与之到最近的以 ISO 格式表示的星期数。以 ISO 格式表示的周数从星期一开始，在第一个示例中，与日期 2004 年 1 月 1 日是最接近的星期一是 2003 年 12 月 29 日。在第二个示例中，与 2004 年 1 月 2 日最接近的星期一是 2004 年 1 月 5 日。

SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week       
---------------------
 2003-12-29 00:00:00
(1 row)

SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week       
---------------------
 2004-01-05 00:00:00
(1 row)

下面的示例把日期值取整到与之最接近的一周，其中周的开始日期被认为是和每月第一天相同。

SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week         
---------------------
 2007-03-08 00:00:00
(1 row)


SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week         
---------------------
 2007-03-01 00:00:00
(1 row)

下面的示例把日期值取整到与之最接近的日期。

SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

         Day         
---------------------
 2007-08-04 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

         Day         
---------------------
 2007-08-05 00:00:00
(1 row)

下面的示例把日期值取整到与之最接近，周日期为星期天的日期。

SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

     Day of Week     
---------------------
 2007-08-05 00:00:00
(1 row)

SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

     Day of Week     
---------------------
 2007-08-12 00:00:00
(1 row)

下面的示例把日期值取整到与之最接近的小时值。

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

Hour
--------------------
09-AUG-07 08:00:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

Hour
--------------------
09-AUG-07 09:00:00
(1 row)

下面的示例把日期值取整到与之最接近的分钟值。

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

Minute
--------------------
09-AUG-07 08:30:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

Minute
--------------------
09-AUG-07 08:31:00
(1 row)

TRUNC

函数 TRUNC 返回一个根据指定模板模式截断的日期类型值。如果没有使用模板模式，那么就把日期截断到离它最近的一天。下面的表格显示了函数 TRUNC 所允许使用的模板模式。

模式	描述
CC, SCC	返回 1 月 1 日，在 cc01 中 cc 是年份的前两位数字
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	返回 1 月 1 日，yyyy 是一个给出的年份
IYYY, IYY, IY, I	返回包含给定日期中 ISO 年份的开始日期
Q	返回包含给定日期中季度的第一天。
MONTH, MON, MM,RM	返回指定月份的第一天。
WW	返回当前日期所在的星期的第一天，以每年 1 月 1 日为第一周第一天。
IW	返回包含给出日期的 ISO 星期的星期一。
W	返回当前日期所在的星期的第一天，以每月 1 日为每周第一天。
DDD, DD, J	返回指定日期的开始时间。
DAY, DY, D	返回一周的开始时间（从周日开始），包含给出的日期。
HH, HH12, HH24	返回小时。
MI	返回分钟。

下面是一些关于使用 TRUNC 函数的示例。

下面的示例把日期向前截断为日期所在世纪的开始日期。

SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century   
-------------
 01-JAN-1901
(1 row)

下面的示例把日期截断到日期中年份的开始日期。

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year     
-------------
 01-JAN-1999
(1 row)

下面的示例把日期截断到 ISO 年份的开始日期。

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year   
-------------
 29-DEC-2003
(1 row)

下面的示例把日期截断到当前日期所在季度的开始日期。

SELECT TRUNC(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

       Quarter       
---------------------
 2007-01-01 00:00:00
(1 row)

下面的示例把日期截断到一个月的开始日期。

SELECT TRUNC(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

        Month        
---------------------
 2007-12-01 00:00:00
(1 row)

下面的示例把日期截断到日期所在周的开始日期，周的开始日期是由一年中第一天的周日期决定的。例如：2007 年的第一天是周一，所以在 1 月 19 日前面的周一的日期是 1 月 15 号。

SELECT TRUNC(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week         
---------------------
 2007-01-15 00:00:00
(1 row)

下面的示例将日期截断到一个 ISO 周的开始。一个 ISO 周在星期一开始，落在 ISO 周的日期是 2004 年 1 月 2 号，那么这个 ISO 周从 2003 年 12 月 29 日开始。

SELECT TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week       
--------------------------------
 2003-12-29 00:00:00
(1 row)

下面的示例把日期截断到一周的开始日期，周的开始日期被认为和一个月的第一天相同。

SELECT TRUNC(TO_DATE('21-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week         
--------------------------------
 2007-03-15 00:00:00
(1 row)

下面的示例把日期截断到一天的开始时间。

SELECT TRUNC(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

         Day         
--------------------------------
 2007-08-04 00:00:00
(1 row)

下面的示例把日期截断到一周的开始日期（周日）。

SELECT TRUNC(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

     Day of Week     
--------------------------------
 2007-08-05 00:00:00
(1 row)

下面的示例把日期截断到小时。

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

Hour
--------------------------------
09-AUG-07 08:00:00
(1 row)

下面的示例把日期截断到分钟。

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

Minute
------------------------------
09-AUG-07 08:30:00
(1 row)

当前日期/时间

AntDB 提供了很多函数，用于返回与当前日期和时间相关的值。SYSDATE 和 SYSTIMESTAMP 返回当前执行 SQL 的时间，其它函数的返回值都是基于当前事务的开始时间，它们的值在事务运行期间不改变，它的目的是允许单个事务有一致的当前时间，所以在同一事务中进行多个修改操作使用相同时间戳。

• CURRENT_DATE

• CURRENT_TIMESTAMP

• LOCALTIMESTAMP

• LOCALTIMESTAMP(precision)

• SYSDATE

• SYSTIMESTAMP

• SESSIONTIMEZONE

• DBTIMEZONE

我们可以给 LOCALTIMESTAMP 提供一个精度参数，这样可以将结果取整到在秒字段中多个小数位。如果没有精度参数，返回是完全有效的精度。

SELECT CURRENT_DATE FROM DUAL;

        DATE         
---------------------
 2020-09-29 17:28:28
(1 row)

SELECT LOCALTIMESTAMP FROM DUAL;

         TIMESTAMP          
----------------------------
 2020-09-29 17:28:41.485867
(1 row)

SELECT LOCALTIMESTAMP(2) FROM DUAL;

       TIMESTAMP        
------------------------
 2020-09-29 17:30:27.93
(1 row)

SELECT SYSDATE FROM DUAL;

     ORA_SYS_NOW     
---------------------
 2020-09-29 17:29:11
(1 row)

SELECT SYSTIMESTAMP FROM DUAL;
        ORA_SYS_NOW         
-----------------------------------------
 2020-09-29 17:30:02.186407
(1 row)

SELECT SESSIONTIMEZONE FROM DUAL;
 ORA_SESSION_TIMEZONE 
--------------------------------------------
 +08:00
(1 row)

SELECT CURRENT_TIMESTAMP FROM DUAL;
        CURRENT_TIMESTAMP         
-----------------------------------------------------
 2020-09-29 17:31:41.56663 +08:00
(1 row)

SELECT DBTIMEZONE FROM DUAL;
 ORA_DBTIMEZONE 
--------------------------------
 +08:00
(1 row)

转换函数

为将不同数据类型（包括日期/时间，整数，浮点数，数值）转换成格式化后的字符和将格式化后的字符串转换为特定的数据类型，AntDB 的格式化函数提供了很多功能强大的工具。这些函数都是有通用的调用约定：第一个参数是格式化的值，第二个参数是字符串模板，用于定义输出或输入的格式。

名称	参数个数	功能	举例
CONVERT	2~3	将字符串从一个字符集转化为另一个字符集，但是 AntDB 和 Oracle 的支持的字符集名字不同，这一点不兼容。	convert('text_in_utf8', 'UTF8', 'LATIN1')
NUMTODSINTERVAL	1	NUMTODSINTERVAL(x) 将数字 x 转换为一个 INTERVAL DAY TO SECOND 类型。	NUMTODSINTERVAL(100, 'day')
NUMTOYMINTERVAL	1	NUMTOYMINTERVAL(x) 将数字 x 转换为一个 INTERVAL YEAR TO MONTH 类型。	NUMTOYMINTERVAL(1,'year')
TO_CHAR (bfile | blob)	1~2	将 BFILE or BLOB 转化为 char 类型。	TO_CHAR(media_col, 873)
TO_CHAR (character)	1	将指定的参数转化为 string。	TO_CHAR('01110')
TO_CHAR (datetime)	1~2	TO_CHAR({ datetime | interval } [, fmt ]) 将日期按照指定的格式输出。	to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’)
TO_CHAR (number)	1~2	TO_CHAR(n [, fmt]) 按照指定的格式格式化数字。	to_char(1210.73, ‘9999.9’)
TO_DATE	1~2	TO_DATE(c[,fmt]) 将 c 转换为日期类型，如果 fmt 参数不为空，则按照 fmt 中指定格式进行转换。	TO_DATE('January 15, 1989, 11:00 A.M.','Month dd, YYYY, HH:MI A.M.')
TO_DSINTERVAL	1	将字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值，该函数可以用来对一个日期时间值进行加减计算。	TO_DSINTERVAL('100 00:00:00') TO_DSINTERVAL('P100DT05H')
TO_MULTI_BYTE	1	与 TO_SINGLE_BYTE 相对。	dump(TO_MULTI_BYTE( 'A'))
TO_NUMBER	1~2	TO_NUMBER(c[,fmt]) 字串按照 fmt 中指定格式转换为数值类型并返回。	TO_NUMBER('100.00', '9G999D99') to_number('$123,233,455,623.3400','$999,999,999,999.0000')
TO_SINGLE_BYTE	1	将 x 中的多字节字符转换为对应的单字节字符。返回类型与 x 类型相同。	TO_SINGLE_BYTE( CHR(15711393))
TO_TIMESTAMP	1~n	TO_TIMESTAMP(c1[,fmt]) 将指定字符按指定格式转换为timestamp 格式。	TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
TO_TIMESTAMP_TZ	1~n	将字符串转换为 TIMESTAMP WITH TIME ZONE。	TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM')
TO_YMINTERVAL	1~n	将字符串 X 转换为一个interval year to month 类型。	SELECT TO_YMINTERVAL('01-02') "14 months" FROM dual; SELECT TO_YMINTERVAL('P1Y2M') FROM dual;

注意：

convert 函数的作用是将字符串从一种字符集转化成另一种字符集，但是 AntDB 支持的字符集和 Oracle 支持的字符集名称不同。所以，虽然函数的功能兼容，但是字符集名称不兼容。

在 TO_CHAR 函数的输出模板字符串中，这里有固定的模式可以识别，并且由格式化的数值所替代，而任何不是标准模式的文字是简单的逐字拷贝。类似的情况，在一个输入模板字符串中（对除了 TO_CHAR 以外的其他函数），模板模式能够标识出输入字符串的部分和要寻找的值。

下面的表显示了在函数 TO_CHAR和TO_DATE 中可以用来格式化数值的模板模式。

模式	描述
HH	一天中的小时（01-12）
HH12	一天中的小时（01-12）
HH24	一天中的小时（00-23）
MI	分钟（00-59）
SS	秒（00-59）
SSSSS	从午夜算起已过去的秒数 （0-86399）
AM、A.M. 、PM 、P.M.	正午指示（大写）
am 、a.m. 、pm 、p.m.	正午指示（小写）
Y,YYY	年（由 4 到多个数字组成），用逗号分隔
YEAR	年
SYEAR	年（表示公元前的日期可在前面加上减号）
YYYY	年（4 位到多个数字组成）
SYYYY	表示年（4 位到多个数字组成）（表示公元前的日期可在前面加上减号）
YYY	表示年的后三位数字
YY	表示年的后两位数字
Y	表示年的最后一位数字
IYYY	符合 ISO 表示法的年份（4 位到多个数字组成）
IYY	符合 ISO 表示法年份的后三位数字
IY	符合 ISO 表示法年份的后两位数字
I	符合 ISO 表示法年份的最后一位数字
BC or B.C. or AD or A.D.	指示公元纪元（大写）
bc or b.c. or ad or a.d.	指示公元纪元（小写）
MONTH	全部大写的月份名称
Month	大小写混合的月份名称
month	全部小写的月份名称
MON	大写月份名称的缩写（3 个英文字符，如果本地化的语言，长度会有所变化）
Mon	大小写月份名称的缩写（3 个英文字符，如果本地化的语言，长度会有所变化）
Mon	小写月份名称的缩写（3 个英文字符，如果本地化的语言，长度会有所变化）
MM	代表月份数字（01-12）
DAY	大写日的名称
Day	大小写混合的日的名称
Day	小写日的名称
DY	大写日名称的缩写（3 个英文字符，如果本地化的语言，长度会有所变化）
Dy	大小写日名称的缩写（3 个英文字符，如果本地化的语言，长度会有所变化）
Dy	小写日名称的缩写（3 个英文字符，如果本地化的语言，长度会有所变化）
DDD	年的天数（001-366）
DD	月的天数（01-31）
D	一周中日期数（1-7, 星期日用1代表）
W	月的周数（1-5）（第一周是从本月的第一天开始）
WW	年的周数（1-53）（第一周从本年度的第一天开始）
IW	符合 ISO 表示法年的周数；新年中第一个星期二所在的第一周的日期
CC	世纪的数（2 位数字）; 21 世纪从 2001 年-01 月-01 日开始
SCC	和世纪的数相同，除了公元前日期前边有一个减号做前缀。
J	儒略日（从公元前 4712 年 1 月 1 日开始算起）
Q	季度
RM	以罗马数字表示的月份（I-XII; I 代表1 月）（大写）
Rm	以罗马数字表示的月份（I-XII; I 代表1 月）（小写）
RR	当给定年的最后两位数字的时候，确定年份的前 2 位数字。结果是通过使用当前年份和所给定的用 2 位数字表示年的算法来得到的。用 2 位数字所表示年份的前两位数字将和当前年份的前两位数字相同。除了下列情况：如果给定用 2 位数字表示的年小于 50，并且当前年的最后两位数字大于等于 50，那么当前年份的前两位数字加上 1 才等于给定年份的前两位数字。 如果给定用 2 位数字表示的年大于等于 50，并且当前年的最后两位数字小于 50，那么当前年份的前两位数字减去 1 等于给定年份的前两位数字。
RRRR	只影响函数 TO_DATE，允许指定用 2 位或 4 位数字表示的年份。如果指定了 2 位数字表示的年份，那么将返回如 RR 格式所表示年的前两位数字，如果使用了 4 位数字表示的年，那么返回 4 位数字的年。

某些修改者可以应用到任何模板模式，以用来修改它的行为。例如，FMMonth 是带有 FM 修改者的 Month 模式。下面的表显示了针对日期/时间格式的模式修改者。

修改者	描述	示例
FM prefix	填充模式（压缩添加的空格和零）	FMMonth
TH suffix	把序数后缀转换成大写	DDTH
th suffix	把序数后缀转换成小写	DDth
FX prefix	固定格式的全局选项（参见使用注意事项）	FX Month DD Day
SP suffix	拼写模式	DDSP

日期/时间格式函数的使用注意事项：

• FM 压缩前面的 0 和尾部的空格，这些 0 和空格用于使输出符合固定宽度的模式。

• 如果没有使用 FX 选项，TO_TIMESTAMP 和 TO_DATE 跳过输入字符串中的多个空格。必须将 FX 指定为模板中第一个成员。例如 TO_TIMESTAMP('2000JUN', 'YYYY MON') 这种方式是正确的，但是 TO_TIMESTAMP('2000 JUN', 'FXYYYYMON') 会返回错误，因为 TO_TIMESTAMP 只是期望一个空格。

• 在 TO_CHAR 函数中允许使用普通文本，并且函数的输出也是以文本方式实现的。

• 在把字符串从类型 timestamp 转换到 date 的过程中，如果这里有 YYY,YYYY, 或者是 Y,YYY 字段，那么 CC 字段可以忽略。如果使用了带有 YY 或者 Y 字段的 CC 值，那么年的计算方式就是 (CC-1)*100+YY。

下面的表格显示了格式化数值时可以使用的模板模式。

模式	描述
9	带有指定数字个数的值
0	以零开头的值
. (period)	小数点
, (comma)	组（以 1000 为单位）的分隔符
$	美元符号
PR	在尖括号中的负号
S	数值的固定符号（使用本地设置）
L	货币符号（使用本地设置）
D	小数点（使用本地设置）
G	组的分隔符（使用本地设置）
MI	在指明的位置的负号（如果数字 < 0）
PL	在指明的位置的正号（如果数字 > 0）
SG	在指明的位置的正/负号
RN or rn	罗马数字（输入范围是从 1 到 3999）
V	所移动数字的位数（参见下面的注意事项）

下面一些数值格式的使用注意事项：

• 数字 9 产生的值和 9s 的一样，如果没有指定数字的话，那么输出空格。

• TH 不转换小于 0 的值，并且不转换小数值。

V 有效地用 10 的 n 次方和输入值相乘，其中 n 是 V.TO_CHAR 后面数字的数量。在这里不支持使用 V 和小数点组合（例如，不允许使用99.9V99 这种形式）。

下面的表显示了一些使用 TO_CHAR 和 TO_DATE 函数的示例。

表达式	结果
TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD HH12:MI:SS')	' Wednesday, 30 11:17:21'
TO_CHAR(CURRENT_TIMESTAMP,'FMDay, FMDD HH12:MI:SS')	' Wednesday, 30 11:16:34'
TO_CHAR(-0.1, '99.99')	' -.10'
TO_CHAR(-0.1, 'FM9.99')	'-.1'
TO_CHAR(0.1, '0.9')	' 0.1'
TO_CHAR(12, '9990999.9')	' 0012.0'
TO_CHAR(12, 'FM9990999.9')	'0012.'
TO_CHAR(485, '999')	' 485'
TO_CHAR(-485, '999')	'-485'
TO_CHAR(1485, '9,999')	' 1,485'
TO_CHAR(1485, '9G999')	' 1,485'
TO_CHAR(148.5, '999.999')	' 148.500'
TO_CHAR(148.5, 'FM999.999')	'148.5'
TO_CHAR(148.5, 'FM999.990')	'148.500'
TO_CHAR(148.5, '999D999')	' 148.500'
TO_CHAR(3148.5, '9G999D999')	' 3,148.500'
TO_CHAR(-485, '999S')	'485-'
TO_CHAR(-485, '999MI')	'485-'
TO_CHAR(485, '999MI')	'485 '
TO_CHAR (-485,'9SG99')	‘4-85’
TO_CHAR (485,'9PL99')	‘4+85’
TO_CHAR(485, 'FM999MI')	'485'
TO_CHAR(-485, '999PR')	'<485>'
TO_CHAR(485, 'L999')	' 485'
TO_CHAR(485, 'RN')	' CDLXXXV'
TO_CHAR(485, 'FMRN')	'CDLXXXV'
TO_CHAR(5.2, 'FMRN')	'V'
TO_CHAR(12, '99V999')	' 12000'
TO_CHAR(12.4, '99V999')	' 12400'
TO_CHAR(12.45, '99V9')	' 125'

聚合和分析函数

聚合函数的作用是根据一个输入值的集合计算出一个单独的结果值，下面的表中列出了内置的聚合函数。

名称	参数个数	功能	举例
AVG	1	用于计算一个组和数据窗口内表达式的平均值。	SELECT avg(col) FROM tb1; avg(distinct col) avg(all col)
COUNT	1	对一组内发生的事情进行累积计数	count(col) count(distinct col) count(all col) count(*)
MAX	1	在一个组中的数据窗口中查找表达式的最大值	max(col) max(distinct col) max(all col)
MIN	1	在一个组中的数据窗口中查找表达式的最小值	min(col) min(distinct col) min(all col)
SUM	1	该函数计算组中表达式的累积和	sum(col) sum(distinct col) sum(all col)

应该注意的是除了函数 COUNT 外，当没有查询出记录时，其它函数返回空值。特别是，如果没有记录的话，函数 SUM 返回空值，而不是像期待的那样，返回 0。当必要的时候，我们可以使用函数 COALESCE 将空值替换为 0。

下面详细介绍通常用于统计分析的聚合函数。分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。之所以和上面的通用聚合函数分开列出，是为了更明确各自的功能。

窗口函数说明

分析函数（也叫窗口函数）与聚合函数，都是对行集组（一组行的集合）进行聚合计算，不同的是，聚合函数每组只能返回一个值（一行），而窗口函数每组可以返回多个值（多行）。行集组又称为窗口（Window），由 analytic_clause 定义。而窗口大小取决于实际的行数或逻辑间隔（例如时间）。组内每一行都是基于窗口的逻辑计算的结果。触发一个分析函数需要特殊的关键字 OVER 来指定窗口。

窗口函数的语法：

analytic_function ( [arguments] ) OVER ( [ partition_clause ]

[ order_by_clause [ windowing_clause ] ] )

其中：

• OVER 是关键字，用于标识分析函数。

• analytic_function 是指定的分析函数的名字，Oracle 分析函数很多。

• 分区子句的格式为： partition by[,value_expr]...关键字 partition by 子句根据由分区表达式的条件逻辑地将单个结果集分成 N 组。这里的"分区 partition"和"组 group"都是同义词。

• 排序子句 order-by-clause 指定数据是如何存在分区内的。其格式为：

order [ siblings ] by { expr | position | c_alias } [ asc | desc ] [ nulls first | nulls last ]

其中：

(1)asc|desc：指定了排列顺序。

(2)nulls first|nulls last：指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。

• 窗口子句 windowing-clause 给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，可用该子句让分析函数计算出它的值。

KEEP 函数说明

keep 是一个特殊的分析函数，他的用法不同于通过 OVER 关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

语法：

min | max(column1) keep (dense_rank first | last ORDER BY column2)

[ OVER (PARTION BY column3) ]

示例：

emp 表格定义见【示例参考表格】

SELECT deptno, MIN(t.mgr)  KEEP (DENSE_RANK FIRST ORDER BY t.sal) a   
FROM emp t  GROUP BY deptno;

含义：按 deptno 分组，分组内按 sal 排序，DENSE_RANK FIRST 表示保留 sal 排在前面的一组数据（当排在前面的 sal 有重复值时，保存多条数据）。然后在每组记录中，执行前面的聚合函数，这里是 min(t.mgr)。

执行结果：

 DEPTNO |  A   
--------+------
     10 | 7782
     30 | 7698
     20 | 7902
(3 rows)

具有统计用途的聚合函数：

名称	参数个数	功能	举例
COVAR_POP	2	COVAR_POP(expr1, expr2) 返回一对表达式的总体协方差。
COVAR_SAMP	2	COVAR_SAMP(expr1, expr2)返回一对表达式的样本协方差。
CUME_DIST	0~n	计算一个值在一组值中的累积分布。	CUME_DIST(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct)
RANK	0~n	基于 OVER 子句中的 ORDER BY 表达式确定一组值的排名。当有相同排序值时，将会有相同的排名，并且值相同的行数会被记录到下个排名中。	RANK(15500) WITHIN GROUP (ORDER BY salary DESC)
DENSE_RANK	0~n	计算一组数值中的排序值，连续排序，有两个第二名时仍然跟着第三名。	DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary)
PERCENT_RANK	0~n	百分比排名。	PERCENT_RANK(15000, .05) WITHIN GROUP (ORDER BY salary
REGR_SLOPE	2	REGR_SLOPE：返回斜率，等于 COVAR_POP(expr1, expr2) / VAR_POP(expr2)。	REGR_SLOPE(expr1, expr2)
REGR_INTERCEPT	2	REGR_INTERCEPT：返回回归线的 y 截距，等于 AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)。	REGR_INTERCEPT(expr1, expr2)
REGR_COUNT	2	返回用于填充回归线的非空数字对的数目。	REGR_COUNT(expr1, expr2)
REGR_R2	2	返回回归线的决定系数，计算式为： If VAR_POP(expr2) = 0 then return NULL If VAR_POP(expr1) = 0 AND VAR_POP(expr2) != 0 then return 1 If VAR_POP(expr1) > 0 AND VAR_POP(expr2 != 0 then return POWER(CORR(expr1,expr),2)。	REGR_R2(expr1, expr2)
REGR_AVGX	2	计算回归线的自变量 (expr2) 的平均值，去掉了空对 (expr1, expr2) 后，等于 AVG(expr2)。	REGR_AVGX(expr1, expr2)
REGR_AVGY	2	计算回归线的应变量 (expr1) 的平均值，去掉了空对 (expr1, expr2) 后，等于 AVG(expr1)。	REGR_AVGY(expr1, expr2)
REGR_SXX	2	返回值等于 REGR_COUNT(expr1, expr2) * VAR_POP(expr2)。	REGR_SXX(expr1, expr2)
REGR_SYY	2	返回值等于 REGR_COUNT(expr1, expr2) * VAR_POP(expr1)。	REGR_SYY(expr1, expr2)
REGR_SXY	2	返回值等于 REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)。	REGR_SXY(expr1, expr2)
STDDEV	1	计算当前行关于组的标准偏离。	stddev(col) stddev(distinct col) stddev(all col)
STDDEV_POP	1	该函数计算总体标准偏离，并返回总体变量的平方根。	STDDEV_POP(salary)
STDDEV_SAMP	1	该函数计算累积样本标准偏离，并返回总体变量的平方根。	STDDEV_SAMP(salary)
VAR_POP	1	该函数返回非空集合的总体变量（忽略null）。	VAR_POP(salary)
VAR_SAMP	1	该函数返回非空集合的样本变量（忽略null）。	VAR_SAMP(salary)
VARIANCE	1	如果表达式中行数为 1，则返回 0，如果表达式中行数大于 1，则返回 VAR_SAMP。	variance(col)
CORR	2	返回一对表达式的相关系数。	CORR(list_price, min_price)

COVAR_POP

计算协方差。

示例：

emp 表格定义见【示例参考表格】

SELECT MGR , COVAR_POP ( SYSDATE - HIREDATE , sal )  AS covar_pop ,
COVAR_SAMP ( SYSDATE - HIREDATE , sal )  AS covar_samp
FROM emp GROUP BY MGR ORDER BY MGR , covar_pop , covar_samp ;

 MGR  |     COVAR_POP     |     COVAR_SAMP     
------+-------------------+---------------------
 7566 |                 0 |                  0
 7698 |             43976 |              54970
 7782 |                 0 |                   
 7788 |                 0 |                   
 7839 | 10547.22222222222 | 15820.833333333332
 7902 |                 0 |                  0
      |                 0 |                   
(7 rows)

COVAR_SAMP

计算样本协方差。

示例：

emp 表格定义见【示例参考表格】

SELECT MGR , COVAR_SAMP( SYSDATE - HIREDATE , sal )  AS covar_pop ,
COVAR_SAMP ( SYSDATE - HIREDATE , sal )  AS covar_samp
FROM emp GROUP BY MGR ORDER BY MGR , covar_pop , covar_samp ;

 MGR  |     COVAR_POP      |     COVAR_SAMP     
------+--------------------+---------------------
 7566 |                  0 |                  0
 7698 |              54970 |              54970
 7782 |                    |                   
 7788 |                    |                   
 7839 | 15820.833333333332 | 15820.833333333332
 7902 |                  0 |                  0
      |                    |                   
(7 rows)

CUME_DIST

计算一个值在一组值中的累积分布。返回值的范围为 0 < CUME_DIST <= 1。

示例：

emp 表格定义见【示例参考表格】

SELECT deptno , ename , sal, cume_dist ( ) OVER ( partition BY deptno ORDER BY sal DESC ) "RANK" FROM emp WHERE sal>2000;
 DEPTNO | ENAME  |  SAL  |        RANK         
--------+--------+-------+---------------------
     10 | KING   | 10000 |  0.3333333333333333
     10 | CLARK  |  7450 |  0.6666666666666666
     10 | MILLER |  6300 |                   1
     20 | ADAMS  |  8100 | 0.16666666666666666
     20 | FORD   |  8000 |                 0.5
     20 | SCOTT  |  8000 |                 0.5
     20 | JONES  |  7975 |  0.6666666666666666
     20 | SMITH  |  6800 |  0.8333333333333334
     20 | SMITH  |  4800 |                   1
     30 | BLAKE  | 11850 | 0.16666666666666666
     30 | ALLEN  |  7600 |  0.3333333333333333
     30 | TURNER |  6500 |                 0.5
     30 | WARD   |  6250 |  0.8333333333333334
     30 | MARTIN |  6250 |  0.8333333333333334
     30 | JAMES  |  5950 |                   1
(15 rows)

RANK

确定一组值的排名。当有相同排序值时，将会有相同的排名，并且值相同的行数会被记录到下个排名中。

示例：

计算在同一个部门内，每个人的工资排名。（emp 表格定义见【示例参考表格】）

SELECT empno,ename,sal, deptno, rank() OVER (PARTITION BY deptno ORDER BY sal)  FROM emp;
 EMPNO | ENAME  |  SAL  | DEPTNO | RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL)  
-------+--------+-------+--------+---------------------------------------------------
  7934 | MILLER |  6300 |     10 |                                              1
  7782 | CLARK  |  7450 |     10 |                                              2
  7839 | KING   | 10000 |     10 |                                              3
  7469 | SMITH  |  4800 |     20 |                                              1
  7369 | SMITH  |  6800 |     20 |                                              2
  7566 | JONES  |  7975 |     20 |                                              3
  7902 | FORD   |  8000 |     20 |                                              4
  7788 | SCOTT  |  8000 |     20 |                                              4
  7876 | ADAMS  |  8100 |     20 |                                              6
  7900 | JAMES  |  5950 |     30 |                                              1
  7521 | WARD   |  6250 |     30 |                                              2
  7654 | MARTIN |  6250 |     30 |                                              2
  7844 | TURNER |  6500 |     30 |                                              4
  7499 | ALLEN  |  7600 |     30 |                                              5
  7698 | BLAKE  | 11850 |     30 |                                              6
(15 rows)

DENSE_RANK

DENSE_RANK 窗口函数基于 OVER 子句中的 ORDER BY 表达式确定一组值中的一个值的排名。如果存在可选的 PARTITION BY子句，则为每个行组重置排名。带符合排名标准的相同值的行接收相同的排名。DENSE_RANK 功能与 RANK 的区别在于：如果两行或多行并列,则排序值序列中无间隔。例如，如果两个行的排名为 1，则下一个排名则为 2。

示例：

emp 表格定义见【示例参考表格】

antdb=# SELECT deptno, ename, sal, DENSE_RANK ( ) OVER ( partition BY deptno ORDER BY sal DESC ) "RANK" FROM emp WHERE sal>6500;
 DEPTNO |  ENAME |  SAL  | RANK 
--------+--------+-------+---------
     10 | KING   | 10000 |    1
     10 | CLARK  |  7450 |    2
     20 | ADAMS  |  8100 |    1
     20 | SCOTT  |  8000 |    2
     20 | FORD   |  8000 |    2
     20 | JONES  |  7975 |    3
     20 | SMITH  |  6800 |    4
     30 | BLAKE  | 11850 |    1
     30 | ALLEN  |  7600 |    2
(9 rows)

PERCENT_RANK

计算给定行的百分比排名。该函数没有参数，但需要空括号。返回值范围介于 0 和 1（含 1）之间。任何集合中的第一行的 PERCENT_RANK 均为 0。

示例：

计算每一个员工在同一种职位内的百分比排名。（emp 表格定义见【示例参考表格】）

SELECT empno,job, sal, percent_rank()  OVER (PARTITION BY job ORDER BY sal)  FROM emp;
 EMPNO |    JOB    |  SAL  | PERCENT_RANK() OVER (PARTITION BY JOB ORDER BY SAL)  
-------+-----------+-------+-------------------------------------------------------
  7902 | ANALYST   |  8000 |                                                    0
  7788 | ANALYST   |  8000 |                                                    0
  7469 | CLERK     |  4800 |                                                    0
  7900 | CLERK     |  5950 |                                                 0.25
  7934 | CLERK     |  6300 |                                                  0.5
  7369 | CLERK     |  6800 |                                                 0.75
  7876 | CLERK     |  8100 |                                                    1
  7782 | MANAGER   |  7450 |                                                    0
  7566 | MANAGER   |  7975 |                                                  0.5
  7698 | MANAGER   | 11850 |                                                    1
  7839 | PRESIDENT | 10000 |                                                    0
  7521 | SALESMAN  |  6250 |                                                    0
  7654 | SALESMAN  |  6250 |                                                    0
  7844 | SALESMAN  |  6500 |                                   0.6666666666666666
  7499 | SALESMAN  |  7600 |                                                    1
(15 rows)

REGR_SLOPE

计算斜率。

示例：

CREATE TABLE tb(id int, num int);
INSERT into tb values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);
SELECT count(*),regr_slope(t.num,tb.num) FROM 
(SELECT id-1 AS id, num FROM tb) AS t,tb WHERE t.id=tb.id ;
 COUNT(*) | REGR_SLOPE(T.NUM,TB.NUM)  
----------+-----------------------------
       28 |         -0.8425517064826854
(1 row)

REGR_INTERCEPT

计算截距。

示例：

CREATE TABLE tb(id int, num int);
INSERT into tb values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);

SELECT count(*),regr_intercept(t.num,tb.num) FROM 
(SELECT id-1 AS id, num FROM tb) AS t,tb WHERE t.id=tb.id ;
 COUNT(*) | REGR_INTERCEPT(T.NUM,TB.NUM)  
----------+---------------------------------
       28 |               655.2852157477586
(1 row)

REGR_COUNT

regr_count(y, x) 计算 x 和 y 都不是空的记录数。

示例：

CREATE TABLE tb(id int, num int);
INSERT into tb values(1, 298),(2, null),(3, null),(null, 328),(null, 108),(6, 667);
SELECT regr_count(id, num) FROM tb;

REGR_COUNT(ID, NUM)  
----------------------
                    2
(1 row)

REGR_R2

计算相关性，相关性越高，说明这组数据用于预估的准确度越高。当获得最高的相关性（接近1）时，预测数据最准确。

示例：

CREATE TABLE tb(id int, num int);
INSERT INTO tb values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);

计算两组数据的相关性：

SELECT count(*),regr_r2(t.num,tb.num) FROM 
(SELECT id-1 AS id, num FROM tb) AS t,tb WHERE t.id=tb.id ;

 COUNT(*) | REGR_R2(T.NUM,TB.NUM)  
----------+--------------------------
       28 |       0.6088521468006012
(1 row)

REGR_AVGX

regr_avgx(y, x) 其实就是算 x 的平均值(数学期望)， y 在这里没有任何作用。

REGR_AVGY

regr_avgy(y, x) 其实就是算 y 的平均值(数学期望) ，x 在这里没有任何作用。

REGR_SXX

返回值等于 REGR_COUNT(expr1, expr2) * VAR_POP(expr2) 。

REGR_SYY

返回值等于 REGR_COUNT(expr1, expr2) * VAR_POP(expr1) 。

REGR_SXY

返回值等于 REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)。

STDDEV

计算当前行关于组的标准偏离。

示例：

例返回部门 30 按雇佣日期排序的薪水值的累积标准偏离。（emp 表格定义见【示例参考表格】）

SELECT ename,hiredate,sal,stddev(sal) OVER(ORDER BY hiredate) a FROM emp WHERE deptno=20;
ENAME |      HIREDATE       | SAL  |         A         
-------+---------------------+------+-------------------
 SMITH | 1980-12-17 00:00:00 | 4800 | 1414.213562373095
 SMITH | 1980-12-17 00:00:00 | 6800 | 1414.213562373095
 JONES | 1981-04-02 00:00:00 | 7975 | 1605.264775667865
 FORD  | 1981-12-03 00:00:00 | 8000 | 1503.935808692202
 SCOTT | 1987-04-19 00:00:00 | 8000 |  1393.24262065155
 ADAMS | 1987-05-23 00:00:00 | 8100 | 1309.428946780491
(6 rows)

STDDEV_POP

该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与 VAR_POP 函数的平方根相同。（emp 表格定义见【示例参考表格】）

示例：

SELECT deptno,ename,sal,stddev_pop(sal) OVER (PARTITION BY deptno) AS pop FROM emp;
 DEPTNO | ENAME  |  SAL  |        POP        
---------------+------------+------------+-------------------
     10 | KING   | 10000 | 1546.142152441216
     10 | CLARK  |  7450 | 1546.142152441216
     10 | MILLER |  6300 | 1546.142152441216
     20 | SCOTT  |  8000 | 1195.339619336511
     20 | SMITH  |  6800 | 1195.339619336511
     20 | JONES  |  7975 | 1195.339619336511
     20 | FORD   |  8000 | 1195.339619336511
     20 | ADAMS  |  8100 | 1195.339619336511
     20 | SMITH  |  4800 | 1195.339619336511
     30 | ALLEN  |  7600 | 2057.506581601462
     30 | MARTIN |  6250 | 2057.506581601462
     30 | TURNER |  6500 | 2057.506581601462
     30 | JAMES  |  5950 | 2057.506581601462
     30 | BLAKE  | 11850 | 2057.506581601462
     30 | WARD   |  6250 | 2057.506581601462
(15 rows)

STDDEV_SAMP

该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与 VAR_POP 函数的平方根相同。

示例：

begin;
CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss',  '2019-07-11',13500);     
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners',  '2018-12-01',14000);     
INSERT INTO employees VALUES(200, 'Ross',  '2019-06-11',13500);     
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part',  '2018-08-11',14000);
COMMIT;
SELECT manager_id, last_name, hiredate, salary,STDDEV_SAMP(salary) OVER (PARTITION BY manager_id 
ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev FROM employees ORDER BY manager_id, last_name, hiredate, salary, cum_sdev;

 MANAGER_ID | LAST_NAME |  HIREDATE  | SALARY |     CUM_SDEV      
------------+-----------+------------+--------+--------------------
        100 | De Haan   | 2018-05-01 |  11000 | 4702.127178203499
        100 | Errazuriz | 2017-07-21 |   1400 |  212.132034355964
        100 | Hartstein | 2019-05-01 |  14000 | 6340.346993658943
        100 | Partners  | 2018-12-01 |  14000 | 6064.899009876422
        100 | Raphaely  | 2017-07-01 |   1700 |                  
        100 | Raphaely  | 2017-07-22 |   1700 |  173.205080756888
        100 | Russell   | 2019-10-05 |  13000 | 6026.474330580265
        100 | Weiss     | 2019-07-11 |  13500 |  6244.31169717116
        200 | Bell      | 2019-05-25 |  13000 |  707.106781186548
        200 | Part      | 2018-08-11 |  14000 |                  
        200 | Ross      | 2019-06-11 |  13500 |               500
(11 rows)

VAR_POP

该函数返回分组序列的总体方差。

示例：

emp 表格定义见【示例参考表格】

SELECT empno, sal, VAR_pop(sal) OVER (ORDER BY hiredate) AS "var_pop"
FROM emp ORDER BY ename, sal, "var_pop" ;

EMPNO |  SAL  |       var_pop        
------------+----------+----------------------
  7876 |  8100 | 2799183.333333333333
  7499 |  7600 | 1386666.666666666667
  7698 | 11850 | 4748003.472222222222
  7782 |  7450 | 4070841.836734693878
  7902 |  8000 | 3328381.076388888889
  7900 |  5950 | 3328381.076388888889
  7566 |  7975 |              1251400
  7839 | 10000 |           3729306.25
  7654 |  6250 | 3401111.111111111111
  7934 |  6300 | 3166597.633136094675
  7788 |  8000 | 2967286.352040816327
  7469 |  4800 |              1000000
  7369 |  6800 |              1000000
  7844 |  6500 |       3678505.859375
  7521 |  6250 |           1044218.75
(15 rows)

VAR_SAMP

该函数返回分组序列的样本方差。

示例：

emp 表格定义见【示例参考表格】

SELECT empno, sal, VAR_samp(sal)  OVER (ORDER BY hiredate) AS "var_samp"
FROM emp ORDER BY ename, sal, "var_samp" ;

 EMPNO |  SAL  |       var_samp       
-------+-------+-----------------------
  7876 |  8100 |              2999125
  7499 |  7600 |              2080000
  7698 | 11850 | 5697604.166666666667
  7782 |  7450 |  4749315.47619047619
  7902 |  8000 | 3630961.174242424242
  7900 |  5950 | 3630961.174242424242
  7566 |  7975 |              1564250
  7839 | 10000 | 4143673.611111111111
  7654 |  6250 |              3826250
  7934 |  6300 | 3430480.769230769231
  7788 |  8000 | 3195539.148351648352
  7469 |  4800 |              2000000
  7369 |  6800 |              2000000
  7844 |  6500 | 4204006.696428571429
  7521 |  6250 | 1392291.666666666667
(15 rows)

VARIANCE

计算指定列的方差。

示例：

计算列 sal 的方差（emp 表格定义见【示例参考表格】）：

SELECT empno, sal, VARIANCE(sal) OVER (ORDER BY hiredate) "Variance"
FROM emp ORDER BY ename, sal, "Variance";
 EMPNO |  SAL  |       Variance       
-------+-------+------------------------
  7876 |  8100 |              2999125
  7499 |  7600 |              2080000
  7698 | 11850 | 5697604.166666666667
  7782 |  7450 |  4749315.47619047619
  7902 |  8000 | 3630961.174242424242
  7900 |  5950 | 3630961.174242424242
  7566 |  7975 |              1564250
  7839 | 10000 | 4143673.611111111111
  7654 |  6250 |              3826250
  7934 |  6300 | 3430480.769230769231
  7788 |  8000 | 3195539.148351648352
  7469 |  4800 |              2000000
  7369 |  6800 |              2000000
  7844 |  6500 | 4204006.696428571429
  7521 |  6250 | 1392291.666666666667
(15 rows)

CORR

计算一对表达式的相关系数。

示例：

-- t1 表格定义：
CREATE TABLE t1(id int, num int);
INSERT into t1 values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);

antdb=# SELECT corr(t.num,t1.num) FROM 
antdb-# (SELECT id-1 AS id, num FROM t1) AS t,t1 WHERE t.id=t1.id ;
 CORR(T.NUM,T1.NUM)  
------------------------------
   -0.7802897838627655
(1 row)

其它类型函数

序列操作函数

本节介绍了 AntDB 中用于操作序列对象的函数。序列对象（也被称为序列产生器或者简称为序列）是一个特定的单行记录表，由 CREATE SEQUENCE 命令创建。一个序列对象通常为表的记录产生唯一标识。在下面列出的序列函数为从序列对象中获取连续的序列数提供了简单，多用户安全的方法。

sequence.NEXTVAL

sequence.CURRVAL

sequence 是在 CREATE SEQUENCE 命令中分配给序列的的标识符。下面介绍了这些函数的使用方法。

NEXTVAL

这个函数的作用是将序列对象的当前位置指到这个对象的下一个值。这个操作完成后是不能撤销的。即使多个会话同步执行 NEXTVAL，每个会话将只能安全的取得一个唯一的序列值。

CURRVAL

返回在当前会话中函数 NEXTVAL 在序列中最近取得的数值。（如果在会话中的序列从来没有调用 NEXTVAL，那么报告一条错误）。需要注意的是因为这个函数返回所在会话的序列数值，它会给出关于从当前会话开始的时候，是否有其他的会话执行了 NEXTVAL 的可预见性答案。

如果已经使缺省参数创建了一个序列对象，那么在这个对象上调用 NEXTVAL 函数将返回从 1 开始的连续数值。通过在 CREATE SEQUENCE 命令中使用特定参数，可以获取序列的其他系统行为。

重要注意事项：为避免从同一序列获取数值的同步事务阻塞，函数 NEXTVAL 操作永远不会回滚；这就是说，一旦获取了一个值，即使执行函数 NEXTVAL 的事务随后中断，也会认为已经使用了这个值。这就意味着中断的事务可能会在已经分配的值的序列留下未使用的值的“漏洞”。

具体使用参考 SQL 命令章节的 CREATE SEQUENCE 命令介绍。

字符集

名称	参数 个数	功能	用例
NLS_CHARSET_ID	1	返回字符集名称参应 id 值，AntDB 的字符集 ID 值和 Name 与 Oracle 不同，这一点与 Oracle 不兼容。	SELECT nls_charset_id('zhs16gbk') FROM dual;
NLS_CHARSET_NAME	1	根据字符 id 返回字符集名称，AntDB 的字符集 ID 值和 Name 与 Oracle 不同，这一点与 Oracle 不兼容。	SELECT nls_charset_name(37) FROM dual;

条件函数

名称	参数 个数	功能	举例
coalesce	1~n	参数中第一个非空值。只有当所有的参数都是空值的时候，才返回空值。
NULLIF	2	NULLIF(value1, value2) 如果 value1 和 value2 相等，返回空值，否则它返回 value1。
LNNVL	1	lnnvl(condition)，参数是一个表达式，如果表达式为 true，则返回 false；表达式为 false，返回 true。	LNNVL(commission_pct >= .2)
NANVL	2	nanvl(n1,n2)，如果 n1 是 NaN 时，就返回 n2，否则返回 n1。	NANVL(4.6,0)
NVL	2	NVL(c1,c2) ，从左向右，返回第一个不为空的参数。若两个参数都为空，返回 null。	NVL(TO_CHAR(commission_pct), 'Not Applicable')
NVL2	3	NVL2(c1,c2,c3)，如果 c1 非空则返回 c2，如果 c1 为空则返回 c3。	NVL2(commission, salary + commission, salary)
DECODE	2n	decode(条件，值 1，返回值 1，值 2，返回值 2，值n，返回值 n，缺省值)，如果条件=值 1，返回“返回值 1；如果条件=值 2，返回“返回值 2” ，依次类推，没有找到对应的值，则返回默认值。

在本节中介绍了在 AntDB 中允许使用的条件表达式。这些条件表达式都是遵从 SQL 标准。

CASE

SQL 语言的 CASE 表达式是一类条件表达式，与其它语言中 if/else 语句的功能类似：

CASE WHEN condition THEN result

[ WHEN ... ]

[ ELSE result ]

END

CASE 子句可以在表达式出现的任何地方使用。condition 是一个表达式，它的返回结果是 BOOLEAN 类型。如果返回结果为 true，那么CASE 表达式的值是在 condition 后面的 result。如果返回结果为 false，那么就会用相同的方式搜索 CASE 表达式后面的 WHEN 子句。如果 WHEN condition 的返回结果不为真，那么 CASE 表达式的值就是在 ELSE 子句中的 result。如果没有使用 ELSE 子句并且没有条件匹配，那么结果返回为空。

示例：

SELECT * FROM t1;

id
---
1
2
3
(3 rows)

SELECT id,
CASE WHEN id=1 THEN 'one'
WHEN id=2 THEN 'two'
ELSE 'other'
END
FROM t1;

id | case
---+-------
1 | one
2 | two
3 | other
(3 rows)

所有 result 表达式的数据类型必须能够转换成一个单一的输出类型。

下面这个简化的 CASE 表达式是上面普通类型表达式的另一种特定使用方式。

CASE expression

WHEN value THEN result

[ WHEN ... ]

[ ELSE result ]

END

首先计算 expression，然后和 WHEN 子句中所有指定的 value 进行了比较，直到找到了一个相等的条件。如果没有找到匹配条件，那么返回在 ELSE 子句中的 result（或者是一个空值）。

我们可以使用简化的 CASE 语法重新写上面的示例：

SELECT id,
CASE id WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM t1;

a | case
--+-------
1 | one
2 | two
3 | other
(3 rows)

CASE 表达式不计算那些不需要确认结果的子表达式。例如，下面是一个允许使用的方法，用来避免除零操作。

SELECT ... 	WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

COALESCE

函数 COALESCE 返回参数中第一个非空值。只有当所有的参数都是空值的时候，才返回空值。

语法：

COALESCE(value [, value2 ] ...)

当需要显示所取出来的值，我们通常使用缺省值来替代空值。

SELECT COALESCE(value1 , value2, default_value)…

和 CASE 表达式一样，COALESCE 不计算那些不需要决定结果的参数。这就是说，第一个非空参数右边的参数是不需要计算的。这个 SQL标准函数提供了与函数 NVL 和 IFNULL 类似的功能，可以在其他的数据库系统中使用。

示例：

CREATE TABLE t1(id int, math int,chinese int, english int);
INSERT into t1(id, math) values(1,88);
INSERT into t1(id, english) values(2,98);
INSERT into t1(id, chinese) values(3,97);
INSERT into t1(id) values(4);

SELECT COALESCE(math, chinese, english, 0) FROM t1;
COALESCE 
-------------------
       88
       98
       97
        0
(4 rows)

SELECT COALESCE(math, chinese, english) FROM t1; 
COALESCE 
------------------
       88
       98
       97
         
(4 rows)

NULLIF

如果 value1 和 value2 相等，函数 NULLIF 返回空值，否则返回 value1。

语法：

NULLIF(value1, value2)

示例：

CREATE TABLE test(id int, math int);
INSERT into test values(1,88),(2,89),(3,100),(4,99);
SELECT nullif(math, 100) FROM test;
 NULLIF 
----------------
     88
     89
       
     99
(4 rows)

LNNVL

LNNVL 函数判断条件中的一个或者两个操作数是否为 NULL。该函数可以在 WHERE 子句中使用，也可以作为 CASE 表达式中的 WHEN 条件。将条件作为参数，如果条件为 FALSE 或 UNKNOWN，则返回 TRUE；如果条件为 TRUE，则返回 FALSE。

语法：

LNNVL(condition)

示例：

-- test 表定义：
CREATE TABLE test(id int, math int);
INSERT into test values(1,88),(2,89),(3,100),(4,99);

SELECT lnnvl(math = 100) FROM test;
 LNNVL(MATH = 100)  
-----------------------------------
 t
 t
 f
 t
(4 rows)

NANVL

语法：

nanvl(n1,n2)

如果 n1 是 NaN 时，就返回 n2，否则返回 n1。要求 n1 和 n2 的类型都是 numeric 或者可以隐式转换成 numeric 的类型。

示例：

SELECT nanvl('NaN',-1) FROM dual;
NANVL('NAN',-1)  
-----------------------
            -1
(1 row)

SELECT nanvl(2,-1) FROM dual;
 NANVL(2,-1)  
---------------------
            2
(1 row)

NVL

NVL 函数从两个表达式返回一个非 NULL 值。如果 expr1 与 expr2 的结果皆为 NULL 值，则 NVL 函数返回 NULL。

语法：

NVL(expr1, expr2)

示例：

SELECT NVL(null, 1) FROM dual;
 NVL(NULL, 1)  
---------------
 1
(1 row)

SELECT NVL(2, null) FROM dual;
 NVL(2, NULL)  
---------------
 2
(1 row)

SELECT NVL(null, null) FROM dual;
 NVL(NULL, NULL)  
------------------
 
(1 row)

NVL2

NVL2 函数根据表达式是否为空，返回不同的值。如果 expr1 不为空，则返回 expr2的值，如果 expr1 为空，则返回 expr3 的值。expr2 和 expr3 类型不同的话，expr3 会转换为 expr1 的类型。

语法：

NVL2(expr1, expr2, expr3)

示例：

-- test表定义：
CREATE TABLE test(id int, math int);
INSERT into test values(1,88),(2,89),(3,100),(4,99);

SELECT math, NVL2(math, 'not null','is null') FROM test;
 MATH | NVL2(MATH, 'NOT NULL','IS NULL')  
------+-----------------------------------
   88 | not null
      | is null
      | is null
      | is null
(4 rows)

DECODE

语法：

decode(condition,x1, y1, x2, y2, x3,y3 [,xn, yn] ……, default_value)

该函数的含义如下：

IF condition=x1 THEN

RETURN(y1)

ELSIF condition=x2 THEN

RETURN(y2)

......

ELSIF condition=xn THEN

RETURN(yn)

ELSE

RETURN(default_value)

END IF

示例：

emp 表格定义见【示例参考表格】

SELECT EMPNO, decode(DEPTNO, 10, 1, 20, 2, 30,3, 0) FROM emp;
 EMPNO | DECODE(DEPTNO, 10, 1, 20, 2, 30,3, 0)  
-------+----------------------------------------
  7521 |                                      3
  7566 |                                      2
  7654 |                                      3
  7782 |                                      1
  7788 |                                      2
  7839 |                                      1
  7844 |                                      3
  7900 |                                      3
  7902 |                                      2
  7934 |                                      1
  7876 |                                      2
  7469 |                                      2
  7698 |                                      3
  7369 |                                      2
  7499 |                                      3
(15 rows)

通用比较函数

GREATEST

GREATEST 函数用于从任意数量的表达式中查询出最大的数值。

语法：

GREATEST(value [, value2 ] ... )

表达式必须都可以转换成一个通用的数据类型，这个通用数据类型是结果的类型。而在列表中的空值是忽略的。只有所有的表达式计算值为空的时候，最后结果才为空值。

需要注意的函数 GREATEST 不在 SQL 标准中体现，而是一个当前普遍的扩展函数。

比较：

MAX 和 MIN 是在一张表的某一 column 的一个 group 上找出最大值和最小值。而 greatest 和 least 可以在一行中找出最大值和最小值。

示例：

查询每个人的考试成绩中的所有科目的最高分。

CREATE TABLE test(id int, name varchar2(10), chinese number, english number, math number, physical number);
INSERT into test values(1,'Tom', 91, 89, 69, 88);
INSERT into test values(2,'Alice', 88, 81, 77, 100);
SELECT name, greatest(chinese, english, math, physical) FROM test;

比较字符串的大小。

SELECT GREATEST('HAPPY', 'HAPPEN', 'HAPPINESS') "Greatest" FROM DUAL;

比较数字的大小。

SELECT GREATEST (1, '3.935', '2.4') "Greatest" FROM DUAL;

LEAST

LEAST 函数用于从任意数量的表达式中查询出最小的数值。

语法：

LEAST(value [, value2 ] ... )

表达式必须都可以转换成一个通用的数据类型，这个通用数据类型是结果的类型。而在列表中的空值是忽略的。只有所有的表达式计算值为空的时候，最后结果才为空值。

需要注意的函数 LEAST 不在 SQL 标准中体现，而是一个当前普遍的扩展函数。

比较：

MAX 和 MIN 是在一张表的某一 column 的一个 group 上找出最大值和最小值。而 greatest 和 least 可以在一行中找出最大值和最小值。

示例：

查询每个人的所有科目中，考试成绩的最低分；

CREATE TABLE test(id int, name varchar2(10), chinese number, english number, math number, physical number);
INSERT into test values(1,'Tom', 91, 89, 69, 88);
INSERT into test values(2,'Alice', 88, 81, 77, 100);
SELECT name, least(chinese, english, math, physical) FROM test;

比较字符串的大小。

SELECT LEAST('HAPPY', 'HAPPEN', 'HAPPINESS')  "Least"  FROM DUAL;

比较数字的大小。

SELECT LEAST (1, '3.925', '2.4')  "Least"  FROM DUAL;

子查询表达式

这个部分介绍了在 AntDB 中遵循 SQL 标准的子查询表达式。所有在这部分提到的表达式返回值都是布尔类型（真/假）结果。

EXISTS

EXISTS 的参数是一条以任意形式出现的 SELECT 语句或者子查询。我们首先运行子查询语句来决定这个子查询语句是否返回记录。

EXISTS ( subquery )

如果 subquery 至少有一条记录返回，那么 EXISTS 的结果是”true”；

如果 subquery 没有返回记录，那么 EXISTS 的结果是”false”。

子查询可以参考从它周围查询传递的变量，这样在运行子查询的时候，可以像一个常量那样操作。

我们通常执行子查询来确定是否至少有一条记录返回，而不是返回的所有记录才结束子查询。写一个有负作用的子查询（例如调用序列函数）是不明智的，而是否有负作用是很难预先判断出来的。

因为 EXISTS 的返回结果只取决于是否有记录返回，而不是这些记录的内容，子查询的输出列表一般来说没什么意义。一般比较通用的编码约定是以 EXISTS（SELECT 1 WHERE...）的形式写所有的 EXISTS 测试。当然，对于这个规则来说，也有些例外的情况，例如使用INTERSECT 的子查询。

这个示例和在列 deptno 上进行的内连接类似，但是对于每个表 dept 的记录来说最多只产生一条输出记录，尽管这里有多条匹配的表emp 的记录（emp 和 dept 表格定义见【示例参考表格】）：

SELECT dname FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);

dname
--------------
ACCOUNTING
RESEARCH
SALES
(3 rows)

IN

操作符 IN 在右侧是括号中的子查询，这个子查询必须只返回一列的内容。我们计算操作符左侧的表达式，然后与子查询结果中的每条记录进行比较。如果找到与子查询中相匹配的记录，那么 IN 操作的结果是 “true”。如果没有相等记录（包括像子查询没有返回记录这种特定的情况）则结果是“false”。

expression IN (subquery)

需要注意的是如果操作符左侧的表达式产生空值，或者在操作符右侧表中的记录没有与之相等的值，并且至少有一条右侧子查询返回的记录产生空值，那么 IN 构造的结果将是空值，而不是”false”。这和对于空值和布尔变量组合的 SQL 一般规则是一致的。

和 EXISTS 一样，设想子查询会完成对所有涉及记录的查询是不明智的。

NOT IN

在操作符右侧的是括号中的子查询，这个子查询必须只返回一列的内容。我们计算操作符左侧的表达式，然后与子查询结果中的每条记录进行比较。如果只返回了与子查询中不相等记录（包括特定的情况，也就是子查询没有返回记录），那么 NOT IN 操作的结果是“true”。如果找到相同的记录，那么返回结果是“false”。

expression NOT IN (subquery)

需要注意的是如果操作符左侧的表达式产生空值，或者在操作符右侧表中的记录没有与之不相等的值，并且操作符右侧子查询至少返回一条产生空值的记录，那么 NOT IN 构造的结果将是空值，而不是“true”。这和对于空值和布尔变量组合的 SQL 一般规则是一致的。

正如和 EXISTS 一样，设想子查询会完成对所有涉及记录的查询是不明智的。

ANY/SOME

在操作符右侧是括号中的子查询，这个子查询必须只返回一列的内容。我们计算操作符左侧的表达式，然后使用给定的操作符和子查询结果的每条记录进行比较，最后产生一个 BOOLEAN 类型的结果。如果取出了返回值为真的结果，那么 ANY 操作的结果是“true”。如果没有找到返回值为真的结果（包括子查询没有返回记录这种特定的情况），那么结果为“false”。

expression operator ANY (subquery)

expression operator SOME (subquery)

SOME 是 ANY 的同义词。IN 等同于‘= ANY’。

需要注意的是如果这里没有成功返回的记录，并且操作符右侧的表达式至少有一行记录对于操作符的结果产生了空值，那么 ANY 操作构造的结果将是空，而不是“false”。这和对于空值和布尔变量组合的 SQL 一般规则是一致的。

正如和 EXISTS 一样，设想子查询会完成对所有涉及记录的查询是不明智的。

ALL

在操作符右侧的是括号中的子查询，这个子查询必须只返回一列的内容。我们计算操作符左侧的表达式,然后使用给定的操作符和子查询结果的每条记录进行比较，最后产生一个 BOOLEAN 类型的结果。如果和所有记录的比较结果都是真（包括子查询没有返回记录这种特殊情况），那么 ALL 操作的结果是“true”。如果找到任何比较结果为“false”的记录，那么 ALL 操作的结果是“false”。如果比较操作对于任何记录不返回“false”，并且为至少一条记录返回空值，那么最后结果是"空值"。

expression operator ALL (subquery)

NOT IN 等同于 ‘<> ALL’。

正如和 EXISTS 一样，设想子查询会完成对所有涉及记录的查询是不明智的。