基于时间戳关联并聚合数据集的SQL实现方案咨询

你的需求核心是：基于主表Table_1的时间节点，划分同一用户的时间区间，聚合关联表Table_2中落在对应区间内的动作数据并拼接成字符串。以下是具体实现方案：

核心思路 #

1. 给Table_1的每条记录标记出同一用户的上一条记录时间戳，以此确定当前记录对应的动作统计区间（上一次主表事件到当前主表事件之间）。
2. 通过左关联将Table_2的动作数据匹配到对应区间，最后用字符串聚合函数拼接动作。

PostgreSQL 实现代码 #

WITH table1_with_prev_timestamp AS (
    SELECT 
        user_id,
        change,
        timestamp,
        -- 获取同一用户上一条记录的时间戳，第一条记录返回NULL
        LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_timestamp
    FROM Table_1
)
SELECT 
    t1.user_id,
    t1.change,
    t1.timestamp,
    -- 聚合符合时间区间的action，无数据则返回NULL
    STRING_AGG(t2.action, ', ') AS actions_since_last_change
FROM table1_with_prev_timestamp t1
LEFT JOIN Table_2 t2 
    ON t1.user_id = t2.user_id
    -- 动作时间需落在上一次主表事件之后、当前主表事件之前
    AND t2.action_timestamp > t1.prev_timestamp
    AND t2.action_timestamp < t1.timestamp
GROUP BY t1.user_id, t1.change, t1.timestamp, t1.prev_timestamp
ORDER BY t1.user_id, t1.timestamp;

关键逻辑说明 #

• LAG窗口函数：按用户分组、时间排序，为每条主表记录生成上一条记录的时间戳，这是划分统计区间的核心依据。
• LEFT JOIN：确保主表所有记录都被保留，即使没有匹配的动作数据时返回NULL，完全符合你的目标表要求。
• 时间筛选条件：精准匹配动作发生在两次主表事件之间的场景，避免时间边界的重复统计。
• STRING_AGG：直接将符合条件的动作拼接成逗号分隔的字符串，无匹配数据时自动返回NULL。

其他SQL方言适配（以SQL Server为例） #

如果使用SQL Server 2017及以上版本，同样支持STRING_AGG，语法与PostgreSQL基本一致。若为旧版本，可通过STUFF+FOR XML PATH实现字符串拼接：

WITH table1_with_prev_timestamp AS (
    SELECT 
        user_id,
        change,
        timestamp,
        LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_timestamp
    FROM Table_1
)
SELECT 
    t1.user_id,
    t1.change,
    t1.timestamp,
    actions_since_last_change = STUFF(
        (SELECT ', ' + t2.action 
         FROM Table_2 t2 
         WHERE t2.user_id = t1.user_id
           AND t2.action_timestamp > t1.prev_timestamp
           AND t2.action_timestamp < t1.timestamp
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    )
FROM table1_with_prev_timestamp t1
ORDER BY t1.user_id, t1.timestamp;

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