PostgreSQL查询修正：按Edition标记Minecraft唯一最新版本

背景 #

我正在构建一个Minecraft内容查阅Web应用，需要在PostgreSQL的versions表中，为Bedrock（'b'）和Java（'j'）两个Edition分别标记对应记录是否为该Edition的最新版本。

现有表结构及初始化数据 #

CREATE TYPE edition AS ENUM (
    'b',
    'j'
);

CREATE TABLE IF NOT EXISTS versions (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    edition edition NOT NULL,
    major integer NOT NULL,
    minor integer NOT NULL,
    patch integer NOT NULL,
    cycle decimal GENERATED ALWAYS AS (
        CAST(
            (CAST(major AS text) || '.' || CAST(minor AS text)) AS decimal
        )
    ) STORED
);

INSERT INTO versions
    (edition, major, minor, patch)
VALUES
    ('b', 1, 16, 0),
    ('b', 1, 17, 0),
    ('b', 1, 18, 0),
    ('b', 1, 19, 0),
    ('j', 1, 16, 0),
    ('j', 1, 17, 0),
    ('j', 1, 18, 0),
    ('j', 1, 19, 0)
;

错误尝试的查询 #

我尝试用以下SELECT查询生成is_latest_bedrock和is_latest_java字段，期望每个字段仅出现一次true：

SELECT
    *,
    (
        edition = 'b'
        AND GREATEST(major) = major
        AND GREATEST(minor) = minor
        AND GREATEST(patch) = patch
    ) AS is_latest_bedrock,
    (
        edition = 'j'
        AND GREATEST(major) = major
        AND GREATEST(minor) = minor
        AND GREATEST(patch) = patch
    ) AS is_latest_java
FROM versions
ORDER BY edition, major, minor, patch;

错误结果 #

实际执行后，同一Edition下的所有记录都被标记为最新版本：

期望结果 #

每个Edition仅一条最新版本记录被标记为true：

解决方案 #

问题出在GREATEST()函数的使用上——未指定分组时，它只会返回当前行对应字段的值，导致每一行都满足等于自身的条件。以下两种方法可以实现按Edition标记最新版本：

方法一：使用窗口函数ROW_NUMBER() #

SELECT
    v.*,
    (edition = 'b' AND rn = 1) AS is_latest_bedrock,
    (edition = 'j' AND rn = 1) AS is_latest_java
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY edition
            ORDER BY major DESC, minor DESC, patch DESC
        ) AS rn
    FROM versions
) v
ORDER BY v.edition, v.major, v.minor, v.patch;

通过PARTITION BY edition按版本组分区，ORDER BY major DESC, minor DESC, patch DESC按版本号从高到低排序，每个分区中第一行即为最新版本，标记rn=1，外层再判断是否对应Edition并设置标记字段。

方法二：使用子查询获取每个Edition的最新版本 #

SELECT
    v.*,
    (v.edition = 'b' AND (v.major, v.minor, v.patch) = (
        SELECT major, minor, patch
        FROM versions
        WHERE edition = 'b'
        ORDER BY major DESC, minor DESC, patch DESC
        LIMIT 1
    )) AS is_latest_bedrock,
    (v.edition = 'j' AND (v.major, v.minor, v.patch) = (
        SELECT major, minor, patch
        FROM versions
        WHERE edition = 'j'
        ORDER BY major DESC, minor DESC, patch DESC
        LIMIT 1
    )) AS is_latest_java
FROM versions v
ORDER BY v.edition, v.major, v.minor, v.patch;

通过子查询分别获取Bedrock和Java的最新版本号组合，再与主表每条记录对比，匹配成功的标记为true。

两种方法均可满足需求，窗口函数的性能更优，适合数据量较大的场景。

内容的提问来源于stack exchange，提问作者ravinggenius