使用函数和操作符
上级主题： 查询数据
在Greenplum数据库中使用函数
表 1. Greenplum数据库中的函数
| 函数类型 | Greenplum支持 | 描述 | 注释 |
|---|---|---|---|
| IMMUTABLE | 支持 | 只依赖直接出现在其参数列表中的信息。给定相同的参数值，总是会返回相同的结果。 | |
| STABLE | 大部分情况下支持 | 在一个单一表扫描中，对相同的参数值返回相同的结果，但是对不同的SQL语句结果会改变。 | 结果依赖于数据库查找或者参数值。current_timestamp函数族是STABLE的，在一次执行中值不会改变。 |
| VOLATILE | 受限支持 | 在一个单一表扫描中函数值可能会改变。例如：random()、currval()、timeofday()。 |
任何有副作用的函数都是volatile，即使它的结果是可预测的也一样。例如：setval()。 |
更多有关Greenplum数据库函数易变性分类的信息请参考PostgreSQL的函数易变性分类文档。
在Greenplum数据库中，数据被划分在Segment上 — 每个Segment是一个独特的PostgreSQL数据库。为了防止不一致或者预期之外的结果，如果分类为VOLATILE的函数以任何形式包含SQL命令或修改数据库，不要在Segment层面上执行它。例如，在Greenplum数据库中不允许在分布数据上执行setval()之类的函数，因为它们可能导致Segment实例间不一致的数据。
为了确保数据一致性，可以在从Master计算和运行的语句中安全地使用VOLATILE以及STABLE函数。例如，下面的运行在Master（没有FROM子句的语句）上的语句：
SELECT setval('myseq', 201);
SELECT foo();
如果语句的FROM子句中包含一个分布表并且FROM子句中的函数返回一个行集合，则该语句可以运行在Segment上：
SELECT * from foo();
Greenplum数据库不支持返回表引用（rangeFuncs）的函数或者使用refCursor数据类型的函数。
函数易变性和计划缓冲
简单的交互式查询会被规划然后立即执行，对这类查询来说STABLE和IMMUTABLE函数易变性分类之间的区别相对较小。一个函数是在规划时被执行一次或是在查询执行启动时被执行一次关系并不大。但是当用户保存计划并且在后来重用它时，区别就非常大了。如果把一个函数误标为IMMUTABLE，Greenplum数据库可能在规划时会过早地把它折叠成一个常量，可能在该计划的后续执行中重用到一个不新鲜的值。在使用PREPARE语句或者使用PL/pgSQL这种缓冲计划的语言时可能会遇到这种危险。
用户定义的函数
Greenplum数据库支持用户定义的函数。更多信息请见PostgreSQL文档中的Extending SQL。
使用CREATE FUNCTION语句注册用户定义的函数，它们可以按在Greenplum数据库中使用函数中所述的方式被使用。默认情况下，用户定义的函数被声明为VOLATILE，因此如果用户定义的函数是IMMUTABLE或者STABLE，必须在注册该函数时指定正确的易变性级别。
在创建用户定义的函数时，避免使用致命错误或者破坏性的调用。Greenplum数据库可能会用突然的关闭或者重启相应这些错误。
在Greenplum数据库中，用户定义的函数的共享库必须位于Greenplum数据库阵列中所有主机（Master、Segment以及镜像）上的同一库路径位置中。
还可以创建并执行用Greenplum数据库过程语言（例如PL/pgSQL）编写的匿名代码块。匿名块作为短暂的匿名函数运行。有关创建和执行匿名块的信息，请见DO命令。
内建函数和操作符
下面的表格列出了PostgreSQL支持的内建函数和操作符的分类。Greenplum数据库中支持PostgreSQL中所有的函数和操作符，除了STABLE以及VOLATILE函数之外，这两种服从在Greenplum数据库中使用函数中记录的限制。更多有关这些内建函数和操作符的信息请见PostgreSQL文档中的函数和操作符小节。
Greenplum数据库包括操作json数据类型值的JSON处理函数。有关JSON数据的信息，请见使用JSON数据。
表 2. 内建函数和操作符
| 操作符/函数分类 | VOLATILE函数 | STABLE函数 | 限制 | ||||
|---|---|---|---|---|---|---|---|
| 逻辑操作符 | |||||||
| 比较操作符 | |||||||
| 数学函数和操作符 | randomsetseed | ||||||
| 字符串函数和操作符 | 所有内建转换函数 | convertpg_client_encoding | |||||
| 二进制串函数和操作符 | |||||||
| 位串函数和操作符 | |||||||
| 模式匹配 | |||||||
| 数据类型格式化函数 | to_charto_timestamp | ||||||
| 日期/时间函数和操作符 | timeofday | agecurrent_date current_time current_timestamp localtime localtimestamp now | |||||
| 枚举支持函数 | |||||||
| 几何函数和操作符 | |||||||
| 网络地址函数和操作符 | |||||||
| 序列操纵函数 | currvallastval nextval setval | ||||||
| 条件表达式 | |||||||
| 数组函数和操作符 | 所有数组函数 | ||||||
| 聚集函数 | |||||||
| 子查询表达式 | |||||||
| 行及数组比较 | |||||||
| 几何返回函数 | generate_series | ||||||
| 系统信息函数 | 所有会话信息函数 所有访问特权查询函数 所有方案可见性查询函数 所有系统目录信息函数 所有注释信息函数 所有事务ID和快照 | ||||||
| 系统管理函数 | set_configpg_cancel_backend pg_reload_conf pg_rotate_logfile pg_start_backup pg_stop_backup pg_size_pretty pg_ls_dir pg_read_file pg_stat_file | current_setting所有数据库对象尺寸函数 | 注意： 函数pg_column_size显示存储该值要求的字节数，该值可能被TOAST压缩过。 |
||||
| XML函数以及类函数表达式 | cursor_to_xml(cursor refcursor, count int, nulls boolean, tableforest boolean, targetns text) cursor_to_xmlschema(cursor refcursor, nulls boolean, tableforest boolean, targetns text) database_to_xml(nulls boolean, tableforest boolean, targetns text) database_to_xmlschema(nulls boolean, tableforest boolean, targetns text) database_to_xml_and_xmlschema(nulls boolean, tableforest boolean, targetns text) query_to_xml(query text, nulls boolean, tableforest boolean, targetns text) query_to_xmlschema(query text, nulls boolean, tableforest boolean, targetns text) query_to_xml_and_xmlschema(query text, nulls boolean, tableforest boolean, targetns text) schema_to_xml(schema name, nulls boolean, tableforest boolean, targetns text) schema_to_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text) schema_to_xml_and_xmlschema(schema name, nulls boolean, tableforest boolean, targetns text) table_to_xml(tbl regclass, nulls boolean, tableforest boolean, targetns text) table_to_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) table_to_xml_and_xmlschema(tbl regclass, nulls boolean, tableforest boolean, targetns text) xmlagg(xml) xmlconcat(xml[, ...]) xmlelement(name name [, xmlattributes(value [AS attname] [, ... ])] [, content, ...]) xmlexists(text, xml) xmlforest(content [AS name] [, ...]) xml_is_well_formed(text) xml_is_well_formed_document(text) xml_is_well_formed_content(text) xmlparse ( { DOCUMENT \ | CONTENT } value) xpath(text, xml) xpath(text, xml, text[]) xpath_exists(text, xml) xpath_exists(text, xml, text[]) xmlpi(name target [, content]) xmlroot(xml, version text \ | no value [, standalone yes\ | no\ | no value]) xmlserialize ( { DOCUMENT \ | CONTENT } value AS type ) xml(text) text(xml) xmlcomment(xml) xmlconcat2(xml, xml) |
窗口函数
下列内建窗口函数是Greenplum对于PostgreSQL数据库的扩展。所有的窗口函数都是immutable。更多有关窗口函数的信息，请见窗口表达式。
表 3. 窗口函数
| 函数 | 返回值 | 完整语法 | 描述 | |
|---|---|---|---|---|
cume_dist() |
double precision |
CUME_DIST() OVER ( [PARTITION BY expr ] ORDER BY expr ) |
计算一组值中一个值的累积分布。具有相等值的行总是具有相同的累积分布值。 | |
dense_rank() |
bigint |
DENSE_RANK () OVER ( [PARTITION BY expr ] ORDER BY expr ) |
计算一个有序行组中一行的无跳跃排名值的排名。具有相等值的行会得到相同的排名值。 | |
first_value(expr) |
和输入expr的类型相同 | FIRST_VALUE( expr ) OVER ( [PARTITION BY expr ] ORDER BY expr `[ROWS |
RANGEframe_expr] )` |
返回一个有续值集合中的第一个值。 |
lag(expr [,offset] [,default]) |
和输入expr的类型相同 | LAG( expr [, offset ] [, default ]) OVER ( [PARTITION BY expr ] ORDER BY expr ) |
在不做自连接的情况下，提供对于同一个表中多于一行的访问。给定一个查询返回的一系列行以及该游标的一个位置，LAG提供对位于该位置之前一个给定物理偏移量的行的访问。默认的offset为1。default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。 |
|
last_value(expr) |
和输入expr的类型相同 | `LAST_VALUE(expr) OVER ( [PARTITION BY expr] ORDER BY expr [ROWS | RANGE frame_expr] )` | 返回一个有续值集合中的最后一个值。 |
lead(expr [,offset] [,default]) |
和输入expr的类型相同 | LEAD(expr [,offset] [,exprdefault]) OVER ( [PARTITION BY expr] ORDER BY expr ) |
在不做自连接的情况下，提供对于同一个表中多于一行的访问。给定一个查询返回的一系列行以及该游标的一个位置，lead提供对位于该位置之后一个给定物理偏移量的行的访问。如果没有指定offset，默认偏移量是1。default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。 |
|
ntile(expr) |
bigint |
NTILE(expr) OVER ( [PARTITION BY expr] ORDER BY expr ) |
把一个有序数据集划分成一些桶（由expr定义）并且为每一行分配一个桶号。 | |
percent_rank() |
double precision |
PERCENT_RANK () OVER ( [PARTITION BY expr] ORDER BY expr ) |
计算一个假设行R的排名减1，然后除以被计算的行数（在一个窗口分区内）减1。 |
|
rank() |
bigint |
RANK () OVER ( [PARTITION BY expr] ORDER BY expr ) |
计算一行在一个有序值组中的排名。根据排名标准有相等值的行得到相同的排名。被占用的行数被加到排名数上来计算下一个排名值。在这种情况下，排名可能不是连续的数字。 | |
row_number() |
bigint |
ROW_NUMBER () OVER ( [PARTITION BY expr] ORDER BY expr ) |
为窗口分区中的每一行或者查询中的每一行分配一个唯一的编号。 |
高级聚集函数
下列内建高级聚集函数是Greenplum对PostgreSQL数据库的扩展。这些函数都是immutable。 注意： 用于分析的Greenplum MADlib扩展提供了额外的高级函数来执行对Greenplum数据库数据的统计分析和机器学习。请见Greenplum数据库参考指南中的Greenplum的MADlib分析扩展。
表 4. 高级聚集函数
| 函数 | 返回类型 | 完整语法 | 描述 |
|---|---|---|---|
MEDIAN (expr) |
timestamp, timestamptz, interval, float |
MEDIAN (expression) 例子： SELECT department_id, MEDIAN(salary) FROM employees GROUP BY department_id; |
可以用一个二维数组作为输入。把这些数组当作矩阵。 |
PERCENTILE_CONT (expr) WITHIN GROUP (ORDER BY expr [DESC/ASC]) |
timestamp, timestamptz, interval, float |
PERCENTILE_CONT(percentage) WITHIN GROUP (ORDER BY expression) 例子： SELECT department_id, PERCENTILE_CONT (0.5) WITHIN GROUP (ORDER BY salary DESC) "Median_cont"; FROM employees GROUP BY department_id; |
执行假设一种连续分布模型的逆分布函数。它接受一个百分数值和一个排序说明，并且返回参数的数字数据类型相同的类型。这个返回值是执行线性插值后的计算结果。在这种计算中忽略空值。 |
PERCENTILE_DISC (expr) WITHIN GROUP (ORDER BY expr [DESC/ASC]) |
timestamp, timestamptz, interval, float |
PERCENTILE_DISC(percentage) WITHIN GROUP (ORDER BY expression) 例子： SELECT department_id, PERCENTILE_DISC (0.5) WITHIN GROUP (ORDER BY salary DESC) "Median_desc"; FROM employees GROUP BY department_id; |
执行假设一种离散分布模型的逆分布函数。它接受一个百分数值和一个排序说明。这个返回值是来自于集合的一个元素。这种计算中忽略空值。 |
sum(array[]) |
smallint[]int[], bigint[], float[] |
sum(array[[1,2],[3,4]]) 例子： CREATE TABLE mymatrix (myvalue int[]); INSERT INTO mymatrix VALUES (array[[1,2],[3,4]]); INSERT INTO mymatrix VALUES (array[[0,1],[1,0]]); SELECT sum(myvalue) FROM mymatrix; sum --------------- { { 1,3},{ 4,4}} |
执行矩阵求和。接受一个二维数组作为输入，并且将它当作一个矩阵。 |
pivot_sum (label[], label, expr) |
int[], bigint[], float[] |
pivot_sum( array['A1','A2'], attr, value) |
使用sum来解决重复项的pivot聚集。 |
unnest (array[]) |
anyelement集合 |
unnest( array['one', 'row', 'per', 'item']) |
把一个一维数组转换成行。返回一个anyelement（一种多态伪类型，请见PostgreSQL中的伪类型）集合。 |