pgSQL常用SQL函数

2022-03-16

字数统计: 4.5k字 | 阅读时长≈ 20分

常用日期函数记录.

常用命令

-- 创建内部表
begin;
create table [if not exists] [schema_name.]table_name(
	id bigint primary key,
    b text,
    ds text,
)
partition by list(ds);
commit;

-- 创建子表
create table if not exists table_name_date_202101 partition of table_name for values in ('202101');

-- 创建外部表
CREATE FOREIGN TABLE odps_students (
	id bigint,
    b text,
    ds text,
)
SERVER odps_server
OPTIONS (project_name 'public_data', table_name 'oracle2odps_cust');

-- 删除表	
DROP TABLE [ IF EXISTS ] table_name [, ...];

-- 删除外部表
DROP FOREIGN TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

-- 修改表名
ALTER TABLE table_name RENAME to new_table_name;

-- 修改外部表名
ALTER FOREIGN TABLE [ IF EXISTS ] name RENAME TO new_name;

-- 增加列
ALTER TABLE IF EXISTS table_name ADD COLUMN new_column_name data_type; 
ALTER TABLE IF EXISTS table_name ADD COLUMN col_add_1 data_type, ADD COLUMN col_add_2 TEXT IF NOT EXISTS col_add_2 data_type;

-- 给表增加注释
COMMENT ON TABLE table_name IS 'my comments on table table_name.';

-- 给列增加注释
COMMENT ON COLUMN table_name.col1 IS 'This my first col1';

-- 给外部表增加注释
COMMENT ON FOREIGN TABLE foreign_table IS ' comments on my foreign table';

-- 查询执行计划
explain querysql;

-- 查询表所在的组名
select property_value from hologres.hg_table_properties where table_name = '表名' and property_key = 'table_group';

-- 查询对应表组的shard count
select * from hologres.hg_table_group_properties where tablegroup_name = '组名' and property_key = 'shard_count';

-- 获取当前用户Account ID
SELECT current_user;

-- 获取当前用户的阿里云显示名称
SELECT user_display_name(current_user);

-- 获取所有用户的阿里云显示名称
SELECT user_display_name(rolname) FROM pg_roles;

-- UNION 
返回位于一个或者两个结果集中的全部行

-- INTERSECT
返回同时位于两个结果集中的所有行

-- EXCEPT
返回位于第一个结果集但不在第二个结果集中的行

注：在所有三种情况下，重复行都会被消除（除非指定ALL）。

索引

存储类型设置

1
2
3

call set_table_property('table1','orientation','[row|colume]');

行存适用于高QPS的基于primary key的点查询，例如where pk=abc,其余场景都应该选用列存方式。

聚簇索引 Clustering key

call set_table_property('table1','clustering_key','a');
or 
call set_table_property('tbl', 'clustering_key', 'a:desc,b:asc');

对建立索引数据进行排序，建立聚簇索引能够加速用户在索引列上的range和filter查询
例：select sum(a) from table1 where a > 100 and a < 200;
注：desc和asc表名构建索引时的排序方式，默认为asc。clustering key创建的时候数据类型不能为float/double，每个表最多只有一个clustering key

分段键Segment Key

call set_table_property('table1','segment key','ds');

分段键帮助Hologres进行一些文件的快速筛选和跳过。指定分段键，当查询条件包含分段列时，查询可以通过segment key快速找到相应数据对应的存储位置。
例：select sum(a) from table1 where ts > '2020-01-01' and ts < '2020-03-02'
注：segment key要求按照数据输入自增，一般只有时间类型的字段（timestamptz）适合设置为segment key，其他场景基本不需要设置。只有列存表支持分段键设置。

比特编码列bitmap columns

call set_table_property('table1','bitmap key','ds');

指定比特编码列使Hologres会在这些列上构建比特编码，相当于把数据与对应的行号做一个映射
例：select * from table1 where a = 100;
注：bitmap可以对segment内部的数据进行快速过滤，因此建议把filter条件的数据建成比特编码。目前Hologres会默认所有text列都会被隐藏式地设置到bitmap_columns中。但是只有列存表支持比特编码列

字典编码列设置

call set_table_property('table1','dictionary_encoding_columns','b');

字典编码可以将字符串的比较转换成数字的比较，加速group by查询
例：select sum(a) from table1 group by b;
注：不建议将基数高的列建为字典编码列，会导致查询性能变差。目前Hologres会默认所有text列都会被隐藏式地设置到bitmap_columns中。但是只有列存表支持比特编码列

分部键distribution key

call set_table_property('table1','distribution key','b');

指定分布列，数据将按照指定列，将数据shuffle到各个shard，同样的数值肯定会在同样的shrad中。
例：select count(1) from tmp1 join tmp2 on tmp1.a = tmp2.b
注：对于有pk的表，其分布键默认就是pk，如果不想pk字段作为分布键，可以指定pk字段的子集，但是不能随意指定。
	可以通过shard_count来指定表的shard数，如果不指定的话每个数据库都有一个默认的shard数，一旦指定了一个表的shard数，其他的表如果想要和这个表做local join，就必须指定colcate with这个表。

数据生命周期管理time_to_live_in_seconds

1
2
3

call set_table_property('table1','time_to_live_in_seconds','96400');

管理数据的声明周期，默认时间是秒，必须是非负数字类型。表数据的TTL并不是精确的时间，当超过设置的TTL后，系统会在某一个时间自动删除数据，因此业务逻辑不能强依赖TTL，以免带来不必要的损失时

函数

获取今天日期获取今天时刻 (Hologres)

select 
    now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    current_date as sta_data, --当前日期(精确到日)
    date_part('hour', clock_timestamp()) sta_hour --当前时刻
    
   
   
select 
    TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as today_date, --今天日期(精确到天)
    TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as yesterday_date, --昨天日期(精确到天)
    TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as seven_days_ago_date, --7天前日期(精确到天)
    current_date as today_date, --今天日期(精确到日)
    date_part('hour', clock_timestamp()) today_hour --当前时刻(昨天的当前时刻)
    
    
    
-- 所有字段都是TEXT类型    
 select 
    CAST(TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT) as today_date, --今天日期(精确到天)
    CAST(TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT)  as yesterday_date, --昨天日期(精确到天)
    CAST(TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT)  as seven_days_ago_date, --7天前日期(精确到天)
    CAST(current_date as TEXT)  as today_date, --今天日期(精确到天)
    CAST(date_part('hour', clock_timestamp()) as TEXT)  today_hour --当前时刻(昨天的当前时刻)
    
  
-- 干掉时间的间隔符号: '2020-01-01' 转变为 '20200101'  
-- 关联其他表报错:  
-- [20:14:38] [执行失败!  0 row  耗时:16.65ms]
-- [20:14:38] 错误原因:ERROR: Feature not supported: Single Row Volatile functions
-- 错误地方就是: CAST(date_part('hour', clock_timestamp()) as TEXT)  today_hour
select 
    now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    REPLACE(CAST(TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as today_date, --今天日期(精确到天)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','')  as yesterday_date, --昨天日期(精确到天)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','')  as seven_days_ago_date, --7天前日期(精确到天)
    CAST(current_date as TEXT)  as today_date, --今天日期(精确到天)
    CAST(date_part('hour', clock_timestamp()) as TEXT)  today_hour --当前时刻(昨天的当前时刻)
    
    
    
 -- TODO 获取时间维度表: 今天此时刻 昨天此时刻 7天前此时刻 当前小时 ,时间类型: TEXT
    select
    now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    REPLACE(CAST(TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as today_date, --今天日期(精确到天)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','')  as yesterday_date, --昨天日期(精确到天)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','')  as seven_days_ago_date, --7天前日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) as today_hour --当前时刻(昨天的当前时刻)
    
    
    
    
    
    
    
--TODO 获取时间维度表(主表): 今天此时刻 昨天此时刻 7天前此时刻 当前小时 ,时间类型: TEXT
    select
    -- now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    REPLACE(CAST(TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as today_yesterday_sevenDaysAgo_date, --今天日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) as today_hour --当前时刻(当前时刻)
    UNION ALL
    select
    -- now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-',''), --昨天日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) --当前时刻(当前时刻)
    UNION ALL
    select
    -- now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-',''), --7天前日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) --当前时刻(当前时刻)



-- TODO 获取时间维度表(主表): 今天此时刻 昨天此时刻 7天前此时刻 当前小时 ,时间类型: TEXT
    select
    now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    REPLACE(CAST(TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as today_date, --今天日期(精确到天)
    'NULL' as yesterday_date, --昨天日期(精确到天)
    'NULL' as seven_days_ago_date, --7天前日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) as today_hour --当前时刻(当前时刻)

    UNION ALL

    select
    now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    'NULL' as today_date, --今天日期(精确到天)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','')  as yesterday_date, --昨天日期(精确到天)
    'NULL' as seven_days_ago_date, --7天前日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) as today_hour --当前时刻(当前时刻)

    UNION ALL

    select
    now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
    'NULL' as today_date, --今天日期(精确到天)
    'NULL' as yesterday_date, --昨天日期(精确到天)
    REPLACE(CAST(TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','')  as seven_days_ago_date, --7天前日期(精确到天)
    SUBSTR(transaction_timestamp()::TEXT ,12,2) as today_hour --当前时刻(当前时刻)
    
    
    
    

--TODO 获取时间维度表(主表): 今天此时刻 昨天此时刻 7天前此时刻 当前小时 ,时间类型: TEXT
        select
        t.now_date, --当前日期(精确到秒)
        t.today_date, --今天日期(精确到天)
        t.yesterday_date, --昨天日期(精确到天)
        t.seven_days_ago_date, --7天前日期(精确到天)
        t.today_hour, --截止到此时今天的各个小时
        t.twenty_four_hh --24小时
        from
        (
            select
             t1.now_date, --当前日期(精确到秒)
             t1.today_date, --今天日期(精确到天)
             t1.yesterday_date, --昨天日期(精确到天)
             t1.seven_days_ago_date, --7天前日期(精确到天)
             t1.today_hour, --截止到此时今天的各个小时
             t1.twenty_four_hh --24小时
            from
            (
                select
                p.now_date, --当前日期(精确到秒)
                p.today_date, --今天日期(精确到天)
                p.yesterday_date, --昨天日期(精确到天)
                p.seven_days_ago_date, --7天前日期(精确到天)
                CASE WHEN p.today_hour>=p.hh_id THEN p.hh_id
                     ELSE '时候未到'
                END AS  today_hour,
                p.hh_id twenty_four_hh
                from
                    (
                        --当前日期到目前小时
                        SELECT
                            now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
                            REPLACE(CAST(TO_CHAR((now()::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as today_date, --今天日期(精确到天)
                            '昨天日期' as yesterday_date, --昨天日期(精确到天)
                            '7天前日期' as seven_days_ago_date, --7天前日期(精确到天)
                            SUBSTR(transaction_timestamp()::TEXT ,12,2) today_hour, --截止到当前小时
                            hh_id   -- 一天的24小时
                        FROM
                            dim_hh
                        order by
                             hh_id
                    )p
                order by
                p.hh_id
            )t1

            UNION ALL

            select
             t2.now_date, --当前日期(精确到秒)
             t2.today_date, --今天日期(精确到天)
             t2.yesterday_date, --昨天日期(精确到天)
             t2.seven_days_ago_date, --7天前日期(精确到天)
             t2.today_hour, --截止到此时今天的各个小时
             t2.twenty_four_hh --24小时
            from
            (
                select
                p.now_date, --当前日期(精确到秒)
                p.today_date, --今天日期(精确到天)
                p.yesterday_date, --昨天日期(精确到天)
                p.seven_days_ago_date, --7天前日期(精确到天)
                CASE WHEN p.today_hour>=p.hh_id THEN p.hh_id
                     ELSE '时候未到'
                END AS  today_hour,
                p.hh_id twenty_four_hh
                from
                    (
                        --当前日期到目前小时
                        SELECT
                            now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
                            '今天日期' as today_date, --今天日期(精确到天)
                            REPLACE(CAST(TO_CHAR(((current_timestamp -interval '1D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as yesterday_date, --昨天日期(精确到天)
                            '7天前日期' as seven_days_ago_date, --7天前日期(精确到天)
                            SUBSTR(transaction_timestamp()::TEXT ,12,2) today_hour, --截止到当前小时
                            hh_id   -- 一天的24小时
                        FROM
                            dim_hh
                        order by
                             hh_id
                    )p
                order by
                p.hh_id
            )t2

            UNION ALL

            select
             t3.now_date, --当前日期(精确到秒)
             t3.today_date, --今天日期(精确到天)
             t3.yesterday_date, --昨天日期(精确到天)
             t3.seven_days_ago_date, --7天前日期(精确到天)
             t3.today_hour, --截止到此时今天的各个小时
             t3.twenty_four_hh --24小时
            from
            (
                select
                p.now_date, --当前日期(精确到秒)
                p.today_date, --今天日期(精确到天)
                p.yesterday_date, --昨天日期(精确到天)
                p.seven_days_ago_date, --7天前日期(精确到天)
                CASE WHEN p.today_hour>=p.hh_id THEN p.hh_id
                     ELSE '时候未到'
                END AS  today_hour, --截止到此时今天的各个小时
                p.hh_id twenty_four_hh --24小时
                from
                    (
                        --当前日期到目前小时
                        SELECT
                            now()::timestamp(0)without time zone as now_date, --当前日期(精确到秒)
                            '今天日期' as today_date, --今天日期(精确到天)
                            '昨天日期' as yesterday_date, --昨天日期(精确到天)
                            REPLACE(CAST(TO_CHAR(((current_timestamp -interval '7D')::timestamp(0)without time zone),'yyyy-MM-dd') as TEXT),'-','') as seven_days_ago_date, --7天前日期(精确到天)
                            SUBSTR(transaction_timestamp()::TEXT ,12,2) today_hour, --截止到当前小时
                            hh_id   -- 一天的24小时
                        FROM
                            dim_hh
                        order by
                             hh_id
                    )p
                order by
                p.hh_id
            )t3
        )t
        order by
            t.today_date, --今天日期(精确到天)
            t.yesterday_date, --昨天日期(精确到天)
            t.seven_days_ago_date, --7天前日期(精确到天)
            t.today_hour, --截止到此时今天的各个小时
            t.twenty_four_hh --24小时

获取日期(dataworks)

--TODO 获取时间维度表(主表): 业务日期(yyyy-mm）月份(mm) ,时间类型: TEXT
   SELECT
   to_char(GETDATE(),'yyyymmdd')  AS today_date,  --当前日期(某年某月某日)
   to_char(GETDATE(),'yyyymm')  AS year_month_date, --当前日期(某年某月)
   to_char(GETDATE(),'mm') as month_date --当前月份(某月)

字段强转为某个类型

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

1	CAST (expression AS data_type)

expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

    二进制，同带binary前缀的效果 : BINARY    
    字符型，可带参数 : CHAR()     
    日期 : DATE     
    时间: TIME     
    日期时间型 : DATETIME     
    浮点数 : DECIMAL      
    整数 : SIGNED     
    无符号整数 : UNSIGNED

当前时间减去一天

1	SUBSTR(TO_CHAR(CURRENT_DATE - '1 day'::INTERVAL, 'yyyymmdd'::TEXT), 1, 8)

去年今天的时间日期

set odps.sql.type.system.odps2=true;
--TODO 获取时间维度表(主表): 业务日期(yyyy-mm）月份(mm) ,时间类型: TEXT
SELECT  to_char(GETDATE(),'yyyy-mm') AS year_month_date    --现在:当前日期(2022-02)
        ,SUBSTR(
            DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP())))
            ,1
            ,7
        ) year_last_month_date    --环比: 上一个月(2022-01)
        , DATE(DATEADD(current_timestamp(),-1,'yyyy')) last_year_current_month --去年的当前月份(2021-02)
;

                         
                         
                         
SELECT  CAST(to_char(GETDATE(),'yyyy-mm') AS STRING ) AS year_month_date    --当前日期(2022-02)
UNION ALL
SELECT  CAST(
            SUBSTR(
                DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP()),DAY(FROM_UNIXTIME(UNIX_TIMESTAMP())))
                ,1
                ,7
            ) AS STRING
        ) year_month_date    --上一个月(2022-01)
UNION ALL
SELECT  CAST(
            SUBSTR(DATE(DATEADD(current_timestamp(),-1,'yyyy')),1,7) AS STRING
        ) year_month_date    --去年的当前月份(2021-02)

七个JOIN


1、左连接
select * from a left join b on a.id = b.id
2、右连接
select * from a right join b on a.id = b.id
3、左连接之后去除共有部分
select * from a left join b on a.id = b.id where b.id is null
4、内连接
select * from a inner join b on a.id = b.id
5、右连接之后去除共有部分
select * from a right join b on a.id = b.id where a.id is null
6、全连接
select * from a left join b on a.id = b.id union select * from a right join b on a.id = b.id
由于mysql不支持全连接写法，所以使用上面的sql语句替代下面的全连接sql语句：
select * from a FULL OUTER JOIN b on a.id = b.id(mysql不支持)
7、全连接之后去除共有部分
select * from a left join b on a.id = b.id where b.id is null union select * from a right join b on a.id = b.id where a.id is null
由于mysql不支持全连接写法，所以使用上面的sql语句替代下面的全连接sql语句：
select * from a FULL OUTER JOIN b on a.id = b.id where a.id is null or b.id is null(mysql不支持)

正则表达式

命令格式：
    regexp_replace(source, pattern, replace_string, occurrence)
返回值： 
    将source字符串中匹配pattern的子串替换成指定字符串后返回，当输入source, pattern, occurrence参数为NULL时返回NULL，若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但pattern不匹配，则返回原串。
参数说明： 
●  source: string类型，要替换的字符串。
●  pattern: string类型常量，要匹配的模式，pattern为空串时抛异常。
●  replace_string:string，将匹配的pattern替换成的字符串。
●  occurrence: bigint类型常量，必须大于等于0，表示将第几次匹配替换成replace_string，为0时表示替换掉所有的匹配子串。其它类型或小于0抛异常。
●  当引用不存在的组时，不进行替换。

例如：
regexp_replace("123.456.7890","([[:digit:]]{3})\\.([[:digit:]]{3})\\.([[:digit:]]{4})","(\\1)\\2-\\3",0) 结果为(123)456-7890

regexp_replace("abcd","(.)","\\1 ",0) 结果为"a b c d "

regexp_replace("abcd","(.)","\\1 ",1) 结果为"a bcd"

regexp_replace("abcd","(.)","\\2",1) 结果为"abcd"，因为pattern中只定义了一个组，引用的第二个组不存在。

regexp_replace("abcd","(.*)(.)$","\\2",0) 结果为"d"

regexp_replace("abcd","a","\\1",0)，结果为” \1bcd”，因为在pattern中没有组的定义，所以\1直接输出为字符。

本文作者： xubatian
本文链接： http://xubatian.cn/pgSQL-Hologres-dataworks常用SQL函数/
版权声明： 本博客所有文章除特别声明外均为原创，采用 CC BY 4.0 CN协议许可协议。转载请注明出处:https://www.xubatian.cn/