PostgreSQL：如何筛选最新的direction为'I'的连续数据行

没问题，我来帮你搞定这个查询需求！针对你给出的基础查询语句，我们可以通过两种实用的方式来获取最新的连续direction='I'的数据行（示例中限制最多4行）：

方法一：用窗口函数标记连续分组 #

这种方法适合处理更复杂的连续数据分组场景，能精准识别出最新的连续I行组：

WITH ordered_messages AS (
    SELECT 
        id, liked, markers, search_body, remote_bare_jid, direction,
        -- 计算当前行与上一行的ID差值，判断是否连续（前提是ID为自增主键）
        id - LAG(id) OVER (ORDER BY id DESC) AS id_diff
    FROM mam_message 
    WHERE user_id='20' AND remote_bare_jid = '5a95c47078f92c6337019521'
    ORDER BY id DESC
),
grouped_messages AS (
    SELECT 
        *,
        -- 标记连续行的分组：当ID差值不为1（或为第一行）时，开启新分组
        SUM(CASE WHEN id_diff = 1 OR id_diff IS NULL THEN 0 ELSE 1 END) OVER (ORDER BY id DESC) AS group_id
    FROM ordered_messages
)
SELECT id, liked, markers, search_body, remote_bare_jid, direction
FROM grouped_messages
WHERE group_id = 0 AND direction = 'I' -- 最新的连续组为group_id=0，筛选其中direction为'I'的行
ORDER BY id DESC
LIMIT 4;

逻辑拆解： #

1. ordered_messages：按ID降序排列目标数据，计算每行与前一行的ID差值，以此判断行是否连续。
2. grouped_messages：通过累加标记生成分组ID，最新的连续行会被分到group_id=0的组中。
3. 最后筛选该分组内direction='I'的行，取前4条结果。

方法二：定位最后一条非'I'数据简化查询 #

如果你的id是自增主键，这种方法更简洁高效，直接定位到最后一条非I的消息，然后取之后的所有I消息：

WITH last_non_incoming AS (
    SELECT id 
    FROM mam_message 
    WHERE user_id='20' AND remote_bare_jid = '5a95c47078f92c6337019521'
      AND direction != 'I'
    ORDER BY id DESC
    LIMIT 1
)
SELECT id, liked, markers, search_body, remote_bare_jid, direction
FROM mam_message 
WHERE user_id='20' AND remote_bare_jid = '5a95c47078f92c6337019521'
  AND direction = 'I'
  -- 如果不存在非'I'的消息，COALESCE会返回0，确保所有'I'消息都被选中
  AND id > COALESCE((SELECT id FROM last_non_incoming), 0)
ORDER BY id DESC
LIMIT 4;

逻辑拆解： #

1. last_non_incoming：找到目标用户和会话中最后一条不是direction='I'的消息ID。
2. 主查询筛选出所有ID大于该值（即更新的消息）且direction='I'的行，取前4条。
3. 用COALESCE处理没有非I消息的极端情况，确保所有I消息都能被查询到。

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