postgreSQL(PostGIS)常用函数
一、空间函数
- st_geometrytype:获取图形类型(点线面、多点线面等)
- st_asgeojson:将空间转换为字符串
- st_geomfromgeojson:将geo字符串转换为空间类型
- st_srid 获取图形空间坐标值
- st_setsrid 设置图形空间坐标
- st_transform 转换图形,可转换为投影坐标
- st_centroid 获取图形中心点
- st_buffer 获取图形缓冲图形
- st_area 获取图形面积:PS:需要在投影坐标系下计算,与st_transform 搭配使用
- st_geometryn 多面(多部件)转换单面(单部件)
- ST_Distance 计算距离(单位:米),注:需在投影坐不下计算
- st_Makeline 创建线(由两个点类型创建)
- ST_NumPoints 获取线类型顶点数量
- ST_PointN 获取指定线类型顶点的点数据
- st_isvalid 验证图形有效性
- st_makevalid 简单修复图形
- ST_GeometryN 返回输入几何图形的从1开始的第N个元素几何图形
- ST_Envelope —返回表示几何图形边界框的几何图形。
二、文本函数
1 填充(补全)函数
- 有lpad (left+pad)左填充),lpad(‘文本’,总字符串位数,‘填充字符’)。
- rpad(right+pad),右填充,rpad(‘文本’,总字符串位数,‘填充字符’)。
- 例子:lpad(‘12’,5,‘0’) --> 00012,rpad(‘12’,5,‘0’) --> 12000
2 窗口函数 - 与聚合函数区别:聚合函数将多行作用一行,窗口函数作用于每一行
- 专门的窗口函数是:RANK,DENSE_RANK,PERCENT_RANK,CUME_DIST,NTILE,ROW_NUMBER,FIRST_VALUE,LAST_VALUE,NTH_VALUE,LEAD和LAG
- 基本用法:函数名([expr]) over子句
- 例子 select row_number() over (partition by user_id order by create_time desc) as row_number
三、时间函数
3.1 date_trunc(‘时间格式’,时间),将时间转化为想要的格式
millennium -- 1000年
century -- 100年
decade -- 10年
year --1年
quarter -- 一刻钟
month -- 月
week -- 周
day -- 天
hour -- 小时
minute -- 分钟
second -- 秒
milliseconds -- 毫秒
microseconds -- 微秒
注:可以与interval 搭配计算时间,如:
select date_trunc('month',current_date) - interval'0 month'
四、其他函数
4.1 自定义函数
- 4.1.1 无参函数
-- 获取UUID
create function getcommonuuid() returns text
language sql
as
$$
select replace(gen_random_uuid()::text, '-', '') as uuid;
$$;
- 4.1.2 带参数返回值
-- 从数组当中查找某个属性个数
-- [{"NAME":"张三","age":1},{"NAME":"李四","age":1}]
create function getCount(arr text default '[]')
returns int4
as
$$
begin
return (select count(0)
from jsonb_to_recordset(arr::jsonb) as x("NAME" text)
-- arr:json数组中若属性是大写需加双引号,否则直接用属性名:x(age int4)
end;
$$
language plpgsql;