Postgres、BigQuery如何实现SQLite式单列GROUP BY分组效果？

在SQLite中执行语句 SELECT * FROM tbl GROUP BY col 时，会按col列分组，若存在col值相同的行，仅保留每组的最后一行。但相同语句在PostgreSQL或BigQuery中无法运行，会报错要求将所有选中列加入GROUP BY子句；若照此操作，分组将基于所有指定列，会出现col值重复的记录。

PostgreSQL实现方案 #

PostgreSQL不支持SQLite这种非标准GROUP BY语法，需借助窗口函数实现分组后保留每组最后一行的效果。核心逻辑是给每个分组内的行按排序规则编号，再筛选出每组的第一行（倒序排序即可对应原需求的"最后一行"）：

推荐写法（基于明确排序字段） #

如果表中有自增主键（如id）或时间戳字段（如create_time），用这些字段排序能精准对应"最后一行"的业务含义：

SELECT *
FROM (
    SELECT *,
           -- 按col分组，组内按id降序编号，最新/最后插入的行编号为1
           ROW_NUMBER() OVER (PARTITION BY col ORDER BY id DESC) AS rn
    FROM tbl
) t
WHERE rn = 1;

模拟SQLite默认行为（无明确排序字段） #

如果没有业务排序字段，可尝试用PostgreSQL内部的ctid（行位置标识符）模拟，但注意ctid会在数据更新、Vacuum操作后变化，仅适合无更新的静态表：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY col ORDER BY ctid DESC) AS rn
    FROM tbl
) t
WHERE rn = 1;

BigQuery实现方案 #

BigQuery同样依赖窗口函数实现，逻辑与PostgreSQL一致，还可利用* EXCEPT()语法简化字段选择：

推荐写法（基于明确排序字段） #

SELECT * EXCEPT(rn)
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY col ORDER BY id DESC) AS rn
    FROM `your-project.your-dataset.tbl`
) t
WHERE rn = 1;

* EXCEPT(rn)会自动排除窗口函数生成的编号列rn，无需手动列出所有业务字段。

模拟SQLite默认行为 #

如果没有明确排序字段，可尝试用BigQuery的_ROW_TIMESTAMP（仅适用于流插入的表）或基于表存储顺序排序，但更建议尽量找到业务上的排序依据，保证结果稳定。

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