欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

相关标签: 数据库 大数据