PostgreSQL按客户端分组获取前3版本及对应语句统计

需求 #

按client分组，获取每个客户端的前3个版本（版本号降序），同时统计每个版本对应的sentence总数，输出结果包含客户端、版本号、语句总数、版本创建时间。

解决方案 #

用窗口函数ROW_NUMBER()给每个客户端的版本按版本号降序编号，再筛选序号≤3的记录即可。根据sentence的存储方式分两种情况：

情况1：需要统计每个版本的sentence数量（单条记录对应一个语句）

WITH ranked_versions AS (
    SELECT
        client,
        version_number,
        created_at,
        COUNT(sentence) AS sentence_total,
        ROW_NUMBER() OVER (PARTITION BY client ORDER BY version_number DESC) AS rn
    FROM "Model Versions"
    GROUP BY client, version_number, created_at
)
SELECT client, version_number, sentence_total, created_at
FROM ranked_versions
WHERE rn <= 3
ORDER BY client, rn;

情况2：表中已有sentence_count字段直接存储语句数

SELECT client, version_number, sentence_count AS sentence_total, created_at
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY client ORDER BY version_number DESC) AS rn
    FROM "Model Versions"
) sub_query
WHERE rn <= 3
ORDER BY client, rn;

关键说明 #

• PARTITION BY client：确保每个客户端的版本独立排序，不会跨客户端混排
• ORDER BY version_number DESC：让最新版本排在最前面，序号最小
• 如果版本号是带点的字符串格式（比如1.3.2），直接排序可能出错，改用数组转换实现正确的版本排序：

  ROW_NUMBER() OVER (PARTITION BY client ORDER BY string_to_array(version_number, '.')::int[] DESC) AS rn
  

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