PostgreSQL无需分组，统计对话文件夹移动ID区间行数

我正在处理跟踪对话内文件夹移动的数据集，现有：

• folder_movement表：记录文件夹移动详情，包含conversation_history_id（唯一操作ID）、conversation_id（对话ID）、updated_at（操作时间）、folder_from/folder_to（移动前后文件夹）
• folder_cum表：已提取出每个交互（1次交互=进出CE Triage各1次）的起始ID conversation_history_id_fp与结束ID conversation_history_id_endp

需求：统计folder_movement表中，按conversation_id和updated_at排序后，位于每个交互的起始ID与结束ID之间的文件夹移动行数。核心挑战是单个对话可包含多次独立交互，不能直接按conversation_id分组。

附简化表结构与期望结果：

folder_movement表（示例数据） #

期望interactions_number表结果 #

注：对话A3的交互未完成（无从文件夹移回CE Triage的操作），后续会通过其他表过滤这类记录。

核心思路是先给每个对话内的操作按时间排序生成连续序号，再通过序号区间统计每个交互的行数。以下是基于CTE的SQL实现：

WITH ranked_movements AS (
    -- 给每个对话内的操作按updated_at倒序排序（匹配示例中交互的起始/结束逻辑）
    SELECT 
        conversation_history_id,
        conversation_id,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY updated_at DESC) AS rn
    FROM folder_movement
),
interactions_number AS (
    -- 关联folder_cum与排序后的操作记录，计算区间行数
    SELECT 
        fc.conversation_history_id_endp,
        fc.conversation_history_id_fp,
        fc.conversation_id,
        ABS(rn_fp.rn - rn_endp.rn) + 1 AS mov_count
    FROM folder_cum fc
    JOIN ranked_movements rn_fp 
        ON fc.conversation_id = rn_fp.conversation_id 
        AND fc.conversation_history_id_fp = rn_fp.conversation_history_id
    JOIN ranked_movements rn_endp 
        ON fc.conversation_id = rn_endp.conversation_id 
        AND fc.conversation_history_id_endp = rn_endp.conversation_history_id
)
SELECT * FROM interactions_number;

逻辑说明 #

1. ranked_movements CTE：按conversation_id分区，对每个对话下的操作按updated_at倒序生成连续序号rn——示例中最新的移回CE Triage操作（如A1的01）是交互起始，对应最小序号，最早的从CE Triage移出操作（如A1的03）是交互结束，对应最大序号。
2. interactions_number CTE：将folder_cum中的每个交互的起始、结束ID分别关联到排序后的记录，获取对应的序号，通过两个序号的差值加1得到区间内的操作行数（比如A1的起始ID01对应rn=1，结束ID03对应rn=3，3-1+1=3，与期望结果一致）。
3. 最终输出结果完全匹配期望的interactions_number表结构。

注意事项 #

• 排序方向可调整：如果业务中交互的起始是最早操作、结束是最晚操作，需将ORDER BY updated_at DESC改为ORDER BY updated_at ASC。
• 确保folder_cum中的ID在folder_movement中存在，避免关联丢失数据。
• 未完成的交互（如A3）会被正常统计，后续可通过过滤folder_to = 'CE Triage'的记录排除这类数据。

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