greenplum中基于sql开发常用的函数等基础知识

greenplum使用手册

注：有一部分借用
另：粗浅的pdf参考：https://download.csdn.net/download/zuozuowuxin/12914741

一、常见语句

1.创建表

create table ods.ods_order_ckd_hi
(
    id           integer,
    fid          integer,
    jdckd        character(255),
    jdfhd        character(255),
    jdxsd        character(255),
    cktime       timestamp,  
    gp_create_time timestamp default now(), -- 直接添加对应的默认值  
    gp_id serial -- 自增主键
)distributed by(id,jdckd,jdfhd,jdxsd,cktime);
-- distributed by 分布键 
-- 使用联合主键构建的分布键可以保证 数据均匀的分布在集群中，避免影响查询速度
-- 修改分布键
alter table ods.ods_order_ckd_hi set distributed randomly;-- 将默认的hash分布改为随机分布

2.添加备注

-- 添加表名备注
comment on table ods.ods_order_ckd_hiis '订单关联表'; 
-- 添加字段名备注
comment on column ods.ods_order_ckd_hi.id is '主键';
comment on column ods.ods_order_ckd_hi.fid is ''; -- 可以置空

3.新增字段,修改默认值

ALTER TABLE dwd.dwd_order_da ALTER COLUMN gp_create_time SET NOT NULL; -- 设置该字段不为空
ALTER TABLE dwd.dwd_order_da ALTER COLUMN gp_create_time SET DEFAULT now();-- 设置该字段默认值为当前时间

二、时间函数

1.基本时间转换函数

序号	函数	返回值	说明	示例
1	to_char(timestamp, text)	text	将timestamp类型的值转换为指定输出格式的字符串	select to_char(timestamp ‘2020-05-07 10:22:23’, ‘yyyy-MM-dd HH12:MI:SS’)
2	to_char(interval, text)	text	将interval类型的值转换为指定输出格式的字符串	select to_char(interval ‘1 day 2 hour’, ‘dd hh12:mi:ss’);
3	to_date(text, text)	date	将指定时间格式的字符串转换成date类型的值	to_date(‘2020-05-10 10:20:12’,‘yyyy-mm-dd’);
4	to_timestamp(text, text)	timestamp	将指定时间格式的字符串转换成timestamp类型的值	to_timestamp(‘2020-05-10 10:20:12’,‘yyyy-mm-dd hh:mi:ss’);
5	to_timestamp(double)	timestamp	将数值型的时间戳值转换成timestamp类型的值	to_timestamp(1588841590);
6	age(timestamp,timestamp)	interval	计算两个timestamp的差值	age(timestamp ‘2020-05-05 10:22:21’, timestamp ‘2020-03-04 19:26:21’);
7	age(timestamp)	interval	计算current_date减去timestamp的差值	age(timestamp ‘2020-03-04 19:26:21’)

2.sql中定义的表示时间含义的字符

序号	标准时间中拆分	解释
1	hh	hour of day (01-12)
2	hh12	hour of day (01-12)
3	hh24	hour of day (00-23)
4	mi	minute (00-59)
5	ss	second (00-59)
6	ms	millisecond (000-999)
7	yyyy	year (4 and more digits)
8	mm	month number (01-12)
9	dd	day of month (01-31)
10	d	day of week (1-7; Sunday is 1)

3.时间数据转换case

01.业务含义的case

-- 2020-10-06日的数据格式，源库与ods层数据保持一致。2020-10-07日已变更，ods层数据日期时间已更改为timestamp
select 
   pay_time
  ,to_timestamp(pay_time) as timestamp_pay_time -- int类型的1548641820转换成时间戳类型 
  ,to_char(to_timestamp(pay_time),'yyyy-mm-dd hh24:mi:ss') as char_pay_time -- 转换成varchar类型,注意事项:时分秒数据是 hh:mi:ss
  ,to_char(to_timestamp(pay_time),'yyyy-mm-dd') as char_pay_time_1 -- 转换成varchar类型的日期数据
  ,to_date(to_char(to_timestamp(pay_time),'yyyy-MM-dd hh:mi:ss'),'yyyy-MM-dd') as date_pay_time
  ,substring(to_char(to_timestamp(pay_time),'yyyy-MM-dd hh:mi:ss') from 1 for 10) as char_pay_time
from ods.ods_order_hi
where order_sn = '201901281016476967'

对应结果：1548641820	2019-01-28 10:17:00	2019-01-28 10:17:00	2019-01-28	2019-01-28	2019-01-28

02.gp获取当前时间

select now(); -- 获取当前时间到年月日timestamp类型：2020-10-07 11:34:11
select to_char(now(),'yyyymmdd');-- 20201007 varchar类型
select to_char(now(),'yyyy-mm-dd hh12:mi:ss');-- 2020-10-07 11:37:39 varchar类型
select to_char(now(),'yyyy-mm-dd hh24:mi:ss');-- 2020-10-07 11:37:39 varchar类型
select current_timestamp; -- 2020-10-07 11:37:39 timestamp类型
select localtimestamp;    -- 2020-10-07 11:37:39 timestamp类型，获取本地时间
select localtime;    -- 11:37:39 timestamp类型，获取本地时间
select current_time; -- 11:37:39 timestamp类型，当前时间
select current_date; -- 2020-10-07 date类型，当前日期

03.gp日期函数加减

select now()+interval '2 day'; -- timestamp类型,2020-10-09 11:44:45
select now()-interval '2 day'; -- timestamp类型,2020-10-05 11:44:45

select now()+interval '2 mon'; -- timestamp类型,2020-12-07 11:44:45
select now()-interval '2 mon'; -- timestamp类型,2020-08-07 11:44:45

04.gp时间截取年月日

select extract(year from now()) -- 为int类型 or to_char(now(),'yyyy')   -- 获取年
select extract(mon from now())  -- 为int类型 or to_char(now(),'mm')     --  获取月
select extract(day from now())  -- 为int类型 or to_char(now(),'dd')     -- 获取日
select to_char(now(),'ww');     -- varchar类型 26 获取当前日期是一年中的第几周

---

三、常用的字符串函数

1.基本字符串函数

序号	作用	函数	返回类型	case	结果
1	字符串拼接	string||string	text	select ‘item’||‘no’ as timeno;	timeno
2	获取字符串中长度	length(string)	int	select length(‘item_no’) as lg;	7
3	指定子字符串中的位置	position(substring in string)	int	select position(‘no’ in ‘item_no’) as po;	6
4	抽取子字符串	substring(string from int for int)	text	select substring(‘000101’ from 1 for 4) as shop_id;	0001
5	从字符串的开头/解为/两边删除只包含characters中字符的最长的字符串	trim(leading/trailing/both characters from string)	text	select trim(both ‘0’ from ‘00010100’) trim_info;	101
6	转换为小写	lower(string)	text	select lower(‘ASDF’) as low;	asdf
7	转换为大写	upper(string)	text	select upper(‘asdf’) as up;	ASDF
8	替换子字符串	overlay(string placing string from int for int)	text	select overlay(‘item_no’ placing ‘’ from 5 for 1) as ove;	itemno
9	把字符串string中出现的所有子字符串替换	replace(string text,from text,to text)	text	select replace(‘2020-01-01’,’-’,’’) as rep;	20200101
10	分割	split_part(string text,delimiter text,field int)	text	select split_part(‘2020-03-01’,’-’,2) as sp;	03

2.sql示例

select 'zuo'||'you' as zuoyou; -- zuoyou
select length('item_no') as lg; -- 7
select position('no' in 'item_no') as po; -- 6
select substring('000101' from 1 for 4) as shop_id; -- 0001
select trim(both '0' from '00010100') trim_info; -- 101
select trim(leading '0' from '00010100') trim_info2; -- 10100
select trim(trailing '0' from '00010100') trim_info3;  -- 000101
select lower('ASDF') as low; -- asdf
select upper('asdf') as up; -- ASDF
select overlay('item_no' placing '' from 5 for 1) as ove; -- itemno
select replace('2020-01-01','-','') as rep; -- 20200101
select split_part('2020-03-01','-',2) as sp; -- 03

四、业务实践注意事项
1.mysql数据库落入gp

数据类型：tinyint(1)的数据进入gp中会自动转换成bool类型的true和false存储.
原因:MYSQL保存boolean值时用1代表true,0代表false。boolean在mysql里的类型为tinyint(1)。
mysql里有四个常量：true,false,TRUE,FALSE分别代表1,0,1,0。

case:mysql库中0,1 --> gp库中对应false,true
-- 解决方案1
select 
  status*1 as status -- 落地为int类型
from ods.ods_order_hi
-- 解决方案2
更改源库(mysql)中该字段类型的长度 ps：tinyint存储长度=2^(1字节,存储范围 -127~128)
tinyint(1) --> tinyint(2)

本文地址：https://blog.csdn.net/zuozuowuxin/article/details/108974359