PostgreSQL中基于分组首尾项的时长计算实现方法咨询

问题说明 #

现有表结构包含字段detected_time、estimated_stay_time、is_first_group、is_last_group、floor_code，原始数据如下：

规则与计算逻辑 #

• 数据已按连续楼层分组，被其他楼层打断则重新分组；is_first_group标识分组首项，is_last_group标识分组尾项，两者均为true时该数据同时是分组首尾项。
• 总时长计算公式：FG - LG + ETM
  • FG：当前分组首项的detected_time值
  • LG：上一个分组尾项的detected_time值
  • ETM：当前分组尾项的estimated_stay_time值
• 预期输出：仅在分组尾项的total字段显示计算结果，其余行total为null，且字段名映射为first_group、last_group、floor，数字格式带千分位逗号。

解决方案 #

使用PostgreSQL窗口函数和CTE（公共表表达式）实现逻辑，假设表名为floor_stays，SQL语句如下：

WITH group_info AS (
    SELECT
        *,
        -- 生成分组ID：每遇到分组首项则ID递增
        SUM(CASE WHEN is_first_group THEN 1 ELSE 0 END) OVER (ORDER BY detected_time DESC) AS group_id,
        -- 获取当前分组的首项detected_time（FG）
        MAX(CASE WHEN is_first_group THEN detected_time END) OVER (PARTITION BY SUM(CASE WHEN is_first_group THEN 1 ELSE 0 END) OVER (ORDER BY detected_time DESC)) AS fg,
        -- 获取当前分组的尾项estimated_stay_time（ETM）
        MAX(CASE WHEN is_last_group THEN estimated_stay_time END) OVER (PARTITION BY SUM(CASE WHEN is_first_group THEN 1 ELSE 0 END) OVER (ORDER BY detected_time DESC)) AS etm
    FROM floor_stays
),
prev_group_last AS (
    SELECT
        group_id,
        -- 获取上一个分组的尾项detected_time（LG），第一个分组无前置则返回null
        LAG(MAX(CASE WHEN is_last_group THEN detected_time END)) OVER (ORDER BY group_id) AS lg
    FROM group_info
    GROUP BY group_id
)
SELECT
    gi.detected_time,
    gi.estimated_stay_time,
    gi.is_first_group AS first_group,
    gi.is_last_group AS last_group,
    gi.floor_code AS floor,
    -- 仅分组尾项计算total，格式化带千分位逗号
    CASE
        WHEN gi.is_last_group THEN
            TO_CHAR(gi.fg - COALESCE(pgl.lg, gi.fg) + gi.etm, 'FM999,999,999')
        ELSE NULL
    END AS total
FROM group_info gi
JOIN prev_group_last pgl ON gi.group_id = pgl.group_id
ORDER BY gi.detected_time DESC;

逻辑解释 #

1. group_info 阶段：

   • 生成group_id：通过累加is_first_group的标记，按detected_time降序排序，将同组连续数据归为同一ID。
   • 提取fg和etm：在每个分组内，分别筛选出首项的detected_time和尾项的estimated_stay_time。

2. prev_group_last 阶段：

   • 按分组ID聚合，使用LAG()函数获取上一个分组的尾项detected_time（LG），第一个分组无前置分组时返回null。

3. 主查询阶段：

   • 映射字段名到预期输出格式。
   • 用CASE判断仅在分组尾项计算total，通过COALESCE处理第一个分组无LG的情况（替换为FG，确保计算为FG - FG + ETM = ETM），并用TO_CHAR格式化数字为带千分位的格式。
   • 按detected_time降序排序，保持原始数据顺序。

执行上述SQL后，将得到与预期一致的输出结果。

内容的提问来源于stack exchange，提问作者Nguyễn Yor