You need to enable JavaScript to run this app. PostgreSQL SELECT语句基于exits空值生成自定义递增group_id的实现
优惠活动
大模型
产品
解决方案
定价
更多

PostgreSQL SELECT语句基于exits空值生成自定义递增group_id的实现

实现方案

这个需求属于典型的连续序列分组场景,借助PostgreSQL原生的窗口函数即可实现,不需要复杂的多层嵌套查询,可直接在12.4版本运行的SQL语句如下:

SELECT
  id,
  -- 计算group_id:统计当前行之前(不含自身)所有exits为NULL的累计数量,加1即为分组编号
  COALESCE(
    SUM(CASE WHEN exits IS NULL THEN 1 ELSE 0 END) 
      OVER (
        ORDER BY id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
      ),
    0
  ) + 1 AS group_id,
  name,
  age,
  enters,
  exits
FROM info
ORDER BY id;
逻辑拆解(适配新手理解)
  • 窗口排序规则:OVER (ORDER BY id) 保证所有行严格按照id升序计算,和示例中的行顺序完全一致,避免数据顺序错乱导致分组错误。
  • 统计范围边界:ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 定义了窗口的计算范围——逐行计算时,只从表的第一行统计到当前行的上一行,不包含当前行本身。这个边界完全匹配分组触发规则:
    • exits为NULL的行本身仍归属当前group
    • 这行之后的所有行,会因为上一行触发了计数+1,自动归属到新的group,直到碰到下一个exits为NULL的行
  • 计数规则:CASE WHEN exits IS NULL THEN 1 ELSE 0 END 逐行判断,碰到exits为NULL就计1,非空就计0,配合SUM()窗口函数得到累计的触发次数。
  • 空值适配:COALESCE(..., 0) 处理第一行的特殊情况——第一行没有上一行,窗口计算结果为NULL,将其转换为0后加1,即可得到初始分组编号1。
结果验证

使用你提供的示例数据逐行计算,最终返回结果和预期完全一致:

id, group_id, name, age, enters, exits
1, 1, orange, 10, null, 8
2, 1, orange, 8, 3, 5
3, 1, orange, 4, 9, null
4, 2, orange, 11, null, 5
5, 2, orange, 3, 3, null
6, 3, lemon, 9, 1, 2
自定义调整说明
  • 如果需要按name字段拆分独立分组(即相同name的记录单独计数group_id,不同name的分组从1重新开始),只需要在窗口子句中增加分区规则PARTITION BY name,修改后的窗口部分为:
    OVER (
      PARTITION BY name
      ORDER BY id 
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    )
    
  • 上述实现完全兼容PostgreSQL 12.4的语法特性,无需额外安装扩展。

内容的提问来源于stack exchange,提问作者The Welsh Dragon

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.15 04:06:02