PostgreSQL实战指南：常用内置函数分类解析与应用场景

1. PostgreSQL内置函数概述

PostgreSQL作为一款功能强大的开源关系型数据库，内置了数百个实用函数，涵盖了数据处理、计算、转换等各个方面。这些函数就像是数据库里的"瑞士军刀"，能帮我们高效解决各种业务问题。

记得我刚接触PostgreSQL时，常常为了处理数据写一大堆复杂的SQL。后来发现很多场景其实用内置函数几行代码就能搞定，效率提升了好几倍。比如要把用户手机号中间四位打码显示，用正则函数一行就能解决，而以前我居然傻乎乎地用程序代码处理。

内置函数主要分为以下几大类：

• 字符串处理：文本清洗、格式转换、正则匹配等
• 数值计算：数学运算、统计分析、随机数生成等
• 日期时间：日期计算、时区转换、时间截取等
• 聚合分析：分组统计、窗口计算、数据透视等
• 类型转换：不同数据类型间的安全转换
• 系统信息：获取数据库元数据、会话信息等

2. 字符串处理函数实战

字符串处理是日常开发中最常用的功能之一。PostgreSQL提供了一套完整的字符串处理函数，比大多数编程语言的字符串库还要强大。

2.1 基础字符串操作

先看几个最常用的基础函数：

-- 字符串连接 SELECT concat('Hello', ' ', 'PostgreSQL'); -- 结果：Hello PostgreSQL -- 带分隔符的连接 SELECT concat_ws('-', '2023', '07', '15'); -- 结果：2023-07-15 -- 大小写转换 SELECT upper('postgresql'), lower('POSTGRESQL'); -- 结果：POSTGRESQL, postgresql -- 字符串长度 SELECT length('数据库'); -- 结果：3（按字符计算） SELECT octet_length('数据库'); -- 结果：9（按字节计算，UTF-8编码）

实际案例：格式化用户姓名，确保首字母大写：

SELECT initcap('john doe'); -- 结果：John Doe

2.2 字符串截取与填充

处理固定格式的字符串时，截取和填充特别有用：

-- 截取子串 SELECT substring('PostgreSQL' FROM 6 FOR 3); -- 结果：SQL -- 左右截取 SELECT left('2023-07-15', 4), right('2023-07-15', 2); -- 结果：2023, 15 -- 填充字符串 SELECT lpad('123', 5, '0'), rpad('123', 5, 'x'); -- 结果：00123, 123xx

实际案例：处理银行账号显示，只显示后四位：

SELECT rpad('*', length(account_number)-4, '*') || right(account_number, 4) FROM accounts;

2.3 高级字符串处理

PostgreSQL的正则表达式支持非常完善：

-- 正则匹配 SELECT regexp_matches('abc123def456', '\d+', 'g'); -- 结果：{123}, {456} -- 正则替换 SELECT regexp_replace('2023-07-15', '(\d{4})-(\d{2})-(\d{2})', '\2/\3/\1'); -- 结果：07/15/2023 -- 字符串分割 SELECT regexp_split_to_table('apple,orange,banana', ','); -- 结果：三行数据

实际案例：提取URL中的域名部分：

SELECT regexp_replace('https://www.example.com/path', '^https?://([^/]+).*$', '\1');

3. 数值计算函数应用

数值计算是数据分析的核心，PostgreSQL提供了丰富的数学函数。

3.1 基础数学运算

-- 四舍五入 SELECT round(3.14159, 2); -- 结果：3.14 -- 取整 SELECT ceil(3.14), floor(3.14); -- 结果：4, 3 -- 绝对值 SELECT abs(-10); -- 结果：10 -- 幂运算 SELECT power(2, 10); -- 结果：1024

实际案例：计算商品折扣价（打8折后四舍五入）：

SELECT round(price * 0.8, 2) FROM products;

3.2 统计与随机

-- 随机数 SELECT random(); -- 0到1之间的随机数 -- 取模 SELECT mod(10, 3); -- 结果：1 -- 平方根 SELECT sqrt(25); -- 结果：5

实际案例：生成6位随机验证码：

SELECT lpad(floor(random() * 1000000)::text, 6, '0');

3.3 高级数学函数

-- 对数 SELECT log(100); -- 以10为底，结果：2 -- 三角函数 SELECT sin(pi()/2); -- 结果：1 -- 阶乘 SELECT factorial(5); -- 结果：120

实际案例：计算两点间距离（经纬度）：

SELECT 6371 * acos( cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)) ) FROM locations;

4. 日期时间函数精讲

日期时间处理是业务系统中最容易出错的环节之一，PostgreSQL提供了全面的时间处理函数。

4.1 获取当前时间

SELECT current_date, current_time, current_timestamp; -- 结果：2023-07-15, 14:30:00.123456+08, 2023-07-15 14:30:00.123456+08 -- 带时区的当前时间 SELECT now(), transaction_timestamp();

实际案例：记录操作时间戳：

UPDATE orders SET updated_at = now() WHERE id = 1001;

4.2 时间计算与提取

-- 时间加减 SELECT now() + interval '1 day'; SELECT current_date - interval '1 month'; -- 提取时间部分 SELECT extract(year FROM now()); -- 当前年份 SELECT date_part('hour', current_time); -- 当前小时

实际案例：计算会员有效期：

SELECT user_id, created_at + interval '1 year' AS expire_date FROM memberships;

4.3 时间格式化

-- 时间转字符串 SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS'); -- 字符串转时间 SELECT to_date('20230715', 'YYYYMMDD'); SELECT to_timestamp('2023-07-15 14:30', 'YYYY-MM-DD HH24:MI');

实际案例：生成月度报表文件名：

SELECT 'report_' || to_char(current_date, 'YYYYMM') || '.csv';

5. 聚合函数深度解析

聚合函数是数据分析的利器，PostgreSQL除了标准聚合函数外，还支持窗口函数等高级特性。

5.1 基础聚合函数

-- 计数 SELECT count(*) FROM orders; -- 求和 SELECT sum(amount) FROM payments; -- 平均值 SELECT avg(rating) FROM product_reviews; -- 最大最小值 SELECT max(price), min(price) FROM products;

实际案例：计算订单统计：

SELECT count(*) as order_count, sum(amount) as total_amount, avg(amount) as avg_amount FROM orders WHERE created_at >= current_date - interval '30 days';

5.2 分组聚合

-- 按类别统计 SELECT category, count(*), avg(price) FROM products GROUP BY category; -- 多级分组 SELECT extract(year FROM created_at) as year, extract(month FROM created_at) as month, count(*) as order_count FROM orders GROUP BY year, month;

实际案例：计算每月用户留存率：

SELECT signup_month, count(*) as signups, count(filter(WHERE last_active >= signup_date + interval '30 days')) as retained_users FROM users GROUP BY signup_month;

5.3 窗口函数

-- 排名 SELECT product_id, sales, rank() OVER (ORDER BY sales DESC) FROM products; -- 移动平均 SELECT date, sales, avg(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM daily_sales;

实际案例：计算销售额累计占比：

SELECT product_id, sales, sum(sales) OVER () as total_sales, sales / sum(sales) OVER () as ratio FROM products;