多对多表按活动创建日期分组单词卡的SQL查询问题

你需要将profile_id=2的单词卡按照活动的创建时间进行区间分组，每个活动对应明确的时间范围：

• 活动1：wordcard.created_at ≤ 2019-01-19 2:12:05
• 活动2：2019-01-19 2:12:05 < wordcard.created_at ≤ 2019-01-19 2:14:22

你的原SQL存在两个核心问题：

1. 没有在关联条件中过滤pa.profile_id = 2，导致匹配到其他用户的活动
2. 关联条件pa.created_at < pwc.created_at会让一个单词卡匹配多个满足条件的活动，不符合“每个单词卡归到唯一对应区间”的需求

我们可以通过窗口函数LAG()为每个活动计算出上一个活动的创建时间，明确每个活动对应的时间区间后，再将单词卡与这些区间精准匹配：

WITH ranked_activities AS (
    SELECT 
        activity_id,
        profile_id,
        created_at AS activity_created_at,
        -- 第一个活动的上一个时间设为极小值，确保早于它的单词卡都能匹配
        LAG(created_at, 1, '1970-01-01 00:00:00') OVER (ORDER BY created_at) AS prev_activity_created_at
    FROM profile_activities
    WHERE profile_id = 2
    ORDER BY created_at
)
SELECT 
    pwc.wordcard_id,
    pwc.profile_id,
    pwc.created_at,
    ra.activity_id,
    ra.activity_created_at
FROM profile_wordcards pwc
JOIN ranked_activities ra 
    ON pwc.profile_id = ra.profile_id
    AND pwc.created_at > ra.prev_activity_created_at
    AND pwc.created_at <= ra.activity_created_at
WHERE pwc.profile_id = 2
ORDER BY ra.activity_id, pwc.created_at;

1. CTE ranked_activities：
   • 先筛选出profile_id=2的所有活动，按创建时间排序
   • 用LAG()函数获取每个活动的上一个活动创建时间，第一个活动的上一个时间设为1970-01-01 00:00:00，覆盖所有早于第一个活动的单词卡
2. JOIN逻辑：
   • 只匹配相同profile_id的记录
   • 让单词卡的创建时间严格落在当前活动的区间内：> 上一个活动时间且<= 当前活动时间
3. 排序：按活动ID和单词卡创建时间排序，和你期望的输出格式完全对齐

• 必须给profile_activities加上profile_id=2的过滤条件，避免混入其他用户的活动
• 不要用LEFT JOIN，因为我们需要每个单词卡匹配唯一的活动区间，INNER JOIN更合适；如果需要保留晚于所有活动的单词卡，可以调整为LEFT JOIN并处理NULL值

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