搞数据库这些年PostgreSQL 用得越多越觉得它的函数库是个宝库。很多业务刚开始跑的时候一条简单 SQL 就够用等数据量上来、逻辑变复杂真正拉开效率差距的往往就是你对函数熟不熟、用得巧不巧。这篇我就把自己日常写 SQL 时最常用、也最容易被忽略的 PostgreSQL 函数整理一遍不只是列语法更会讲清楚每个函数适合什么场景、有哪些坑、为什么这么写比那么写好。新手可以当索引照着查老手也能当一次查漏补缺。1. 聚合函数COUNT 到底在数什么聚合函数是 SQL 里最先接触的一批函数但越简单的东西越容易踩细节坑。就拿 COUNT 来说很多人在它身上翻过车。1.1 COUNT 的三种写法差别在哪COUNT(*) 数的是行数不管这行里有没有 NULLCOUNT(column) 数的是该列非 NULL 的值的个数COUNT(DISTINCT column) 数的是该列去重后非 NULL 的值的个数。看下面这组数据-- 假设有一张订单表 orders其中 discount 列存在 NULL SELECT COUNT(*) AS total_rows, -- 全部订单行数 COUNT(discount) AS has_discount, -- 折扣不为空的订单数 COUNT(DISTINCT customer_id) AS unique_customers -- 去重客户数 FROM orders;同一个表三个结果可能完全不一样。尤其是 COUNT(discount) 和 COUNT(*) 的差异本质上反映的是数据质量——有没有该填没填的字段。我排查数据缺失时经常先用这条 SQL 看比例比一个个翻表快得多。还有一个很容易被忽略的写法COUNT(DISTINCT (a, b))。在 PostgreSQL 里如果你想统计“字段 a 和字段 b 的组合”有多少种可以写两列的组合不需要像其他数据库那样拼字符串。这个特性在做多维度去重统计时非常实用。提示COUNT 不会计算 NULL但 SUM、AVG 同样会忽略 NULL只是它们的结果可能因此变成 NULL。所以聚合结果里见到 NULL 别慌先想想源数据是不是有 NULL 传进来了。1.2 聚合与 GROUP BY 的配合及 HAVING单表聚合只是热身实际业务里绝大多数是 GROUP BY 分组后聚合。这里有一个新手必踩的坑SELECT 里出现的非聚合列必须出现在 GROUP BY 里否则 PostgreSQL 会直接报错。这个设计其实是在保护你避免你无意中拿到一个“随机行”。举一个真实业务场景统计每个用户的订单总额、订单数和最近下单时间。SELECT customer_id, SUM(amount) AS total_amount, COUNT(*) AS order_count, MAX(created_at) AS last_order_time FROM orders WHERE status paid GROUP BY customer_id HAVING COUNT(*) 5;字段说明SUM(amount)该用户已支付订单的总金额COUNT(*)已支付订单数MAX(created_at)最近一次下单时间HAVING COUNT(*) 5只保留订单数不少于 5 的用户等于在分组结果上再做一次过滤WHERE 和 HAVING 的分工要理解清楚WHERE 先过滤掉不需要的行再执行分组和聚合HAVING 是在分组聚合之后筛组。这个顺序直接影响查询性能和数据正确性。尽量把能提前过滤的条件都放到 WHERE 里让进入聚合的数据量越小越好。2. 窗口函数排名、偏移与累计聚合函数的问题是它把多行压成一行丢失了其他行的明细。窗口函数则不同它保留所有行同时把某一组范围内的计算结果附加到每一行上。做排行榜、计算同比环比、求累计值窗口函数是不可替代的主角。2.1 三种排名函数选哪个ROW_NUMBER()、RANK()、DENSE_RANK()看起来都叫“排名”行为却完全不同。我做了个表一眼就能看出区别分数ROW_NUMBERRANKDENSE_RANK9911198222983229744396554ROW_NUMBER()是纯粹的行号就算分数相同也会强制分出先后适合“只要前 N 条”的场景。RANK()遇到相同分数会跳过名次出现 1、2、2、4 这种“留空”的情况适合竞赛排名。DENSE_RANK()不跳号同名次后直接顺延适合“按档位看数据”。实际使用时要特别注意ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)的常见用法分组后又按时间或分数排序再加一个外层查询筛选rn 1就能取“每组最新一条”或“每组第一名”。这是实现分组 Top-N 最标准的写法。-- 取每个客户最近一次下单的完整记录 WITH ranked AS ( SELECT orders.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn FROM orders ) SELECT * FROM ranked WHERE rn 1;用 CTE 包一层再筛主要是为了让代码可读性更强。优化器通常会把它优化得很干净不会比子查询慢多少。2.2 LAG/LEAD 和累加计算排名之外窗口函数另一大用途是“跨行访问”。LAG()取上一行的值LEAD()取下一行的值这对算环比、对比上一次行为非常有用。比如统计每个用户上一次和下一次订单的间隔时间SELECT customer_id, created_at, LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_order_time, LEAD(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS next_order_time, created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS gap FROM orders;这里LAG(created_at)会在每个用户的第一行返回 NULL因为你没有上一行。NULL 是窗口函数里的“边界信号”做计算时记得用COALESCE处理。累计求和也是高频需求。普通SUM()配 GROUP BY 是分组汇总但SUM(amount) OVER (ORDER BY created_at)不加 PARTITION BY 时就是按时间顺序不断累加的“运行总和”常用于分析收入曲线、库存流水等。SELECT created_at::date AS day, SUM(amount) AS daily_amount, SUM(SUM(amount)) OVER (ORDER BY created_at::date) AS cumulative_amount FROM orders WHERE status paid GROUP BY created_at::date;注意这里有个嵌套聚合SUM(amount)先按天汇总外层SUM(SUM(amount))再对每天的汇总结果做累加。窗口函数在聚合之后执行所以可以拿聚合结果继续开窗这是理解窗口函数执行顺序的关键。提示窗口函数有一个通用限制——不能直接写在 WHERE 里。因为 WHERE 在窗口计算之前执行此时窗口结果还不存在。想按排名过滤必须包一层子查询或 CTE就像前面 ROW_NUMBER 的例子那样。3. 日期时间函数时区、格式化与区间日期时间处理是业务 SQL 里绕不开的话题也是 PostgreSQL 相比很多数据库更讲究的地方。它内置了完整的日期时间类型和丰富的运算能力但正是因为类型体系复杂才更容易踩坑。3.1 先搞懂 timestamp 和 timestamptz 的区别这是 PostgreSQL 里最经典、也最容易被忽视的日期类型问题。timestamp不带时区它存的就是字面时间值比如“2024-06-01 12:00:00”timestamptz带时区内部存储的是 UTC 时间显示时会根据当前会话的时区自动转换。如果业务涉及跨时区的用户行为分析强烈建议统一使用timestamptz。否则你记录的用户下单时间在不同时区下读取出来会差好几个小时。我曾经处理过一个线上事故前后端和数据库各用各的时区认知最后统计的凌晨峰值完全错位查了一整天才发现是类型用错了。日常取当前时间常见的几个函数SELECT now() AS session_now, -- 当前事务开始时的时间timestamptz CURRENT_TIMESTAMP AS session_ts, -- 同 now() CURRENT_DATE AS today, -- 当前日期date 类型 CURRENT_TIME AS session_time; -- 当前时间time 类型now()在同一个事务里多次调用返回的是事务开始的时间点不是每次调用的实时时间。如果确实需要“每次调用都取最新系统时间”用clock_timestamp()。默认写代码时用now()就够了这条语义能避免同一事务内前后取值不一致的问题。时区转换的核心函数是AT TIME ZONE。它的行为要特别记牢SELECT now() AT TIME ZONE UTC AS utc_naive, -- timestamptz 转成 UTC 的墙钟时间 now() AT TIME ZONE Asia/Shanghai AS sh_naive, -- 转成指定时区的墙钟时间 2024-06-01 12:00:00::timestamp AT TIME ZONE Asia/Shanghai AS from_naive;timestamptz AT TIME ZONE xxx得到的是不带时区的timestamp代表该时区下的墙上时间反过来timestamp AT TIME ZONE xxx得到的是timestamptz表示把“墙钟时间”当作该时区的本地时间再转成绝对时刻。这两个方向完全相反我见过不止一个人在这里搞混然后数据整体偏了好几个小时。3.2 格式化与日期运算的日常写法to_char()是最常见的日期格式化函数但也隐藏着不少“习惯性错误”。比如月份和分钟都是MM和MI的区别SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 24 小时制 SELECT to_char(now(), YYYY-mm-DD); -- 错误mm 会被当成分钟 SELECT to_char(now(), Month); -- 英文全名月份mm在模板里表示分钟不是月份。月份要用MM。这个错误在拼YYYY-mm-DD时不会立即报错因为分钟存在时不会崩但结果会非常诡异。建议统一大写格式串避免靠记忆猜测。日期运算在 PostgreSQL 里非常直观直接做加减法就行SELECT CURRENT_DATE 1 AS tomorrow, CURRENT_DATE - INTERVAL 1 day AS yesterday, now() INTERVAL 3 hours AS three_hours_later, DATE_TRUNC(month, now()) AS start_of_this_month, DATE_TRUNC(week, now()) AS start_of_this_week;DATE_TRUNC(month, ...)会把日期截断到当月 1 号零点是月报统计最基础的工具。配合generate_series()还能生成一整段时间内的连续日期序列SELECT generate_series( 2024-01-01::date, 2024-01-07::date, INTERVAL 1 day ) AS day;这个函数特别适合做“缺失日期补零”。比如你统计每天的订单量如果某天没有订单LEFT JOIN 一张日期序列表再配合COALESCE(SUM(amount), 0)就能把空档补成 0而不是跳行。4. 字符串函数拼接、切分与正则字符串处理在数据清洗场景里占大头。PostgreSQL 的字符串函数相当丰富但很多人在||和CONCAT之间都没想到它们对 NULL 的处理竟然不同。4.1 拼接字符串||和 concat 怎么选||运算符在拼接时如果任意一侧是 NULL整个结果就是 NULL。很多人第一次写first_name || last_name时只要中间有一个 NULL最后整段姓名都消失。而CONCAT函数则聪明得多它会把 NULL 当作空字符串处理。SELECT Hello || NULL AS a, -- 结果是 NULL CONCAT(Hello, NULL) AS b; -- 结果是 Hello单纯看这个例子似乎CONCAT更好。但有些场景你恰恰需要“NULL 传播”比如拼地址时某个字段缺失整段地址就不应该输出。这时||正好符合预期。建议明确拼接的字段都非空或希望保留部分结果用CONCAT希望整段拼接遇到 NULL 就放弃用||。字符串定位和截取也比较常用SELECT POSITION( IN userexample.com) AS pos, -- 返回 5从 1 开始计 SUBSTRING(userexample.com FROM 1 FOR 4) AS prefix, SPLIT_PART(userexample.com, , 2) AS domain, -- 取第 2 段结果是 example.com LEFT(PostgreSQL, 6) AS left_part, RIGHT(PostgreSQL, 3) AS right_part;SPLIT_PART按分隔符切分并取指定段处理 CSV 字段、提取邮箱域名、解析 URL 参数都很顺手。注意它是从左往右数段段索引超过实际段数时返回空字符串不会报错。4.2 正则函数与常见清洗场景PostgreSQL 自带的正则能力在清洗脏数据时几乎是神器。最常用的三个SELECT REGEXP_REPLACE(订单号: AB12345, [^0-9], , g) AS cleaned, -- 去掉所有非数字 REGEXP_MATCHES(apple, banana; cherry, ([a-z]), g) AS words, SUBSTRING(订单号: AB12345 FROM [A-Z]{2}[0-9]{5}) AS order_no;REGEXP_REPLACE的第四个参数g表示全局替换。不加它默认只替换第一个匹配项这个细节非常容易漏。REGEXP_MATCHES返回的是一个集合每个匹配项是一行记录。如果你想提取所有匹配项并用逗号拼起来需要用ARRAY()把它转成数组再用array_to_string拼接。提示正则是强工具但也很昂贵。在几千万行的表上直接跑REGEXP_REPLACE做清洗相当于全表扫描加逐行正则编译。如果正则模式固定可以先看看能否用普通字符串函数代替不能代替的尽量把数据范围先用 WHERE 缩到足够小。5. JSON 与 JSONB 函数从取数到改数现在业务表里存 JSON 字段太常见了。PostgreSQL 的 JSON 支持在关系型数据库里属于第一梯队尤其是jsonb类型。但很多人只会用-和-取值遇到嵌套结构就不知道怎么拆了。5.1 先理解 json 和 jsonb 的定位json是文本的精确映射保留原始格式、插入顺序和重复键存储有冗余不能建索引jsonb是二进制存储会解析并规范化 JSON去掉重复键排序键存储更紧凑支持 GIN 索引。日常业务几乎无脑选jsonb。你需要关注的不是“保留 JSON 原文”这种极少数需求而是查询性能和功能丰富度。jsonb支持、?、?|等运算符可以直接判断“是否包含某个键”“某个键是否存在”这些在json类型下是没有的。5.2 取路径字段、拆数组、改局部值取值的核心区别是-返回 JSON 类型-返回文本类型。比如SELECT {name: 张三, tags: [vip, new], address: {city: 北京}}::jsonb AS doc ) t, doc-name AS name_text, -- 张三text 类型 doc-tags AS tags_json, -- [vip, new]jsonb 类型 doc-tags-0 AS first_tag, -- vip doc-address-city AS city; -- 北京字段不存在时-返回 NULL不会报错。这个特性让取值逻辑很安全但也要注意你用这个值去 JOIN 或过滤时NULL 会被忽略容易造成数据静默缺失。拆 jsonb 数组是另一个高频操作。jsonb_array_elements()把数组展开成多行SELECT item-product_id AS product_id, item-quantity AS quantity FROM orders, jsonb_array_elements(order_items) AS item WHERE order_id 1024;这里用了隐式的 LATERAL JOINjsonb_array_elements(order_items)会为外层每一行展开一次非常灵活。局部更新 JSON 字段用jsonb_setUPDATE orders SET attribute jsonb_set(attribute, {status}, shipped, true) WHERE order_id 1024;注意jsonb_set的第三个参数要传一个合法的 JSON 字符串shipped里的双引号代表它是 JSON 字符串不是裸文本。第四个参数是create_missing设成 true 表示路径不存在时也创建适合给新字段赋初始值。提示jsonb字段建了 GIN 索引后这类操作才能跑得高效。没有索引的jsonb查询就是全表扫描加逐行解析数据量一大就非常难受。建索引时建议用USING GIN (attribute jsonb_path_ops)大多数等值判断场景比默认 gin 索引更省空间、更快。6. 条件逻辑与类型转换NULL 不背锅条件逻辑是写 SQL 的底层能力。很多人习惯在应用层做逻辑判断导致 SQL 里 80% 都是简单查询。其实把条件逻辑下沉到 SQL 里不仅减少数据传输还能利用数据库的计算能力。6.1 COALESCE 和 NULLIF 的对称用法COALESCE是最常用的 NULL 兜底函数返回第一个非 NULL 参数。比如SELECT COALESCE(nickname, username, 匿名用户) AS display_name FROM users;NULLIF(a, b)则相反当a b时返回 NULL否则返回a。它和COALESCE形成一组很好的对称操作。例如用户表中age 0表示“未填写”你想把它当作 NULL 处理SELECT COALESCE(NULLIF(age, 0), 18) AS actual_age FROM users;这里NULLIF(age, 0)先把 0 转成 NULLCOALESCE再给它一个默认值 18。这种“先归一化再赋默认值”的写法比CASE WHEN age 0 THEN 18 ELSE age END简洁不少。NULLIF还有两个妙用防止除零amount / NULLIF(total, 0)当 total 为 0 时返回 NULL避免报错聚合占比COUNT(NULLIF(paid, true))可以统计非 true 的行数6.2 类型转换的坑与安全写法PostgreSQL 的类型转换有好几种写法CAST(expr AS type)、expr::type、以及部分场景下直接用字符串赋值。最直观的是::写法但容易忽略它的严格性。SELECT 123::INTEGER AS int_ok, 12.5::NUMERIC AS numeric_ok, abc::INTEGER; -- 报错invalid input syntax字符串转数字失败是常态数据清洗时尤其常见——原始数据里混着“N/A”、“-”等非数字字符。不建议直接::INTEGER而是先用正则或REGEXP_REPLACE清洗或者用CASE WHEN做合法性判断。另一个常见坑是隐式转换的行为。比如SELECT * FROM orders WHERE created_at 2024-06-01;这条 SQL 不会报错但created_at是timestamptz时字符串2024-06-01会被自动转成完整时间戳2024-06-01 00:00:00而这一天的所有订单只有在零点整那条才会被匹配。想查一整天应该写成SELECT * FROM orders WHERE created_at 2024-06-01::timestamptz AND created_at 2024-06-02::timestamptz;这个左闭右开区间是日期范围匹配的标准写法既能命中当天所有记录又能用上索引。7. 那些容易忽略的细节与性能习惯函数用得对不代表用得快。函数在 SELECT 里好用但在 WHERE 里如果包裹了索引列索引就可能失效。这一部分是实操中积累出的“函数使用习惯”很大程度上决定线上 SQL 能不能扛住压力。7.1 函数包裹索引列的问题假设你在created_at上建了索引却写SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;DATE(created_at)相当于把每一行的created_at都先转换再比较优化器无法直接走普通 B-tree 索引只能全表扫描后逐行计算。同样的查询改成范围条件SELECT * FROM orders WHERE created_at 2024-06-01::timestamptz AND created_at 2024-06-02::timestamptz;就能完美命中索引。这是性能优化的最基本经验不要用函数包住索引列改成对常量的函数计算或者直接用范围条件。如果你确实需要经常按DATE(created_at)查询也可以建表达式索引CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));这样即便 SQL 里继续写DATE(created_at) ...也能走索引。但要权衡索引会占空间、拖慢写入不是万不得已不建议这么做。7.2 小函数大用处LATERAL 和 EXPLAINLATERAL不是函数但它跟函数配合起来非常强大。它允许子查询引用外层查询的列实现“对每一行执行一个子查询”。比如你要取每个用户近 3 笔订单SELECT u.user_id, o.order_id, o.amount FROM users u LEFT JOIN LATERAL ( SELECT order_id, amount FROM orders WHERE user_id u.user_id ORDER BY created_at DESC LIMIT 3 ) o ON true;这个写法比 ROW_NUMBER 子查询更直观尤其是在每一行只取少量数据、且对应索引良好时性能常常更优。LEFT JOIN LATERAL ... ON true保证没订单的用户也会出现只是订单字段是 NULL。写复杂 SQL 时EXPLAIN ANALYZE是终审法官。它不只是看 SQL 是否走索引还会告诉你每一步的实际耗时和行数估算误差。我给自己定的规矩是任何涉及函数或 JOIN 重组的复杂 SQL 上线前至少跑一遍EXPLAIN ANALYZE重点看Seq Scan出现在哪、actual rows和estimated rows差距是不是超过一个数量级。如果差距大通常意味着统计信息过期或者函数写法阻挡了优化器。EXPLAIN ANALYZE SELECT ... FROM orders WHERE created_at 2024-06-01::timestamptz AND created_at 2024-06-02::timestamptz;结果里能看到实际耗时和循环次数能直观对比两种写法之间的性能差别。很多时候一个看似“差不多”的写法在百万行数据上就是 10 倍以上的差距。最后再分享一个我自己的习惯写 SQL 时先把“想拿什么数据”写清楚再想“用哪个函数最自然”最后才考虑“怎么避开性能坑”。顺序反了代码会越来越歪。PostgreSQL 的函数体系非常庞大这篇覆盖的是日常业务里出现频次最高的那一批足够应付绝大多数报表、数据清洗和后端查询场景。你在实际项目里踩过哪些函数的坑或者觉得哪个函数特别好用欢迎交流补充。