欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 科技 > 名人名企 > PostgreSQL常用函数

PostgreSQL常用函数

2025/4/23 13:27:37 来源:https://blog.csdn.net/shy_1762538422/article/details/144448892  浏览:    关键词:PostgreSQL常用函数

PostgreSQL常用函数

PostgreSQL作为一款企业级开源数据库,其丰富的函数库为开发者提供了强大的数据处理工具。本文将全面深入地探讨PostgreSQL中的常用函数,帮助读者系统地掌握数据库函数的应用。

1. 字符串处理函数

1.1 基础字符串操作

  • length(string):计算字符串长度
  • trim():去除字符串两端空格
  • left(string, n):从左侧截取指定长度字符串
  • right(string, n):从右侧截取指定长度字符串
SELECT length('PostgreSQL');  -- 输出 10
SELECT trim('  PostgreSQL  ');  -- 输出 'PostgreSQL'
SELECT left('PostgreSQL', 4);  -- 输出 'Post'
SELECT right('PostgreSQL', 3);  -- 输出 'SQL'

1.2 高级字符串处理

  • regexp_replace():使用正则表达式替换
  • split_part():字符串分割
  • repeat():重复字符串
  • reverse():反转字符串
-- 使用正则表达式替换电话号码中的特殊字符
SELECT regexp_replace('123-456-7890', '[^0-9]', '', 'g');  
-- 输出 '1234567890'SELECT split_part('2024-01-15', '-', 2);  -- 输出 '01'
SELECT repeat('PG ', 3);  -- 输出 'PG PG PG '
SELECT reverse('PostgreSQL');  -- 输出 'LQSergsoP'

2. 数值处理函数

2.1 精确数学运算

  • power(x, y):计算x的y次方
  • sqrt():平方根
  • mod():取模运算
  • width_bucket():数值分桶
SELECT power(2, 3);  -- 输出 8
SELECT sqrt(16);  -- 输出 4
SELECT mod(10, 3);  -- 输出 1-- 将0-100的数据分成5个桶
SELECT width_bucket(score, 0, 100, 5) AS grade_bucket 
FROM exam_scores;

2.2 随机数和统计函数

  • random():生成0-1之间随机数
  • setseed():设置随机数种子
  • percentile_cont():连续百分位数
-- 设置随机数种子并生成随机数
SELECT setseed(0.5);
SELECT random();-- 计算连续百分位数
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary 
FROM employees;

3. 日期和时间函数

3.1 日期计算与转换

  • extract():提取日期/时间的特定部分
  • date_part():获取日期/时间组件
  • to_char():日期格式化
  • interval:时间间隔计算
-- 提取年份和月份
SELECT extract(year from current_date) AS current_year,extract(month from current_date) AS current_month;-- 格式化日期
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS');-- 计算两个日期间隔
SELECT current_date + interval '1 year 2 months';

3.2 时区处理

  • at time zone:转换时区
  • current_setting():获取当前时区设置
-- 转换时区
SELECT current_timestamp AT TIME ZONE 'UTC';-- 获取当前时区
SELECT current_setting('timezone');

4. 高级聚合函数

4.1 窗口函数

  • row_number():行号
  • rank():排名
  • dense_rank():密集排名
  • lag()lead():访问前后行数据
-- 按部门薪资排名
SELECT name, department, salary,rank() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;-- 获取前后行数据
SELECT name, sale_date, amount,lag(amount) OVER (ORDER BY sale_date) as previous_sale
FROM sales;

5. JSON和数组处理函数

5.1 JSON操作

  • jsonb_extract_path():提取JSON路径
  • jsonb_set():修改JSON值
  • json_array_length():数组长度
-- JSON数据处理
SELECT jsonb_extract_path(user_info, 'address', 'city') AS city 
FROM users;-- 修改JSON值
SELECT jsonb_set('{"name": "John"}'::jsonb, '{age}', '30'::jsonb);

5.2 数组函数

  • array_agg():聚合数组
  • unnest():数组展开
  • array_cat():数组合并
-- 将分组结果聚合为数组
SELECT department, array_agg(name) AS employee_names 
FROM employees 
GROUP BY department;-- 数组合并
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);  -- 输出 [1,2,3,4]

6. 安全与性能相关函数

6.1 加密与哈希

  • digest():生成哈希值
  • encode():编码转换
-- MD5哈希
SELECT digest('password', 'md5');-- Base64编码
SELECT encode('PostgreSQL', 'base64');

6.2 性能分析

  • pg_sleep():模拟延迟
  • generate_series():生成序列
-- 生成测试数据
SELECT * FROM generate_series(1, 10);-- 性能测试模拟
EXPLAIN ANALYZE 
SELECT pg_sleep(1);

结语

PostgreSQL的函数生态系统极其丰富,本文仅展示了冰山一角。深入理解和灵活运用这些函数,不仅能提高开发效率,还能编写出更加简洁和高性能的数据库代码。

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

热搜词