GREATEST / LEAST
GREATEST 与 LEAST 是一对"跨列取极值"的函数：前者在一组参数里挑出最大的，后者挑出最小的。它们常被误认为 max / min 聚合函数的简写，实际上作用维度完全不同——聚合函数跨行求极值，而 GREATEST / LEAST 跨同一行内的多个表达式求极值，二者不可互换。更需要注意的是：它们属于 PostgreSQL 的非标准扩展（SQL 标准里没有），并且采用NULL 传染规则——任一参数为 NULL，结果就是 NULL。本节从函数视角讲清它的类型统一规则、NULL 传播、替代写法，以及与聚合函数的边界。
与「COALESCE / NULLIF / GREATEST / LEAST」运算符章节的分工
COALESCE / NULLIF / GREATEST / LEAST 一章从表达式写法出发，重点讲"怎么写、写在 WHERE 里注意什么"；本节从函数视角出发，重点讲参数类型统一规则、NULL 传播的成因、忽略 NULL 的替代写法，以及它与 max / min 聚合函数的维度差异。两节互补。
前置知识
阅读本节前，建议先了解：COALESCE / NULLIF
基础概念
| 概念 | 术语 | 说明 |
|---|---|---|
| 跨列取大 | GREATEST | 返回参数列表中值最大的那个 |
| 跨列取小 | LEAST | 返回参数列表中值最小的那个 |
| 非标准扩展 | Non-Standard Extension | SQL 标准未定义，PostgreSQL/MySQL 等各自实现 |
| NULL 传染 | NULL Propagation | 任一参数为 NULL，结果即为 NULL |
| 类型统一 | Type Unification | 所有参数归约到公共类型，返回该类型 |
| 跨行聚合 | Aggregate | max / min 在整组行上求极值（与本节函数对立） |
| 夹取 | Clamp | 用 LEAST(GREATEST(x, lo), hi) 把值限制在区间内 |
二者的语义签名：
| 表达式 | 语义签名 | 返回类型 |
|---|---|---|
GREATEST | greatest(v1, v2, ...) | 所有参数的公共类型 |
LEAST | least(v1, v2, ...) | 所有参数的公共类型 |
最小示例：
SELECT
greatest(1, 5, 3) AS max_of_three, -- 5
least(1, 5, 3) AS min_of_three, -- 1
greatest(1.5, 2) AS mixed_numeric, -- 2.0
least('c', 'a', 'b') AS min_of_text; -- a max_of_three | min_of_three | mixed_numeric | min_of_text
--------------+--------------+---------------+-------------
5 | 1 | 2.0 | a
(1 row)语法与用法
GREATEST ( 值1, 值2 [, ...] )
LEAST ( 值1, 值2 [, ...] )两条硬规则：
- 参数个数不限，实际使用里通常是 2~4 个；参数少于 2 个没有意义；
- 所有参数必须可归约到同一类型，否则报错——这条与
COALESCE完全一致。
-- 标准形态
SELECT
greatest(a, b, c) AS newest_score,
least(start_date, end_date) AS earlier_date
FROM t;最小可运行示例，覆盖返回值边界：
SELECT
greatest(3, 3) AS ties, -- 3（相等时返回任一）
least(0, -1, NULL) AS with_null, -- NULL（NULL 传染）
greatest(-5, -10) AS negatives, -- -5
least('2024-01-01'::date, now()::date) AS earlier; ties | with_null | negatives | earlier
------+-----------+-----------+------------
3 | | -5 | 2024-01-01
(1 row)least(0, -1, NULL) 这一行是本节的核心陷阱：直觉上"最小值"应该是 -1，但因为有 NULL，结果变成了 NULL。
详细说明
非标准扩展的身份
GREATEST / LEAST 不是 SQL 标准的一部分，而是 PostgreSQL 从早期就提供的扩展。理解这一点有两个实际影响：
- 跨数据库移植时行为可能不同。多数数据库（MySQL、Oracle、SQLite）都实现了同名函数，但 NULL 处理策略存在差异（详见下文）；
- 不要在它们上面叠加"标准语义"的预期，比如以为它像
max一样会跳过 NULL。
-- PostgreSQL 中确实存在这两个"函数名"
SELECT proname, prokind
FROM pg_proc
WHERE proname IN ('greatest', 'least'); proname | prokind
----------+---------
greatest | f
least | f
(2 rows)COALESCE / NULLIF 在 pg_proc 里查不到（它们是解析节点），而 GREATEST / LEAST 能查到——这是它们"更像普通函数"的一个证据。但它们仍由解析器特殊处理，参数会先做类型统一。
NULL 传播规则（最重要）
任一参数为 NULL，结果即为 NULL。这条规则与 max / min 聚合函数"忽略 NULL"的行为完全相反，是引入线上静默错误的头号原因：
SELECT
greatest(10, NULL) AS gle_null, -- NULL
least(10, NULL) AS le_null, -- NULL
greatest(NULL, 1, 2, 3) AS greater_than, -- NULL
greatest(10, 20) AS no_null; -- 20 gle_null | le_null | greater_than | no_null
----------+---------+--------------+---------
| | | 20
(1 row)为什么是传染而不是跳过？因为 GREATEST 的语义定义在比较运算之上：它逐个用 > 比较，而 10 > NULL 的结果是 NULL（未知），无法据此确定"谁更大"，所以整体退化为 NULL。这与 COALESCE 完全不同——COALESCE 判断的是"是否为 NULL"这个布尔事实，不需要大小比较。
-- 证明它是基于比较的
SELECT (10 > NULL) AS cmp_result; -- NULL（未知），而非 false cmp_result
------------
(1 row)最常见的线上事故
用 greatest 从几个可能为 NULL 的列里取最大值做统计，结果整列变 NULL：
-- 错误：任一分店销量为 NULL（数据缺失），当天的最高销量就变成 NULL
SELECT biz_date, greatest(shop_a, shop_b, shop_c) AS max_sales
FROM daily_sales;正确做法是先兜底再取极值（见下一小节），或改写成忽略 NULL 的形式。
忽略 NULL 的替代写法
PostgreSQL 不支持 GREATEST(...) IGNORE NULLS 这种语法——这是 Oracle/Spark 等数据库的特性，在 PostgreSQL 里会直接报语法错误：
-- 错误！PostgreSQL 不支持 IGNORE NULLS
SELECT greatest(10, 20, NULL) IGNORE NULLS;ERROR: syntax error at or near "IGNORE"
LINE 1: SELECT greatest(10, 20, NULL) IGNORE NULLS;要"忽略 NULL 取极值"，有两条可行路线。
路线一：先 COALESCE 兜底——适合可用业务下限/上限替代缺失值的场景：
-- 把缺失值当成 0（销量最小值），再取最大
SELECT greatest(coalesce(shop_a, 0), coalesce(shop_b, 0), coalesce(shop_c, 0)) AS max_sales
FROM daily_sales;路线二：把参数装进数组，用聚合函数求极值——适合"真正想跳过 NULL"的场景，因为 max / min 聚合天然忽略 NULL：
-- 参数变数组后，聚合函数会跳过 NULL
SELECT (SELECT max(v) FROM unnest(ARRAY[10, 20, NULL]) AS v) AS max_ignoring_null; -- 20
SELECT (SELECT min(v) FROM unnest(ARRAY[10, 20, NULL]) AS v) AS min_ignoring_null; -- 10 max_ignoring_null
-------------------
20
(1 row)把这条模式封装成可复用函数，就能得到"真正忽略 NULL 的 GREATEST"：
CREATE OR REPLACE FUNCTION greatest_ignore_null(VARIADIC vals numeric[])
RETURNS numeric
LANGUAGE sql IMMUTABLE AS $$
SELECT max(v) FROM unnest(vals) AS v WHERE v IS NOT NULL;
$$;
SELECT greatest_ignore_null(10, NULL, 20, NULL) AS result; -- 20| 需求 | 推荐写法 |
|---|---|
| 缺失值有业务默认值 | greatest(coalesce(a, 0), coalesce(b, 0)) |
| 想彻底跳过 NULL | (SELECT max(v) FROM unnest(ARRAY[a,b,c]) v) |
| 频繁使用且要复用 | 封装为 greatest_ignore_null() 函数 |
| 参数都不为 NULL | 直接用 greatest(a, b, c) |
参数类型统一规则
与 COALESCE 完全一致：所有参数先归约到公共类型，返回值即该类型。
| 参数组合 | 推导结果 | 说明 |
|---|---|---|
greatest(1, 2.5) | numeric | int4 与 numeric 归约到 numeric |
greatest(1, 2) | integer | 同类型保持不变 |
greatest(1::smallint, 2) | integer | 归约到更宽的 integer |
greatest(1, 'abc') | 报错 | 'abc' 被解析为 integer 失败 |
greatest(1, true) | 报错 | 跨类型类别，无法归约 |
验证类型归约：
SELECT
greatest(1, 2.5) AS value,
pg_typeof(greatest(1, 2.5)) AS value_type,
pg_typeof(least(1, 2)) AS int_type,
pg_typeof(greatest('a', 'b')) AS text_type; value | value_type | int_type | text_type
-------+------------+----------+-----------
2.5 | numeric | integer | text
(1 row)跨类型类别同样直接报错：
-- 错误！integer 与 boolean 无法归约
SELECT greatest(1, true);ERROR: GREATEST types integer and boolean cannot be matched
LINE 1: SELECT greatest(1, true);规避办法同样是显式转型：
-- 正确：把冲突一侧显式转型
SELECT greatest(1::text, '0') AS to_text; -- 1编译期验证：日期与时间戳混合也会报错，需要先对齐类型：
-- 错误！date 与 timestamptz 不能直接归约
SELECT greatest('2024-01-01'::date, now());ERROR: GREATEST types date and timestamp with time zone cannot be matched
LINE 1: SELECT greatest('2024-01-01'::date, now());-- 正确：统一到 timestamptz
SELECT greatest('2024-01-01'::date::timestamptz, now()) AS newer;与 max / min 聚合函数的区别（必须分清）
这是本节最需要记牢的对照。GREATEST 是行内多列求极值，max 是跨行单列求极值：
| 维度 | GREATEST(a, b, c) | max(col) |
|---|---|---|
| 作用范围 | 同一行的多个表达式 | 一组行的同一列 |
| 是否聚合 | 否，逐行返回 | 是，整组返回一个值 |
| 参数 | 具体值/表达式，个数固定 | 列名 |
| NULL 处理 | 传染（结果为 NULL） | 忽略（跳过 NULL） |
| 能否用于 GROUP BY | 可作为分组表达式 | 自身就是聚合 |
| 空集行为 | 不适用 | 返回 NULL |
| 典型场景 | 多个分店取最高销量 | 所有分店合计里的最高销量 |
用一个表把差异摆出来：
CREATE TEMP TABLE shop_sales (shop text, amount numeric);
INSERT INTO shop_sales VALUES
('A店', 100),
('B店', 250),
('C店', NULL); -- C 店数据缺失
-- 聚合：跨行求最高销量，忽略 NULL → 250
SELECT max(amount) AS max_across_rows FROM shop_sales;
-- 行内：同一行的多列，若任一列为 NULL 则传染
SELECT shop, greatest(amount, 0) AS per_row FROM shop_sales ORDER BY shop; max_across_rows
-----------------
250
(1 row)
shop | per_row
------+---------
A店 | 100
B店 | 250
C店 |
(3 rows)把两个维度组合起来才是完整的业务语义（一行内先兜底取极值，再跨行聚合）：
-- 每个日期里，三个分店销量的最大值，再取这些最大值的平均
SELECT
biz_date,
avg(greatest(coalesce(shop_a, 0), coalesce(shop_b, 0), coalesce(shop_c, 0))) AS avg_daily_max
FROM daily_sales
GROUP BY biz_date
ORDER BY biz_date;夹取（Clamp）：把值限制在区间内
least(greatest(x, lo), hi) 是"把 x 限制在 [lo, hi] 区间"的固定写法，广泛用于进度百分比、评分、折扣等需要边界的场景：
夹取逻辑：
greatest(x, lo) → 保证不低于 lo（下界）
least(..., hi) → 保证不高于 hi（上界）SELECT
x,
least(greatest(x, 0), 100) AS clamped_pct
FROM (VALUES (-10), (0), (45.6), (100), (250)) AS t(x); x | clamped_pct
------+-------------
-10 | 0
0 | 0
45.6 | 45.6
100 | 100
250 | 100
(5 rows)夹取对 NULL 依然无效
若 x 为 NULL，greatest(NULL, 0) 传染为 NULL，整个夹取结果也是 NULL。要保证"缺失值也落在区间内"，必须加一层兜底：
SELECT least(greatest(coalesce(x, 0), 0), 100) AS clamped FROM t;取较早 / 较晚的时间
LEAST 取较早时间、GREATEST 取较晚时间，是审计、有效期、状态时间线里的高频用法：
-- 审计记录的实际生效时刻：取创建时间与最后更新时间的较早者（首次生效）
SELECT id, least(created_at, updated_at) AS first_effective
FROM audit_log;
-- 记录集的最后活动时刻：取 max 与 deleted_at 的较晚者
SELECT id, greatest(updated_at, coalesce(deleted_at, updated_at)) AS last_activity
FROM audit_log;时间字段可能为 NULL
updated_at 若允许为 NULL（从未更新过），least(created_at, updated_at) 会直接变 NULL。要么把列约束为 NOT NULL DEFAULT now()，要么在函数里先 coalesce。
非负兜底：greatest(x, 0) 的边界
用 greatest(x, 0) 把负数清零是很常见的写法，但它只处理负数，不处理 NULL：
SELECT
greatest(5, 0) AS positive, -- 5
greatest(-3, 0) AS negative, -- 0
greatest(NULL, 0) AS null_input; -- NULL（不是 0！） positive | negative | null_input
----------+----------+------------
5 | 0 |
(1 row)要同时处理负数和 NULL，必须组合使用：
SELECT greatest(coalesce(x, 0), 0) AS non_negative FROM t;表达式索引可用性
与 COALESCE 一样，只要参数都是不可变表达式，GREATEST / LEAST 就能用于表达式索引与生成列：
-- 实践中：把「实际生效日期」固化为生成列并建索引
ALTER TABLE contracts
ADD COLUMN effective_date date
GENERATED ALWAYS AS (least(start_date, end_date)) STORED;
CREATE INDEX idx_contracts_effective ON contracts (effective_date);不可变要求
索引/生成列表达式必须满足 IMMUTABLE。least(created_at, now()) 中的 now() 是 STABLE，会导致 CREATE INDEX 报 "functions in index expression must be marked IMMUTABLE"。时间相关的比较只适合放在查询里，不适合做索引表达式。
实战示例
场景：分店日报的极值统计 + 进度百分比夹取 + 多来源时间合并。
步骤 1：建表并造数据
DROP TABLE IF EXISTS daily_sales;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS contracts;
CREATE TABLE daily_sales (
biz_date date NOT NULL,
shop_a numeric,
shop_b numeric,
shop_c numeric
);
CREATE TABLE tasks (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
done integer NOT NULL DEFAULT 0,
total integer NOT NULL
);
CREATE TABLE contracts (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code text NOT NULL,
start_date date,
end_date date
);
INSERT INTO daily_sales VALUES
('2024-05-01', 100, 250, 180),
('2024-05-02', 120, NULL, 160),
('2024-05-03', -20, 300, 210);
INSERT INTO tasks (name, done, total) VALUES
('报表重构', 8, 10),
('索引优化', 12, 10),
('数据迁移', -3, 10);
INSERT INTO contracts (code, start_date, end_date) VALUES
('C-001', '2024-01-01', '2024-12-31'),
('C-002', '2024-06-01', NULL);CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 3
INSERT 0 2步骤 2：行内取极值（观察 NULL 传染）
SELECT
biz_date,
greatest(shop_a, shop_b, shop_c) AS raw_max,
least(shop_a, shop_b, shop_c) AS raw_min
FROM daily_sales
ORDER BY biz_date; biz_date | raw_max | raw_min
------------+---------+---------
2024-05-01 | 250 | 100
2024-05-02 | | 0
2024-05-03 | 300 | -20
(3 rows)5 月 2 日 B 店缺失，导致 raw_max 与 raw_min 全变 NULL——这就是传染。
步骤 3：忽略 NULL 的正确写法
-- 路线一：兜底
SELECT
biz_date,
greatest(coalesce(shop_a, 0), coalesce(shop_b, 0), coalesce(shop_c, 0)) AS max_default_zero
FROM daily_sales
ORDER BY biz_date;
-- 路线二：数组 + 聚合，真正跳过 NULL
SELECT
biz_date,
(SELECT max(v) FROM unnest(ARRAY[shop_a, shop_b, shop_c]) AS v) AS max_ignoring_null,
(SELECT min(v) FROM unnest(ARRAY[shop_a, shop_b, shop_c]) AS v) AS min_ignoring_null
FROM daily_sales
ORDER BY biz_date; biz_date | max_default_zero
------------+------------------
2024-05-01 | 250
2024-05-02 | 160
2024-05-03 | 300
(3 rows)
biz_date | max_ignoring_null | min_ignoring_null
------------+-------------------+-------------------
2024-05-01 | 250 | 100
2024-05-02 | 160 | 120
2024-05-03 | 300 | -20
(3 rows)两条路线的结果可能不同：路线一把缺失当 0，5 月 2 日的结果是 160；路线二跳过 NULL，结果也是 160；但若缺失值出现在最小值位置，两条路线会给出不同的 min。
步骤 4：进度百分比夹取
SELECT
name,
done,
total,
round(done::numeric / NULLIF(total, 0) * 100, 1) AS raw_pct,
least(greatest(round(done::numeric / NULLIF(total, 0) * 100, 1), 0), 100) AS clamped_pct
FROM tasks
ORDER BY id; name | done | total | raw_pct | clamped_pct
----------+------+-------+---------+-------------
报表重构 | 8 | 10 | 80.0 | 80.0
索引优化 | 12 | 10 | 120.0 | 100.0
数据迁移 | -3 | 10 | -30.0 | 0.0
(3 rows)索引优化 的原始值是 120%（数据异常），被夹到 100%；数据迁移 的 -30% 被夹到 0%，避免了下游图表出界。
步骤 5：多来源时间合并
-- 合同实际生效日：早于开始日则取开始日（防脏数据）
SELECT
code,
start_date,
end_date,
least(coalesce(start_date, end_date), coalesce(end_date, start_date)) AS effective_date
FROM contracts
ORDER BY id; code | start_date | end_date | effective_date
-------+------------+------------+----------------
C-001 | 2024-01-01 | 2024-12-31 | 2024-01-01
C-002 | 2024-06-01 | | 2024-06-01
(1 row)C-002 的 end_date 为 NULL，coalesce 兜底后仍能得到合法结果。
步骤 6：与聚合组合（行内极值 → 跨行聚合）
SELECT
avg(greatest(coalesce(shop_a, 0), coalesce(shop_b, 0), coalesce(shop_c, 0))) AS avg_daily_max,
max(greatest(coalesce(shop_a, 0), coalesce(shop_b, 0), coalesce(shop_c, 0))) AS best_daily_max
FROM daily_sales; avg_daily_max | best_daily_max
--------------------+----------------
236.66666666666667 | 300
(1 row)这里 greatest 是行内表达式，avg / max 是跨行聚合——两个维度协同才能得到"每日最高销量的平均值/峰值"。
步骤 7：清理
DROP TABLE daily_sales;
DROP TABLE tasks;
DROP TABLE contracts;注意事项
- NULL 传染是最容易踩的坑。任一参数为 NULL，结果就是 NULL，与
max/min聚合"忽略 NULL"的行为相反：
SELECT greatest(10, NULL); -- NULL，不是 10PostgreSQL 不支持
IGNORE NULLS，写出来会报语法错误。要跳过 NULL 只能用coalesce兜底或unnest + max。参数类型必须能归约，跨类别或跨不相容类型直接报错：
-- 错误！
SELECT greatest(1, true); -- GREATEST types integer and boolean cannot be matched
SELECT greatest(now(), '2024-01-01'::date); -- GREATEST types date and timestamp with time zone cannot be matched不要把它当
max/min聚合的别名。前者是行内多列、逐行返回；后者是跨行单列、整组返回。greatest(x, 0)不能处理 NULL，只处理负数。要同时兜底必须写greatest(coalesce(x, 0), 0)。夹取
least(greatest(x, lo), hi)对 NULL 无效，缺省值需要额外兜底。least(created_at, updated_at)在updated_at为 NULL 时会变 NULL。要么约束列NOT NULL，要么先coalesce。它属于非标准扩展，跨数据库迁移时 NULL 行为可能不同，不要假定所有数据库都一致。
表达式索引/生成列要求参数不可变。
least(col, now())无法建索引。参数个数少于 2 没有意义；
greatest(1)在 PostgreSQL 里可执行但语义空洞，不要这样写。
最佳实践
先想清楚"NULL 意味着什么"：是"缺失"（该跳过）还是"0"（该兜底）。前者用
unnest + max，后者用coalesce：sql-- 缺失按 0 处理 SELECT greatest(coalesce(a, 0), coalesce(b, 0)); -- 真正跳过缺失 SELECT (SELECT max(v) FROM unnest(ARRAY[a, b]) AS v);夹取统一写成
least(greatest(coalesce(x, 0), 0), 100)，把 NULL、越下界、越上界三种异常一次处理干净。别用嵌套
CASE替代greatest/least，多参数场景下前者冗长且易漏分支：sql-- 不推荐 CASE WHEN a > b THEN a ELSE b END -- 推荐 greatest(a, b)需要频繁"忽略 NULL 取极值"时，封装为函数，一次定义处处复用：
sqlCREATE OR REPLACE FUNCTION greatest_ignore_null(VARIADIC vals numeric[]) RETURNS numeric LANGUAGE sql IMMUTABLE AS $$ SELECT max(v) FROM unnest(vals) AS v WHERE v IS NOT NULL; $$;时间字段尽量加
NOT NULL DEFAULT now()，从源头消除greatest/least的 NULL 传染，比在查询里到处coalesce更省心。严格区分行内极值与跨行聚合。要"每行的最大值"用
greatest，要"整组的最大值"用max，两者组合才表达完整业务语义。参数类型显式对齐，尤其是日期/时间戳混用时提前转型，别让解析器去猜：
sqlSELECT greatest(start_date::timestamptz, now());在
CHECK约束里表达区间限制时可以用least/greatest，但要注意 NULL：CHECK (score = least(greatest(score, 0), 100))中若score为 NULL，表达式为 NULL，约束会通过（CHECK把 NULL 视为通过）。
下一节
继续学习：CASE WHEN（函数式用法）