GaussDB数据库基础函数介绍-上

​
目录

一、函数在数据库中的作用

二、GaussDB常用基础函数介绍与示例

1、数字操作函数

2、时间和日期处理函数

3、类型转换函数

4、数组函数

5、范围函数

6、窗口函数

7、聚集函数

8、安全函数

9、系统信息函数

10、动态脱敏函数.

Tip：由于篇幅缘故，“5.范围函数、6.窗口函数、7.聚集函数、8.安全函数、9.系统信息函数、10.动态脱敏函数”部分内容见下一篇《GaussDB数据库基础函数介绍-下》

前言
函数是数据库中最基本的组成部分之一，它们用于定义和操作数据库中的表格、记录、索引和视图等对象。在数据库中，函数的作用非常重要，因为它们可以实现数据的复杂操作，如查询、更新、删除和排序等。

作为华为自主创新研发的分布式关系型云数据库，GaussDB也拥有强大的函数支持体系。 本文主要从：数字操作函数、时间和日期处理函数、类型转换函数、数组函数、范围函数、窗口函数、聚集函数、安全函数、系统信息函数、动态脱敏函数等方面作一个简单介绍。

一、函数在数据库中的作用
首先，我们先来了解一下函数在数据库中的作用：

实现数据的复杂操作：函数可以用于实现数据的复杂操作，如查询、更新、删除和排序等，从而提高了数据库的操作效率。
提高程序的可读性：函数可以用于定义复杂的SQL语句，从而使得程序的代码更加简洁明了。这有助于提高程序的可读性，使得代码更容易被他人理解和维护。
提高程序的效率：函数可以用于减少重复编写程序段的工作量，从而提高程序的编译和运行效率。此外，函数还可以减少网络传输的数据量，从而提高程序的运行效率。
支持标准组件式编程：函数可以用于实现标准组件式编程，从而提高程序的可重用性、共享性和可移植性。
支持算法设计的基本要求：函数可以用于实现算法设计的基本要求，如正确性、可读性、健壮性、效率和低存储量需求。这些要求可以通过函数来实现，从而使得程序更加健壮、高效和可靠。
二、GaussDB常用基础函数介绍与示例
1、数字操作函数
在GaussDB数据库中，数字操作函数是指用于执行数字操作的函数，例如加减乘除、取余、幂运算等。这些函数通常在数学、计算机科学和工程学等领域中使用，用于处理数字数据和执行数字操作。

以下是一些常见的数字操作函数：

--abs(x)

–描述：绝对值。

–返回值类型：和输入相同。

SELECT abs(-17.4);



--ceil(x)

–描述：不小于参数的最小的整数。

–返回值类型：整数。

select ceil(42.3),ceil(-42.3)



--div(y numeric, x numeric)

–描述：y除以x的商的整数部分。

–返回值类型：numeric

select div(10,4)



--floor(x)

–描述：不大于参数的最大整数。

–返回值类型：与输入相同。

select floor(-42.3),floor(42.3)



--random()

–描述：0.0到1.0之间的随机数。

–返回值类型：double precision

select random(),random()



--multiply(x double precision or text, y double precision or text)

–描述：x和y的乘积。

–返回值类型：double precision

select multiply('4.0',3),multiply(4,'3.0')



--mod(x,y)

–描述：x/y的余数（模） ,如果x是0，则返回y。

–返回值类型：与参数类型相同。

select mod(10,3),mod(0,2)



--round(x)

–描述：离输入参数最近的整数。

–返回值类型：与输入相同。

select round(-10.9),round(10.1)



--round(v numeric, s int)

–描述：保留小数点后s位，s后一位进行四舍五入。

–返回值类型：numeric

select round(0.123456,2),round(10.654321,1)



--trunc(x)

–描述：截断（取整数部分）。

–返回值类型：与输入相同。

SELECT trunc(10.23)

--trunc(v numeric, s int)

–描述：截断为s位小数。

–返回值类型：numeric

SELECT trunc(10.123456,2),trunc(10.654321,1)

2、时间和日期处理函数
在GaussDB数据库中，时间和日期处理函数用于处理和操作日期和时间相关的数据。这些函数包括获取当前日期和时间的函数、将日期和时间转换为数字格式的函数、处理日期和时间的函数等。

用户在使用时间和日期操作符时，对应的操作数请使用明确的类型前缀修饰，以确保数据库在解析操作数的时候能够与用户预期一致，不会产生用户非预期的结果。

以下是一些常见的数据库时间和日期处理函数：

--age(timestamp, timestamp)

–描述：将两个参数相减，并以年、月、日作为返回值。若相减值为负，则函数返回亦为负，入参可以都带timezone或都不带timezone。

–返回值类型：interval

--age(timestamp)

–描述：当前时间和参数相减，入参可以带或者不带timezone。

–返回值类型：interval

SELECT age(timestamp '2023-04-19', timestamp '1988-08-13'),age(timestamp '1988-08-13');

--clock_timestamp()

–描述：实时时钟的当前时间戳。volatile函数，每次扫描都会取最新的时间戳，因此在一次查询中每次调用结果不相同。

–返回值类型：timestamp with time zone

--current_time

–描述：当前时间。

–返回值类型：time with time zone

--current_date

–描述：当前日期。

–返回值类型：date

--current_timestamp

–描述：当前日期及时间。语句级别时间，同一个语句内返回结果不变。

–返回值类型：timestamp with time zone

select clock_timestamp(),current_time,current_date,current_timestamp

--date_part(text, timestamp)

–描述：获取日期或者时间值中子域的值，例如年或者小时的值。

–等效于extract(field from timestamp)。

--timestamp类型：abstime、date、interval、reltime、time with time zone、time without time zone、timestamp with time zone、timestamp without time zone。

–返回值类型：double precision

--date_part(text, interval)

–描述：获取月份的值。如果大于12，则取与12的模。等效于extract(field from timestamp)。

–返回值类型：double precision

SELECT date_part('hour', timestamp '2023-04-19 18:30:40'),date_part('month', interval '2 years 3 months');

--date_trunc(text, timestamp)

–描述：截取到参数text指定的精度。

–返回值类型：interval、timestamp with time zone、timestamp without time zone

--trunc(timestamp)

–描述：默认按天截取。

--trunc(arg1, arg2)

–描述：截取到arg2指定的精度。

–arg1类型：interval、timestamp with time zone、timestamp without time zone

–arg2类型：text

–返回值类型：interval、timestamp with time zone、timestamp without time zone

SELECT date_trunc('hour', timestamp  '2023-04-16 20:38:40')

      ,trunc(timestamp '2023-04-16 20:38:40')

      ,trunc(timestamp  '2023-04-16 20:38:40', 'minute')

;

–isfinite(date)

–描述：测试是否为有效日期。

–返回值类型：Boolean

--isfinite(timestamp)

–描述：测试判断是否为有效时间。

–返回值类型：Boolean

SELECT isfinite(date '2023-04-19'),isfinite(timestamp '2023-4-19 21:28:30');

--localtime

–描述：当前时间。

–返回值类型：time

--localtimestamp

–描述：当前日期及时间。

–返回值类型：timestamp

--now()

–描述：当前日期及时间。事务级别时间，同一个事务内返回结果相同。

–返回值类型：timestamp with time zone

--timenow()

–描述：当前日期及时间。

–返回值类型：timestamp with time zone

select localtime,localtimestamp,now(),timenow();

–add_months(d,n)

–描述：用于计算时间点d再加上n个月的时间。

–d：timestamp类型的值，以及可以隐式转换为timestamp类型的值。

–n：INTEGER类型的值，以及可以隐式转换为INTEGER类型的值。

–返回值类型：timestamp

SELECT add_months(to_date('2023-4-19', 'yyyy-mm-dd'), 11);

3、类型转换函数
在GaussDB数据库中，类型转换函数是指用于将数据库中的数据类型转换为其他数据类型的函数。这些函数通常用于处理不同类型的数据，例如将整数转换为浮点数、将字符串转换为数字等。

以下是一些常见的数据库类型转换函数：

--cast(x as y)

–描述：类型转换函数，将x转换成y指定的类型。

SELECT cast('20-Apr-2023' as timestamp)

,cast('20230420' as timestamp)

,cast('20230420' as int)

;

--cast(x as y)

--to_char (datetime/interval [, fmt])

–描述：将一个DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE或者TIMESTAMP WITH LOCAL TIME ZONE类型的DATETIME或者INTERVAL值按照fmt指定的格式转换为VARCHAR类型。

–可选参数fmt可以为以下几类：日期、时间、星期、季度和世纪。每类都可以有不同的模板，模板之间可以合理组合，常见的模板有：HH、MM、SS、YYYY、MM、DD。

–模板可以有修饰词，常用的修饰词是FM，可以用来抑制前导的零或尾随的空白。

–返回值类型：varchar

SELECT to_char(current_timestamp,'HH12:MI:SS')

      ,to_char(current_timestamp,'yyyymmdd')

      ,to_char(current_timestamp,'dd-mm-yyyy')

;

--to_char (numeric/smallint/integer/bigint/double precision/real[, fmt])

–描述：将一个整型或者浮点类型的值转换为指定格式的字符串。

–可选参数fmt可以为以下几类：十进制字符、“分组”符、正负号和货币符号，每类都可以有不同的模板，模板之间可以合理组合，常见的模板有：9、0、,（千分隔符）、.（小数点）。

–模板可以有类似FM的修饰词，但FM不抑制由模板0指定而输出的0。

–要将整型类型的值转换成对应16进制值的字符串，使用模板X或x。

–返回值类型：varchar

SELECT to_char(9527,'9,999'),to_char(9527.0,'9,999.999');

--to_char (string)

–描述：将CHAR、VARCHAR、VARCHAR2、CLOB类型转换为VARCHAR类型。

–如使用该函数对CLOB类型进行转换，且待转换CLOB类型的值超出目标类型的范围，则返回错误。

–返回值类型：varchar

SELECT to_char('9527');

--to_date(text)

–描述：将文本类型的值转换为指定格式的时间戳。

–返回值类型：timestamp without time zone

--to_date(text, text)

–描述：将字符串类型的值转换为指定格式的日期。

–返回值类型：timestamp without time zone

SELECT  to_date('2023-04-20'),to_date('20 Apr 2023', 'DD Mon YYYY');

4、数组函数
在GaussDB数据库中，数组函数是指用于操作数组的函数，例如插入、删除、修改、排序等。这些函数通常用于处理大量数据，例如表中的行数据。

以下是一些常见的数据库数组函数：

--array_append(anyarray, anyelement)

–描述：向数组末尾添加元素，只支持一维数组。

–返回类型：anyarray

--array_prepend(anyelement, anyarray)

–描述：向数组开头添加元素，只支持一维数组。

–返回类型：anyarray

SELECT array_append(ARRAY[1,2], 3) as result1 ,array_prepend(1,ARRAY[2,3]) as result2;

--array_cat(anyarray, anyarray)

–描述：连接两个数组，支持多维数组。

–返回类型：anyarray

--array_union(anyarray, anyarray)

–描述：连接两个数组，只支持一维数组。

–返回类型：anyarray

--array_union_distinct(anyarray, anyarray)

–描述：连接两个数组，并去重，只支持一维数组。

–返回类型：anyarray

--array_intersect(anyarray, anyarray)

–描述：两个数组取交集，只支持一维数组。

–返回类型：anyarray

SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]) AS RESULT1

      ,array_union(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT2

      ,array_union_distinct(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT3

      ,array_intersect(ARRAY[1,2,3], ARRAY[3,4,5]) AS RESULT4

;

--array_delete(anyarray)

–描述：清空数组中的元素并返回一个同类型的空数组。

–返回类型：anyarray

--array_deleteidx(anyarray, int)

–描述：从数组中删除指定下标的元素并返回剩余元素组成的数组。

–返回类型：anyarray

--array_trim(anyarray, int)

–描述：从数组尾部删除指定个数个元素。

–返回类型：anyarray

SELECT array_delete(ARRAY[1,8,3,7]) AS RESULT1

      ,array_deleteidx(ARRAY[1,2,3,4,5], 1) AS RESULT2

      ,array_trim(ARRAY[1,8,3,7],1) AS RESULT4

;

详情参考：云数据库GaussDB

​