【PostgreSQL数据分析实战：从数据清洗到可视化全流程】附录-A. PostgreSQL常用函数速查表
👉 点击关注不迷路
👉 点击关注不迷路
👉 点击关注不迷路
文章大纲
- PostgreSQL常用函数速查表：从数据清洗到分析的全场景工具集
- 引言
- 一、字符串处理函数
- 1.1 基础操作函数
- 1.2 模式匹配函数（正则表达式）
- 二、数值计算函数
- 2.1 基础运算函数
- 2.2 统计相关函数
- 三、日期与时间函数
- 3.1 基础日期操作
- 3.2 日期算术运算
- 四、聚合函数与窗口函数
- 4.1 聚合函数
- 4.2 窗口函数
- 五、类型转换函数
- 六、条件判断函数
- 结语
我将围绕《PostgreSQL数据分析实战》附录-A 的内容，以常用函数分类的形式，详细介绍各函数的描述、语法、示例及返回值，通过表格和具体数据让内容更清晰易懂，为读者呈现一份实用的速查表博客。
PostgreSQL常用函数速查表：从数据清洗到分析的全场景工具集
引言
在PostgreSQL数据分析场景中，熟练掌握内置函数是提升数据处理效率的关键。
- 本文基于
《PostgreSQL数据分析实战：从数据清洗到可视化全流程》附录A，整理了字符串处理、数值计算、日期操作、聚合统计等核心功能模块的常用函数，结合具体业务场景示例，帮助读者快速掌握函数应用技巧。 所有示例基于以下测试表结构：
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),salary NUMERIC(10,2),hiredate DATE,department VARCHAR(30)
);-- 示例数据
INSERT INTO employees VALUES
(1, 'Alice Smith', 85000.00, '2020-01-15', 'Engineering'),
(2, 'Bob Johnson', 72000.50, '2021-03-20', 'Marketing'),
(3, 'Charlie Brown', 68000.75, '2022-05-25', 'Sales');
一、字符串处理函数
字符串函数是数据清洗阶段的核心工具，常用于处理不规范文本、提取关键信息或拼接输出内容。
1.1 基础操作函数
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| CONCAT | 拼接字符串 | CONCAT(str1, str2, …) | CONCAT(‘Hello’, ’ ', ‘World’) | “Hello World” |
| SUBSTRING | 提取子字符串 | SUBSTRING(str FROM start [FOR length]) | SUBSTRING(‘PostgreSQL’, 3, 4) | “stgr” |
| TRIM | 去除首尾指定字符（默认空格） | TRIM([LEADING/TRAILING/BOTH] [chars] FROM str) | TRIM(’ data ') | “data” |
| REPLACE | 替换指定子串 | REPLACE(str, old_str, new_str) | REPLACE(‘a_b_c’, ‘_’, ‘-’) | “a-b-c” |
UPPER/LOWER | 转换大小写 | UPPER(str)/LOWER(str) | UPPER(‘hello’) | “HELLO” |
1.2 模式匹配函数（正则表达式）
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
REGEXP_MATCHES | 返回匹配正则的所有子串 | REGEXP_MATCHES(str, pattern, [flags]) | REGEXP_MATCHES(‘a1b2c3’, ‘\d’) | {1,2,3}（数组） |
REGEXP_REPLACE | 使用正则替换子串 | REGEXP_REPLACE(str, pattern, replacement, [flags]) | REGEXP_REPLACE(‘abc123’, ‘\d+’, ‘NUM’) | “abcNUM” |
SPLIT_PART | 按分隔符拆分字符串并返回指定部分 | SPLIT_PART(str, delimiter, part) | SPLIT_PART(‘a,b,c’, ‘,’, 2) | “b” |
- 业务场景示例：清洗员工姓名格式
-- 去除姓名前后空格并统一首字母大写
UPDATE employees SET name = INITCAP(TRIM(name));
-- 提取姓名中的姓氏（假设姓名格式为“名 姓”）
SELECT name, SPLIT_PART(name, ' ', 2) AS last_name FROM employees;
二、数值计算函数
数值函数用于数据转换、统计计算和数学运算，支持整数、浮点数和精确数值类型。
2.1 基础运算函数
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| ROUND | 四舍五入 | ROUND(num [, decimal_places]) | ROUND(123.456, 2) | 123.46 |
CEIL/FLOOR | 向上/向下取整 | CEIL(num)/FLOOR(num) | CEIL(3.14) | 4 |
| ABS | 绝对值 | ABS(num) | ABS(-100) | 100 |
| MOD | 取模运算 | MOD(num, divisor) | MOD(7, 3) | 1 |
| POWER | 幂运算 | POWER(base, exponent) | POWER(2, 3) | 8 |
2.2 统计相关函数
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| AVG | 平均值 | AVG(column) | AVG(salary) | 75000.42（假设） |
| SUM | 求和 | SUM(column) | SUM(salary) | 225001.25 |
| COUNT | 计数 | COUNT([DISTINCT] column) | COUNT(DISTINCT department) | 3 |
业务场景示例：计算员工薪资等级
-- 将薪资四舍五入到千位并分类
SELECT name, salary, CASE WHEN ROUND(salary / 1000) >= 80 THEN '高薪'WHEN ROUND(salary / 1000) >= 70 THEN '中等'ELSE '基础'END AS salary_level
FROM employees;
三、日期与时间函数
日期函数在时间序列分析、数据过滤和趋势计算中不可或缺，支持DATE、TIME、TIMESTAMP类型。
3.1 基础日期操作
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
CURRENT_DATE | 获取当前日期 | CURRENT_DATE | CURRENT_DATE | 2025-05-14（当前日期） |
| AGE | 计算时间间隔 | AGE(end_date, start_date) | AGE(hiredate, ‘2020-01-01’) | 间隔年数/月数/天数 |
| EXTRACT | 提取日期部分 | EXTRACT(part FROM date) | EXTRACT(YEAR FROM hiredate) | 2020 |
DATE_PART | 等价于EXTRACT | DATE_PART(part, date) | DATE_PART(‘month’, hiredate) | 1 |
DATE_TRUNC | 截断日期到指定精度 | DATE_TRUNC(part, date) | DATE_TRUNC(‘year’, hiredate) | 2020-01-01 |
3.2 日期算术运算
| 操作符 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| + | 日期加天数/时间间隔 | date + INTERVAL 'n day' | hiredate + INTERVAL ‘1 year’ | 2021-01-15 |
| - | 日期减天数/计算时间差 | date - INTERVAL 'n day' | CURRENT_DATE - hiredate | 间隔天数 |
BETWEEN | 判断日期是否在区间内 | date BETWEEN start AND end | hiredate BETWEEN ‘2020-01-01’ AND ‘2022-12-31’ | TRUE/FALSE |
- 业务场景示例：计算员工工龄
SELECT name,EXTRACT(YEAR FROM AGE(hiredate)) AS years_of_service, -- 完整工龄（年）(EXTRACT(YEAR FROM CURRENT_DATE) * 12 + EXTRACT(MONTH FROM CURRENT_DATE)) -- 当前总月数- (EXTRACT(YEAR FROM hiredate) * 12 + EXTRACT(MONTH FROM hiredate)) -- 入职总月数AS months_worked -- 累计完整月份数
FROM employees;
四、聚合函数与窗口函数
聚合函数用于分组统计，窗口函数则在保留行级别数据的同时进行分组计算，常用于排名、累计值等场景。
4.1 聚合函数
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| COUNT | 统计行数 | COUNT(*) / COUNT(column) | COUNT(*) FROM employees | 3 |
| SUM | 求和 | SUM(salary) | SUM(salary) | 225001.25 |
| AVG | 平均值 | AVG(salary) | AVG(salary) | 75000.42 |
| MAX/MIN | 最大值/最小值 | MAX(salary)/MIN(salary) | MAX(salary) | 85000.00 |
4.2 窗口函数
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| ROW_NUMBER() | 行号排序（无并列） | ROW_NUMBER() OVER (ORDER BY salary DESC) | 按薪资降序排名 | 1,2,3 |
RANK() | 排名（并列同号，跳过后续） | RANK() OVER (ORDER BY salary DESC) | 薪资排名（可能有并列） | 1,2,2 |
DENSE_RANK() | 密集排名（并列同号，不跳过） | DENSE_RANK() OVER (ORDER BY salary DESC) | 连续排名 | 1,2,2 |
SUM() OVER() | 分组累计和 | SUM(salary) OVER (PARTITION BY department ORDER BY hiredate) | 部门内按入职时间累计薪资 | 累计值序列 |
- 业务场景示例：查询各部门薪资排名
SELECT name, department, salary,RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
五、类型转换函数
类型转换函数用于处理不同数据类型间的兼容问题，确保计算和存储的正确性。
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| CAST | 显式类型转换 | CAST(expr AS type) | CAST(‘123’ AS INT) | 123 |
:: | 类型转换操作符 | expr::type | ‘2020-01-01’::DATE | 日期类型值 |
| TO_CHAR | 日期/数值转字符串 | TO_CHAR(date, format) | TO_CHAR(hiredate, ‘YYYY-MM-DD’) | “2020-01-15” |
| TO_NUMBER | 字符串转数值 | TO_NUMBER(str, format) | TO_NUMBER(‘12,345’, ‘99G999’) | 12345 |
- 业务场景示例：格式化薪资输出
SELECT name, TO_CHAR(salary, '$999,999.00') AS formatted_salary
FROM employees;
六、条件判断函数
条件函数用于根据不同逻辑分支返回对应结果，是数据清洗和业务逻辑实现的核心。
| 函数名 | 描述 | 语法 | 示例 | 返回值 |
|---|---|---|---|---|
| CASE WHEN | 多条件分支判断 | CASE WHEN condition THEN result [ELSE default] END | CASE WHEN salary > 80000 THEN ‘Senior’ ELSE ‘Junior’ END | “Senior"或"Junior” |
| COALESCE | 返回第一个非空值 | COALESCE(expr1, expr2, …) | COALESCE(NULL, ‘Default’) | “Default” |
NULLIF | 相等则返回NULL，否则返回原值 | NULLIF(expr1, expr2) | NULLIF(10, 10) | NULL |
- 业务场景示例：处理缺失部门数据
UPDATE employees SET department = COALESCE(department, 'Unknown');
- 快速查询各函数帮助文档
-- 输入 \h 函数名 查看帮助（以 CONCAT 为例）：
\h CONCAT-- \df 是 psql 的元命令，用于列出数据库中所有函数（可结合通配符过滤）。
\df CONCAT -- 查看所有名为 CONCAT 的函数-- 快速查询各函数帮助文档
-- 示例：查询CONCAT函数用法
-- 查询 CONCAT 函数的元数据
SELECT proname AS 函数名,pg_get_function_arguments(oid) AS 参数,pg_typeof(prorettype) AS 返回类型,prosrc AS 函数定义（部分）
FROM pg_proc
WHERE proname = 'concat';
结语
本文整理的PostgreSQL常用函数覆盖了
数据清洗、转换、分析和可视化的全流程场景。
- 建议读者通过实际数据集练习函数组合使用，
例如结合正则表达式清洗不规范文本，再通过窗口函数进行分组排名，最终用TO_CHAR函数格式化输出结果。- 后续文章将深入探讨如何利用这些函数构建复杂数据分析模型，敬请关注！
以上博客全面梳理了PostgreSQL常用函数。
- 你若觉得某些函数介绍不够详细，或想增加其他类型函数，欢迎告诉我。