SQLite中按通话时间范围关联Event表并取最后一条事件

现有表结构 #

Calls表 #

index_call  start_datetime      nextcall_datetime
--------------------------------------------------
1           2024-01-01 08:00AM  2024-01-01 08:24AM
2           2024-01-01 08:24AM  2024-01-01 08:35AM
3           2024-01-01 08:35AM  2024-01-01 08:45AM

Event表 #

id_event    id_datetime     
------------------------------ 
1           2024-01-01 08:04AM
2           2024-01-01 08:12AM
3           2024-01-01 08:25AM
4           2024-01-01 08:30AM

需求 #

找出每个通话时间段内发生的事件，若同一时间段存在多个事件，仅保留最后一条。

原语句问题分析 #

你写的SQL产生笛卡尔积，主要有两个问题：

1. LEFT JOIN未指定ON关联条件，导致两张表全量匹配
2. 字段名错误，Event表没有CreatedDate字段，应该用id_datetime

原错误语句：

SELECT 
    calls.index_call,
    calls.start_datetime,
    calls.nextcall_datetime,
    event.id_datetime
FROM 
    calls calls 
LEFT JOIN 
    event event 
WHERE 
    events.CreatedDate BETWEEN calls.start_datetime AND calls.nextcall_datetime

正确SQL实现 #

方法一：子查询筛选最晚事件 #

SELECT
    c.index_call,
    c.start_datetime,
    c.nextcall_datetime,
    e.id_datetime
FROM calls c
LEFT JOIN (
    -- 找出每个通话时间段对应的最晚事件时间
    SELECT
        MAX(e.id_datetime) AS last_event_time,
        (SELECT index_call FROM calls WHERE e.id_datetime BETWEEN start_datetime AND nextcall_datetime) AS call_id
    FROM event e
    GROUP BY call_id
) AS last_events ON c.index_call = last_events.call_id
LEFT JOIN event e ON e.id_datetime = last_events.last_event_time
ORDER BY c.index_call;

方法二：窗口函数实现（SQLite 3.25+支持） #

如果你的SQLite版本支持窗口函数，可以用更简洁的写法：

WITH event_with_call AS (
    SELECT
        e.id_datetime,
        -- 为每个事件匹配所属的通话记录ID
        (SELECT index_call FROM calls WHERE e.id_datetime BETWEEN start_datetime AND nextcall_datetime) AS call_id
    FROM event e
),
ranked_events AS (
    SELECT
        call_id,
        id_datetime,
        -- 按通话分组，事件时间倒序排名，取第1条就是最晚事件
        ROW_NUMBER() OVER (PARTITION BY call_id ORDER BY id_datetime DESC) AS rn
    FROM event_with_call
    WHERE call_id IS NOT NULL
)
SELECT
    c.index_call,
    c.start_datetime,
    c.nextcall_datetime,
    r.id_datetime
FROM calls c
LEFT JOIN ranked_events r ON c.index_call = r.call_id AND r.rn = 1
ORDER BY c.index_call;

执行结果 #

index_call  start_datetime      nextcall_datetime    id_datetime
-----------------------------------------------------------------------
1           2024-01-01 08:00AM  2024-01-01 08:24AM   2024-01-01 08:12AM
2           2024-01-01 08:24AM  2024-01-01 08:35AM   2024-01-01 08:30AM
3           2024-01-01 08:35AM  2024-01-01 08:45AM

内容的提问来源于stack exchange，提问作者Luis Carmona Martinez