《GreenPlum系列》GreenPlum初级教程-06GreenPlum函数

最新推荐文章于 2024-09-02 12:27:20 发布
最新推荐文章于 2024-09-02 12:27:20 发布
阅读量1.4k 收藏 18
点赞数 19
分类专栏： Greenplum 文章标签： greenplum postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51197424/article/details/135736089
版权
本文详细介绍了GreenPlum数据库中的各种内置函数，包括单行函数（如算术运算、数值处理、字符串操作和时间计算）、流程控制函数（如CASEWHEN）、行列转换函数、窗口函数（如聚合函数、lag/lead和排名函数），以及序列号生成函数generate_series，帮助用户快速上手并提高数据库操作效率。
摘要由CSDN通过智能技术生成

文章目录

第六章 函数

1.函数介绍

GreenPlum会将常用的逻辑封装成函数给用户进行使用，类似于Java中方法。好处就是不用重复写逻辑，直接用即可。所以为了在使用更快速便捷的入手，需要了解下GP数据库提供了哪些内置函数，然后根据需要选择对应的函数进行使用。

2.单行函数

单行函数的特点就是一进一出，输入一行，输出一行。

2.1 算术运算符

运算符描述
A+BA和B 相加
A-BA减去B
A*BA和B 相乘
A/BA除以B
A%BA对B取余
A&BA和B按位取与
A|BA和B按位取或
A^BA和B按位取异或
~AA按位取反

2.2 数值函数

函数返回类型描述例子结果
abs(x)(与x相同)绝对值--
ceil(dp或numeric)\ceiling(与输入相同)不小于参数的最小整数--
exp(dp或numeric)(与输入相同)自然指数--
ln(dp或numeric)(与输入相同)自然对数--
log(dp或numeric)(与输入相同)以10 为底的对数--
log(b numeric,x numeric)numeric以b为底的对数--
mod(y,x)(与参数类型相同)y/x的余数--
pi()dpπ--
power(a numeric,b numeric)numerica的b次幂--
radians(dp)dp把角度转为弧度--
random()dp0~1之间的随机数
floor(dp或numeric)(与输入相同)不大于参数的最大整数--
round(v numeric,s int)numeric圆整为s位小数round(42.4382,2)42.44
sign(dp或numeric)(与输入相同)参数的符号(-1,0,+1)sing(-8,4)-1
sqrt(dp或numeric)(与输入相同)平方根--
cbrt(dp)dp立方根--
trunc(v numeric,s int)numeric截断为s位小数--
# 1.random随机0-1之间的数值
zxy=# select random();
      random
------------------
 0.89662363845855
(1 row)

# 2.ceil向上取整
zxy=# select ceil(3.14);
 ceil
------
    4
(1 row)

# 3.floor向下取整
zxy=# select floor(3.14);
 floor
-------
     3
(1 row)

# 4.保留两位小数
zxy=# select round(3.1415926,2);
 round
-------
  3.14
(1 row)

2.3 字符串函数

函数返回类型描述例子结果
string||stringtext字符串连接
length(string)intstring中字符的数目length(‘jose’)4
position(substring in string)int指定的子字符串的位置position(‘om’in’Tomas’)3
substring(string[from int][for int])text抽取子字符串substring(‘Thomas’from 2 for 3)hom
trim([leading|trailing|both][characters]from string)text从字符串string的开头/结尾/两边删除只包含characters中字符（默认是空白）的最长的字符串trim(both ‘x’ from ‘xTomxx’)Tom
lower(string)text把字符串转化为小写
upper(string)text把字符串转化为大写
overlay(string placing string from int [for int])text替换子字符串overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4)Thomas
replace(string text,from text,to text)text把字符串string中出现的所有子字符串from替换成子字符串toreplace(‘abcdefabcdef’,’cd,’XX’)abXXefabXXef
split_part(string text, delimiter text,filed int)text根据delimiter分隔string返回生成的第field个子字符串（1开始）split_part(‘abc|def|ghi’,’|’,2)def
concat_ws(string text,string text…)text使用分隔符A拼接多个字符串，或者一个数组的所有元素。concat_ws(‘-’,‘a’,‘b’,‘c’)a-b-c
# 1.拼接字符串
zxy=# select 'a'||'b';
 ?column?
----------
 ab
(1 row)

# 2.使用连接符连接字符串
zxy=# select concat_ws('-','a','b','c');
 concat_ws
-----------
 a-b-c
(1 row)

# 3.根据指定符号分割字符串
zxy=# select split_part('a-b-c','-',1);
 split_part
------------
 a
(1 row)

2.4 时间函数

使用interval类型可以直接对事件类型进行计算，用来计算时间的加减

函数返回类型描述例子结果
age(timestamp,timestamp)interval减去参数后的”符号化”结果age(timestamp’2001-04-10’,timestamp’1957-06-13)43 years 9 mons 27 das
age(timestam)interval从current_date减去参数中的日期age(timestam’1957-06-13)-
current_datedate当前的日期--
current_timetime with time zone当日时间--
current_timestamptimestamp with time zone当前事务开始时的事件戳--
date_part(text,timestamp)double precision获取子域（等效于extract)date_part(‘hour’,timestamp’2001-02-16 20:38:40)20
date_trunc(text,timestamp)timestamp截断成指定的精度date_trunc(‘hour’,timestamp ‘2001-02-16 20:38:40’)2001/2/16 20:00
extract(field from timestamp)double precision获取子域（同date_part）（同date_part）
now()timestampe with time zone当前事务开始的时间戳-
# 1.求日期相差的年龄
zxy=# select age('2002-01-01'::timestamp,'2000-02-13'::timestamp);
          age
------------------------
 1 year 10 mons 17 days
(1 row)

# 2.从日期中提取年
zxy=# select extract(year from current_date);
 date_part
-----------
      2024
(1 row)

# 3.从日期中提取年
zxy=# select date_part('year',current_date);
 date_part
-----------
      2024
(1 row)

# 4.interval日期相加
zxy=# select '2000-01-01'::timestamp + interval '1 days';
      ?column?
---------------------
 2000-01-02 00:00:00
(1 row)

2.5 流程控制函数

case when：条件判断函数

case when a then b [when c then d]* [else e] end

如果a为true，则返回b；如果c为true，则返回d；否则返回e;

# 1.查看emp表
zxy=# select * from ods.emp;
 empno | ename  | job  | sal  | deptno
-------+--------+------+------+--------
  7521 | 王五   | 行政 | 1250 |     10
  7566 | 赵六   | 销售 | 2975 |     40
  7844 | 小明   | 销售 | 1500 |     40
  7900 | 小元   | 讲师 |  950 |     30
  7369 | 张三   | 研发 |  800 |     30
  7499 | 李四   | 财务 | 1600 |     20
  7698 | 马八   | 研发 | 2850 |     30
  7902 | 小海   | 行政 | 3000 |     10
  7934 | 小红明 | 讲师 | 1300 |     30
  7782 | 金九   |      | 2450 |     30
  7654 | 侯七   | 研发 | 1250 |     30
  7876 | 小李   | 行政 | 1100 |     10
  7788 | 银十   | 行政 | 3000 |     10
  7839 | 小芳   | 销售 | 5000 |     40
  7999 | 小八   | 行政 | 4000 |     10
(14 rows)

# 2.case when匹配对应deptno
zxy=# select empno,ename,job,sal,
zxy-# case deptno when 10 then '行政部'
zxy-# when 20 then '财务部'
zxy-# when 30 then '教学部'
zxy-# when 40 then '销售部'
zxy-# end as dname
zxy-# from ods.emp;
 empno | ename  | job  | sal  | dname
-------+--------+------+------+--------
  7788 | 银十   | 行政 | 3000 | 行政部
  7839 | 小芳   | 销售 | 5000 | 销售部
  7369 | 张三   | 研发 |  800 | 教学部
  7499 | 李四   | 财务 | 1600 | 财务部
  7698 | 马八   | 研发 | 2850 | 教学部
  7902 | 小海   | 行政 | 3000 | 行政部
  7934 | 小红明 | 讲师 | 1300 | 教学部
  7782 | 金九   |      | 2450 | 教学部
  7521 | 王五   | 行政 | 1250 | 行政部
  7566 | 赵六   | 销售 | 2975 | 销售部
  7844 | 小明   | 销售 | 1500 | 销售部
  7900 | 小元   | 讲师 |  950 | 教学部
  7654 | 侯七   | 研发 | 1250 | 教学部
  7876 | 小李   | 行政 | 1100 | 行政部
  7999 | 小八   | 行政 | 4000 | 行政部

(14 rows)

3.行列转换函数

3.1 行转列函数

string_agg(column,parten)

zxy=# select string_agg(ename,',') from ods.emp;
                               string_agg
-------------------------------------------------------------------------
 侯七,小李,银十,小芳,张三,李四,马八,小海,小红明,金九,王五,赵六,小明,小元,小八
(1 row)

3.2 列转行函数

regexp_split_to_table(column,parten)

zxy=# select regexp_split_to_table('侯七,小李,银十,小芳,张三,李四,马八,小海,小红明,金九,王五,赵六,小明,小元,小八',',');
 regexp_split_to_table
-----------------------
 侯七
 小李
 银十
 小芳
 张三
 李四
 马八
 小海
 小红明
 金九
 王五
 赵六
 小明
 小元
 小八
(14 rows)

4.窗口函数

4.1 概述

窗口函数，能为每行数据划分一个窗口，然后对窗口范围内数据进行计算，最后将计算结果返回给该行数据。

4.2 常用窗口函数

1）聚合函数

  • max：最大值

    zxy=# select empno,ename,job,max(sal) over(partition by job) from ods.emp;
 empno | ename  | job  | max
-------+--------+------+------
  7521 | 王五   | 行政 | 4000
  7788 | 银十   | 行政 | 4000
  7902 | 小海   | 行政 | 4000
  7876 | 小李   | 行政 | 4000
  7999 | 小八   | 行政 | 4000
  7934 | 小红明 | 讲师 | 1300
  7900 | 小元   | 讲师 | 1300
  7782 | 金九   |      | 2450
  7654 | 侯七   | 研发 | 2850
  7698 | 马八   | 研发 | 2850
  7369 | 张三   | 研发 | 2850
  7844 | 小明   | 销售 | 5000
  7566 | 赵六   | 销售 | 5000
  7839 | 小芳   | 销售 | 5000
  7499 | 李四   | 财务 | 1600
(14 rows)

  • min：最小值

    zxy=# select empno,ename,job,min(sal) over(partition by job) from ods.emp;
 empno | ename  | job  | min
-------+--------+------+------
  7499 | 李四   | 财务 | 1600
  7369 | 张三   | 研发 |  800
  7698 | 马八   | 研发 |  800
  7654 | 侯七   | 研发 |  800
  7844 | 小明   | 销售 | 1500
  7566 | 赵六   | 销售 | 1500
  7839 | 小芳   | 销售 | 1500
  7902 | 小海   | 行政 | 1100
  7521 | 王五   | 行政 | 1100
  7876 | 小李   | 行政 | 1100
  7788 | 银十   | 行政 | 1100
  7999 | 小八   | 行政 | 1100
  7934 | 小红明 | 讲师 |  950
  7900 | 小元   | 讲师 |  950
  7782 | 金九   |      | 2450
(14 rows)

  • sum：求和

    zxy=# select empno,ename,job,sum(sal) over(partition by job) from ods.emp;
 empno | ename  | job  |  sum
-------+--------+------+-------
  7876 | 小李   | 行政 | 12350
  7999 | 小八   | 行政 | 12350
  7521 | 王五   | 行政 | 12350
  7902 | 小海   | 行政 | 12350
  7788 | 银十   | 行政 | 12350
  7900 | 小元   | 讲师 |  2250
  7934 | 小红明 | 讲师 |  2250
  7782 | 金九   |      |  2450
  7499 | 李四   | 财务 |  1600
  7698 | 马八   | 研发 |  4900
  7369 | 张三   | 研发 |  4900
  7654 | 侯七   | 研发 |  4900
  7839 | 小芳   | 销售 |  9475
  7844 | 小明   | 销售 |  9475
  7566 | 赵六   | 销售 |  9475
(15 rows)

  • avg：平均值

    zxy=# select empno,ename,job,avg(sal) over(partition by job) from ods.emp;
 empno | ename  | job  |       avg
-------+--------+------+------------------
  7698 | 马八   | 研发 | 1633.33333333333
  7369 | 张三   | 研发 | 1633.33333333333
  7654 | 侯七   | 研发 | 1633.33333333333
  7844 | 小明   | 销售 | 3158.33333333333
  7566 | 赵六   | 销售 | 3158.33333333333
  7839 | 小芳   | 销售 | 3158.33333333333
  7788 | 银十   | 行政 |             2470
  7902 | 小海   | 行政 |             2470
  7999 | 小八   | 行政 |             2470
  7876 | 小李   | 行政 |             2470
  7521 | 王五   | 行政 |             2470
  7934 | 小红明 | 讲师 |             1125
  7900 | 小元   | 讲师 |             1125
  7782 | 金九   |      |             2450
  7499 | 李四   | 财务 |             1600
(15 rows)

  • count：计数

    zxy=# select empno,ename,job,count(sal) over(partition by job) from ods.emp;
 empno | ename  | job  | count
-------+--------+------+-------
  7499 | 李四   | 财务 |     1
  7521 | 王五   | 行政 |     5
  7788 | 银十   | 行政 |     5
  7902 | 小海   | 行政 |     5
  7999 | 小八   | 行政 |     5
  7876 | 小李   | 行政 |     5
  7900 | 小元   | 讲师 |     2
  7934 | 小红明 | 讲师 |     2
  7782 | 金九   |      |     1
  7698 | 马八   | 研发 |     3
  7369 | 张三   | 研发 |     3
  7654 | 侯七   | 研发 |     3
  7839 | 小芳   | 销售 |     3
  7844 | 小明   | 销售 |     3
  7566 | 赵六   | 销售 |     3
(15 rows)

2）跨行取值函数

  • lag和lead

    lag(column,offset,default)获取当前行的上某行，某个字段的值

    lead(column,offset,default)获取当前行的下某行，某个字段的值

    • column是指定字段
    • offset是指定偏移量
    • default指定找不到符合条件后的默认值
    zxy=# select empno,ename,job,lag(empno,1,'1') over(partition by job order by empno) lagno,lead(empno,1,'9999') over(partition by job order by empno) leadno from ods.emp;
 empno | ename  | job  | lagno | leadno
-------+--------+------+-------+--------
  7521 | 王五   | 行政 |     1 |   7788
  7788 | 银十   | 行政 |  7521 |   7876
  7876 | 小李   | 行政 |  7788 |   7902
  7902 | 小海   | 行政 |  7876 |   7999
  7999 | 小八   | 行政 |  7902 |   9999
  7900 | 小元   | 讲师 |     1 |   7934
  7934 | 小红明 | 讲师 |  7900 |   9999
  7782 | 金九   |      |     1 |   9999
  7499 | 李四   | 财务 |     1 |   9999
  7369 | 张三   | 研发 |     1 |   7654
  7654 | 侯七   | 研发 |  7369 |   7698
  7698 | 马八   | 研发 |  7654 |   9999
  7566 | 赵六   | 销售 |     1 |   7839
  7839 | 小芳   | 销售 |  7566 |   7844
  7844 | 小明   | 销售 |  7839 |   9999
(15 rows)

3）排名函数

  • row_number

    连续不重复

    zxy=# select job,ename,sal,row_number() over(partition by job order by sal) from ods.emp where job = '行政';
 job  | ename | sal  | row_number
------+-------+------+------------
 行政 | 小李  | 1100 |          1
 行政 | 王五  | 1250 |          2
 行政 | 小海  | 3000 |          3
 行政 | 银十  | 3000 |          4
 行政 | 小八  | 4000 |          5
(5 rows)

  • rank

    不连续不重复

    zxy=# select job,ename,sal,rank() over(partition by job order by sal) from ods.emp where job = '行政';
 job  | ename | sal  | rank
------+-------+------+------
 行政 | 小李  | 1100 |    1
 行政 | 王五  | 1250 |    2
 行政 | 银十  | 3000 |    3
 行政 | 小海  | 3000 |    3
 行政 | 小八  | 4000 |    5
(5 rows)

  • dense_rank

    连续重复

    zxy=# select job,ename,sal,dense_rank() over(partition by job order by sal) from ods.emp where job = '行政';
 job  | ename | sal  | dense_rank
------+-------+------+------------
 行政 | 小李  | 1100 |          1
 行政 | 王五  | 1250 |          2
 行政 | 小海  | 3000 |          3
 行政 | 银十  | 3000 |          3
 行政 | 小八  | 4000 |          4
(5 rows)

5.其他函数

1）序列号生成函数-generate_series(x,y,t)

生成多行数据从x到y，步长为t，默认步长是1

zxy=# select generate_series(1,10,1);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)
DATA数据猿
关注
专栏目录
greenplum常用函数-基础
10-08
gp常用的建表语句，修改新增字段等常见操作，以及时间函数转换和case，基本函数使用，和具体表中遇到的问题。 ps:借用了其他同学的部分总结
Greenplum中的函数
朝着梦想 渐行前进
10-21 6101
Functions Greenplum postgresql
Greenplum数据库中常用函数
weixin_72431427的博客
07-25 728
聚合函数,字符串函数,时间函数,数值函数,条件函数
[ 知识点 ] Greenplum常用函数
且陶陶。
05-26 5465
rgeger
Greenplum 字符串行列转换函数
魂醉的一亩二分地
03-31 4380
Greenplum 字符串行列转换函数 hank=> create table test (id bigint,str text)  DISTRIBUTED BY (id); CREATE TABLE hank=> insert into test values(1,'dazuiba'); INSERT 0 1 hank=> insert into test values(1,'appl
Greenplum行列转换——tablefunc
Focus on PostgreSQL
09-09 3364
1、说明 行列转换是数据库查询中比较常见的一种需求，在Greenplum这种偏向OLAP场景的数据库中更是比较常见了。那么我们如何在gp中实现行列转换呢？我们可以使用postgresql的tablefunc扩展在gp中来直接实现行列转换的功能。 不过在gp中并不像postgresql那样自带tablefunc扩展，因此我们需要将postgresql中的该扩展解压到gp中来使用。 2、安装 安装前要先根据greenplum的版本选择对应的postgresql版本，例如这里我使用的gp5.23版本，所以这里要先
《GreenPlum系列-开发相关》GreenPlum数据库及语法使用
https://blog.datasource.space
03-16 5754
GreenPlum数据库使用 1.启动和停止 su gpadmin gpstart -a(-a的原因是不需要在手动输入yes) gpstop -a 2.查看数据库状态 psql -l pgstate 3.命令行连接访问数据库 命令 意义 \h 查看SQL命令的解释，比如\h select ? 查看PSQL命令列表 \l 列出所有数据库 \c [datebase_name] 连接其他数据库 \d 列出当前数据库的所有表格 \d [table_name] 列出某一张表格的结构
greenplum-db-6.13.0-rhel7-x86_64.zip
12-12
《Greenplum数据库详解与安装指南》 Greenplum是一款高度可扩展的并行数据库系统，专为大数据分析设计。其基于MPP（大规模并行处理）架构，能够高效处理PB级别的数据，广泛应用于商业智能、数据分析等领域。该系统...
greenplum-db-5.3.0-rhel7-code.zip
09-28
《Greenplum数据库5.3.0在RHEL7系统中的应用与解析》 Greenplum数据库是一款由Pivotal公司开发的开源大数据并行数据库系统，它基于PostgreSQL，但设计了高度优化的分布式架构，能处理PB级别的数据。在本资料中，...
greenplum-db-5.0.0-rhel7-x86_64.zip
01-08
同时，Greenplum还提供了高级的数据分析功能，如并行化的数据加载、复杂的SQL查询支持和高级统计函数。 在“greenplum-db-5.0.0-rhel7-x86_64.zip”压缩包中，我们看到一个名为“greenplum-db-5.0.0-rhel7-x86_64....
greenplum-db（6.19.0-src-full.zip）
01-13
2. **src/include**: 这里包含了所有头文件，定义了 Greenplum 数据库系统中使用的类型、结构体、函数原型等。头文件按功能组织，如 `access` 目录用于存储与数据访问相关的头文件，`catalog` 包含元数据定义，`...
greenplum常用函数
zqmy_的博客
01-20 1993
--【常用字符串函数】 --字符串连接 select 'green'||'plum'; --greenplum --string中字符的数目 select length('greenplum'); --9 --抽取子字符串 select substring('greenplum' from 6 for 4); --plum --删除字符两端空格 select trim(' greenplum '); --greenplum --删除字符串的指定字符 select trim(both .
greenplum使用总结之常用SQL及函数
CS_GUNNER的博客
01-28 3592
---------------------------------------------------建表等----------------------------------------------------- –创建序列 –eg:CREATE SEQUENCE users181020_ID_seq 2 START WITH 100 3 INCREMENT BY 1 4 NO MINVALUE 5 NO MAXVALUE 6 CACHE 1; 使用: select nextval(‘users18102
GreenPlum常用字符串函数
热门推荐
HJ.Lam的专栏
01-14 2万+
GreenPlum常用字符串函数如下： 测试代码：eda_gp=> select 'green'||'plum' as dbname; dbname ----------- greenplum (1 row)eda_gp=> select length('greenplum'); length -------- 9 (1 row)eda_gp=> select posi
regexp_split_to_table和regexp_split_to_array
代元培
10-14 5505
regexp_split_to_table、regexp_split_to_array这两个函数都是用来将字符串转换成格式化数据，一个是转换成结果集，一个是转换成数组。 select regexp_split_to_table('a,b,c',','); select regexp_split_to_table('/home/work/pg.sh','\/'); select regexp_split_to_table(coalesce('/home/work/pg.sh',''),E'\/');
GreenPlum
m0_64735594的博客
11-03 274
在典型的Shared-Nothing中，每个节点上所有的资源的CPU、内存、磁盘都是独立的，每个节点都只有全部数据的一部分，也只能使用本节点的数据资源。在Greenplum中，需要存储的数据在进入到表时，将先进行数据分布的处理工作，将一个表中的数据平均分布到每个节点上，并为每个表指定一个分布列（Distribute Column）,之后便根据Hash来分布数据，基于Shared-Nothing的原则，Greenplum这样处理可以充分发挥每个节点处IO的处理能力。Master节点一般只有一个或二个。
greenplum数据库的使用
云计算大数据学习之小学生的博客
08-13 1万+
三、           Greenplum数据库的基本使用 1.       启动和停止 su gpadmin gpstart -a ( -a的原因是不需要在手动输入yes) gpstop -a   2.       查看数据库状态 psql -l pgstate   3.       命令行连接访问数据库     在命令行界面也可以输入以下 \h：查看SQL命令的解...
PostgreSql数据库-行转列函数crosstab
qq_42213727的博客
06-01 3326
PostgreSql数据库-行转列函数crosstab
greenplum string_agg将多行数据连接成一个字符串
最新发布
weixin_72431427的博客
09-02 392
如果你想用竖线作为分隔符，语法非常相似：1John Doe2。
Greenplum数据库操作：常用字符串与时间函数解析
本文主要介绍了Greenplum数据库中的一些常用SQL命令，特别是涉及字符串操作和时间日期处理的函数。 在Greenplum中，字符串处理是数据操作的重要部分。以下是一些常用的字符串函数： 1. 字符串拼接：使用 `||` 符号...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DATA数据猿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值