在 Greenplum 数据库开发中，经常会遇到诸如行转列、单行变多行等需求，熟练使用 Greenplum 自带的函数，实现这些功能会变得得心应手。
表结构设计
- 表创建
GP 系统表中保存的表名都是以小写保存。通常 SQL 语句中表名对大小写不敏感，但是不允许在建表语句中使用双引号（""）包括表名，这样会影响系统表中存储的名称，使得表名存在大小写或特殊字符。
数据类型
| 序号 | 类型 | 存储空间 | 描述 |
| 1 | integer | 4 字节 | 常用的整数 |
| 2 | numeric | 变长 | 用户声明精度，精确 |
| 3 | varchar(n) | 变长字符，有长度限制 | |
| 4 | text | 变长字符，无长度限制 | |
| 5 | date | 4 字节 | 只用于表示日期 |
| 6 | timestamp | 8 字节 | 日期和时间，不带时区 |
| 7 | timestamptz | 8 字节 | 日期和时间，带时区 |
| 8 | interval | 12 字节 | 时间间隔 |
rownum 伪列
Oracle 中常用的 rownum 在 Greenplum 中没有，可以通过开窗函数模拟类似 rownum 的列来使用。
postgres=# select row_number() over() as rownum, * from emp; rownum | empno | ename | job | mgr | hiredate | sal | comm | deptno --------+-------+--------+-----------+------+------------+---------+---------+-------- 1 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 2 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 3 | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 4 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 5 | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 6 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 8 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 9 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 10 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 11 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 12 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 13 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 14 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 (14 rows)
字符串拆分
单行变多行
- regexp_split_to_table
postgres=# select row_number() over() as rownum, * from (select regexp_split_to_table('A001/D/200198', '/') as param) t; rownum | param --------+-------- 1 | A001 2 | D 3 | 200198 postgres=# select row_number() over() as rownum, * from (select regexp_split_to_table('苹果,橘子,葡萄', ',') as param) t; rownum | param --------+------- 1 | 苹果 2 | 橘子 3 | 葡萄
- regexp_split_to_table 字段操作
postgres=# create table test_str(id int, str varchar(100)) distributed by (id); postgres=# insert into test_str values(1, 'hello|world'); postgres=# insert into test_str values(2, 'database|greenplum|system'); postgres=# select * from test_str; id | str ----+--------------------------- 2 | database|greenplum|system 1 | hello|world postgres=# select id, regexp_split_to_table(str, E'\\|') str from test_str; id | str ----+----------- 1 | hello 1 | world 2 | database 2 | greenplum 2 | system
注：由于 | 字符被转义了，因此想正确表达，必须对其进行转义。
- string_to_array
postgres=# select unnest(string_to_array('A001,D,200198', ',')) param; param -------- A001 D 200198
单行变多列
- split_part
postgres=# select split_part('A001,D,200198', ',', 1) param1, split_part('A001,D,200198', ',', 2) param2, split_part('A001,D,200198', ',', 3) param3; param1 | param2 | param3 --------+--------+-------- A001 | D | 200198
多行变单行
- string_agg
postgres=# create table test_str(id int, str varchar(20)) distributed by (id); postgres=# insert into test_str values(1, 'hello'); postgres=# insert into test_str values(1, 'world'); postgres=# insert into test_str values(2, 'greenplum'); postgres=# insert into test_str values(2, 'database'); postgres=# insert into test_str values(2, 'system'); postgres=# select * from test_str; id | str ----+----------- 1 | hello 1 | world 2 | greenplum 2 | database 2 | system postgres=# select id, string_agg(str, ',' order by str) from test_str group by id order by id; id | string_agg ----+--------------------------- 1 | hello,world 2 | database,greenplum,system
行转列
- decode
postgres=# with fruit as ( select 1 id, '苹果' name, 1000 nums union all select 2 id, '苹果' name, 2000 nums union all select 3 id, '橘子' name, 5000 nums union all select 4 id, '葡萄' name, 3500 nums union all select 5 id, '芒果' name, 4200 nums ) select * from fruit; id | name | nums ----+------+------ 1 | 苹果 | 1000 2 | 苹果 | 2000 3 | 橘子 | 5000 4 | 葡萄 | 3500 5 | 芒果 | 4200 postgres=# with fruit as ( select 1 id, '苹果' name, 1000 nums union all select 2 id, '苹果' name, 2000 nums union all select 3 id, '橘子' name, 5000 nums union all select 4 id, '葡萄' name, 3500 nums union all select 5 id, '芒果' name, 4200 nums ) select sum(decode(name, '苹果', nums)) as apple, sum(decode(name, '橘子', nums)) as orange, sum(decode(name, '葡萄', nums)) as grape, sum(decode(name, '芒果', nums)) as mango from fruit; apple | orange | grape | mango -------+--------+-------+------- 3000 | 5000 | 3500 | 4200
列转行
- unnest(array)
postgres=# with fruit as ( select 1 id, '苹果' name, 1000 q1, 2000 q2, 3300 q3, 5000 q4 union all select 2 id, '橘子' name, 3000 q1, 3000 q2, 3200 q3, 1500 q4 union all select 3 id, '香蕉' name, 2500 q1, 3500 q2, 2200 q3, 2500 q4 union all select 4 id, '葡萄' name, 1500 q1, 2500 q2, 1200 q3, 3500 q4 ) select * from fruit; id | name | q1 | q2 | q3 | q4 ----+------+------+------+------+------ 1 | 苹果 | 1000 | 2000 | 3300 | 5000 2 | 橘子 | 3000 | 3000 | 3200 | 1500 3 | 香蕉 | 2500 | 3500 | 2200 | 2500 4 | 葡萄 | 1500 | 2500 | 1200 | 3500 postgres=# with fruit as ( select 1 id, '苹果' name, 1000 q1, 2000 q2, 3300 q3, 5000 q4 union all select 2 id, '橘子' name, 3000 q1, 3000 q2, 3200 q3, 1500 q4 union all select 3 id, '香蕉' name, 2500 q1, 3500 q2, 2200 q3, 2500 q4 union all select 4 id, '葡萄' name, 1500 q1, 2500 q2, 1200 q3, 3500 q4 ) select id, -- 聚合列 name, -- 聚合列 unnest(array['Q1', 'Q2','Q3', 'Q4']) as colname, -- 自定义名称，与需转换的列数量一致 unnest(array[q1, q2, q3, q4]) as colvalue -- 需转换的列 FROM fruit; id | name | colname | colvalue ----+------+---------+---------- 1 | 苹果 | Q1 | 1000 1 | 苹果 | Q2 | 2000 1 | 苹果 | Q3 | 3300 1 | 苹果 | Q4 | 5000 2 | 橘子 | Q1 | 3000 2 | 橘子 | Q2 | 3000 2 | 橘子 | Q3 | 3200 2 | 橘子 | Q4 | 1500 3 | 香蕉 | Q1 | 2500 3 | 香蕉 | Q2 | 3500 3 | 香蕉 | Q3 | 2200 3 | 香蕉 | Q4 | 2500 4 | 葡萄 | Q1 | 1500 4 | 葡萄 | Q2 | 2500 4 | 葡萄 | Q3 | 1200 4 | 葡萄 | Q4 | 3500
集合操作
并集
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
- UNION 默认会去重，即重复数据只会返回一行
postgres=# select * from test1; id | name ----+-------- 1 | Apple 2 | Orange postgres=# select * from test2; id | name ----+-------- 1 | Apple 3 | Banana postgres=# select * from test1 union select * from test2 order by 1; id | name ----+-------- 1 | Apple 2 | Orange 3 | Banana
- UNION ALL 会保留重复数据，返回所有行
postgres=# select * from test1 union all select * from test2 order by 1; id | name ----+-------- 1 | Apple 1 | Apple 2 | Orange 3 | Banana
交集
- INTERSECT 两个查询共有的记录
postgres=# select * from test1 intersect select * from test2; id | name ----+------- 1 | Apple
差集
- EXCEPT 第一个查询的结果集减去第二个查询的结果集
postgres=# select * from test1 except select * from test2; id | name ----+-------- 2 | Orange
GET DIAGNOSTICS
PostgreSQL PL/pgSQL 命令 GET DIAGNOSTICS 可用于检索有关当前执行状态的信息。
get [ current ] diagnostics variable { = | := } item [ , ... ];
例如：
get diagnostics integer_var = row_count;
捕获运行过程中的状态值
| 序号 | 名称 | 类型 | 描述 |
| 1 | row_count | bigint | 最近的 SQL 命令处理的行数 |
| 2 | pg_context | 变长 | 描述当前调用堆栈的文本行 |
- 最近的 SQL 命令处理的行数
create table fruits as select * from (values (1, 'banana'), (2, 'pear'), (3, 'apple')) t (id, name) distributed by (id); create or replace function update_fruits() returns setof fruits language plpgsql as $function$ declare rows integer; begin update fruits set id = id + 1; get diagnostics rows = row_count; return query select null::int, format ('%s rows updated', rows); return query select * from fruits order by id; get diagnostics rows = row_count; return query select null::int, format ('%s rows retrieved', rows); end; $function$; postgres=# select * from scott.update_fruits(); id | name ----+------------------ | 3 rows updated 2 | banana 3 | pear 4 | apple | 3 rows retrieved
- 描述当前调用堆栈的文本行
create or replace function inner_func() returns text language plpgsql as $function$ declare stack text; begin get diagnostics stack = pg_context; raise notice E'---Call Stack---\n%', stack; return 'Succeed...'; end; $function$; create or replace function outer_func() returns text language plpgsql as $function$ begin return inner_func(); end; $function$; postgres=# select scott.outer_func(); NOTICE: ---Call Stack--- PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS PL/pgSQL function outer_func() line 3 at RETURN CONTEXT: PL/pgSQL function outer_func() line 3 at RETURN outer_func ------------ Succeed...
捕获异常时的 STACK 内容
| 序号 | 名称 | 类型 | 描述 |
| 1 | returned_sqlstate | text | 异常的 sqlstate 错误代码 |
| 2 | message_text | text | 异常的主要消息的文本 |
| 3 | pg_exception_context | text | 描述异常时调用堆栈的文本行 |
create or replace function scott.use_diagnostics() returns void language plpgsql as $function$ declare v_state text; v_msg text; v_context text; begin create table test(id int) distributed by(id); create table test(id int) distributed by(id); exception when others then get stacked diagnostics v_state = returned_sqlstate, v_msg = message_text, v_context = pg_exception_context; raise exception E'got exception: state : % message: %', v_state, v_msg || ', ' || v_context; end; $function$; postgres=# SELECT scott.use_diagnostics(); ERROR: got exception: state : 42P07 message: relation "test" already exists, SQL statement "create table test(id int) distributed by(id)" PL/pgSQL function use_diagnostics() line 8 at SQL statement
存储过程日志记录
- 创建存储过程日志记录表
CREATE TABLE RT_PUB_PROC_LOGS ( VC_ID VARCHAR2(100) DEFAULT SYS_GUID() NOT NULL, VC_OWNER VARCHAR2(50), VC_MODULE_NAME VARCHAR2(50) DEFAULT 'PROCEDURE', VC_PROC_NAME VARCHAR2(50), D_STARTDATE DATE, D_ENDATE DATE, C_PARAMS CLOB, C_PARAMS2 CLOB, VC_SQLCODE VARCHAR2(50), VC_SQLERRM VARCHAR2(2000), VC_ERR_CONTENT VARCHAR2(2000), T_STARTTIME TIMESTAMP(6), T_ENDTIME TIMESTAMP(6), F_ELAPSEDTIME INTEGER, VC_STATUS VARCHAR2(2), D_UPDATETIME DATE DEFAULT SYSDATE ); COMMENT ON TABLE MPUB_F_PROC_LOGS IS '数据处理日志记录表'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_ID IS '唯一标识'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_OWNER IS '所属用户'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_MODULE_NAME IS '模块名称'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_PROC_NAME IS '模块代码'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_STARTDATE IS '模块参数（开始日期）'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_ENDATE IS '模块参数（结束日期）'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.C_PARAMS IS '模块参数（组合代码、计划代码...）'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.C_PARAMS2 IS '预留模块参数，可为空'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_SQLCODE IS 'SQLCODE'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_SQLERRM IS 'SQLERRM'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_ERR_CONTENT IS '异常信息'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.T_STARTTIME IS '程序开始时间'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.T_ENDTIME IS '程序结束时间'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.F_ELAPSEDTIME IS '总耗时（毫秒）'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.VC_STATUS IS '运行状态（1 正在运行；0 结束；-1 异常）'; COMMENT ON COLUMN MPUB_F_PROC_LOGS.D_UPDATETIME IS '更新时间'; ALTER TABLE MPUB_F_PROC_LOGS ADD CONSTRAINT PK_MPUB_F_PROC_LOGS PRIMARY KEY (VC_ID);
- 创建用于记录时间的函数
CREATE OR REPLACE FUNCTION F_TIMESTAMP_DIFF(I_STARTTIME IN TIMESTAMP,
I_ENDTIME IN TIMESTAMP)
RETURN INTEGER AS
/****************************************************************\
* xxxxxx
* COPYRIGHT (C) 2020, xxxxxx
* ===============================================================
*
* 名 称：F_TIMESTAMP_DIFF
* 功能 描述：存储过程运行时间
*
* 参 数：I_START_TIME 开始时间
* I_END_TIME 结束时间
*
* 修订 记录：
* 版本号 编辑时间 编辑人 修改描述
* 1.0.0 2020-03-10 xxx 创建此函数
\****************************************************************/
STR VARCHAR2(50);
MISECOND INTEGER; --毫秒
SECONDS INTEGER; --秒
MINUTES INTEGER; --分
HOURS INTEGER; --小时
DAYS INTEGER; --天
BEGIN
STR := TO_CHAR(I_ENDTIME - I_STARTTIME);
MISECOND := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 10, 3));
SECONDS := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 7, 2));
MINUTES := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 4, 2));
HOURS := TO_NUMBER(SUBSTR(STR, INSTR(STR, ' ') + 1, 2));
DAYS := TO_NUMBER(SUBSTR(STR, 1, INSTR(STR, ' ')));
RETURN DAYS*24*60*60*1000 + HOURS*60*60*1000 + MINUTES*60*1000 + SECONDS*1000 + MISECOND;
END;
- 创建日志记录存储过程
CREATE OR REPLACE PROCEDURE P_MPUB_F_PROC_LOGS(I_ID IN VARCHAR2,
I_OWNER IN VARCHAR2 DEFAULT NULL,
I_MODULE_NAME IN VARCHAR2 DEFAULT NULL,
I_PROC_NAME IN VARCHAR2 DEFAULT NULL,
I_STARTDATE IN DATE DEFAULT NULL,
I_ENDDATE IN DATE DEFAULT NULL,
I_SQLCODE IN NUMBER DEFAULT NULL,
I_SQLERRM IN VARCHAR2 DEFAULT NULL,
I_ERR_CONTENT IN VARCHAR2 DEFAULT NULL,
I_PARAMS IN CLOB DEFAULT NULL,
I_PARAMS2 IN CLOB DEFAULT NULL,
I_STATUS IN VARCHAR2) IS
/****************************************************************\
* xxxxxx
* COPYRIGHT (C) 2020, xxxxx
* ===============================================================
*
* 名 称：P_MPUB_F_PROC_LOGS
* 功能 描述：存储过程日期记录
*
* 参 数：I_USER_NAME 用户名
* I_MODULE_NAME 模块分类，默认为 'PROCEDURE'
* I_PROC_NAME 模块名称
* I_STARTDATE 模块参数（开始日期）
* I_ENDDATE 模块参数（结束日期）
* I_PARAMS 模块参数（组合代码、计划代码...）
* I_PARAMS2 预留模块参数，可为空
* I_SQLCODE SQLCODE
* I_SQLERRM SQLERRM
* I_ERR_CONTENT 异常信息
* I_STATUS 模块运行状态（1 正在运行；0 结束；-1 异常）
*
* 修订 记录：
* 版本号 编辑时间 编辑人 修改描述
* 1.0.0 2020-03-10 xxx 创建此存储过程
\****************************************************************/
V_STARTTIME TIMESTAMP; --开始时间
V_ENDTIME TIMESTAMP; --结束时间
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--程序运行中...
IF I_STATUS = '1' THEN
SELECT SYSTIMESTAMP INTO V_STARTTIME FROM DUAL;
INSERT INTO MPUB_F_PROC_LOGS (
VC_ID,
VC_OWNER,
VC_MODULE_NAME,
VC_PROC_NAME,
D_STARTDATE,
D_ENDATE,
VC_SQLCODE,
VC_SQLERRM,
VC_ERR_CONTENT,
C_PARAMS,
C_PARAMS2,
T_STARTTIME,
T_ENDTIME,
F_ELAPSEDTIME,
VC_STATUS,
D_UPDATETIME
)
SELECT
I_ID,
I_OWNER,
I_MODULE_NAME,
I_PROC_NAME,
I_STARTDATE,
I_ENDDATE,
NULL,
NULL,
NULL,
I_PARAMS,
I_PARAMS2,
V_STARTTIME,
NULL,
NULL,
I_STATUS,
SYSDATE
FROM DUAL;
--程序结束...
ELSIF I_STATUS = '0' THEN
SELECT SYSTIMESTAMP INTO V_ENDTIME FROM DUAL;
SELECT T_STARTTIME INTO V_STARTTIME FROM MPUB_F_PROC_LOGS WHERE VC_ID = I_ID;
UPDATE MPUB_F_PROC_LOGS S
SET S.VC_SQLCODE = I_SQLCODE,
S.VC_SQLERRM = I_SQLERRM,
S.T_ENDTIME = V_ENDTIME,
S.F_ELAPSEDTIME = F_TIMESTAMP_DIFF(V_ENDTIME, V_STARTTIME),
S.VC_STATUS = I_STATUS
WHERE S.VC_ID = I_ID;
--程序异常...
ELSIF I_STATUS = '-1' THEN
UPDATE MPUB_F_PROC_LOGS S
SET S.VC_SQLCODE = I_SQLCODE,
S.VC_SQLERRM = I_SQLERRM,
S.VC_ERR_CONTENT = I_ERR_CONTENT,
S.VC_STATUS = I_STATUS
WHERE S.VC_ID = I_ID;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END P_MPUB_F_PROC_LOGS;
- 主存储过程中调用日志记录存储过程
PROCEDURE P_TEST(I_STARTDATE IN VARCHAR2,
I_ENDDATE IN VARCHAR2,
I_PARAM IN CLOB,
O_ERRCODE OUT INTEGER,
O_ERRMSG OUT VARCHAR2) IS
/*****************************************************************************\
* xxxxxx
* COPYRIGHT (C) 2020, xxxxxx
* ===========================================================================
*
* 名 称：P_TEST
* 功能 描述：xxxxx
*
* 参 数：传入 -> xxxxx
* 传出 -> xxxxx
*
* 修订 记录:
* 版本号 编辑时间 编辑人 修改描述
* 1.0.0 2020-02-28 Li.chaoxiang 创建此存储过程
\*****************************************************************************/
V_STARTDATE DATE; --开始日期
V_ENDDATE DATE; --结束日期
V_OWNER VARCHAR2(20); --所属用户
V_ID VARCHAR2(100); --唯一标识
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
O_ERRCODE := 0;
O_ERRMSG := '运行成功';
V_STARTDATE := TO_DATE(I_STARTDATE, 'YYYY-MM-DD');
V_ENDDATE := TO_DATE(I_ENDDATE, 'YYYY-MM-DD');
--获取所属用户
SELECT USERNAME INTO V_OWNER FROM USER_USERS;
WHILE (V_LOOPDATE <= V_ENDDATE) LOOP
--获取唯一标识
SELECT SYS_GUID() INTO V_ID FROM DUAL;
--程序开始，写入日志信息
P_MPUB_F_PROC_LOGS(I_ID => V_ID,
I_OWNER => V_OWNER,
I_MODULE_NAME => 'PROCEDURE',
I_PROC_NAME => 'P_TEST',
I_STARTDATE => V_STARTDATE,
I_ENDDATE => V_ENDDATE,
I_PARAMS => V_FUNDCODE,
I_STATUS => '1');
--程序结束，更新状态
P_MPUB_F_PROC_LOGS(I_ID => V_ID,
I_SQLCODE => O_ERRCODE,
I_SQLERRM => O_ERRMSG,
I_STATUS => '0');
V_LOOPDATE := V_LOOPDATE + 1;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
--程序异常，写入日志信息
P_MPUB_F_PROC_LOGS(I_ID => V_ID,
I_SQLCODE => SQLCODE,
I_SQLERRM => SQLERRM,
I_ERR_CONTENT => SUBSTR('未处理异常:' || SQLERRM || '。' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
1,
1000),
I_STATUS => '-1');
ROLLBACK;
O_ERRCODE := SQLCODE;
O_ERRMSG := SUBSTR('未处理异常:' || SQLERRM || '。' ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
1,
1000);
END P_TEST;
删除重复记录
在 GreenPlum 中，表中有两个列是系统分配的，gp_segment_id（分配在哪个节点上），ctid（在每个节点上是唯一的），因此可以利用这两个字段删除数据。
- 构造数据
postgres=# create table scott.t_emp as select * from scott.emp distributed by (empno); postgres=# insert into t_emp select * from emp limit 5;
- 查看重复记录（其中 empno = 7521 重复）
postgres=# select gp_segment_id, ctid, * from t_emp order by empno; gp_segment_id | ctid | empno | ename | job | mgr | hiredate | sal | comm | deptno ---------------+--------+-------+--------+-----------+------+------------+---------+---------+-------- 1 | (0,1) | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 0 | (0,1) | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 2 | (0,6) | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 2 | (0,1) | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 1 | (0,2) | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
- 删除重复记录
postgres=# delete from t_emp where ctid in ( select max(ctid) from t_emp group by empno having count(1) > 1 );
随机数
生成随机数字
- 生成 0 到 1 之间的随机数
postgres=# select random(); random --------------------- 0.00576547440141439
该函数返回的数据类型为 double precision，每次调用都会返回不同的结果。
- 生成指定范围内的随机数
# 生成一个大于等于 low，小于 high 的随机数 low + random() * (high - low) # 生成一个大于等于 10 且小于 30 的随机数 postgres=# select 10 + random() * (30 - 10) random; random ------------------ 29.9697697721422
- 生成某个范围内的随机整数
# floor(low + random() * (high - low)) # 生成 100 以内的随机整数 postgres=# select floor(0 + random() * (100 - 0)) random; random -------- 19
- 生成 6 位数字手机验证码
postgres=# select to_char(random() * 1000000, '099999') as captcha; captcha --------- 051033
生成随机字符串
Greenplum 没有提供专门生成随机字符串的函数，但是可以通过其他函数进行模拟。
- 使用
chr函数，生成固定长度的随机字符串
postgres=# select chr(floor(random() * 26)::integer + 65); chr ----- P
- 创建函数
create or replace function random_string ( num integer, chars text default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' ) returns text language plpgsql strict as $function$ declare res_str text := ''; begin if num < 1 then raise exception 'invalid length'; end if; for __ in 1..num loop res_str := res_str || substr(chars, floor(random() * length(chars))::int + 1, 1); end loop; return res_str; end; $function$;
- 生成一个由字母和数字组成，长度为 10 的随机字符串
postgres=# select random_string(10); random_string --------------- irXuzT961z
- 指定随机长度参数，生成一个长度大于等于 10 且小于等于 20 的随机字符串
postgres=# select random_string(floor(10 + random() * 11)::int); random_string --------------- OarmButFct6w
生成 UUID
UUID（Universal Unique Identifier）或者 GUID（Globally Unique Identifier）是一个 128 比特（Bit） 的数字，可以用于唯一标识每个网络对象或资源。由于它的生成机制，一个 UUID 可以保证几乎不会与其他 UUID 重复，因此常常用于生成数据库中的主键值。
- 启用加/解密的扩展模块 pgcrypto
postgres=# create extension pgcrypto;
- 使用函数返回一个 UUID
postgres=# select gen_random_uuid(); gen_random_uuid -------------------------------------- 12adb859-b97d-45e3-8762-33d4a8e54d5b
- 该函数返回的数据类型为 uuid，如果想要生成没有
-的 UUID 字符串，可以使用 REPLACE 函数
postgres=# select replace(gen_random_uuid()::text, '-', ''); replace ---------------------------------- 955f86bb43d6472b8ac9d7857cb4aedc
- 将 UUID 添加到表
create table fruit ( id uuid default gen_random_uuid() primary key, name varchar(10) ); postgres=# insert into fruit(name) values ('Apple'); postgres=# select * from fruit; id | name --------------------------------------+------- 0bbe0aad-6154-4357-98e1-5308c0eae318 | Apple
自动递增主键
在 Greenplum 中，通过将数据类型设置为 SERIAL 来创建自动递增的键。
create table fruit ( id serial, name varchar(10) ) distributed by (id); postgres=# insert into fruit(name) values ('Apple'); postgres=# insert into fruit(name) values ('Orange'); postgres=# select * from fruit order by id; id | name ----+-------- 1 | Apple 2 | Orange
- 删除 id = 2 的记录
postgres=# delete from fruit where id = 2;
- 重新插入一条记录
postgres=# insert into fruit(name) values ('Orange'); postgres=# select * from fruit order by id; id | name ----+-------- 1 | Apple 3 | Orange
空值处理函数
| 序号 | 函数 | 描述 |
| 1 | nullif(exp1, exp2) | 如果 exp1 等于 exp2，则返回空；否则返回 exp1 |
| 2 | coalesce(exp1, exp2 ... expn) | 返回列表中第1个不为空的值，如果全部元素为空，则返回空 |
- nullif
postgres=# select nullif(3, 2); nullif -------- 3 postgres=# select nullif(3, 3); nullif -------- postgres=# select nullif(1, null); nullif -------- 1 postgres=# select nullif(null, null); nullif --------
- coalesce
postgres=# select coalesce(null, 1, null, 2, 3); coalesce ---------- 1 postgres=# select coalesce(null, 0); coalesce ---------- 0 postgres=# select coalesce(null, 'AAA'); coalesce ---------- AAA
单引号字符串转义
执行 insert 语句时，若字符串中带有单引号，会报错，就需要转义之后才能插入。
insert into test values (1,'''test-name''');
连乘
exp ( sum ( ln ( decode(t.yield_d + 1, 0, 1, t.yield_d + 1) ) ) ) - 1
原创文章，转载请注明出处：http://www.opcoder.cn/article/61/