greenplum中基于sql开发常用的函数等基础知识
程序员文章站
2022-03-02 21:28:50
greenplum使用手册一、常见语句1.创建表create table ods.ods_order_ckd_hi( id integer, fid integer, jdckd character(255), jdfhd character(255), jdxsd character(255), cktime timestamp, gp_creat...
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