KADB学习之常用函数

KADB 是北京人大金仓信息技术股份有限公司基于开源的greenplum数据库研发的一款分布式关系型数据库，全称是KingbaseAnalyticsDataBase。

学习kadb的常用函数

连接测试数据库；

psql postgres

手动执行常用系统操作函数测试语句集；

一、创建测试表和数据

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing, Engineering', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

1、常用时间函数

1.1、两个日期的减计算

select age('2020-01-31', '2018-01-10');

1.2、日期加和减指定年、月、天数

select date_part('year','2020-01-31'::timestamp - interval '2 years');

select date_part('month','2020-01-31'::timestamp - interval '2 years');

select date_part('day','2020-01-31'::timestamp - interval '2 years');

select date_part('year','2020-01-31'::timestamp + interval '2 years');

select date_part('month','2020-01-31'::timestamp + interval '2 years');

select date_part('day','2020-01-31'::timestamp + interval '2 years');

1.3、两个时间的加和减计算

select '17:35:10'::time-'14:16:09'::time;

select '17:35:10'::time with time zone + '5:16:09'::time;

1.4、时间加和减指定的小时、分钟、秒数

select date_part('hour','17:35:10' + interval '2 hour');

select date_part('minute','17:35:10' + interval '2 hour');

select date_part('second','17:35:10' + interval '2 hour');

select date_part('hour','17:35:10' - interval '2 hour 1 minute 2 second');

select date_part('minute','17:35:10' - interval '2 hour 1 minute 2 second');

select date_part('second','17:35:10' - interval '2 hour 1 minute 2 second');

1.5、两个时间戳减计算

select '2020-08-14 14:28:39'::timestamp - '2018-09-23 11:45:12'::timestamp;

1.6、时间戳加和减指定的年、月、天、时、分、秒数

select date_part('year','2020-01-31 14:28:39'::timestamp - interval '2 years');

select date_part('month','2020-01-31 14:28:39'::timestamp - interval '2 years');

select date_part('day','2020-01-31 14:28:39'::timestamp - interval '2 years');

select date_part('hour','2020-01-31 14:28:39'::timestamp - interval '2 years');

select date_part('minute','2020-01-31 14:28:39'::timestamp - interval '2 years');

select date_part('second','2020-01-31 14:28:39'::timestamp - interval '2 years');

select date_part('year','2020-01-31 14:28:39'::timestamp + interval '2 years');

select date_part('month','2020-01-31 14:28:39'::timestamp + interval '2 years');

select date_part('day','2020-01-31 14:28:39'::timestamp + interval '2 years');

select date_part('hour','2020-01-31 14:28:39'::timestamp + interval '2 years');

select date_part('minute','2020-01-31 14:28:39'::timestamp + interval '2 years');

select date_part('second','2020-01-31 14:28:39'::timestamp + interval '2 years');

二、字符串函数

select concat('abcde', 2, 1, 22);

select substring('hello world' from 1 for 5);

select position('lo' in 'hello');

select regexp_split_to_table(dept,',') from department where emp_id=1;

三、分组和聚合函数

select * from department group by id;

select * from department order by id desc;

select * from department group by id having count(id) >= 1;

select min(emp_id) from department ;

select max(emp_id) from department ;

select sum(emp_id) from department ;

select count(emp_id) from department ;

select avg(emp_id) from department ;

select distinct(emp_id) from department ;

四、数值函数

SELECT ABS(-4);

SELECT RANDOM();