SQL 别裁新解：PostgreSQL函数分类速查表

Author喜乐君 Publish

SQL 分类

1. 主流的函数分类方法及其局限

目前市面上主流的函数分类方法是按照函数引用的字段类型进行划分的。

  • 按引用数据类型分类：
    • 字符串函数（String Functions）：例如 LEFT 函数，因为它只能引用一个字符串（String）字段，然后保留左边位数。
    • 日期函数（Date Functions）：例如 DATEPART，它用于摘取日期的一部分（如月份），或计算两个日期之间的间隔（如 DATADATEDIFF）。
  • 局限性与补充分类：
    • 这种分类方式的缺点是比较混乱。有些函数难以粗放地放入特定的数据类型中，例如逻辑函数（Logical Functions），如 CASE WHEN 或 IF。它们可以引用字符串，也可以引用日期，因此不能限定引用的类型。
    • 当无法按引用类型分类时，会转而按照功能进行分类。
      • 例如，聚合函数（Aggregate Functions）（如 SUM、COUNT、MAX）就是按功能划分的一类。
      • 例如将 DATE（将字符串转换为日期）归类为类型转换函数。
PostgreSQL 函数速查表

PostgreSQL 函数分类速查表

SQL 别裁新解 · 基础工具箱
@喜乐君 2025 | 这里的每一行代码，都是通往数据真相的阶梯
字符串函数 (String) TEXT PROCESSING
CONCAT(a, b) 或 a || b PG
拼接字符串。
例: 'Hi' || ' ' || 'World'
SUBSTRING(str, start, len)
截取文本。
例: SUBSTRING('Data', 1, 2) -> ‘Da’
SPLIT_PART(str, del, n) PG常用
拆分取值，神器。
例: SPLIT_PART('a-b-c', '-', 2) -> ‘b’
UPPER() / LOWER()
转大写 / 转小写，常用于不区分大小写的比较。
TRIM() / REPLACE()
去空格 / 替换字符。清洗数据必备。
LENGTH(str)
返回字符串长度。
日期与时间函数 (Date & Time) TEMPORAL
CURRENT_DATE / NOW()
获取今天的日期 / 当前的时间戳。
DATE_TRUNC('part', date) BI核心
日期对齐/取整 (Day, Month, Year)。
例: DATE_TRUNC('month', now())
EXTRACT(part FROM date)
提取数字部分 (如 Year, Month)。
例: EXTRACT(YEAR FROM now())
AGE(timestamp, timestamp)
计算时间差。
例: AGE('2025-01-01', '2020-01-01')
date + interval '1 day' PG
日期加减运算。
例: now() - interval '1 week'
逻辑与空值 (Logic & Nulls) CONTROL FLOW
COALESCE(v1, v2, ...) 必会
返回第一个非空值。处理 NULL 的首选。
例: COALESCE(null, 0) -> 0
NULLIF(v1, v2)
如果 v1=v2 返回 NULL，否则返回 v1。
常用于避免除以0: a / NULLIF(b,0)
CASE WHEN condition THEN result ELSE end
强大的 IF-THEN 逻辑。BI 中创建”计算字段”的基础。
数值与类型转换 (Numeric & Cast) MATH & TYPE
ROUND(num, decimals)
四舍五入。
例: ROUND(3.14159, 2) -> 3.14
FLOOR() / CEIL()
向下取整 / 向上取整。
CAST(val AS type)
标准类型转换。
value::type PG极简
Postgres 特有转换符。
例: '100'::integer, order_date::text
高级分析：聚合与窗口 (Advanced Analysis) AGGREGATE & WINDOW
聚合函数 (多行变一行)
COUNT(*) / COUNT(col)
计数 (包含NULL / 不含NULL)。
SUM() / AVG()
求和 / 求平均值。
MIN() / MAX()
最小值 / 最大值。
STRING_AGG(col, del) PG
文本聚合。
例: STRING_AGG(name, ', ')
窗口函数 & JSON (上下文与结构)
ROW_NUMBER() OVER()
生成唯一行号。
RANK() OVER()
排名 (允许并列，名次跳跃)。
LAG(col) / LEAD(col)
访问前一行 / 后一行数据 (做同比环比)。
json_col ->> 'key' JSON
提取 JSON 字段为文本。
核心提示： PG 标记代表 Postgres 特有或常用语法； BI 标记代表在 BI 工具中逻辑高度一致的函数。
SQL 别裁新解 · 知识图谱

2. 个人推荐的分类思路：基于计算的性质

您个人推荐的分类思路是按照计算的性质，即函数计算所处的环境或上下文（Context），也就是其完成计算的详细级别来划分。

通过这种方式，您可以将函数分为两个最核心的类别：

A. 行级别计算（Row-Level Calculation）

行级别计算的特点是：计算是一行一行（Row by Row）完成的。计算结果绝不会跨行出现。

  • 定义和目的： 行级别计算处于数据表的明细级别，其核心作用是数据准备（Data Preparation），为后续的分析（聚合）提前做好准备。它一定发生在聚合之前。
  • 包含的类型：
    1. 字符串函数：所有的字符串函数都是行级别的。
    2. 日期函数：由于日期被视为特殊的字符串，日期函数也都是行级别的。
    3. 算数函数（或代数函数）：例如简单的乘法计算金额（amount = price * qty），也是一行一行的计算。

B. 聚合函数（Aggregate Functions）

聚合函数与行级别函数不同，其计算必然跨行。

  • 定义和特点： 聚合函数不是在原有表格中做出行上的改变，而是产生了全新的、抽象的内容。这种计算是从“有到无”（指明细表中没有的结果），例如计算不同客户的贷款金额总和，需要做跨行相加。
  • 包含的类型： 例如 SUM、COUNT、MAX，以及文本的聚合。

3. 通用计算（Expression）的定位

为了使函数分类体系更加清晰，您将既可以在明细行上做，也可以在聚合之后做的通用计算，独立出来放到了**表达式（Expression）**体系中。

  • 独立的原因： 这种区分避免了通用计算与核心函数类别的混淆。表达式被认为是 Function 的上一级。
  • 转移到 Expression 的功能：
    • 逻辑判断函数：例如 CASE WHEN 和 IF。
    • 窗口函数（Window Functions）：例如 RANK。

通过将这些不限定引用类型或涉及上下文判断的复杂计算归入“表达式”中（例如 Tableau 中的 Fixed LOD 和 PowerBI 中的 Calculate），您的核心函数分类就清晰地聚焦在**“在明细上完成”或“为聚合而处理”**的计算上。

总结来说，您的函数分类思路是从传统的面向数据类型，升级到面向计算上下文（或详细级别）的高级分析框架。

这就像将一个图书馆的图书不再仅仅按“纸张类型”分类（如硬皮书、软皮书），而是按“读者使用场景”分类（如在阅览室借阅的、可带回家的），从而提供了一个更贴合分析需求和计算环境的体系。

/

类似文章