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 Doe2.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.12345608, 2023-07-15 14:30:00.12345608 -- 带时区的当前时间 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;