企业官网建设流程全解析

搞数据库这些年，PostgreSQL 用得越多越觉得它的函数库是个宝库。很多业务刚开始跑的时候，一条简单 SQL 就够用；等数据量上来、逻辑变复杂，真正拉开效率差距的，往往就是你对函数熟不熟、用得巧不巧。这篇我就把自己日常写 SQL 时最常用、也最容易被忽略的 PostgreSQL 函数整理一遍，不只是列语法，更会讲清楚每个函数适合什么场景、有哪些坑、为什么这么写比那么写好。新手可以当索引照着查，老手也能当一次查漏补缺。

1. 聚合函数：COUNT 到底在数什么

聚合函数是 SQL 里最先接触的一批函数，但越简单的东西越容易踩细节坑。就拿 COUNT 来说，很多人在它身上翻过车。

1.1 COUNT 的三种写法，差别在哪

COUNT(*) 数的是行数，不管这行里有没有 NULL；COUNT(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_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 'user@example.com') AS pos, -- 返回 5，从 1 开始计 SUBSTRING('user@example.com' FROM 1 FOR 4) AS prefix, SPLIT_PART('user@example.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 JOIN，jsonb_array_elements(order_items)会为外层每一行展开一次，非常灵活。

局部更新 JSON 字段用jsonb_set：

UPDATE 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 转成 NULL，COALESCE再给它一个默认值 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 和 EXPLAIN

LATERAL不是函数，但它跟函数配合起来非常强大。它允许子查询引用外层查询的列，实现“对每一行执行一个子查询”。比如你要取每个用户近 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 的函数体系非常庞大，这篇覆盖的是日常业务里出现频次最高的那一批，足够应付绝大多数报表、数据清洗和后端查询场景。你在实际项目里踩过哪些函数的坑，或者觉得哪个函数特别好用，欢迎交流补充。